how to capture cases in store procedure codes

15 pts.
Tags:
SQL
SQL stored procedures
I wish to creat a query in store procedure (with variables such as Start Datetime, End Datetime, Parameter varchar(3) as you can see on my store procedure for other reports). But for this report, I wish to capture a case might fall into one of the columns (only one column such as DECLEAREDDATE OR SOMATICMCCLEAREDDATE OR PSYCMCCLEAREDDATE). If any cases fall into one of columns such as DEClearedDate or SomaticMCClearedDate or PsycMCClearedDate and then the same case returns for the second review. The second review is either DESecondClearedDate or SomaticMCSecondClearedDate or PsycMCSecondClearedDate, then that case can not count as a case cleared with no returns. Or for the case for third review, DEThirdClearedDate or SomaticMCThirdClearedDate, or PsycMCThirdClearedDate, then can not count as a case cleared with no returns. For example, I have a main table, which called ROCAPData and it contains multiple columns field name like the one I've sent you. Now, my question is how can I capture the cases cleared with no returns and count SSN only once (SSN is case ID, and set as Primary key. So I think you don't have to worry about double count for SSN. WHAT DO YOU THINK ABOUT SSN?) The outlook that I wish to have is: These columns field name below are the headers field name of the report (you can see on my previous store procedure of other reports). DDS is the office names such as BO, CT, ME, NH, RI, WO, VT BOCLEARED CTCLEARED NHCLEARED.....TOTALCLEARED CLEAREDATE DDS Below is the sample of the outlook that I wish to have 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 P.S: If you have any questions to clarify my question for you, please let me know.

Answer Wiki

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

To be honest, I found your question a little hard to understand, and I must also say that IMO when people see a question this long and it is not very clear, they will probably not get interested.

On the other hand, when I see table names like SomaticMCCleared and PsycMCCleared, or tables like DESecondCleared and DEThirdCleared, I would think that there is a problem with the underlying database design. For example, I think you could have a table ‘Clearance’ in which you would store all clearances, identifying the clearance reason with a ‘reason_id’ column, and the number of review with a ‘review_number’ column. Why ? What happens if the business changes, and more reasons need to be added, or more possible reviews ? You would need to create more and more tables.

However, if you keep your current design, I guess that for a case to be considered as a case cleared with no returns, you just need to check if the cases exists in the tables for the next review level.

For example, for a DECleared case:

SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared DE
WHERE (DEClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = ‘ALL’ OR DDS = @Parameter )
AND NOT EXISTS (SELECT 1 FROM FROM dbo.DESecondCleared WHERE ssn = DE.ssn)
AND NOT EXISTS (SELECT 1 FROM FROM dbo. SomaticMCSecondCleared WHERE ssn = DE.ssn)
AND NOT EXISTS (SELECT 1 FROM FROM dbo.PsycMCSecondCleared WHERE ssn = DE.ssn)

Regards,

Discuss This Question: 3  Replies

 
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
  • Twlp123
    Its hard to understand your question, but I'm only looking at your case function. The basic syntax of the search case function would be:- Cae WHEN Boolean _expression THEN result_expression [...n] [ ELSE else_result_expression ] END Example Select Leasevendor, LeaseNumber, CASE --some vendors have id of sales reps incorporated in LeaseNumbers When LeaseVendor = 'Trigon PS' Then Substring(LeaseNumber,5,12) When LeaseVendor Like 'BB%' Then SubString(LeaseNumber,9,8) When LeaseVendor Like 'MMEX%' Then SubString(LeaseNumber,7,6) When LeaseVendor = 'DAFS' Then SubString(LeaseNumber,8,11) Else 'UnKnown' END [LeaseAgent], ContractDate [ContractDate] from Lease as per your case syntax, since you are repeating the case function for each office name values, then you must include DDS after each end statement of each case function, as in my example above I'm not sure whether yiour CASE example will work but you must test it out separately with some dummy data first
    165 pointsBadges:
    report
  • carlosdl
    Yes, I think that the query should be split in parts too, and each part should be tested separately, to make sure it does what it is supposed to do, before adding it to the complete statement.
    69,045 pointsBadges:
    report
  • Twlp123
    and I agree with carlosdl remarks that it seems you have a major flaw in your database design and it could be re-designed in such a way to enable a smaller and faster procedure to process the report and achive the desired result.
    165 pointsBadges:
    report

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