March 20, 2009 at 6:46 am
Hello ALL,
Say I have written codes that create the column header field name such as BOMCleared, CTMCCleared,...., TotalMCCleared. Now, I wish to create two sub-header field name under each header names like the lay out. So, the main header name is BOMCCLEARED, and under main header name is two sub-field name such as SomaticMC and PsycMC. The formula for SomaticMC equal to SomaticMCCleared + SomaticMCSecondCleared + SomaticMCThirdCleared. And the formula for PsycMC = PsycMCCleared + PsycMCSecondCleared + PsycMCThirdCleared. I wish to display the outlook like
BOMCCleared CTMCleared TOTALMCCleared
SomaticMC PsycMC SomaticMC PsycMC SomaticMC PsycMC
2 3 1 4 3 7
1 2 3 2 4 4
----------------------------------------------------
3 5 4 6 7 11
Before I have written the codes which did not have 2 sub field name under main header field name. Now, I wish to create 2 sub field names under each main field name. I don't know how to do it, Would anyone please tell me how to do it?
I will take all feeds back from experts.
Below are the codes that I wrote for one column header field name without the sub field name under each main header name.
These codes below are using in Store procedure.
ALTER PROCEDURE [dbo].[WklyClearances]
-- Add the parameters for the stored procedure here
@Start Datetime,
@End Datetime,
@Parameter varchar(3) = 'ALL'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
Select @Start = COALESCE( @Start, '01-Jan-2000'),
@End = COALESCE( @End, GETDATE() ),
@Parameter = COALESCE( @Parameter, 'ALL')
;WITH AllDDS
AS
(
SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared
WHERE (DEClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROM dbo.SomaticMCCleared
WHERE (SomaticMCClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROM dbo.PsycMCCleared
WHERE (PsycMCClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROM dbo.DESecondCleared
WHERE (DESecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROM dbo.SomaticMCSecondCleared
WHERE (SomaticMCSecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROM dbo.PsycMCSecondCleared
WHERE (PsycMCSecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROM dbo.DEThirdCleared
WHERE (DEThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROM dbo.SomaticMCThirdCleared
WHERE (SomaticMCThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROM dbo.PsycMCThirdCleared
WHERE (PsycMCThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROM dbo.DEFourthCleared
WHERE (DEFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROM dbo.SomaticMCFourthCleared
WHERE (SomaticMCFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROM dbo.PsycMCFourthCleared
WHERE (PsycMCFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
),
PivotDDS
AS
(
SELECT ClearedDate,
ISNULL( SUM( ISNULL( [BO], 0 ) ), 0 ) AS [BO],
ISNULL( SUM( ISNULL( [CT], 0 ) ), 0 ) AS [CT],
ISNULL( SUM( ISNULL( [NH], 0 ) ), 0 ) AS [NH],
ISNULL( SUM( ISNULL( [ME], 0 ) ), 0 ) AS [ME],
ISNULL( SUM( ISNULL( [RI], 0 ) ), 0 ) AS [RI],
ISNULL( SUM( ISNULL( [VT], 0 ) ), 0 ) AS [VT],
ISNULL( SUM( ISNULL( [WO], 0 ) ), 0 ) AS [WO]
FROM AllDDS
PIVOT
(
SUM( Cleared ) FOR DDS IN( [BO], [CT], [NH], [ME], [RI], [VT], [WO] )
) P
GROUP BY ClearedDate
),
FinalDDS
AS
(
SELECT ClearedDate, [BO] AS BOCleared, [CT] AS CTCleared, [NH] AS NHCleared,
[ME] AS MECleared, [RI] AS RICleared, [VT] AS VTCleared, [WO] AS WOCleared,
[BO] + [CT] + [NH] + [ME] + [RI] + [VT] + [WO] AS TotalCleared,
( CASE WHEN [BO] > 0 THEN ', BO' ELSE ' ' END )
+ ( CASE WHEN [CT] > 0 THEN ', CT' ELSE ' ' END )
+ ( CASE WHEN [NH] > 0 THEN ', NH' ELSE ' ' END )
+ ( CASE WHEN [ME] > 0 THEN ', ME' ELSE ' ' END )
+ ( CASE WHEN [RI] > 0 THEN ', RI' ELSE ' ' END )
+ ( CASE WHEN [VT] > 0 THEN ', VT' ELSE ' ' END )
+ ( CASE WHEN [WO] > 0 THEN ', WO' ELSE ' ' END ) AS DDS
FROM PivotDDS
)
SELECT *
From (
Select Sum(BOCleared) as BOCleared, Sum(CTCleared) as CTCleared, Sum(NHCleared) as NHCleared, Sum(MECleared) as MECleared, Sum(RICleared) as RICleared, Sum(VTCleared) as VTCleared, Sum(WOCleared) as WOCleared,
Sum(TotalCleared) AS TotalCleared,ClearedDate AS ClearedDate, SUBSTRING( DDS, 3, 1000 ) AS DDS
FROM FinalDDS
GROUP BY ClearedDate, SUBSTRING(DDS, 3, 1000) WITH ROLLUP
)D
Where (ClearedDate IS NULL AND DDS IS NULL) OR (ClearedDate IS NOT NULL AND DDS IS NOT NULL)
Order BY ISNULL( ClearedDate, '31-Dec-2090')
END
Thank you so much for all helps.
P.S: If you don't know what I am trying to display the outlook of the data, because I don't know how to write the codes with sub-header field name under main field name. Please please please email me at josephptran2002@hotmail.com So I can explain to you on the regular mail. Thank you
March 20, 2009 at 6:52 am
No cross posts please: http://www.sqlservercentral.com/Forums/Topic680328-149-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply