Store Procedure Codes Please help

15 pts.
Tags:
SQL
SQL stored procedures
I have a problem in SQL, and please expert helps me out and give me an advice of how to solve this issue. 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: Create Table [dbo][WeeklyActualClearedCasewithNoReturns] ( [SSN][INT]NULL,[DDS][Varchar](3)NULL,[DECLEAREDDATE][Datetime]NULL,[SOMATICMCCLEAREDDATE][Datetime]NULL,[PSYCMCCLEAREDDATE][DATETIME]NULL,[DESECONDCLEAREDDATE][DATETIME]NULL,[SOMATICMCSECONDCLEAREDDATE][DATETIME]NULL, [PSYCMCSECONDCLEAREDDATE][DATETIME]NULL )ON [PRIMARY] INSERT INTO WeeklyActualClearedCasewithNoReturns Values('000112222', 'CT', '1/1/2009', ' ', ' ', ' ', ' ', ' ') INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('111335555', 'ME', ' ', ' ', '1/4/2009', ' ', ' ', ' ') INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('666223333', 'CT', ' ', '1/6/2009', ' ', ' ', ' ', ' ') INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('999228888', 'RI', ' ', ' ', '1/11/2009', ' ', ' ', ' ') 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. 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: 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. 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') Thank you

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think you would have more chances to get useful responses if you synthesize the question a little.

So, I would re-word the question being as specific and concise as possible, would post an example of the desired output (if applicable), would try to clearly specify what my problem is, and if I was getting errors I would also post them, and I would include just the relevant portions of code.

Regards,

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following