May 31, 2016 at 3:22 pm
Hi There,
I hope someone can help me resolve this issue.
I am working on query which requires following as a result,
Office,
Subprogram,
ServiceType,
Total # Of Clients whose appointment date fall between given range of date and whose appointment status is "Done",
Total # Of Clients whose start date is within 5 days from appointment date
I wrote following code to get Office, SubProgram, ServiceType and Total # Of Clients whose appointment date fall between given range of date and whose appointment status is "Done"
SELECT
fa.new_sitename AS Office,
fs.new_subprogramname As Subprogram,
Fsrv.name AS Servicetype,
Count(fa.new_clientidname) As Total#OfClients
FROM
Firstappointment fa
join
FirstServiceAppointment fs
on
fa.new_appointment id=fs.new_appointment lookup
join
FirstService Fsrv
on
fa.new_service=Fsrv.serviceid
Where
fs.new_visittypename like 'First'
AND fs.statuscodename like 'done'
AND fa.createdon BETWEEN '05/18/2016' AND '05/30/2016'
group by fa.new_sitename, fs.new_subprogramname,
Fsrv.name
and wrote following code to get Office, SubProgram, ServiceType and Total # Of Clients whose start date is within 5 days from appointment date
SELECT
count(fa.new_clientidname) as "Total # of Clients receiving FV within 5 days"
,fa.new_sitename AS Office
, fs.new_subprogramname As Subprogram
, Fsrv.name AS Servicetype
FROM
Firstappointment fa
join
FirstServiceAppointment fs
on
fa.new_appointmentid=fs.new_appointmentlookup
join
FirstService Fsrv
on
fa.new_service=Fsrv.serviceid
Where
fs.new_visittypename like 'first'
AND fs.statuscodename like 'done'
AND fa.createdon BETWEEN '05/18/2016' AND '05/30/2016'
AND DATEDIFF(DAY,cast(fa.createdon AS Date),cast(fs.scheduledstart AS Date))<=5
group by fa.new_sitename, fs.new_subprogramname,
Fsrv.name Fsrv.name
Now How to combine this two result set into one and make it something like
Office, Subprogram, ServiceType, Total#OfClients (From First Query), Total # of Clients receiving FV within 5 days" (From Second Query)
Please help.
Thanks.
June 1, 2016 at 9:02 am
For questions like this, it best to give us some sample data and table set up.
Can you provide some create and insert statements? It would also probably help to give an example of what the end data will look like. Then we all know what you're working towards.
Cheers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 1, 2016 at 2:51 pm
If I understand what you are trying to do correctly, you can use a case statement to add the additional restriction of being within 5 days of the appointment:
SELECT
fa.new_sitename AS Office,
fs.new_subprogramname As Subprogram,
Fsrv.name AS Servicetype,
Count(fa.new_clientidname) As Total#OfClients,
count
(
CASE
WHEN DATEDIFF(DAY,cast(fa.createdon AS Date),cast(fs.scheduledstart AS Date))<=5
THEN fa.new_clientidname
ELSE Null
END
) AS "Total # of Clients receiving FV within 5 days"
FROM
Firstappointment fa
join
FirstServiceAppointment fs
on
fa.new_appointmentid=fs.new_appointmentlookup
join
FirstService Fsrv
on
fa.new_service=Fsrv.serviceid
Where
fs.new_visittypename like 'first'
AND fs.statuscodename like 'done'
AND fa.createdon BETWEEN '05/18/2016' AND '05/30/2016'
group by
fa.new_sitename,
fs.new_subprogramname,
Fsrv.name Fsrv.name
The case statement outputs fa.new_clientidname only when the dates are within 5 days, otherwise it outputs a Null. Since aggregate functions do not count nulls, you end up with a count of only those clients with a start date within 5 days of appointment date.
I hope this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply