[dba-SQLServer] SQL "CROSS TAB" - Solved

jeffrey.demulling at usbank.com jeffrey.demulling at usbank.com
Thu Jan 20 08:43:59 CST 2005


I figured it out last night at home.  Here is my solution

SELECT tempReport1.*, (tempReport1.[0-31]+ tempReport1.[31-60] +
tempReport1.[61-90]+ tempReport1.[91+]) AS RowTotal
FROM(SELECT
tempReport.OfficeName,
tempReport.[TR Category],
SUM(CASE [Aging Category - CDR Setup Status] WHEN '0-30' THEN CountofAging
ELSE 0 END) AS '0-31',
SUM(CASE [Aging Category - CDR Setup Status] WHEN '31-60' THEN CountofAging
ELSE 0 END) AS '31-60',
SUM(CASE [Aging Category - CDR Setup Status] WHEN '61-90' THEN CountofAging
ELSE 0 END) AS '61-90',
SUM(CASE [Aging Category - CDR Setup Status] WHEN '91+' THEN CountofAging
ELSE 0 END) AS '91+'

FROM
(SELECT
tblOffices.Name AS OfficeName,
tblProductTypes.[TR Category],

[Aging Category - CDR Setup Status] = CASE WHEN  [tblDeals].[Deal Status]
<> 3 THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Done] IS NOT NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NOT NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
CASE WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<31 THEN
'0-30'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<61 THEN
'31-60'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<91 THEN
'61-90'
Else
'91+'
End
End
End
End
End,

COUNT(CASE WHEN  [tblDeals].[Deal Status] <> 3 THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Done] IS NOT NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NOT NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
CASE WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<31 THEN
'0-30'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<61 THEN
'31-60'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<91 THEN
'61-90'
Else
'91+'
End
End
End
End
End) AS CountofAging

FROM
tblDeals
LEFT JOIN tblProductTypes
ON tblDeals.[Product Code] = tblProductTypes.Number
LEFT JOIN tblOffices
ON tblDeals.Office = tblOffices.Number

WHERE
CASE WHEN  [tblDeals].[Deal Status] <> 3 THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Done] IS NOT NULL THEN
'Complete'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
'Due Date Not Set'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NOT NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
CASE WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<61 THEN
'Open'
Else
'Past Due'
End
End
End
End
End IN ('Open', 'Past Due')

GROUP BY
tblOffices.Name,
tblProductTypes.[TR Category],

CASE WHEN  [tblDeals].[Deal Status] <> 3 THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Done] IS NOT NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NOT NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
CASE WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<31 THEN
'0-30'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<61 THEN
'31-60'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<91 THEN
'61-90'
Else
'91+'
End
End
End
End
End

) AS tempReport

GROUP BY
tempReport.OfficeName,
tempReport.[TR Category]) as tempReport1


                                                                           
             jeffrey.demulling                                             
             @usbank.com                                                   
             Sent by:                                                   To 
             dba-sqlserver-bou         dba-sqlserver at databaseadvisors.com  
             nces at databaseadvi                                          cc 
             sors.com                                                      
                                                                   Subject 
                                       [dba-SQLServer] SQL "CROSS TAB"     
             01/19/2005 03:00                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             dba-sqlserver at dat                                             
             abaseadvisors.com                                             
                                                                           
                                                                           




Using the sql below I need to create a "cross tab" that looks like this:

Office ;TR Category ;0-30;31-60;61;90;91+
Office1;Category1;0;0;1;5
Office1;Category3;1;0;4;0
Office2;Category5;7;0;6;10

Any and all help would be appreciated as I am at a complete loss.  Thank
you in advance.


SELECT
tblRegions.Name AS RegionName,
tblOffices.Name AS OfficeName,
tblNames_1.Name AS [AM Name],
tblNames_2.Name AS [NS Name],
tblNames.Name AS [ASU Name],
tblProductTypes.Name AS ProductCodeName,
tblProductTypes.[TR Category],

[Aging Category - CDR Setup Status] = CASE WHEN  [tblDeals].[Deal Status]
<> 3 THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Done] IS NOT NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NOT NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
CASE WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<31 THEN
'0-30'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<61 THEN
'31-60'
WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<91 THEN
'61-90'
Else
'91+'
End
End
End
End
End

FROM
tblDeals
LEFT JOIN tblNames
ON tblDeals.[Account Set Up Person Key] = tblNames.Number
LEFT JOIN tblNames AS tblNames_1
ON tblDeals.[Relationship Specialist Key] = tblNames_1.Number
LEFT JOIN tblNames AS tblNames_2
ON tblDeals.[Negotiation Specialist Key] = tblNames_2.Number
LEFT JOIN tblProductTypes
ON tblDeals.[Product Code] = tblProductTypes.Number
LEFT JOIN tblOffices
ON tblDeals.Office = tblOffices.Number
LEFT JOIN tblRegions
ON tblOffices.Region = tblRegions.Number

WHERE
CASE WHEN  [tblDeals].[Deal Status] <> 3 THEN
'N/A'
Else
CASE WHEN [tblDeals].[CDR Setup Done] IS NOT NULL THEN
'Complete'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
'Due Date Not Set'
Else
CASE WHEN [tblDeals].[CDR Setup Due] IS NOT NULL AND [tblDeals].[CDR Setup
Done] IS NULL THEN
CASE WHEN DATEDIFF(DAY,[tblDeals].[Actual Close Date], GETDATE())<61 THEN
'Open'
Else
'Past Due'
End
End
End
End
End IN ('Open', 'Past Due')


------------------------------------------------------------------------------

Electronic Privacy Notice. This e-mail, and any attachments, contains
information that is, or may be, covered by electronic communications
privacy laws, and is also confidential and proprietary in nature. If you
are not the intended recipient, please be advised that you are legally
prohibited from retaining, using, copying, distributing, or otherwise
disclosing this information in any manner. Instead, please reply to the
sender that you have received this communication in error, and then
immediately delete it. Thank you in advance for your cooperation.
==============================================================================


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


------------------------------------------------------------------------------

Electronic Privacy Notice. This e-mail, and any attachments, contains
information that is, or may be, covered by electronic communications
privacy laws, and is also confidential and proprietary in nature. If you
are not the intended recipient, please be advised that you are legally
prohibited from retaining, using, copying, distributing, or otherwise
disclosing this information in any manner. Instead, please reply to the
sender that you have received this communication in error, and then
immediately delete it. Thank you in advance for your cooperation.
==============================================================================





------------------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
==============================================================================




More information about the dba-SQLServer mailing list