SQL - display the number of sessions grouped by time

Question

I have a table

tblAppointment {APP_ID, App_Date, User_ID}

I currently have an operator that returns the number of appointments grouped by year and month

SELECT YEAR(App_Date) AS Year, MONTH(App_Date) AS Month, count(*) AS "No of Appointments" FROM tblapplication GROUP BY YEAR(App_Date), MONTH(App_Date) 

I'm not sure how to write a select statement to return it with the {time frame, No of applications} headers and then have the data in

• line 1: time interval = week so far, no application = x.
• line 2: time interval = month, until there is no application = y.
• ro3 3: time interval = year so far, no application - z.

I would like to know how many appointments there are for 1. this week, 2. this month. 3. current year, and each result in its own line.

Any help in the right direction would be greatly appreciated. The actual problem is much larger than this, but I believe that I have simplified it to the end of this question.

Answer 1

If you're okay with the results on one line, this is relatively easy:

 select count(case when datepart(wk, App_Date) = datepart(wk, getdate()) then 1 end) as WeekSofFar , count(case when datepart(m, App_Date) = datepart(m, getdate()) then 1 end) as MonthSofFar , count(*) as YearSoFar from tblApplications where datepart(y, App_Date) = datepart(y, getdate()) 

If single lines are required, try something like:

 select 'WeekSoFar' as Period , ( select count(*) from tblApplications where datepart(y, App_Date) = datepart(y, getdate()) and datepart(wk, App_Date) = datepart(wk, getdate()) ) as NumberOfApps union all select 'MonthSoFar' , ( select count(*) from tblApplications where datepart(y, App_Date) = datepart(y, getdate()) and datepart(m, App_Date) = datepart(m, getdate()) ) union all select 'YearSoFar' , ( select count(*) from tblApplications where datepart(y, App_Date) = datepart(y, getdate()) ) 

Answer 2

I assumed that SQL 2008 is not specified.

 SELECT X.TimePeriod, Count( CASE X.Which WHEN 3 THEN CASE WHEN App_Date > DateAdd(Day, -DatePart(Weekday, GetDate()), GetDate()) THEN 1 END WHEN 2 THEN CASE WHEN Month(App_Date) = Month(GetDate()) THEN 1 END ELSE 1 END ) [No of Appointments] FROM tblApplication CROSS JOIN ( VALUES (1, 'Year to date'), (2, 'Month to date'), (3, 'Week to date') ) X (Which, TimePeriod) WHERE App_Date < Convert(date, GetDate() + 1) AND App_Date >= DateAdd(Year, DateDiff(Year, '19000101', App_Date), '19000101') GROUP BY X.Which, X.TimePeriod ORDER BY X.Which 

If you have a lot of data in your table and an index in App_Date, this query will work much better than one, using date functions on the entire table without filtering.

I also have a built-in assumption that your App_Date values ​​do not have a time part (they are all set to 12am). If this is not the case, please let me know so that I can change case 3 correctly.

If someone wants to try the code here, make the following settings:

 CREATE TABLE tblApplication ( App_Date datetime ) INSERT tblApplication VALUES ('2/1/2012'), ('2/5/2012'), ('2/10/2012'), ('1/2/2012'), ('1/9/2012'), ('1/15/2012'), ('1/28/2012'), ('12/1/2012'), ('12/5/2012'), ('12/10/2012'), ('11/2/2012'), ('11/9/2012'), ('11/15/2012'), ('11/28/2012') 

Sorry to not use 'YYYYMMDD', I didn’t think when I printed it.