i have query case statement , need group by on alias. understand cannot done tried use subquery, not working.
here query working on:
select a.vendor, a.month_sold --sum(sd.sbqshp) sales_qty, --sum(sd.sbeprc) sales_dlr (select sd.ifprvn vendor, vn.acname vendor_name, case when sd.sbindt between '2012-07-30' , '2012-08-26' 'august 2012' when sd.sbindt between '2012-08-27' , '2012-09-30' 'september 2012' when sd.sbindt between '2012-10-01' , '2012-10-28' 'october 2012' when sd.sbindt between '2012-10-29' , '2012-11-25' 'november 2012' when sd.sbindt between '2012-11-26' , '2012-12-31' 'december 2012' when sd.sbindt between '2013-01-01' , '2013-01-27' 'january 2013' when sd.sbindt between '2013-01-28' , '2013-02-24' 'febuary 2013' when sd.sbindt between '2013-02-25' , '2013-03-31' 'march 2013' end month_sold dbo.salesdata sd inner join dbo.s2k_vend vn on vn.acvend = sd.ifprvn sd.sbindt > '2012-07-29' , sd.sbcls in ('1500') , sd.sbdiv not in ('4000') )a group a.vendor, a.month_sold order a.vendor, a.month_sold the 2 columns commented out need included somehow. suggestions?
really, putting pretty labels on date output should job presentation tier. format() in c# etc. pretty powerful. barring that, don't convert pretty string labels until last possible point, require 1 more layer:
select vendor, datename(month, month_sold) + ' ' + rtrim(year(month_sold)), sales_qty, sales_dlr ( select vendor, month_sold, sum(sbqshp) sales_qty, sum(sbeprc) sales_dlr ( select sd.ifprvn vendor, vn.acname vendor_name, -- why here? case when sd.sbindt between '2012-07-30' , '2012-08-26' '2012-08-01' when sd.sbindt between '2012-08-27' , '2012-09-30' '2012-09-01' ... when sd.sbindt between '2013-01-28' , '2013-02-24' '2013-02-01' when sd.sbindt between '2013-02-25' , '2013-03-31' '2013-03-01' end month_sold dbo.salesdata sd inner join dbo.s2k_vend vn on vn.acvend = sd.ifprvn sd.sbindt > '2012-07-29' , sd.sbcls in ('1500') , sd.sbdiv not in ('4000') ) group vendor, month_sold ) b order vendor, month_sold; 
Comments
Post a Comment