Pivot Tables in SQL2005

I currently  have a pivot table that is working great but I need to add to it.  The below code is giving me the total ServiceTime per date.  I need to split this service time out depending on the stage of this note.  

1) If the note has been signed
2) If the note has been signed and countersigned

SUM(CASE WHEN countersigned_id IS NULL AND signed_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
 0 END) as PendingServiceTime,

SUM(CASE WHEN countersigned_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
 0 END) as ApprovedServiceTime

How do I add this to my pivot table query?
SELECT lastname + ', ' + firstname as FullName, [12/3/2007], [12/4/2007], [12/5/2007]
FROM (SELECT p.LastName, p.FirstName, t.ServiceDate, 
 
ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime 
 
FROM dbo.allNotes(8) AS t 
LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID 
LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID 
LEFT JOIN dbo.Clients as c on t.ClientID = c.ID 
LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral' 
LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19) 
LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid 
WHERE t.AgencyID = 8 AND t.tableName = 'collateral' 
AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL 
AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL)) 
AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007') 
) rs Pivot (SUM(rs.ServiceTime) FOR rs.ServiceDate IN ([12/3/2007], [12/4/2007], [12/5/2007]

Open in new window

huerita37Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
brejkConnect With a Mentor Commented:
Pseudocode:

USE YourDatabaseName
GO

CREATE PROC YourProcedureName
AS
SET NOCOUNT ON

SELECT
  T1.FullName,
  T1.[12/3/2007] AS [12/3/2007 - Approved Service Time],
  T1.[12/4/2007] AS [12/4/2007 - Approved Service Time],
  T1.[12/5/2007] AS [12/5/2007 - Approved Service Time],
  T2.[12/3/2007] AS [12/3/2007 - Pending Service Time],
  T2.[12/4/2007] AS [12/4/2007 - Pending Service Time],
  T2.[12/5/2007] AS [12/5/2007 - Pending Service Time]
FROM ( <place SELECT with PIVOT "Approved Time" here>) T1
INNER JOIN (<place SELECT with PIVOT "Pending Time" here) T2
ON T1.FullName = T2.FullName

Of course you may need to make the dynamic SQL instead of static query. If you need some assistance on how to implement a dynamic PIVOT use one of my answers: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23051963.html (unfortunately, no points for that ;-)).
0
 
chapmandewCommented:
You can only pivot by 1 field....so, PIVOT may not be what you need.  Try this to see if it works:

SELECT lastname + ', ' + firstname as FullName, [12/3/2007], [12/4/2007], [12/5/2007]
FROM (SELECT p.LastName, p.FirstName, t.ServiceDate, 
 
ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime 
 
FROM dbo.allNotes(8) AS t 
LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID 
LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID 
LEFT JOIN dbo.Clients as c on t.ClientID = c.ID 
LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral' 
LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19) 
LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid 
WHERE t.AgencyID = 8 AND t.tableName = 'collateral' 
AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL 
AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL)) 
AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007') 
) rs Pivot (SUM(CASE WHEN countersigned_id IS NULL AND signed_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
 0 END) as PendingServiceTime FOR rs.ServiceDate IN ([12/3/2007], [12/4/2007], [12/5/2007]

Open in new window

0
 
huerita37Author Commented:
I get an error when I run that query but I need both numbers not just one or the other.  any other ideas?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
brejkCommented:
A little bit weird but try what's in the snippet. I wasn't sure where countersigned_id and signed_id columns come from so I assumed they come from dbo.allNotes table (if it's not true, change the table alias for these columns in my query).
SELECT 
  rs.lastname + ', ' + rs.firstname as FullName, 
  SUM(
    CASE 
      WHEN rs.ServiceDate = CONVERT(DATETIME, '12/03/2007') THEN PendingServiceTime 
      ELSE 0
    END
  ) AS [12/3/2007 - Pending Service Time], 
  SUM(
    CASE 
      WHEN rs.ServiceDate = CONVERT(DATETIME, '12/03/2007') THEN ApprovedServiceTime 
      ELSE 0
    END
  ) AS [12/3/2007 - Approved Service Time],
  SUM(
    CASE 
      WHEN rs.ServiceDate = CONVERT(DATETIME, '12/04/2007') THEN PendingServiceTime 
      ELSE 0
    END
  ) AS [12/4/2007 - Pending Service Time], 
  SUM(
    CASE 
      WHEN rs.ServiceDate = CONVERT(DATETIME, '12/04/2007') THEN ApprovedServiceTime 
      ELSE 0
    END
  ) AS [12/4/2007 - Approved Service Time],
  SUM(
    CASE 
      WHEN rs.ServiceDate = CONVERT(DATETIME, '12/05/2007') THEN PendingServiceTime 
      ELSE 0
    END
  ) AS [12/5/2007 - Pending Service Time], 
  SUM(
    CASE 
      WHEN rs.ServiceDate = CONVERT(DATETIME, '12/05/2007') THEN ApprovedServiceTime 
      ELSE 0
    END
  ) AS [12/5/2007 - Approved Service Time]
FROM 
(
  SELECT 
    p.LastName, 
    p.FirstName, 
    t.ServiceDate,
    CASE 
      WHEN t.countersigned_id IS NULL AND t.signed_id IS NOT NULL 
        THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) 
      ELSE
        0 
    END AS PendingServiceTime,
    CASE 
      WHEN t.countersigned_id IS NOT NULL 
        THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) 
      ELSE
        0 END AS ApprovedServiceTime,
    ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime 
  FROM dbo.allNotes(8) AS t 
  LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID 
  LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID 
  LEFT JOIN dbo.Clients as c on t.ClientID = c.ID 
  LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral' 
  LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19) 
  LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid 
  WHERE t.AgencyID = 8 AND t.tableName = 'collateral' 
  AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL 
  AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL)) 
  AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007')
) AS rs
GROUP BY rs.lastname + ', ' + rs.firstname
ORDER BY rs.lastname + ', ' + rs.firstname

Open in new window

0
 
huerita37Author Commented:
The user will have up to 16 dates (possibly moe in the future) that they can search through.  I also have 13 different reports that I use UNION to combine all of the queries into one sql query.  If I use this method it will be the longest sql string that I have ever seen.  Will this impact the speed of running the query?
0
 
brejkCommented:
You can build two views - one calculating Pending Service Time for each date and the second - calculating Approved Service Time for each date. Both these views can use PIVOT operator (the query should be much shorter then). Then you can just join those two views on some identifier (FullName?) and you will have all you need.
0
 
huerita37Author Commented:
How would I do that?  How would I be able to see the two seperate times for each date?
0
 
brejkCommented:
See the snippet for sample PIVOT use (I wrote it without any tests so you must try it). This can be a source code for one of the views. Then you may prepare the second view (similar - you just change the column in SUM function). And finally just join two views.
SELECT 
  rs.lastname + ', ' + rs.firstname as FullName, 
  [12/3/2007],
  [12/4/2007],
  [12/5/2007]
FROM 
(
  SELECT 
    p.LastName, 
    p.FirstName, 
    t.ServiceDate,
    CASE 
      WHEN t.countersigned_id IS NULL AND t.signed_id IS NOT NULL 
        THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) 
      ELSE
        0 
    END AS PendingServiceTime,
    CASE 
      WHEN t.countersigned_id IS NOT NULL 
        THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) 
      ELSE
        0 END AS ApprovedServiceTime,
    ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime 
  FROM dbo.allNotes(8) AS t 
  LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID 
  LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID 
  LEFT JOIN dbo.Clients as c on t.ClientID = c.ID 
  LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral' 
  LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19) 
  LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid 
  WHERE t.AgencyID = 8 AND t.tableName = 'collateral' 
  AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL 
  AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL)) 
  AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007')
) AS rs
PIVOT (
  SUM(rs.ApprovedServiceTime)
  FOR rs.ServiceDate IN ([12/3/2007],[12/4/2007],[12/5/2007])
) AS P

Open in new window

0
 
huerita37Author Commented:
I can't use a view because there are a couple of items that are dynamic, in particular the dates.  So I tried writing a function but again the dynamic dates are throwing me off.

Any other suggestions?  This is a tough one.
0
 
brejkCommented:
You can build a stored procedure that will execute those two PIVOTs and will join their results. You can use dynamic SQL in stored procedures.
0
 
huerita37Author Commented:
Unfortunately I don't know how to build a stored procedure, I can change them but I don't really understand how it works. How would I do this?
0
 
huerita37Author Commented:
Ok, I am working this out but I am not getting all of the rows that I need.  You show an INNER JOIN to join the two pivot tables.  This only gives me the rows that are in both result sets.  I need all of the rows.  I can't do a INNER, LEFT, or RIGHT join because they don't give me what I need.  

 What else could I use?
0
All Courses

From novice to tech pro — start learning today.