Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Need help converting a select query into a case statement

    I have the following query:
    Code:
      (SELECT     MIN(CFGDates.AccountPdEnd)
                                FROM          CFGDates LEFT JOIN
                                                       AR ON AR.Period = CFGDates.Period
                                WHERE      AR.Period = '200408')
    I need to convert this into a case statement.
    I tried various ways but did not get the result that I was after

    Thanks,
    Laura

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Convert a SELECT statement into a CASE statement?

    I haven't a clue what you are intending to do.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Posts
    105
    This is the query that I am dealing with:
    Code:
    SELECT     PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice AS InvoiceNumber, 
    CASE WHEN (AR.InvoiceDate = '') 
     THEN '0'
     ELSE DATEDIFF(day, AR.InvoiceDate,  ********* need to insert case statement to find the end of the period ************** ) END AS DaysOld, 
                          SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount WHEN LedgerAR.TransType = 'CR' AND 
                          LedgerAR.SubType = 'T' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) AS InvoiceBalance, 
                          SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'I' THEN - 1 * LedgerAR.Amount ELSE 0 END) AS Interest, 
                          SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'R' THEN LedgerAR.Amount ELSE 0 END) AS Retainage, 
                          SUM(CASE WHEN (LedgerAR.WBS2 <> '9001' AND AR.Period = '200408') THEN - 1 * LedgerAR.Amount ELSE 0 END) AS total
    FROM         PR LEFT OUTER JOIN
                          AR ON AR.WBS1 = PR.WBS1 AND PR.WBS2 = '' AND PR.WBS3 = '' LEFT JOIN
                          LedgerAR ON AR.WBS1 = LedgerAR.WBS1 AND AR.WBS2 = LedgerAR.WBS2 AND AR.WBS3 = LedgerAR.WBS3 AND 
                          AR.Invoice = LedgerAR.Invoice LEFT JOIN
                          LD ON PR.WBS1 = LD.WBS1 AND PR.WBS2 = LD.WBS2 AND PR.WBS3 = LD.WBS3 LEFT JOIN
                          CFGDates ON CFGDates.Period = AR.Period
    WHERE     (LedgerAR.TransType = 'IN') AND (LedgerAR.SubType <> ' X ' OR
                          LedgerAR.SubType IS NULL) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND (LedgerAR.Amount <> 0) AND 
                          (AR.PaidPeriod > 200408) AND (PR.WBS1 = '001-298') OR
                          (LedgerAR.TransType = ' CR ') AND (LedgerAR.SubType IN (' R ', ' T ')) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND 
                          (LedgerAR.Amount <> 0) AND (PR.WBS1 = '001 - 298 ')
    GROUP BY PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice, Ar.Period, CFGDates.AccountPdEnd
    HAVING      SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) <> 0
    Within the DateDiff function I need to find the end of the period using a case statment such as
    Code:
    Case When AR.Period='200408' Then Min(CFGDates.AccountPdEnd) Else '0' End As EndPeriod
    The problem is that I am having a hard time integrating this into the top query. By using the case statement that I wrote above, I get an error stating that there is a problem near 'As' . I am wondering if there is a way to perform a case statement within a case statement.

    Hope that clears things up.

    Thanks,
    Laura

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's right, you cannot assign an alias to a value if that value is inside a function

    just drop the As EndPeriod

    oh, and note that you are looking for the difference in days between AR.InvoiceDate and the value of this case expression, so make sure not to use quotes around the zero if indeed you want december 31, 1899, which seems rather unlikely, but that is, i think, the date that corresponds to zero
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a CASE statement that incorporates your logic:

    CASE WHEN (AR.InvoiceDate = '') THEN '0'
    WHEN AR.Period='200408' THEN DATEDIFF(day, AR.InvoiceDate, Min(CFGDates.AccountPdEnd))
    ELSE DATEDIFF(day, AR.InvoiceDate, 0)
    END AS DaysOld

    ...but whether it will integrate with the rest of your SQL statement, I do not know.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, that's the same as the nested version (you are allowed to nest CASEs, you know)

    that date equal to an empty string gives me the creeps

    and the quotes around the zero don't make me feel too hot either

    also, i just noticed, CFGDates.AccountPdEnd is in the GROUP BY, so why even bother finding the MIN for it in the DATEDIFF, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, I certainly didn't mean to imply that was ALL that was wrong with her code.

    What gives me the heebie-jeebies about it is all the hard-coded values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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