Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    10

    Unanswered: SQL Qustion(Separating one colmn into 2)

    Hi,

    I have a data set in sql that looks like this

    Code:
    OrderDate      EDPCount	Reason
    07/01/12	          14	EDP Order Cancelled
    07/01/12	          68	EDP Order Reduced
    07/02/12	          59	EDP Order Cancelled
    07/02/12	        198	EDP Order Reduced

    The query is below

    Select o.OrderDate,Count(ed.Reason) EDPReason,ed.Reason
    From LMSDATA.dbo.tblOrder O
    JOIN LMSDATA.dbo.EnhancedDocumentationPolicyST ED
    ON O.OrderID = ed.OrderNumber
    Where o.OrderDate >='4/2/12'
    --And Reason = 'EDP Order Cancelled'
    Group BY OrderDate, ed.Reason
    Order By OrderDate ASC


    I need the data to look like two colums for one date instead of one column
    Code:
                           Canceled    Reduced
    EX Date 7/1/12    25          0
                                                                
               7/2/12    45          35
    Last edited by Pat Phelan; 07-17-12 at 13:10. Reason: Added code blocks to show formatting

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select	OrderDate,
    		SUM(Case when Reason = 'EDP Order Cancelled' then EDPCount else 0 end) as EDPOrderCancelled,
    		SUM(Case when Reason = 'EDP Order Reduced' then EDPCount else 0 end) as EDPOrderReduced
    from	LMSDATA.dbo.tblOrder
    Where	OrderDate >='4/2/12'
    group by OrderDate
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Dec 2010
    Posts
    10

    Thank you,This soo worked :)

    select OrderDate,
    SUM(Case when Reason = 'EDP Order Cancelled' then EDPCount else 0 end) as EDPOrderCancelled,
    SUM(Case when Reason = 'EDP Order Reduced' then EDPCount else 0 end) as EDPOrderReduced
    from (

    Select o.OrderDate,Count(ed.Reason) EDPCount,ed.Reason
    From LMSDATA.dbo.tblOrder O
    JOIN LMSDATA.dbo.EnhancedDocumentationPolicyST ED
    ON O.OrderID = ed.OrderNumber
    Where o.OrderDate >='4/2/12'
    --And Reason = 'EDP Order Cancelled'
    Group BY OrderDate, Reason
    )A

    Group By OrderDate
    Order By OrderDate ASC

    I was going crazy!!!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's still overly complex. There's no need for the subquery.
    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
  •