asked on

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

chapmandew

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

Jacque Scott

ASKER

I get an error when I run that query but I need both numbers not just one or the other. any other ideas?

brejk

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

Jacque Scott

ASKER

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?

brejk

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.

Jacque Scott

ASKER

How would I do that? How would I be able to see the two seperate times for each date?

brejk

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

Jacque Scott

ASKER

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.

brejk

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.

Jacque Scott

ASKER

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?

ASKER CERTIFIED SOLUTION

brejk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jacque Scott

ASKER

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?