SQL Pivot Table

I need to create a pivot table where going down the left side it will show 7 days of data from the date I have picked, and then on top will be 4 different times, and the need to count the records.
So basically on 1-14-09 there are 5 appointments at 08:15 AM, 5 appointments at 11:00 AM and so on.
I have never created a pivot table in SQL before, so this is very new to me.
SELECT COUNT(dtPartyTime) AS [8:45] 
FROM tblSalesManifesGAA 
WHERE (dtPartyTime = '08:45 AM') AND (dtDate BETWEEN @dtDate AND dateadd(day, 7, @dtDate))
 
This is the code I am using to just count values for the specific times, just not sure how to pivot this out in order to get the same information just showing each of the 7 days, and then totalling each time at the bottom.
 
Any help would be appreciated.

Open in new window

daleimiAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT tsmg.dtDate,
    SUM(CASE WHEN dtPartyTime = '08:45 AM' THEN 1 ELSE 0 END) AS [8:45],
    SUM(CASE WHEN dtPartyTime = '11:00 AM' THEN 1 ELSE 0 END) AS [11:00]  --, ...
    --SUM(CASE THEN dtPartyTime = '....' THEN 1 ELSE 0 END) AS [....], ...
FROM tblSalesManifesGAA tsmg
INNER JOIN (
    SELECT @dtDate AS selectedDate UNION ALL SELECT DATEADD(DAY, 1, @dtDate) UNION ALL
    SELECT DATEADD(DAY, 2, @dtDate) UNION ALL SELECT DATEADD(DAY, 3, @dtDate) UNION ALL
    SELECT DATEADD(DAY, 4, @dtDate) UNION ALL SELECT DATEADD(DAY, 5, @dtDate) UNION ALL
    SELECT DATEADD(DAY, 6, @dtDate)
) AS selectedDates ON selectedDate = tsmg.dtDate
GROUP BY tsmg.dtDate
0
 
SharathData EngineerCommented:
can you post your expected result?
0
 
daleimiAuthor Commented:
Date      8:45      11:00      1:15      3:30
2/10/2009      5      3      3      0
2/11/2009      4      5      3      0
2/12/2009      5      6      4      1
2/13/2009      6      3      2      2
2/14/2009      3      5      4      3
2/15/2009      5      6      5      2
2/16/2009      6      7      6      2
      34      35      27      10
This is what I would be looking for.  I will use a gridview for the view, which will be bound to a calendar control.
I hope this helps.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
can you provide some sample data also?
0
 
daleimiAuthor Commented:
I keep getting an execution error where I must declare the variable @dtDate.
0
 
Scott PletcherSenior DBACommented:
Sorry, you referenced @dtDate in your original query so I thought you already had it DECLAREd.

DECLARE @dtDate DATETIME

SET @dtDate = '20090201' --<<-- set to desired STARTING date
0
 
daleimiAuthor Commented:
I hope this makes sense, The @dtDate within my application has been referencing a calendar control, so when the code has seen the @dtDate construct it pulls information using the calendar, I have never explicity declared the @dtDate within my code up to this point other than say from an update statement where it is dtDate=@dtDate so it is declared there I believe.
I am not sure where this declaration even goes as I have placed it in a few different spots, and am still getting execution errors.

Sorry have been thrust into a development role, and am learning as I go along.
0
 
daleimiAuthor Commented:
Okay I am beginning to get this to work, I will post back.
0
 
daleimiAuthor Commented:
Okay I got the code to work, thank you so much, but something interesting keeps happening when I bring up the sqldatasource, query builder, I keep getting this error "Unable to parse query text."  Although the code is still working once I go out and test the query.  Any ideas why this would be happening, all in all though the query is working within my application as expected.
0
 
Scott PletcherSenior DBACommented:
Some query parsers don't "understand" CASE statements.  Unfortunately, I don't know of any other way to efficiently do what you want to do.
0
 
daleimiAuthor Commented:
It's not a problem I did some research on the issue and have a little better understanding as to why it throws the error, but the code is working as expected and I was able to add a count statement to it in order to produce a totals column at the end, so it is perfect for what I need at this moment.  Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.