Need Qry to calculate avg from same day prior wks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need Qry to calculate avg from same day prior wks

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2010-08-01 : 11:51:23

I have a table that stores daily transactions by Location. Every Day each location must log into the Database and enter their totals. I want to pull reports that sum up the Transactions Trended by Day by Location. Here is the tricky part if a Location did not enter a Transaction for a given day (Null) I want the query to grab the last three entries on the same day from the prior 3 weeks and calculate an average as a place holder for the missing day.

Example:
The site enter the following transactions

Dallas,6/5/2010,$45,monday
Dallas,6/6/2010,$35,tuesday
Dallas,6/7/2010,$50,wednesday
Dallas,6/8/2010,$25,thursday
Dallas,6/9/2010,$56,friday
Dallas,6/12/2010,$45,monday
Dallas,6/13/2010,$35,tuesday
Dallas,6/14/2010,$25,wedensday
Dallas,6/15/2010,$25,thursday
Dallas,6/16/2010,$56,friday
Dallas,6/19/2010,$45,monday
Dallas,6/20/2010,$35,tuesday
Dallas,6/21/2010,$37.5,wedensday day was missing so query needs to take prior 2 or 3 wedensdays and calculated avg
Dallas,6/22/2010,$25,thursday
Dallas,6/23/2010,$56,friday

Brian

PavanKK
Starting Member

32 Posts

Posted - 2010-08-02 : 04:04:47

Brain,

following will insert missing date record with average of previous 3 weeks.
Let me know if this is the functionality you are looking for.

---------

DECLARE @Location TABLE
(
Location VARCHAR(50),
TrDate DATETIME,
Amount NUMERIC(18,2),
TrDay VARCHAR(10)
)
INSERT INTO @Location VALUES
('Dallas','6/7/2010','45','monday'),
('Dallas','6/8/2010','35','tuesday'),
('Dallas','6/9/2010','50','wednesday'),
('Dallas','6/10/2010','25','thursday'),
('Dallas','6/11/2010','56','friday'),
('Dallas','6/14/2010','45','monday'),
('Dallas','6/15/2010','35','tuesday'),
('Dallas','6/16/2010','25','wednesday'),
('Dallas','6/17/2010','25','thursday'),
('Dallas','6/18/2010','56','friday'),
('Dallas','6/21/2010','45','monday'),
('Dallas','6/22/2010','35','tuesday'),
--('Dallas','6/21/2010',NULL,'wednesday'), --day was missing so query needs to take prior 2 or 3 wedensdays and calculated avg
('Dallas','6/24/2010','25','thursday'),
('Dallas','6/25/2010','56','friday')

;WITH cteAll AS
(
SELECT Location,MIN(TrDate) MinDate,MAX(TrDate) MaxDate
FROM @Location
GROUP BY Location

UNION ALL

SELECT C.Location,DATEADD(DD,1,C.MinDate),C.MaxDate
FROM cteAll C
WHERE DATEADD(DD,1,C.MinDate) <= C.MaxDate
)

INSERT INTO @Location
SELECT L.Location,C.MinDate,AVG(L.Amount) Amount,L.TrDay
FROM @Location L
CROSS JOIN cteAll C
WHERE C.Location = L.Location
AND DATEPART(DW,C.MinDate) = DATEPART(DW,L.TrDate)
AND DATEDIFF(WW,L.TrDate,C.MinDate) BETWEEN 1 AND 3
AND NOT EXISTS ( SELECT 1 FROM @Location WHERE Location = C.Location AND TrDate = C.MinDate )
GROUP BY L.Location,L.TrDay,C.MinDate

SELECT * FROM @Location
-------------

KK :)

bconner
Starting Member

48 Posts

Posted - 2010-08-02 : 14:10:21

Hi Pavenkk
Actually instead of updating table I just want the Average to be derived in a query because I want the site to eventually go back and update missing numbers...

Brian

PavanKK
Starting Member

32 Posts

Posted - 2010-08-03 : 01:23:46

Hi Brain,

If the averages are needs to be displayed in resultset instead of updating table you can use the following.

------------

;WITH cteAll AS
(
SELECT Location,MIN(TrDate) MinDate,MAX(TrDate) MaxDate
FROM @Location
GROUP BY Location

UNION ALL

SELECT C.Location,DATEADD(DD,1,C.MinDate),C.MaxDate
FROM cteAll C
WHERE DATEADD(DD,1,C.MinDate) <= C.MaxDate
)

SELECT Location,TrDate,Amount,TrDay
FROM @Location

UNION ALL

SELECT L.Location,C.MinDate,AVG(L.Amount) Amount,L.TrDay
FROM @Location L
CROSS JOIN cteAll C
WHERE DATEPART(DW,C.MinDate) = DATEPART(DW,L.TrDate)
AND C.Location = L.Location
AND DATEDIFF(WW,L.TrDate,C.MinDate) BETWEEN 1 AND 3
AND NOT EXISTS ( SELECT 1 FROM @Location WHERE Location = C.Location AND TrDate = C.MinDate )
GROUP BY L.Location,L.TrDay,C.MinDate
ORDER BY Location,TrDate

------------

KK :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources