Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Store procedure codes Please help

    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
    Last edited by josephptran; 04-02-2009 at 07:21 PM.

  • #2
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh gentlements and ladies in this forum,

    Please help me to solve this issues

    Thank you so much

  • #3
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please help me ladies and gentlements

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •