Reduce select code | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Reduce select code

Hi all,
I have store proc below. That’s select data to this format
|Product code|Qty(month 7-10)| Qty(Month11)| Qty( Month12)| Qty( Month01)|….
currently,with a month i select to a colunm. I want to reduce code. Thank in adv. CREATE proc ExportProduct_rpt
(
@from as datetime,
@to as datetime
)
as
Begin select Product_ID, Product_Code,Product_Desc
,sum(Qty710) as Qty710,sum(Qty11) as Qty11,sum(Qty12) as Qty12
,sum(Qty01) as Qty01,sum(Qty02) as Qty02,sum(Qty03) as Qty03
,sum(Qty04) as Qty04,sum(Qty05) as Qty05,sum(Qty06) as Qty06
from (
–Month 7
Select Product_ID, Product_Code,Product_Desc
,sum(Qty) as Qty710, 0 as Qty11, 0 as Qty12, 0 as Qty01
, 0 as Qty02, 0 as Qty03, 0 as Qty04, 0 as Qty05, 0 as Qty06
from vw_ExportProduct
where (CI_Date >= @From and month(CI_Date)in (7,8,9,10))AND(CI_Date <= @To)
group by Product_ID, Product_Code,Product_Desc union all
— Month 11
Select Product_ID, Product_Code,Product_Desc
,sum(Qty) as Qty710, 0 as Qty11, 0 as Qty12, 0 as Qty01
, 0 as Qty02, 0 as Qty03, 0 as Qty04, 0 as Qty05, 0 as Qty06
from vw_ExportProduct
where (CI_Date >= @From and month(CI_Date) =11) AND(CI_Date <= @To)
group by Product_ID, Product_Code,Product_Desc ………………………. ) a
group by Product_ID, Product_Code,Product_Desc
end

try to achive this with good case logic
GIVE ME SAMPLE PLEASE.
Your UNION ALL doesn’t make sense, since you seem to be doing the exact same calculation for both months 7-10 and month 11, and then you sum the two again. That is the same as doing the calculation for months 7-11 in one go: Select Product_ID, Product_Code,Product_Desc
,sum(Qty) as Qty710, 0 as Qty11, 0 as Qty12, 0 as Qty01
, 0 as Qty02, 0 as Qty03, 0 as Qty04, 0 as Qty05, 0 as Qty06
from vw_ExportProduct
where (CI_Date between @From and <= @To)
and month(CI_Date) between 7 and 11
group by Product_ID, Product_Code,Product_Desc If you need to have separate SUM values for (months 7-10) and (month 11) you can use a case expression to group on: Select Product_ID, Product_Code,Product_Desc,
(CASE WHEN month(CI_Date) = 11 THEN "month 11" ELSE "month 7-10" END)
,sum(Qty) as Qty710, 0 as Qty11, 0 as Qty12, 0 as Qty01
, 0 as Qty02, 0 as Qty03, 0 as Qty04, 0 as Qty05, 0 as Qty06
from vw_ExportProduct
where (CI_Date between @From and <= @To)
and month(CI_Date) between 7 and 11
group by Product_ID, Product_Code,Product_Desc,
(CASE WHEN month(CI_Date) = 11 THEN "month 11" ELSE "month 7-10" END)

I’ve moved to relevant Forum.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
Uh, are you trying to get the SUM split out per month, each month on a separate column? Select Product_ID, Product_Code,Product_Desc,
SUM(CASE WHEN MONTH(CI_Date) = 1 THEN Qty ELSE 0 END) [January],
SUM(CASE WHEN MONTH(CI_Date) = 2 THEN Qty ELSE 0 END) [February]
etc.
from vw_ExportProduct
where CI_Date between @From and <= @To
group by Product_ID, Product_Code,Product_Desc

Thank so much,
Also, Read about Cross-tab Reports in sql server help file Madhivanan Failing to plan is Planning to fail
]]>