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.
==============================================================================