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