Store Procedure - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Store Procedure

Author

Topic

Josephptran
Starting Member

14 Posts

Posted - 2009-04-01 : 12:49:03

Hello ALL,

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 are empty. 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

WOULD ALL EXPERTS HELP ME?

Thank you
Joseph

Joseph

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 13:16:00

your question is not clear..please explain by giving some sample data and output in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Josephptran
Starting Member

14 Posts

Posted - 2009-04-01 : 14:57:46

[code]Create Table WEEKLYCASESCLEAREDWITHNORETURNS(SSN varchar(9), DDS varchar(9), DECLEAREDDATE DateTime, SOMATICMCCLEAREDDATE Datetime, PsycMCCLEAREDDATE Datetime, DESECONDCLEAREDDATE, SOMATICMCSECONDCLEAREDDATE [/code]

[code]Insert into WEEKLYCASESCLEAREDWITHNORETURNS(SSN, DDS, DECLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCCLEAREDDATE, DESECONDCLEAREDDATE, SOMATICSECONDCLEAREDDATE)
Select 001223333, 'SSN', BO, 'DDS', 1/1/2009, 'SomaticMCClearedDate' Union ALL Select 004651234, 'SSN', CT, 'DDS', 1/3/2009, 'DECLEAREDDATE' Union ALL Select 123453921, 'SSN', ME, 'DDS', 1/7/2009, 'PsycMCCLearedDate' Union ALL Select 153211987, 'SSN', NH, 'DDS', 1/9/2009, 'SomaticMCClearedDate', 1/10/2009, 'DESECONDCLEAREDDATE' [/code]

[code]Select * from WEEKLYCASESCLEAREDWITHNORETURNS CROSS JOIN WEEKLYCASESCLEAREDWITHNORETURNS

[/code]

Thank you

Joseph

Josephptran
Starting Member

14 Posts

Posted - 2009-04-01 : 14:59:42

[code][/code]Create Table WEEKLYCASESCLEAREDWITHNORETURNS(SSN varchar(9), DDS varchar(9), DECLEAREDDATE DateTime, SOMATICMCCLEAREDDATE Datetime, PsycMCCLEAREDDATE Datetime, DESECONDCLEAREDDATE, SOMATICMCSECONDCLEAREDDATE

[code][/code]Insert into WEEKLYCASESCLEAREDWITHNORETURNS(SSN, DDS, DECLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCCLEAREDDATE, DESECONDCLEAREDDATE, SOMATICSECONDCLEAREDDATE)
Select 001223333, 'SSN', BO, 'DDS', 1/1/2009, 'SomaticMCClearedDate' Union ALL Select 004651234, 'SSN', CT, 'DDS', 1/3/2009, 'DECLEAREDDATE' Union ALL Select 123453921, 'SSN', ME, 'DDS', 1/7/2009, 'PsycMCCLearedDate' Union ALL Select 153211987, 'SSN', NH, 'DDS', 1/9/2009, 'SomaticMCClearedDate', 1/10/2009, 'DESECONDCLEAREDDATE'

[code][/code]Select * from WEEKLYCASESCLEAREDWITHNORETURNS CROSS JOIN WEEKLYCASESCLEAREDWITHNORETURNS

Joseph

Josephptran
Starting Member

14 Posts

Posted - 2009-04-01 : 15:05:31

Hello there,

If you don't mind, would you please give me your email? So I can send you an output that I wish to display. I don't know how to Insert codes in here. I am new to SQL, I am an amature.

Thank you so much for your helps.

P.S: my email is Josephptran2002@hotmail.com

quote:

Originally posted by Josephptran

Create Table WEEKLYCASESCLEAREDWITHNORETURNS(SSN varchar(9), DDS varchar(9), DECLEAREDDATE DateTime, SOMATICMCCLEAREDDATE Datetime, PsycMCCLEAREDDATE Datetime, DESECONDCLEAREDDATE, SOMATICMCSECONDCLEAREDDATE

Insert into WEEKLYCASESCLEAREDWITHNORETURNS(SSN, DDS, DECLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCCLEAREDDATE, DESECONDCLEAREDDATE, SOMATICSECONDCLEAREDDATE)
Select 001223333, 'SSN', BO, 'DDS', 1/1/2009, 'SomaticMCClearedDate' Union ALL Select 004651234, 'SSN', CT, 'DDS', 1/3/2009, 'DECLEAREDDATE' Union ALL Select 123453921, 'SSN', ME, 'DDS', 1/7/2009, 'PsycMCCLearedDate' Union ALL Select 153211987, 'SSN', NH, 'DDS', 1/9/2009, 'SomaticMCClearedDate', 1/10/2009, 'DESECONDCLEAREDDATE'

Select * from WEEKLYCASESCLEAREDWITHNORETURNS CROSS JOIN WEEKLYCASESCLEAREDWITHNORETURNS

Thank you

Joseph

Joseph

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources