[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Pivot Table

Posted on 2009-02-10
11
Medium Priority
?
535 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:daleimi
  • 6
  • 3
  • 2
11 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 23606664
can you post your expected result?
0
 

Author Comment

by:daleimi
ID: 23606701
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 23606731
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 41

Expert Comment

by:Sharath
ID: 23606826
can you provide some sample data also?
0
 

Author Comment

by:daleimi
ID: 23606845
I keep getting an execution error where I must declare the variable @dtDate.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23606926
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
 

Author Comment

by:daleimi
ID: 23607012
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
 

Author Comment

by:daleimi
ID: 23607122
Okay I am beginning to get this to work, I will post back.
0
 

Author Comment

by:daleimi
ID: 23607272
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23612001
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
 

Author Comment

by:daleimi
ID: 23613314
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question