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.
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.
Sharath S
can you post your expected result?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can you provide some sample data also?
ASKER
I keep getting an execution error where I must declare the variable @dtDate.
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
DECLARE @dtDate DATETIME
SET @dtDate = '20090201' --<<-- set to desired STARTING date
ASKER
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.
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.
ASKER
Okay I am beginning to get this to work, I will post back.
ASKER
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.
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.
ASKER
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.