Hate cursors? Are you a genius query optimizer?
-
OK, I am having a block on this and can't come up with the t-sql to do the following without a cursor. The performance is horrible, so any help would be appreciated:
Example table:
Location int
Item int
SalesWeek int
Qty int
PK is location, item, salesweek.
Tables contains records for many locations for many weeks for many items. I need to remove the top 2 entries for each location, item, salesweek combination ranked by qty. Is there an obvious way to do this that I am missing?
-
Please post the actual ddl of the table, some sample data along with the needed results and we'll build the query for you.
-
Also, sample data and expected output is very helpful
--------------------
Colt 45 - the original point and click interface
-
Maybe I'm not clear in my requests "... some sample data along with the needed results and..."
-
I think Phil stopped reading when he reached.."actual ddl of the table..." - just proves what you said sometime back about people not reading through everything -
remi - please come back!!!
**ASCII stupid question, get a stupid ANSI !!!** -
As the BBS say : Never Gone
. -
Bulletin Board System ?!?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Backstreet Boys
... not that actually like them
. -
Maybe
SELECT a.Location,a.Item,a.SalesWeek,a.Qty
FROM [Table] a
WHERE EXISTS (SELECT 1
FROM (SELECT TOP 2 x.Location,x.Item,x.SalesWeek,x.Qty
FROM [Table] x
WHERE x.Location=a.Location AND x.Item=a.Item AND x.SalesWeek=a.SalesWeek
ORDER BY x.Qty DESC ) y
WHERE y.Location=a.Location AND y.Item=a.Item AND y.SalesWeek=a.SalesWeek AND y.Qty=a.Qty)
Guess at poor performance though
If volume allows maybe better to create a temp table with an IDENTITY column to use as a key to simplify the above query
Also what about combinations with more than 1 or 2 rows with the same qty ???
Far away is close at hand in the images of elsewhere.
Anon. -
Another alternative
CREATE TABLE #temp (Location int,Item int,SalesWeek int,Qty int)
INSERT INTO #temp (Location,Item,SalesWeek,Qty)
SELECT Location,Item,SalesWeek,MAX(Qty) FROM [#Table]
GROUP BY Location,Item,SalesWeek
SELECT a.Location,a.Item,a.SalesWeek,a.Qty FROM #temp a
UNION
SELECT b.Location,b.Item,b.SalesWeek,MAX(b.Qty)
FROM [#Table] b
INNER JOIN #temp c
ON c.Location=b.Location
AND c.Item=b.Item
AND c.SalesWeek=b.SalesWeek
AND c.Qty > b.Qty
GROUP BY b.Location,b.Item,b.SalesWeek
DROP TABLE #temp
Add index(es) to improve optimization
Far away is close at hand in the images of elsewhere.
Anon. -
Is this a catch or a bad request ???
"PK is location, item, salesweek.
Tables contains records for many locations for many weeks for many items. I need to remove the top 2 entries for each location, item, salesweek combination ranked by qty. Is there an obvious way to do this that I am missing?"
According to your SPECS you CAN'T have two
rows with same (loc,item,salesweek) and diff qty BUT you are requesting the top 2 entries for each COMB(loc,item,salesweek) ?
I missed something here?
Kindest Regards,Vasc
-
Oooops!
Missed that, glad someone noticed
Far away is close at hand in the images of elsewhere.
Anon. -
Yup nice catch.
Easy fix then :
Truncate table TableName
-
Sorry - grouping is by location and item. I need to remove the 2 weeks with the highest sales qty for each location/item combination.
-
declare @t table
(Location int,
Item int,
SalesWeek int,
Qty int)
insert into @t
select 1,1,1,1 union all
select 1,1,2,6 union all
select 1,1,3,5 union all
select 1,1,4,2 union all
select 1,1,5,7 union all
select 2,1,1,4 union all
select 2,1,2,1 union all
select 2,1,3,1 union all
select 2,1,4,1 union all
select 2,1,5,2 union all
select 2,2,1,3 union all
select 2,2,2,6
select * from @t
--select *
delete a
from @t a
where
1 in
(select top 2
case when a.salesweek=salesweek then 1 else 0 end col
from @t where a.location=location and a.item=item
order by qty desc)
select * from @t
Kindest Regards,Vasc
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply