June 19, 2009 at 7:47 am
I am trying to figure out a set-based method of trying to calculate the average of the count of records in 3-week increments. The average should be the CntSales field and grouped by 3 consecutive weeks of the "Archived" date. Just a quick note about the "archived" date and the DatePart/Max/Convert methods. There can and are typically two different grouping of an Archived date... one with completed Sales (Invoice done) that was archived at a specific date/time, then the Sales pending archived a couple of minutes later. A Sales person may post their data two times in a week...
select count(Invoices) as CntSales, Min(SalesDate) as Min, Max(SalesDate) as Max,
DATEPART(ww,Archived) as WorkWeek, MAX(CONVERT(varchar,Archived,101)) as 'Last Archived'
from dbo.SalesHistory
WHERE Archived > '3/1/09'
GROUP BY DATEPART(ww,Archived)
ORDER BY 'Last Archived'
Here is the data from the query:
CntSalesMinMaxWorkWeekLast Archived
305442009-01-06 00:00:002009-02-28 00:00:001003/04/2009
309982009-01-06 00:00:002009-03-07 00:00:001103/10/2009
391772009-01-19 00:00:002009-03-14 00:00:001203/18/2009
116542008-12-30 00:00:002009-03-23 00:00:001303/25/2009
572132009-01-02 00:00:002009-03-27 00:00:001404/01/2009
204712009-02-18 00:00:002009-04-06 00:00:001504/07/2009
73642009-01-23 00:00:002009-04-10 00:00:001604/15/2009
368592009-02-24 00:00:002009-04-17 00:00:001704/22/2009
251902009-02-27 00:00:002009-04-24 00:00:001804/28/2009
397952009-01-23 00:00:002009-05-04 00:00:001905/06/2009
156452009-03-10 00:00:002009-05-01 00:00:002005/12/2009
280022009-03-26 00:00:002009-05-11 00:00:002105/19/2009
48562009-04-14 00:00:002009-05-12 00:00:002205/29/2009
682009-04-27 00:00:002009-05-19 00:00:002306/03/2009
128NULLNULL2406/10/2009
Ignore the Min and Max NULL values in the last dataset... we are trying to figure out what happened with the last archive and why the values are nulls.
Since I was on a time crunch, I had to run the query, put the data into Excel and send it to the Managers... but I can imagine there will be weekly requests for this data from now on, thus the desire to have a SQL solution that is more permanent.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
June 19, 2009 at 9:23 am
To me it's not clear what would be the criteria for building the group of three weeks: Does it start Jan. 1st or starting from this week backward?
Anyhow, I would use GROUP BY DATEPART(wk,[Date])/3, maybe slightly modified.
If you need further information please provide sample data in a ready to use format as describe in the link in my signature.
June 19, 2009 at 9:51 am
Easy enough to generate the data... as for the "beginning" date, it would be a rolling three-month time period from todays date....
WHERE Archived > ''+GetDate()-90+''
DECLARE @Hist TABLE (Seq INT IDENTITY(1,1), CntSales int, MinDate smalldatetime, MaxDate smalldatetime, WorkWeek int, Archived varchar(10));
insert into @Hist
SELECT '30544', 'Jan 6 2009 12:00AM', 'Feb 28 2009 12:00AM', '10', '03/04/2009' UNION ALL
SELECT '30998', 'Jan 6 2009 12:00AM', 'Mar 7 2009 12:00AM', '11', '03/10/2009' UNION ALL
SELECT '39177', 'Jan 19 2009 12:00AM', 'Mar 14 2009 12:00AM', '12', '03/18/2009' UNION ALL
SELECT '11654', 'Dec 30 2008 12:00AM', 'Mar 23 2009 12:00AM', '13', '03/25/2009' UNION ALL
SELECT '57213', 'Jan 2 2009 12:00AM', 'Mar 27 2009 12:00AM', '14', '04/01/2009' UNION ALL
SELECT '20471', 'Feb 18 2009 12:00AM', 'Apr 6 2009 12:00AM', '15', '04/07/2009' UNION ALL
SELECT '7364', 'Jan 23 2009 12:00AM', 'Apr 10 2009 12:00AM', '16', '04/15/2009' UNION ALL
SELECT '36859', 'Feb 24 2009 12:00AM', 'Apr 17 2009 12:00AM', '17', '04/22/2009' UNION ALL
SELECT '25190', 'Feb 27 2009 12:00AM', 'Apr 24 2009 12:00AM', '18', '04/28/2009' UNION ALL
SELECT '39795', 'Jan 23 2009 12:00AM', 'May 4 2009 12:00AM', '19', '05/06/2009' UNION ALL
SELECT '15645', 'Mar 10 2009 12:00AM', 'May 1 2009 12:00AM', '20', '05/12/2009' UNION ALL
SELECT '28002', 'Mar 26 2009 12:00AM', 'May 11 2009 12:00AM', '21', '05/19/2009' UNION ALL
SELECT '4856', 'Apr 14 2009 12:00AM', 'May 12 2009 12:00AM', '22', '05/29/2009' UNION ALL
SELECT '68', 'Apr 27 2009 12:00AM', 'May 19 2009 12:00AM', '23', '06/03/2009' UNION ALL
SELECT '128',NULL,NULL,'24','06/10/2009'
SELECT *
from @Hist
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
June 19, 2009 at 11:05 am
Thanx for the sample data.
What would be your expected result?
June 19, 2009 at 11:37 am
I was going to try to get the MAX(CONVERT(varchar,Archived,101)) as the Date and the 3-week average.
I could do this programmatically (either via cursor or cursor-less), but that is a "rbar" solution... I was hoping to develop something much faster. I played around a bit with CTE(s) and row_number()s but I have not figured out a good way to group three weeks together for one average, thus the post.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
June 19, 2009 at 11:43 am
For my example data, the final result would look as follows:
ArchivedDate 3 Week Average
3/18/200933,573
4/7/2009 29,779
4/28/200923,138
5/19/200927,814
6/10/20091,684
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
June 19, 2009 at 12:30 pm
Hi,
the following code uses a CTE to select the first day of the range.
This first day then is applied to the table and used for grouping.
DECLARE @Hist TABLE (Seq INT IDENTITY(1,1), CntSales int, MinDate smalldatetime, MaxDate smalldatetime, WorkWeek int, Archived varchar(10));
insert into @Hist
SELECT '30544', 'Jan 6 2009 12:00AM', 'Feb 28 2009 12:00AM', '10', '03/04/2009' UNION ALL
SELECT '30998', 'Jan 6 2009 12:00AM', 'Mar 7 2009 12:00AM', '11', '03/10/2009' UNION ALL
SELECT '39177', 'Jan 19 2009 12:00AM', 'Mar 14 2009 12:00AM', '12', '03/18/2009' UNION ALL
SELECT '11654', 'Dec 30 2008 12:00AM', 'Mar 23 2009 12:00AM', '13', '03/25/2009' UNION ALL
SELECT '57213', 'Jan 2 2009 12:00AM', 'Mar 27 2009 12:00AM', '14', '04/01/2009' UNION ALL
SELECT '20471', 'Feb 18 2009 12:00AM', 'Apr 6 2009 12:00AM', '15', '04/07/2009' UNION ALL
SELECT '7364', 'Jan 23 2009 12:00AM', 'Apr 10 2009 12:00AM', '16', '04/15/2009' UNION ALL
SELECT '36859', 'Feb 24 2009 12:00AM', 'Apr 17 2009 12:00AM', '17', '04/22/2009' UNION ALL
SELECT '25190', 'Feb 27 2009 12:00AM', 'Apr 24 2009 12:00AM', '18', '04/28/2009' UNION ALL
SELECT '39795', 'Jan 23 2009 12:00AM', 'May 4 2009 12:00AM', '19', '05/06/2009' UNION ALL
SELECT '15645', 'Mar 10 2009 12:00AM', 'May 1 2009 12:00AM', '20', '05/12/2009' UNION ALL
SELECT '28002', 'Mar 26 2009 12:00AM', 'May 11 2009 12:00AM', '21', '05/19/2009' UNION ALL
SELECT '4856', 'Apr 14 2009 12:00AM', 'May 12 2009 12:00AM', '22', '05/29/2009' UNION ALL
SELECT '68', 'Apr 27 2009 12:00AM', 'May 19 2009 12:00AM', '23', '06/03/2009' UNION ALL
SELECT '128',NULL,NULL,'24','06/10/2009'
;WITH CTE_Min_wk
AS
(
SELECT MIN(Archived) AS ArchMin from @Hist
)
SELECT MAX(Archived) AS ArchivedDate, AVG(CntSales) AS [3 Week Average]
FROM @Hist CROSS APPLY CTE_Min_wk
GROUP BY DATEDIFF(wk,CTE_Min_wk.ArchMin ,Archived)/3
/* result set
ArchivedDate 3 Week Average
03/18/200933573
04/07/200929779
04/28/200923137
05/19/200927814
06/10/20091684*/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply