Group By day of month across calendar days - SQL Server 2012

Question

I have a query that produces results like the following:

Company     Member          Account     Date        Check-ins
================================================================
Acme, Inc.  Amanda Smith    4145886     7/3/2014    1
Acme, Inc.  Amanda Smith    4145886     7/9/2014    1
Acme, Inc.  Amanda Smith    4145886     7/23/2014   1
Acme, Inc.  Gladys Jones    800138618   7/5/2014    1
Acme, Inc.  Joe Ortega      800123972   7/15/2014   1
Acme, Inc.  Joe Ortega      800123972   7/29/2014   1

Here is the query:

Select 
    com.CompanyName as [Company], 
    p.FirstName + ' ' + p.LastName as [Member],
    a.AccountID as [Account], 
    CAST(mc.CheckInDate AS Date) [Date], 
    count(*) AS [Check-ins]
from 
    gym.Person p 
join 
    gym.AccountPeople ap on p.PersonID = ap.PersonID
join 
    gym.Account a on a.AccountID = ap.AccountID
join 
    gym.MembershipStatus ms on a.MembershipStatusID = ms.MembershipStatusID 
join 
    gym.Company com on a.CompanyID = com.CompanyID
join 
    gym.MemberCheckin mc on mc.PersonID = p.PersonID
where 
    mc.CheckInDate > '2014-7-1'
    and mc.CheckInDate < DATEADD(dd, 1, CAST('2014-7-31' AS Date))
GROUP BY 
    com.CompanyName, a.AccountID, 
    p.FirstName + ' ' + p.LastName, CAST(mc.CheckInDate AS Date)
order by 
    com.CompanyName, p.FirstName + ' ' + p.LastName

I'm puzzling over how to get this a little differently. Notice how Amanda checked in 3 times in July, on the 3rd, the 9th, and the 23rd. I need the results to show the check-in count across every day of the month. So in place of the "Date" column I need 31 columns (for each possible day of the longest possible month), like so:

1   2   3   4   5   6   7   8   9   10 .....
==============================================
0   0   1   0   0   0   0   0   1   0 ......

Notice the "check-in" count under 3 and 9, for July 3rd and July 9th. How can I build the query to produce results like that?

bluefeet: This is completely different from the simple pivot example here: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server -- I need group by counts spread over calendar days, which is a very different thing. — HerrimanCoder, Aug 05 '14 at 20:52
it's pivot plus date extract...not quite the same example, but has been answered on stackoverflow before. A little confused though...why would you possibly want the results like that? — Twelfth, Aug 05 '14 at 20:56
That's what the customer wants - they want to see visually how many check-ins for each day of the month by member and company. — HerrimanCoder, Aug 05 '14 at 21:00
Can you link to where it's been answered before, that exactly fits my scendario? — HerrimanCoder, Aug 05 '14 at 21:01
The duplicate is a general answer on pivoting, which seems a bit of a stretch? At what point can you mark every SQL question as duplicate of the ANSI SQL spec? — Andomar, Aug 05 '14 at 21:07
Heh, customer may ask for dumb things, but they are always right. There isn't anywhere that will fit your exact example (read as 'I dont think anyone has purposefully wanted something this hard to read in the past')...but the components you need to know are there. Debateble if this should have been closed. — Twelfth, Aug 05 '14 at 21:09
I'm not sure if I can come up with a script that doesn't repeat case when day([date]) = 1 then 1 else 0 end as 'day1',case when day([date]) = 2 then 1 else 0 end as 'day2', etc... — Twelfth, Aug 05 '14 at 21:12
@SweatCoder Actually it is exactly the same thing. You'll place your daynumber in the new columns. You can place your existing query without the `group by`, `count` inside a subquery and you'll create a new column with the `datepart` for each day (call it yourdatecolumn), and then `count(CheckInDate) for yourdatecolumn in ([1], [2], [3]...)` — Taryn, Aug 05 '14 at 21:19
@Andomar Are you seeing something out of the ordinary with this question that makes it a complex pivot? This can easily be done by getting the datepart and pivoting on those values. — Taryn, Aug 05 '14 at 21:26
Bluefeet, that sounds great, but can you show an SQL example? — HerrimanCoder, Aug 05 '14 at 21:30
Well, that's the point of saying that this is a duplicate: not answer the same question again — Lamak, Aug 05 '14 at 21:32
@SweatCoder Well I offered a suggestion in my comment on how you could resolve it and the duplicate shows how to pivot. I'd suggest maybe trying out that syntax to see if it would work. If you then run into a syntax issue, post a question. I totally get that pivot isn't the easiest concept to understand but obviously you knew the term when you posted your original question. — Taryn, Aug 05 '14 at 21:33
A SQL Example? In BlooFoot's answer, there are those curious SQL Fiddle with Demo links. You won't believe what happens when you click those — billinkc, Aug 05 '14 at 21:33
@SweatCoder [how about this](http://stackoverflow.com/questions/13977241/sql-pivot-on-dates-column) or even [this](http://stackoverflow.com/questions/16492344/pivot-table-error) == examples with `datepart` — Taryn, Aug 05 '14 at 21:40
Guys I apologize I don't understand pivots as well as I should. I am determined to learn them better. Meantime, Twelfth's answer fit the bill nicely. Thanks everyone for taking the time to help. — HerrimanCoder, Aug 05 '14 at 22:41

score 1 · Accepted Answer · answered Aug 05 '14 at 21:16

1

Ugly answer...but you should be able to create 31 case statements to get this to work. In your select (at the end works) include:

sum(case when day([date]) = 1 then 1 else 0 end) as day1,
sum(case when day([date]) = 2 then 1 else 0 end) as day2,
sum(case when day([date]) = 3 then 1 else 0 end) as day3,
sum(case when day([date]) = 4 then 1 else 0 end) as day4,
etc
sum(case when day([date]) = 31 then 1 else 0 end) as day31

Would be curious if there is a better answer, but it should function for you. They are sum columns, so the existing group bys should function fine.

answered Aug 05 '14 at 21:16

Twelfth

7,070
3
26
34

Twelfth, this looks promising, but how do I flatten the rows? In other words, I don't want to see 3 rows for Amanda Smith--just 1. The series of case statements would show her checkins accurately across the month, day by day. – HerrimanCoder Aug 05 '14 at 21:24
The group by statement ya have is a bit flawed: GROUP BY com.CompanyName, a.AccountID, p.FirstName + ' ' + p.LastName, CAST(mc.CheckInDate) --that will crteate a new row per date. Remove the checkindate from your group by statement and ensure that the group by statement is 1 row in the results you want. – Twelfth Aug 05 '14 at 21:27
You'll need to remove CAST(mc.CheckInDate AS Date) [Date], from the select clause... – Twelfth Aug 05 '14 at 21:29
Twelfth, I don't know about the elegance, but your solution worked awesome for me. THANK YOU. Customer is happy. – HerrimanCoder Aug 05 '14 at 22:39
I guess that is all the matters. and lol...question marked as a duplicate again. If you are forced to this again...excel makes an excellent template for creating 31 nearly identical lines like this. – Twelfth Aug 05 '14 at 23:58

Group By day of month across calendar days - SQL Server 2012

1 Answers1