Flatten Results of Query

  • I have the following query:

    select

    c.reportrundate as Day1

    ,a.MaterialNumber

    ,a.MaterialDescription

    ,a.MaterialGroup

    ,Case

    When a.plant = 'DC15' then a.unrestrictedstock

    end as Day1LDCStock

    ,Case

    When a.plant = 'V040' then a.unrestrictedstock

    End

    as Day1DDCStock

    from DailyInventory a

    join processlog c

    on a.processlogid = c.processlogid

    where

    a.plant in ('V040','DC15')

    and c.reportrundate = '2008-03-28 00:00:00'

    and a.materialnumber = 'supa01157'

    group by

    Case

    When a.plant = 'DC15' then a.unrestrictedstock

    end

    ,Case

    When a.plant = 'V040' then a.unrestrictedstock

    End

    ,c.reportrundate

    ,a.MaterialNumber

    ,a.MaterialDescription

    ,a.MaterialGroup

    and the results of:

    2008-03-28 SUPA01157 SUP NOKIA 2610 GEL SKIN - MAGENTAACCNULL1171

    2008-03-28 SUPA01157 SUP NOKIA 2610 GEL SKIN - MAGENTAACC891NULL

    How Can I flatten the results into 1 line?

  • It's a bit tricky to tell what's what, but try something like this...

    c.reportrundate as Day1

    ,a.MaterialNumber

    ,a.MaterialDescription

    ,a.MaterialGroup

    ,max(Case

    When a.plant = 'DC15' then a.unrestrictedstock

    end) as Day1LDCStock

    ,max(Case

    When a.plant = 'V040' then a.unrestrictedstock

    End)

    as Day1DDCStock

    from DailyInventory a

    join processlog c

    on a.processlogid = c.processlogid

    where

    a.plant in ('V040','DC15')

    and c.reportrundate = '2008-03-28 00:00:00'

    and a.materialnumber = 'supa01157'

    group by

    c.reportrundate

    ,a.MaterialNumber

    ,a.MaterialDescription

    ,a.MaterialGroup

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Thanks, that work beautifully!

  • I added another table inorder to get the remaining plants summed. I'm getting double amounts for the RetailInstock and RetailinTransit.

    select

    DCLog.reportrundate as Day1

    ,DC.MaterialNumber

    ,DC.MaterialDescription

    ,DC.MaterialGroup

    ,max(Case

    When DC.plant = 'DC15' then DC.unrestrictedstock

    end) as Day1LDCStock

    ,max(Case

    When DC.plant = 'V040' then DC.unrestrictedstock

    End)

    as Day1DDCStock

    ,max(Case

    When DC.plant = 'DC15' then DC.unrestrictedstock

    end) + max(Case

    When DC.plant = 'V040' then DC.unrestrictedstock

    End) as DCTTL

    ,sum(Retail.unrestrictedstock) as RetailinStock

    ,sum(Retail.StockInTransit) as RetailinTransit

    from DailyInventory DC

    join processlog DCLog

    on DC.processlogid = DCLog.processlogid

    join DailyInventory Retail

    on DC.MaterialNumber = Retail.MaterialNumber

    join processlog RetailLog

    on Retail.processlogid = Retaillog.processlogid

    where

    DC.plant in ('V040','DC15')

    and DCLog.reportrundate = '2008-03-28 00:00:00'

    and DC.materialnumber = 'supa01157'

    and Retail.Plant not in ('V040','DC15')

    and RetailLog.reportrundate = '2008-03-28 00:00:00'

    and Retail.materialnumber = 'supa01157'

    group by

    DCLog.reportrundate

    ,DC.MaterialNumber

    ,DC.MaterialDescription

    ,DC.MaterialGroup

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

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