Hi I need to create some sql which will tell us how our application is being used at each client site. I created some sql statements to generate the necessary data, but I am not sure about how to label the data and send it out to a report. I've pasted the sql below. Thanks for any assistance.

--How many schedules in the last 90 days?
select count() from schedules where sched_dt between today() - 90 and today()
--How many active employees?
select count(
) from employee where emp_status_id in ('A')
--How many non-deleted users?
select count() from users where deleted <> 'T'
--How many users have logged in during the last 90 days?
select count(
) from users where prev_login_dt BETWEEN today() - 90 AND today();
--How many self-scheduling users?
select count() from users where user_category = '3';
--How many self-scheduling users have logged in within the past 90 days?
select count(
) from users where user_category = '3' and prev_login_dt BETWEEN today() - 90 AND today();
--Which modules are installed?
select descr, installed from if_entities where installed = 'T';
--Which interfaces are installed?
SELECT * FROM if_entities WHERE installed = 'T' AND descr not in ('SIS','EDUCATION','VIEW-ONLY') and substr(descr,1,7) <> 'SURGERY' AND substr(descr,1,3) <> 'SMM' AND substr(descr,1,3) <> 'HL7' and Upper(trim(right(descr, 6))) <> 'MODULE'

asked 27 Sep '13, 19:06

dejstone's gravatar image

dejstone
959405069
accept rate: 0%


Put an OUTPUT TO 'filespec' APPEND after each SELECT, and run the script via dbisql in batch mode.

permanent link

answered 28 Sep '13, 08:31

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

If dbisql isn't an option, you could also use UNLOAD SELECT ... TO CLIENT FILE ... APPEND ON although it takes a bit of work to get that going in a client app (option settings, callbacks, etc).

(29 Sep '13, 08:56) John Smirnios
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90

question asked: 27 Sep '13, 19:06

question was seen: 2,434 times

last updated: 29 Sep '13, 08:56