...

View Full Version : Store procedure codes Please help



josephptran
04-01-2009, 09:45 PM
Hello ALL,

How are you doing? First, thank you so much for those people who try to help me out and solve my codes, I am greatly appreciated your helps and this is a homework that I try to do. So I can understand it. Second, But let me explain my situation for you, so you can help me out.

I have a main table where to store the data,

I have mutliple columns and those are

SSN, DECLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCCLEAREDDATE, DESECONDCLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCSECONDCLEAREDDATE, DDS.


SSN INT, DDS VARCHAR, and the rests are Datetime.

If there is a case (SSN), which has been assigned to one of three columns (only one column) such as DECLEAREDDATE OR SOMATICMCCLEAREDDATE, OR PSYCMCCLEAREDDATE. For example,

SSN is 111223333 is assigned to DECLEAREDDATE then the rests of two columns such as SOMATICMCCLEAREDDATE, and PSYCMCLEAREDDATE should be blanked. If a different case is assigned to SOMATICMCCLEAREDDATE then DECLEAREDDATE and PSYCMCCLEAREDDATE should be blanked. If the column has been assigned to determine the outcome of case, for example like case 111223333 is assigned to DECLEAREDDATE then this column will have a date such as 1/1/2009.

But if there is a case 222334444 has been assigned to SomaticMCClearedDate to determine the outcome, somehow that case returned for the second time. Because the first determination was wrong and second time reviews should be SOMATICMCSECONDCLEAREDDATE, then DESECONDCLEAREDDATE & PSYCMCSECONDCLEAREDDATE should be emptied. But this case won't count as a cleared case with no returns (this case is a return case).

However, if a case likes 111223333 is a cleared case with no returns then it should have the office name where this case was cleared by what office. So DDS is an office name where contains the office variable BO, CT, NH, VT, ME, RI.


I wish to count and display the number of cases cleared with no returns. And the outlook that I wish to have:

BOCLEARED CTCLEARED NHCLEARED TOTALCLEARED CLEAREDDATE DDS




I have written the codes for this other reports, and they count all cases. But this report I get stuck because of the conditions and that condition are

Count cases cleared with no returns, and SSN is count only once time (SSN is a primary key, so there is no way that SSN can be duplicated or count twice). So I ignore SSN condition in this factor.


Thank you so so so much for all efforts that you help me.

May god bless your helps to new SQL developer like me. and try to learn as fast as possible.


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 Very Much,
Joseph Tran

josephptran
04-06-2009, 04:43 PM
Oh gentlements and ladies in this forum,

Please help me to solve this issues

Thank you so much

josephptran
04-06-2009, 04:44 PM
Please help me ladies and gentlements

Old Pedant
04-06-2009, 08:48 PM
Ummm...you posted a SQL SERVER question in a MYSQL forum. So not surprising that you don't get any answers. Not likely that too many here will know SQL Server oddities.

I would suggest posting this a http://www.SQLTeam.com

That's where the real SQL Server gurus hang out.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum