# 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

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

Hi Arunc

No feedback from my
OTHER
Post

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