question

SSGC avatar image
SSGC asked

continue count a value which sort by time

Our Client want to get a list for customer orders, but the order count need sort by time, if customer change the order name, it will count separately. The sample data as below:

  CREATE TABLE #D(ID INT, Name char(2),adddate datetime) 
INSERT into #D 
SELECT		 1,'AA','1-1-2000' 
UNION SELECT 1,'AA','1-2-2000' 
UNION SELECT 1,'CC','1-3-2000' 
UNION SELECT 1,'AA','1-4-2000'
UNION SELECT 1,'AA','1-5-2000' 
UNION SELECT 1,'AA','1-6-2000' 
UNION SELECT 1,'BB','1-7-2000'
UNION SELECT 2,'AA','1-8-2000' 
UNION SELECT 2,'CC','1-9-2000' 
UNION SELECT 2,'AA','1-10-2000'
UNION SELECT 2,'AA','1-11-2000' 
UNION SELECT 2,'BB','1-12-2000' 
UNION SELECT 2,'AA','1-13-2000'

SELECT * FROM #D ORDER BY 3
--DROP TABLE #D

the results will like this:

		SELECT 1 AS ID,'AA' AS Name,2 AS Count,'2000-01-01 00:00:00.000' AS StartDate
UNION	SELECT 1,'CC',1,'2000-01-03 00:00:00.000'
UNION	SELECT 1,'AA',3,'2000-01-04 00:00:00.000'
UNION	SELECT 1,'BB',1,'2000-01-07 00:00:00.000'
UNION	SELECT 2,'AA',1,'2000-01-08 00:00:00.000'
UNION	SELECT 2,'CC',1,'2000-01-09 00:00:00.000'
UNION	SELECT 2,'AA',2,'2000-01-10 00:00:00.000'
UNION	SELECT 2,'BB',1,'2000-01-12 00:00:00.000'
UNION	SELECT 2,'AA',1,'2000-01-13 00:00:00.000'


Could you please help how to query it to get results? Thanks!

sqlfunctionscounts
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I might be missing the point of your query, but the expected output doesn't match the results. Your counts don't match the inputted data, your inputted data will result in one row for each month - was that your intention?

Also, this order by:

ORDER BY 3

Using numbers in the ORDER BY is not recommended, if anything was to change, i.e. table structure, then this would cause unexpected results.

0 Likes 0 ·

Thank you WRBI, the results may add ORDER BY 4, the output should like this:

Can you help me for the query to get this list?

0 Likes 0 ·
untitled.png (22.9 KiB)
Kev Riley avatar image
Kev Riley answered

The wonder of aggregate windowing functions

select distinct
	id,
	name,
	count(*)over(partition by unique_grp order by adddate rows between unbounded preceding and unbounded following) as count,
	min(adddate)over(partition by unique_grp order by adddate) as startdate
from
	(
	select
		id, name, adddate,
		dense_rank()over(order by id, name, grp) as unique_grp
	from (
		select
			id, name, 
			row_number()over(order by adddate)- row_number()over(order by id,name ,adddate) as grp,
			adddate
		from #d
		)table1
	)table2
order by startdate


1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank You very much! Kev Riley! This is great solutions. I use while loop can get it, but i think your code which using window function is much better than while loop. Thanks!

0 Likes 0 ·
SSGC avatar image
SSGC answered

Can someone help me for this query?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

Wow Kev, that's a lesson in a nutshell. Thank you for that!

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, learnd lot

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.