MIN MAX and 'end' in one select

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

SQL Server 2005 Forums

Transact-SQL (2005)

MIN MAX and 'end' in one select

Author

Topic

Dargon
Starting Member

26 Posts

Posted - 2008-08-12 : 11:22:30

Hi all,
I am trying to display MIN MAX and 'end' values for the same range.
Here is data sample:

declare @t table(PrdID int,DateIndex int,Qty float)
Insert into @t values(1,1,11)
Insert into @t values(1,2,22)
Insert into @t values(1,3,13)
Insert into @t values(1,4,14)
Insert into @t values(1,5,20)
Insert into @t values(1,6,16)
Insert into @t values(1,7,33)
Insert into @t values(1,8,18)
Insert into @t values(1,9,19)

declare @ranges table(StartR int,EndR int)
Insert into @ranges values(1,4)
Insert into @ranges values(5,10)

So I want to have result like this:

PrdID StartR EndR MinQty MaxQty EndQty

1......1.........4......11.......22.......14
1..... 5........10......16.......33.......19

The query looks pretty simple, but the problem is that @t table is actually result of multiple joins and calculations and if it takes time to run it. I would like to avoid to join @t multiple times. The @ranges table is small, so I am trying to use it to get all needed number in one shoot. Any Ideas hot to do it? I have feeling that it could be done, but I am stuck.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 11:34:58

[code]DECLARE @Products TABLE
(
ProdID INT,
DateIndex INT,
Qty FLOAT
)

INSERT @Products
SELECT 1, 1, 11 UNION ALL
SELECT 1, 2, 22 UNION ALL
SELECT 1, 3, 13 UNION ALL
SELECT 1, 4, 14 UNION ALL
SELECT 1, 5, 20 UNION ALL
SELECT 1, 6, 16 UNION ALL
SELECT 1, 7, 33 UNION ALL
SELECT 1, 8, 18 UNION ALL
SELECT 1, 9, 19

DECLARE @Ranges TABLE
(
StartR INT,
EndR INT
)

INSERT @Ranges
SELECT 1, 4 UNION ALL
SELECT 5, 10

SELECT ProdID,
StartR,
EndR,
MIN(Qty) AS MinQty,
MAX(Qty) AS MaxQty,
MAX(CASE WHEN RecID = 1 THEN Qty ELSE 0 END) AS EndQty
FROM (
SELECT p.ProdID,
r.StartR,
r.EndR,
p.Qty,
ROW_NUMBER() OVER (PARTITION BY p.ProdID, r.StartR ORDER BY p.DateIndex DESC) AS RecID
FROM @Ranges AS r
INNER JOIN @Products AS p ON p.DateIndex BETWEEN r.StartR AND r.EndR
) AS d
GROUP BY ProdID,
StartR,
EndR[/code]

E 12°55'05.25"
N 56°04'39.16"

Dargon
Starting Member

26 Posts

Posted - 2008-08-12 : 11:47:25

Oh, interesting... I never used PARTITION BY before. Just wondering what the performance would be. Thanks, I'll give it a try.

Dargon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources