Average of Quantity for a week

Hi All,

I am trying to find the average of the quantity of items by a weeks timeframe...And if multiple quantities of the item are present on the same day then I need to average out the quantity for that day and sum the total quantity divided by 7..Can you please help here...Following is my DDL..Also I have provided the screenshot of the output needed and the calculation for reference...Also attaching output and calculation for reference...

create table #quantity

(SKUID varchar(50),

quantity int ,

startdate date

)

insert into #quantity values

('A4562','100','2019-06-01'),
('A4562','30','2019-06-01 '),
('E3412','10','2019-06-03'),
('A4562','23','2019-06-03'),
('A4562','43','2019-06-04'),
('E3412','30','2019-06-03'),
('E3412','50','2019-06-05'),
('A4562','79','2019-06-07'),
('E3412','50','2019-06-07'),
('e3412','40','2019-06-03')

hi

i tried to do this
hope it helps

i love feedback thanks
:slight_smile: :slight_smile:

drop create data ...
DROP TABLE #quantity 

go 

CREATE TABLE #quantity 
  ( 
     skuid     VARCHAR(50), 
     quantity  DECIMAL(10, 2), 
     startdate DATE 
  ) 

go 

INSERT INTO #quantity 
VALUES      ('A4562', 
             '100', 
             '2019-06-01'), 
            ('A4562', 
             '30', 
             '2019-06-01 '), 
            ('E3412', 
             '10', 
             '2019-06-03'), 
            ('A4562', 
             '23', 
             '2019-06-03'), 
            ('A4562', 
             '43', 
             '2019-06-04'), 
            ('E3412', 
             '30', 
             '2019-06-03'), 
            ('E3412', 
             '50', 
             '2019-06-05'), 
            ('A4562', 
             '79', 
             '2019-06-07'), 
            ('E3412', 
             '50', 
             '2019-06-07'), 
            ('E3412', 
             '40', 
             '2019-06-03') 

go 

SELECT * 
FROM   #quantity 
ORDER  BY skuid, 
          startdate 

go
SQL ...
;WITH cte 
     AS (SELECT skuid, 
                startdate, 
                Sum(quantity) / Count(*) AS avgd 
         FROM   #quantity 
         GROUP  BY skuid, 
                   startdate 
         HAVING Count(*) > 1), 
     cte1 
     AS (SELECT a.skuid, 
                Sum(quantity) AS avgd 
         FROM   #quantity a 
                JOIN cte b 
                  ON a.startdate <> b.startdate 
                     AND a.skuid = b.skuid 
         GROUP  BY a.skuid) 
SELECT a.skuid, 
       Sum(a.avgd + b.avgd) / 7.0 
FROM   cte a 
       JOIN cte1 b 
         ON a.skuid = b.skuid 
GROUP  BY a.skuid 

go

image

Hi Arunc

No feedback from my
OTHER
Post
:grin::+1:

Thanks Harish!!...This works...Appreciate your help!!!....I will come back to you on the other post....Please give me some time...I am validating the results for that...