December 10, 2014 at 4:09 am
Hello Gurus,
I need your help in calculating count and average daily every hour starting 4:30 evening
4:30 - 5:30 -- Count
5:30-6:30 -- Count and so on
Thank you for your help.
--This is what I came up with, but it is not working as desired
--Appt_time and appt_date are time and date datatype
----respectively. I tried combining them but the combined date
--went 2 days back some reason.
SELECT Appt_Date
,CAST(DATEPART(Hour, APPT_TIME) as varchar) + ':00' as 'Hour'
,COUNT(Candidate_ID) as 'Hourly_Count'
,Appt_Type
,Venue
FROM dbo.appointment
WHERE APT_TIME >='16:30:00'
GROUP BY ApPt_Date , CAST(DATEPART(Hour, APPT_TIME) as varchar), appt_TYPE, Venue
ORDER BY APPT_Date, CAST(DATEPART(Hour, APPT_TIME) as varchar) asc
December 10, 2014 at 4:19 am
Please share the structure of the appointment table and the format of the desired output.
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 10, 2014 at 5:00 am
SQL_path (12/10/2014)
Hello Gurus,I need your help in calculating count and average daily every hour starting 4:30 evening
4:30 - 5:30 -- Count
5:30-6:30 -- Count and so on
Thank you for your help.
--This is what I came up with, but it is not working as desired
--Appt_time and appt_date are time and date datatype
----respectively. I tried combining them but the combined date
--went 2 days back some reason.
SELECT Appt_Date
,CAST(DATEPART(Hour, APPT_TIME) as varchar) + ':00' as 'Hour'
,COUNT(Candidate_ID) as 'Hourly_Count'
,Appt_Type
,Venue
FROM dbo.appointment
WHERE APT_TIME >='16:30:00'
GROUP BY ApPt_Date , CAST(DATEPART(Hour, APPT_TIME) as varchar), appt_TYPE, Venue
ORDER BY APPT_Date, CAST(DATEPART(Hour, APPT_TIME) as varchar) asc
First you would need to get a range of times. WHERE Apt_Time >= @startTime AND Apt_Time < @endTime
Notice the end time is only less than as an appointment would be included in the next time group.
The more I look at this the less I can help as you seem to be on the correct path.
December 10, 2014 at 6:23 am
Using a "divide and conquer" approach to the problem, you first want to generate a set of times you're going to use to query your dbo.Appointment table.
with cteStart(starting_time) as (
select cast('04:30' as time)
),
cteTimeWindows(starting_time, ending_time) as (
select dateadd(minute, (t.N - 1) * 60, s.starting_time), dateadd(minute, (t.N) * 60, s.starting_time)
from cteStart s
cross apply dbo.Tally t
where t.N < 25
)
select *
from cteTimeWindows;
From here, I made the assumption that you wanted to preserve the sort order in your original post and start at 4:30.
with cteStart(starting_time) as (
select cast('04:30' as time)
),
cteTimeWindows(starting_time, ending_time, sort_order) as (
select dateadd(minute, (t.N - 1) * 60, s.starting_time), dateadd(minute, (t.N) * 60, s.starting_time),
ROW_NUMBER() over(order by t.n)
from cteStart s
cross apply dbo.Tally t
where t.N < 25
)
select times.starting_time, times.ending_time, COUNT(a.id)
from cteTimeWindows times
left outer join dbo.Appointment a on a.apt_time between times.starting_time and times.ending_time
group by times.starting_time, times.ending_time, times.sort_order
order by times.sort_order;
You may need to change the columns a little and don't forget to filter by the date range you want to look at. If you want to see only those time slots with appointments, simply change the left outer join to an inner join.
This approach requires a Tally table. If you aren't familiar with them yet, take the time to read the article at http://www.sqlservercentral.com/articles/T-SQL/62867/ and get familiar with them. The article is well worth the time to read and may very well end up changing your expectations of performance.
December 14, 2014 at 4:22 am
Thank you so much for your help. I was able to get the desired data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply