continue count a value which sort by time

Question

question

SSGC asked Mar 12, '19

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!

sql functions counts

2

10 |1200

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

WRBI commented · Mar 12, 2019 at 12:07 PM

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 ·

Show more comments

Answer 1 · 2019-03-13T15:37:44Z

Kev Riley answered Mar 13, '19

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

10 |1200

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

SSGC commented · Mar 13, 2019 at 05:02 PM

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 ·

Answer 2 · 2019-03-13T14:51:14Z

SSGC answered Mar 13, '19

Can someone help me for this query?

10 |1200

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

Answer 3 · 2019-03-14T13:54:42Z

Jon Crawford answered Mar 14, '19

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

1

10 |1200

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

SSGC commented · Mar 14, 2019 at 03:28 PM

Yes, learnd lot

0 ·

question