Hi,
I need help calculating rolling count of 10 for each weekly and I have no clue on how to achieve this.
And i want to get this for last five weeks.
Here is an example:
Thanks in Advance!
This works in SQL Server 2012+
declare @t table(Weeks int, IDs int);
insert @t values(1,5),(2,5),(3,0),(4,8),(5, 5);
select Weeks, IDs, sum(IDs)
over(order by weeks
rows between unbounded preceding
and current row) RunningTotal
from @t;
1 Like
Hi Jotorre,
Thanks for the reply,
But this is running total, what I am looking for is a rolling count where it should consider only 10 ID's each week.
As i want it for each part and each week, i think i need to group by part and partition by week?
I need to get the week number based on the date.
And every week I want to consider latest 10 ID's for each part.
if count of ID's in a week is > 10 , then include the last 10 ID's only
if count of ID's in a week is < 10 , then include ID's from the previous week so the count becomes 10
if count of ID's in a week = 10 , the include those 10 for that week
I only want to consider 10 ID's for each week for each part.
Thanks!
If you post the sample data as I did in my running total I'll take a stab at it.
Thanks a lot for your help.
I put some data together
declare @t table(Part varchar(25), IDs varchar(25), date_field datetime);
insert @t values('Part1','ID_P1_1','2018-05-11 11:56:18'),('Part1','ID_P1_2','2018-05-11 11:58:20'),('Part1','ID_P1_3','2018-05-10 10:20:20'),('Part1','ID_P1_4','2018-05-08 10:21:00'),('Part1','ID_P1_5','2018-05-08 10:09:20'),
('Part1','ID_P1_6','2018-05-07 08:26:00'),('Part1','ID_P1_7','2018-05-07 09:22:22'),('Part1','ID_P1_8','2018-05-07 09:25:22'),
('Part1','ID_P1_9','2018-05-04 07:15:20'), ('Part1','ID_P1_10','2018-05-04 07:26:12'),('Part1','ID_P1_11','2018-05-03 07:19:00'),('Part1','ID_P1_12','2018-05-03 08:10:00'),('Part1','ID_P1_13','2018-05-03 08:15:26'),
('Part1','ID_P1_14','2018-05-01 11:11:24'),('Part1','ID_P1_15','2018-05-01 11:26:12'),('Part1','ID_P1_16','2018-05-01 11:28:05'),
('Part2','ID_P2_1','2018-05-11 07:10:23'),('Part2','ID_P2_2','2018-05-011 07:20:10'), ('Part2','ID_P2_3','2018-05-10 07:25:07'),('Part2','ID_P2_4','2018-05-10 08:01:00'),
('Part2','ID_P2_5','2018-05-04 08:21:14'),('Part2','ID_P2_6','2018-05-04 09:12:14'),('Part2','ID_P2_7','2018-05-04 09:20:14'),('Part2','ID_P2_8','2018-05-04 09:21:19'),('Part2','ID_P2_9','2018-05-04 09:24:10'),('Part2','ID_P2_10','2018-05-04 09:29:14');
select * from @t
so this is how my data looks, i have unique ID's for each part which we receive on different times each day.
I want to group ID's by by weeks for each part
And every week I want to consider only the latest 10 ID's for each part.
if count of ID's in a week is > 10 , then include the last 10 ID's only
if count of ID's in a week is < 10 , then include some ID's from the previous week so the count becomes 10
if count of ID's in a week = 10 , the include those 10 for that week
Thank you!!
Is this what you're after?
DECLARE @t table
(
Part varchar(25)
, IDs varchar(25)
, date_field datetime
) ;
INSERT
@t
VALUES
(
'Part1'
, 'ID_P1_1'
, '2018-05-11 11:56:18'
)
, (
'Part1'
, 'ID_P1_2'
, '2018-05-11 11:58:20'
)
, (
'Part1'
, 'ID_P1_3'
, '2018-05-10 10:20:20'
)
, (
'Part1'
, 'ID_P1_4'
, '2018-05-08 10:21:00'
)
, (
'Part1'
, 'ID_P1_5'
, '2018-05-08 10:09:20'
)
, (
'Part1'
, 'ID_P1_6'
, '2018-05-07 08:26:00'
)
, (
'Part1'
, 'ID_P1_7'
, '2018-05-07 09:22:22'
)
, (
'Part1'
, 'ID_P1_8'
, '2018-05-07 09:25:22'
)
, (
'Part1'
, 'ID_P1_9'
, '2018-05-04 07:15:20'
)
, (
'Part1'
, 'ID_P1_10'
, '2018-05-04 07:26:12'
)
, (
'Part1'
, 'ID_P1_11'
, '2018-05-03 07:19:00'
)
, (
'Part1'
, 'ID_P1_12'
, '2018-05-03 08:10:00'
)
, (
'Part1'
, 'ID_P1_13'
, '2018-05-03 08:15:26'
)
, (
'Part1'
, 'ID_P1_14'
, '2018-05-01 11:11:24'
)
, (
'Part1'
, 'ID_P1_15'
, '2018-05-01 11:26:12'
)
, (
'Part1'
, 'ID_P1_16'
, '2018-05-01 11:28:05'
)
, (
'Part2'
, 'ID_P2_1'
, '2018-05-11 07:10:23'
)
, (
'Part2'
, 'ID_P2_2'
, '2018-05-011 07:20:10'
)
, (
'Part2'
, 'ID_P2_3'
, '2018-05-10 07:25:07'
)
, (
'Part2'
, 'ID_P2_4'
, '2018-05-10 08:01:00'
)
, (
'Part2'
, 'ID_P2_5'
, '2018-05-04 08:21:14'
)
, (
'Part2'
, 'ID_P2_6'
, '2018-05-04 09:12:14'
)
, (
'Part2'
, 'ID_P2_7'
, '2018-05-04 09:20:14'
)
, (
'Part2'
, 'ID_P2_8'
, '2018-05-04 09:21:19'
)
, (
'Part2'
, 'ID_P2_9'
, '2018-05-04 09:24:10'
)
, (
'Part2'
, 'ID_P2_10'
, '2018-05-04 09:29:14'
) ;
WITH
YrWk AS
(
SELECT 100*Year(t.date_field)+DatePart(ww, t.date_field) YrWk
FROM @t t GROUP BY 100*Year(t.date_field)+DatePart(ww, t.date_field)
),
OutputSet AS
(
SELECT
t.Part
, t.IDs
, t.date_field
, 100*Year(t.date_field)+DatePart(ww, t.date_field) YrWk
, Row_Number() OVER (PARTITION BY 100*Year(t.date_field)+DatePart(ww, t.date_field) ORDER BY t.date_field DESC) Rn
FROM @t t
)
SELECT
o.IDs
, y.YrWk
FROM OutputSet o
JOIN
YrWk y ON o.YrWk = y.YrWk
WHERE o.Rn<11
UNION ALL
SELECT
'Total: '+Cast(Count(o.IDs) AS varchar(20)) Count
, y.YrWk
FROM OutputSet o
JOIN
YrWk y ON o.YrWk = y.YrWk
WHERE o.Rn<11
GROUP BY y.YrWk;