SQLTeam.com | Weblogs | Forums

Rolling count each week - urgent help!


#1

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!


#2

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;

image


#3

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!


#4

If you post the sample data as I did in my running total I'll take a stab at it.


#5

Hi @jotorre_riversidedpss.org

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!!


#6

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;

image