April 14, 2008 at 5:01 pm
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 - MAGENTA ACC NULL 1171
2008-03-28 SUPA01157 SUP NOKIA 2610 GEL SKIN - MAGENTA ACC 891 NULL
How Can I flatten the results into 1 line?
April 14, 2008 at 5:27 pm
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.
April 15, 2008 at 11:53 am
Thanks, that work beautifully!
April 15, 2008 at 12:07 pm
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