September 11, 2013 at 8:00 am
Hi,
I did this code to select 2 best selling prodID (by quantity) for each Year period and put it in pivoting format needed for report (ssrs)
Just curious how it can be optimized (or left formatted inside of ssrs)
this is my sample
;with t4 as (
select '2011-01-02' as dd, 123 as prodID union
select '2011-01-03' as dd, 345 as prodID union
select '2011-01-04' as dd, 345 as prodID union
select '2011-01-05' as dd, 123 as prodID union
select '2011-01-06' as dd, 123 as prodID union
select '2012-01-02' as dd, 567 as prodID union
select '2012-01-03' as dd, 567 as prodID union
select '2012-01-04' as dd, 567 as prodID union
select '2012-01-04' as dd, 777 as prodID union
select '2012-01-05' as dd, 777 as prodID union
select '2012-01-06' as dd, 888 as prodID
)
--select * From t4
select *, RANK () over (partition by year order by numb desc) as Rank
into #tt from (
select COUNT(*) numb, DATEPART(year,dd) year, prodID
from t4
group by DATEPART(year,dd), prodID
)
b
---select * from #tt
select t1.prodID as best_11 ,
t2.prodID as best_12, t1.Rank
from #tt t1
join #tt t2 on t2.rank = t1.rank
where t1.year = '2011'
and t2.year = '2012'
and t1.Rank < 3
order by 3
Tx
Mario
September 11, 2013 at 9:02 am
You can remove the use of the derived table:
;with t4 as (
select '2011-01-02' as dd, 123 as prodID union
select '2011-01-03' as dd, 345 as prodID union
select '2011-01-04' as dd, 345 as prodID union
select '2011-01-05' as dd, 123 as prodID union
select '2011-01-06' as dd, 123 as prodID union
select '2012-01-02' as dd, 567 as prodID union
select '2012-01-03' as dd, 567 as prodID union
select '2012-01-04' as dd, 567 as prodID union
select '2012-01-04' as dd, 777 as prodID union
select '2012-01-05' as dd, 777 as prodID union
select '2012-01-06' as dd, 888 as prodID
)
selectCOUNT(*) numb,
DATEPART(year,dd) year,
prodID,
RANK () over (partition by DATEPART(year,dd) order by COUNT(*) desc) as Rank
INTO #tt
from t4
group by DATEPART(year,dd), prodID
MCITP SQL 2005, MCSA SQL 2012
September 11, 2013 at 10:43 am
Tx,
Sorry but I need format on report like on my sample:
2011 | 2012
______________
prod01 | prod03
prod03 | prod02
September 11, 2013 at 10:23 pm
You can do away with the temporary table.
with t4 as (
select '2011-01-02' as dd, 123 as prodID union
select '2011-01-03' as dd, 345 as prodID union
select '2011-01-04' as dd, 345 as prodID union
select '2011-01-05' as dd, 123 as prodID union
select '2011-01-06' as dd, 123 as prodID union
select '2012-01-02' as dd, 567 as prodID union
select '2012-01-03' as dd, 567 as prodID union
select '2012-01-04' as dd, 567 as prodID union
select '2012-01-04' as dd, 777 as prodID union
select '2012-01-05' as dd, 777 as prodID union
select '2012-01-06' as dd, 888 as prodID
)
SELECT best_11=MAX(CASE WHEN Yr=2011 THEN prodID END)
,best_12=MAX(CASE WHEN Yr=2012 THEN prodID END)
,rn
FROM (
SELECT Yr=DATEPART(year, dd), prodID, Items=COUNT(*)
,rn=RANK() OVER (PARTITION BY DATEPART(year, dd) ORDER BY COUNT(*) DESC)
FROM t4
GROUP BY DATEPART(year, dd), prodID
) a
WHERE rn < 3
GROUP BY rn;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 12, 2013 at 3:29 am
mario17 (9/11/2013)
Tx,Sorry but I need format on report like on my sample:
2011 | 2012
______________
prod01 | prod03
prod03 | prod02
My apologies, I hadnt copied the last part of your query that displayed the data, so had an incomplete solution.
MCITP SQL 2005, MCSA SQL 2012
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply