How to get the values without case

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

General SQL Server Forums

New to SQL Server Programming

How to get the values without case

Author

Topic

vandana
Starting Member

29 Posts

Posted - 2013-10-23 : 00:29:53

Hi All,

i have produts(a,b,c....) and marketing group(abc,def....)
when product "a" or "b" and marketing group "abc" or "def" i want all these to be as product "a" only .

now am using case statement, can anyone suggest me other way to get the result!!!!

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 01:22:49

you need to create a mapping table for table with product and marketinggroup as columns.
Otherwise only way is to use case statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vandana
Starting Member

29 Posts

Posted - 2013-10-23 : 01:41:31

below is my query

select Row_number() OVER (ORDER BY a.BusinessYear, a.businessmonth) AS RowNumber, a.* from
(select DT.BusinessYear, dt.businessmonth,
(case when dm.ProductGroupID in('o3','o4') or dm.MarketingGroupID in('SH10','S11')then 'Da' else 'nan' end)as ProductGroup
,dt.YearPeriod,sum(FS.quantity_invoiced) as quantity from Factsales as FS
inner join DimCustomer as DC on fs.Customer=dc.CustomerNumber
inner join Dimitem as di on FS.Item_number=di.ItemNumber
inner join DimMarketingGroup dm on di.MarketingGroupID=dm.MarketingGroupID
inner join DimTime as DT on fs.Invoice_date=DT.DateKey
where (dt.DateKey between '2013-04-01' and '2013-04-30')
and DC .Division IN ('AA' ,'BBs' )
group by (case when dm.ProductGroupID in('o3','o4') or dm.MarketingGroupID in('SH10','S11')then 'Da' else 'nan' end)
, DT.BusinessMonth, DT.BusinessYear,dt.YearPeriod) as a
where a.ProductGroup<>'nan'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 01:59:52

Create mapping table if you dont want to use CASE statements for each mapping required

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources