Where no sales indicate 0 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Where no sales indicate 0

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-04-18 : 04:54:52

Hi, I need help please.
I have to check if there was sales for a few items for a period
and where there is none it must indicate 0
but my query only returns the items where there was sales.

code:

select
rtrim(MCI_CorpItem) as Item
,sum(MCI_Net_Volume) as 'SalesVolume'
,sum(MCI_Net_Value_R) as 'SalesValue'
-- ,case when rtrim(MCI_CorpItem) = '' then '0' else sum(MCI_Net_Value_R) end as Sales

from dbo.MCI_Sales
where MCI_CorpItem in ('000112','007300' etc..)

Please Assist!

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 05:01:00

[code]select
rtrim(t.MCI_CorpItem) as Item
,sum(MCI_Net_Volume) as 'SalesVolume'
,sum(MCI_Net_Value_R) as 'SalesValue'
-- ,MAX(case when rtrim(m.MCI_CorpItem) = '' then '0' else sum(m.MCI_Net_Value_R) end as Sales

from (select MCI_CorpItem from dbo.MCI_Sales MCI_CorpItem in ('000112','007300' etc..))t
left outer join dbo.MCI_Sales m
on m.MCI_CorpItem=t.MCI_CorpItem
and m.period={yourperiodvalue}
group by t.MCI_CorpItem[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-18 : 05:24:55

And dont use single quotes for alias names

Select data from
(
select 1 as data
) as t

Select 'data' from
(
select 1 as 'data'
) as t

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-04-18 : 08:30:39

Thank You very much!!!

Apologies for the late reply as the qry ran long due to date range.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources