June 18, 2009 at 12:36 pm
I am trying to calculate the following order counts based on the orderdate parameter:
For example if we run on first of every month(06/01/09), I am trying to get the following data:
1. LastMonth: Get counts for all of last month orders(May 2009)
2. LastMonth_Of_LastYear: Get order counts for all of May 2008
3. Last_12_Months: Get counts for 6/1/09 to 6/1/08
4. LastYear_Last12Months: Get counts for 6/1/08 to 6/1/07
Please let me know better way of doing it. Thanks bunch.
Here is the sample data:
Create TABLE #temp(ID int, orderdate [datetime])
INSERT INTO #temp (ID, orderdate)
SELECT 1, '2009-05-27 09:00:00.000'
UNION
SELECT 2, '2009-05-01 11:00:00.000'
UNION
SELECT 3, '2009-05-12 07:00:00.000'
UNION
SELECT 4, '2009-05-02 06:00:00.000'
UNION
SELECT 5, '2008-05-15 11:00:00.000'
UNION
SELECT 6, '2008-05-27 14:00:00.000'
UNION
SELECT 7, '2009-05-30 09:00:00.000'
UNION
SELECT 8, '2008-05-01 09:00:00.000'
UNION
SELECT 9, '2009-06-01 09:00:00.000'
UNION
SELECT 10, '2009-05-27 05:00:00.000'
UNION
SELECT 11, '2009-05-02 07:00:00.000'
UNION
SELECT 12, '2008-06-01 09:00:00.000'
UNION
SELECT 13, '2009-05-27 09:00:00.000'
UNION
SELECT 14, '2008-07-02 09:00:00.000'
UNION
SELECT 15, '2008-08-15 09:00:00.000'
UNION
SELECT 16, '2007-06-05 09:00:00.000'
UNION
SELECT 17, '2007-07-10 09:00:00.000'
UNION
SELECT 18, '2007-12-31 10:00:00.000'
UNION
SELECT 19, '2008-04-01 17:00:00.000'
UNION
SELECT 20, '2008-05-02 19:00:00.000'
SELECT * FROM #temp
June 18, 2009 at 12:54 pm
Does this give you what you are looking for?
Had to add as an attachment.
June 19, 2009 at 2:55 am
SELECT count(*) nomCount ,convert(varchar(4),year(orderdate))+ '-' + convert(varchar(2),month(orderdate)) 'Descr' FROM #temp
group by year(orderdate),month(orderdate)
having (year(orderdate)=2008 and month(orderdate) = 5) or (year(orderdate)=2009 and month(orderdate) = 5)
union
select count(1) as NomCount, 'Get counts for 6/1/09 to 6/1/08' as 'Descr' from #temp where orderdate between '6/1/2008' and '6/1/2009'
union
select count(1) as NomCount,'Get counts for 6/1/08 to 6/1/07' as 'Descr' from #temp where orderdate between '6/1/2007' and '6/1/2008'
June 19, 2009 at 10:01 am
Thanks for the reply, actually instead of passing in the orderdate parameter, the users want to pass in the order month and year as parameter.
When the month and year are passed as parameters then, we should pull the following data for that month and year, for example when March 2009 is passed in as parameter then the following needs to be calculated
for March 2009:
Please note: If nothing is passed into the parameter then parameter should default to the previous month(for example May 2009 as default).
Assuming that March 2009 is passed in as parameter then the following needs to be calculated for March 2009:
1. MonthCount: counts for all the orders of the parameter month and year(March 2009)
2. MonthCount_of_LastYear: counts for all the orders of the parameter month of last year(March 2008)
3. Last_12_Months: counts for 03/01/09 to 03/01/08
4. LastYear_Last12Months: counts for 03/01/08 to 03/01/07
Please help!! Thanks bunch.
Sample data:
Create TABLE #temp(ID int, orderdate [datetime])
INSERT INTO #temp (ID, orderdate)
SELECT 1, '2009-05-27 09:00:00.000'
UNION
SELECT 2, '2009-05-01 11:00:00.000'
UNION
SELECT 3, '2009-05-12 07:00:00.000'
UNION
SELECT 4, '2009-05-02 06:00:00.000'
UNION
SELECT 5, '2008-05-15 11:00:00.000'
UNION
SELECT 6, '2008-05-27 14:00:00.000'
UNION
SELECT 7, '2009-05-30 09:00:00.000'
UNION
SELECT 8, '2008-05-01 09:00:00.000'
UNION
SELECT 9, '2009-06-01 09:00:00.000'
UNION
SELECT 10, '2009-05-27 05:00:00.000'
UNION
SELECT 11, '2009-05-02 07:00:00.000'
UNION
SELECT 12, '2008-06-01 09:00:00.000'
UNION
SELECT 13, '2009-05-27 09:00:00.000'
UNION
SELECT 14, '2008-07-02 09:00:00.000'
UNION
SELECT 15, '2008-08-15 09:00:00.000'
UNION
SELECT 16, '2007-06-05 09:00:00.000'
UNION
SELECT 17, '2007-07-10 09:00:00.000'
UNION
SELECT 18, '2007-12-31 10:00:00.000'
UNION
SELECT 19, '2008-04-01 17:00:00.000'
UNION
SELECT 20, '2008-05-02 19:00:00.000'
SELECT * FROM #temp
June 19, 2009 at 12:06 pm
Please let me know. Thanks!!
June 19, 2009 at 12:27 pm
All you have to do to use my code is figure out how to change "March 2009" to 2009-03-01.
Example, using a yet to be defined function: set @RunDate = ConvertMyDate('March 2009');
Work on that, and you will solve your problem. Any questions, let us know.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply