CODEWITHSUNDEEP
sqlsql-server
Skeeter
Skeeter

Reputation: 41

SQL - Display running count of appointments grouped by time frame

I have a table

tblAppointment { App_ID, App_Date, User_ID }

I currently have a statement that returns 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)

Im not sure how to write a select statement to return it with headings {time frame, No of applications}, and then have data in

I would like to know how many appointments there are for 1. the current week, 2. the current month. 3. the current year, And have each result in its own row.

Any help in the right direction would be greatly appreciated. The actual problem is much greater than this but believe I have simplified it to the crux of the matter for now.

Upvotes: 4

Views: 1494

Answers (2)

ErikE
ErikE

Reputation: 50241

I assumed SQL 2008 as you didn't specify.

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 on App_Date, this query will perform hugely better than one using date functions on the entire table without filtering.

I also have an embedded assumption that your App_Date values have no time portion (they are all set to 12am). If this is not true please let me know so I can modify case 3 to be correct.

If anyone wants to try the code here's some setup:

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 about not using 'YYYYMMDD', I wasn't thinking when I typed it out.

Upvotes: 0

Andomar
Andomar

Reputation: 238086

If you're okay with the results on one row, it's 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 the separate rows are a must-have, 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()) 
       )

Upvotes: 1

Related Questions