Case select sql 2000

  • Good day,

    Is it possible to do this on a case select on sql query. Thanks

    ALTER PROC DoubleSidedSheets

    @StartDate datetime ,

    @EndDate datetime ,

    @SCount int=0,

    @DCount int=0,

    @DSheets int=0,

    @SSheets int=0

    AS

    SELECT Right(rpt.[Cost Center],4) as [Cost Center Name] ,rpt.[User Id] as [User ID],

    COUNT (CASE when rpt.Pages = rpt.[Sheets] THEN

    @SCount=(@SCount + rpt.[Sheets]) as [Single Sided Print],

    @DCount=0 as [Double Sided Print],

    ELSE

    @DSheets = (Pages /2)

    @SSheets = (Sheets - @DSheets)

    @DCount = @DCount + @DSheets as[Double Sided Print],

    @SCount = @SCount + @SSheets as [Single Sided Print]

    END

    FROM rpt_print_transactions rpt

    WHERE

    rpt.[Date/Time] >=@StartDate and rpt.[Date/Time] <=@EndDate

    Group By rpt.[Cost Center],rpt.[User Id]

    exec DoubleSidedSheets

    '2014-02-01','2014-02-02'

  • You have some errors on the way you want to generate the query. You can't combine the assignment of variables with the return of results and you need to write a CASE for each column on your query (at least).

    However, I don't understand why would you want to count a value returned by a formula. Wouldn't you need to use SUM?

    Here's my interpretation from your query. I have nothing to test and I don't know what you expect from this. For better answers, please read the article linked in my signature.

    ALTER PROCEDURE DoubleSidedSheets @StartDate DATETIME,

    @EndDate DATETIME,

    @SCount INT = 0,

    @DCount INT = 0,

    @DSheets INT = 0,

    @SSheets INT = 0

    AS

    SELECT Right(rpt.[Cost Center], 4) AS [Cost Center Name],

    rpt.[User Id] AS [User ID],

    CASE

    WHEN rpt.Pages = rpt.[Sheets]

    THEN @SCount + rpt.[Sheets]

    ELSE @SCount + (Sheets - (Pages / 2))

    END AS [Single Sided Print],

    CASE

    WHEN rpt.Pages = rpt.[Sheets]

    THEN @DCount = 0

    ELSE (Pages / 2) + @DCount

    END AS [Double Sided Print]

    FROM rpt_print_transactions rpt

    WHERE rpt.[Date/Time] >= @StartDate

    AND rpt.[Date/Time] <= @EndDate

    GROUP BY rpt.[Cost Center],

    rpt.[User Id]

    GO

    ---OR

    ALTER PROCEDURE DoubleSidedSheets @StartDate DATETIME,

    @EndDate DATETIME,

    @SCount INT = 0,

    @DCount INT = 0,

    @DSheets INT = 0,

    @SSheets INT = 0

    AS

    SELECT Right(rpt.[Cost Center], 4) AS [Cost Center Name],

    rpt.[User Id] AS [User ID],

    SUM(CASE

    WHEN rpt.Pages = rpt.[Sheets]

    THEN @SCount + rpt.[Sheets]

    ELSE @SCount + (Sheets - (Pages / 2))

    END) AS [Single Sided Print],

    SUM(CASE

    WHEN rpt.Pages = rpt.[Sheets]

    THEN @DCount = 0

    ELSE (Pages / 2) + @DCount

    END) AS [Double Sided Print]

    FROM rpt_print_transactions rpt

    WHERE rpt.[Date/Time] >= @StartDate

    AND rpt.[Date/Time] <= @EndDate

    GROUP BY rpt.[Cost Center],

    rpt.[User Id]

    GO

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you. That was meant to be SUM. I will test it and thank you for the link as well.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply