Store Procedure Codes Please help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Store Procedure Codes Please help

Author

Topic

Josephptran
Starting Member

14 Posts

Posted - 2009-04-01 : 15:16:50

I have created a query in store procedure and wish to capture a case if that case falls into one of the columns. For example,

CaseID DECLEAREDDATE SOMATICMCCLEAREDDATE... DDS

A...................... 1/1/2009........... BO
B.... 1/3/2009............................. CT

If there is a case and that case names "A" is assigned to SomaticMCClearedDate to determine the outcome of this case. Then DEClearedDate and PsycMCClearedDate Should be empty, because a case can only be assigned to one field name. Or case B is assigned to DEClEAREDDATE to determine, and of course SomaticMCClearedDate and PsycMCClearedDate shoud be empty because there is only one field name which should be assigned to determine the outcome of a case. I would like to capture the case as single record "1". CASE ID IS PRIMARY KEY.
DDS is an office name, BO and CT are office abbreviation name. There are more than 2 fields name in the table, because there is no space for me to show, so I would like all to execuse me for such inconveniently.

I wish to have an outlook like below:

BOCLEARED CTCLEARED... TOTALCLEARED CLEAREDDATE DDS
....1........................1.......1/1/2009.......BO
..............1..............1.......1/3/2009.......CT
-----------------------------------
....1.........1..............2

I wrote the codes for the outlook above, but somehow it can not capture the case falls in either column field name. Please take a look at my codes and point out what I need to add or eliminate in order to capture what I need to display.

ALTER PROCEDURE [dbo].[FYTDClearances]
-- 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 Very Much,
Joseph Tran

Joseph

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 15:17:28

Duplicate, locking topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources