Group and Sequence quantity based on <=3 or > 3

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)

Group and Sequence quantity based on <=3 or > 3

Author

Topic

tm
Posting Yak Master

160 Posts

Posted - 2009-06-23 : 12:22:19

Hi,

I was wondering if anyone can help in sequencing.

I generated this script as I thought of using CTE but it currently does not give the correct result but I am not sure what is the best way to do this.
I could write a script that checks each records and set the value but I was wondering if there is a better way.

As per the data I would like to group and sequence based on Quantity if <= 3 or > 3. If the quantity changes between the groups I would like to increase the sequence number (please see required result below from sample data).

declare @OrigTable table (date datetime, quantity int)
insert into @OrigTable
select '2009-06-15', 2
union
select '2009-06-14', 1
union
select '2009-06-12', 3
union
select '2009-06-11', 5
union
select '2009-06-09', 1

declare @t table (date datetime, quantity int, row1 int, row2 int)
insert into @t
select date, quantity
, row_number() over (order by date) as row1
, row_number() over (order by date) - 1 as row2
from @OrigTable

; with CTE(date, row1, seq)
as
(
select max(date) as date, row1, 1 as [seq]
from @t group by date, row1
union all
Select t.date, t.row1, seq+1
from @t t
inner join
CTE on t.row2 = CTE.row1
where
)
select date, seq from CTE
order by date desc

Result from above script:
date,seq
2009-06-15 00:00:00.000,1
2009-06-15 00:00:00.000,2
2009-06-15 00:00:00.000,3
2009-06-15 00:00:00.000,4
2009-06-15 00:00:00.000,5
2009-06-14 00:00:00.000,4
2009-06-14 00:00:00.000,3
2009-06-14 00:00:00.000,2
2009-06-14 00:00:00.000,1
2009-06-12 00:00:00.000,1
2009-06-12 00:00:00.000,2
2009-06-12 00:00:00.000,3
2009-06-11 00:00:00.000,2
2009-06-11 00:00:00.000,1
2009-06-09 00:00:00.000,1

(15 row(s) affected)

Required result:
2009-06-15 00:00:00.000,1
2009-06-14 00:00:00.000,1
2009-06-12 00:00:00.000,1
2009-06-11 00:00:00.000,2
2009-06-09 00:00:00.000,3

thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-23 : 13:55:30

I have this:

select 
[date],
row_number() over (partition by qty+recnum order by [date] desc) as seq
from (select row_number() over (partition by case when quantity > 3 then 1 else 0 end order by date desc) as recnum,[date], quantity, case when quantity > 3 then 1 else 0 end as qty from @OrigTable)dt
order by [date] desc

But not a clue how to start over with value 3 after the change of qty-groups.

Maybe this is an approach for someone...

No, you're never too old to Yak'n'Roll if you're too young to die.

tm
Posting Yak Master

160 Posts

Posted - 2009-06-23 : 15:08:30

Thank you very much webfred. I will try a few things with your script.
If anyone else could shed some light on the issue that would be most appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-23 : 15:19:40

There are probably better ways to do this, but I was having a mental block on how to do it in a nice set-based way. I also added some more data becasue I had a super simple solution that worked for your sample data, but probably wouldn't have worked on real data. At any rate, here is something that seems to be working:

declare @OrigTable table (date datetime, quantity int, Seq INT)
insert into @OrigTable
select '2009-06-15', 2, 1
union
select '2009-06-14', 1, 1
union
select '2009-06-12', 3, 1
union
select '2009-06-11', 5, 1
union
select '2009-06-09', 1, 1
union
select '2009-05-09', 11, 1
union
select '2009-05-06', 11, 1
union
select '2009-05-05', 11, 1
union
select '2009-04-09', 1, 1

-- Create a new table to hold all the boundry dates
DECLARE @Table TABLE (date DATETIME)

INSERT 
	@Table
SELECT 
	A.date
FROM
	(
		SELECT 
			*,
			CASE WHEN quantity <= 3 THEN 0 ELSE 1 END AS Part,
			ROW_NUMBER() OVER (ORDER BY date DESC) AS RowNum
		FROM @OrigTable
	) AS A
	LEFT JOIN
	(
		SELECT 
			*,
			CASE WHEN quantity <= 3 THEN 0 ELSE 1 END AS Part,
			ROW_NUMBER() OVER (ORDER BY date DESC) AS RowNum
		FROM @OrigTable
	) AS B
	ON A.RowNum + 1 = B.RowNum
	WHERE
		A.Part <> B.Part
		OR B.Part IS NULL

-- Loop through each boundry date updating everything less than that date
DECLARE @Date DATETIME
SELECT @Date = MIN(date) FROM @Table

WHILE @Date IS NOT NULL
BEGIN
	UPDATE @OrigTable
	SET Seq = Seq + 1
	WHERE date < @Date
	
	SELECT @Date = MIN(date) FROM @Table WHERE date > @Date

END

SELECT *
FROM @OrigTable
ORDER BY Date DESC

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-06-23 : 15:20:27

your question is not very clear to me, whats the logic to be followed for seq in your resultset?

tm
Posting Yak Master

160 Posts

Posted - 2009-06-23 : 15:40:31

Thank you very much Lamprey!! That is great. Exactly what I wanted. Much much better than what I was going to do, looping through each record. Thank you again!

rohitkumar .. As to Lamprey solution I wanted to group <=3 and if it change to >3 then I wanted to increase the sequence and if it changes back to <=3 increase the sequence again. Sorry to be confusing but Lamprey got the solution and it's great.

As to the result from Lamprey (exactly what I wanted)..
date,quantity,Seq
2009-06-15 00:00:00.000,2,1
2009-06-14 00:00:00.000,1,1
2009-06-12 00:00:00.000,3,1
2009-06-11 00:00:00.000,5,2
2009-06-09 00:00:00.000,1,3
2009-05-09 00:00:00.000,11,4
2009-05-06 00:00:00.000,11,4
2009-05-05 00:00:00.000,11,4
2009-04-09 00:00:00.000,1,5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources