t-sql help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

t-sql help

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-06-18 : 14:42:44

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-18 : 15:11:19

This only works for the 1st day of the month. It can be re-written so that an input of '06/30/2009'
will give the same results

declare @temp TABLE (ID int, orderdate [datetime])
declare @date datetime

set @date = '06/01/2009'

INSERT INTO @temp (ID, orderdate)
SELECT 1, '2009-05-27 09:00:00.000'
UNION ALL
SELECT 2, '2009-05-01 11:00:00.000'
UNION ALL
SELECT 3, '2009-05-12 07:00:00.000'
UNION ALL
SELECT 4, '2009-05-02 06:00:00.000'
UNION ALL
SELECT 5, '2008-05-15 11:00:00.000'
UNION ALL
SELECT 6, '2008-05-27 14:00:00.000'
UNION ALL
SELECT 7, '2009-05-30 09:00:00.000'
UNION ALL
SELECT 8, '2008-05-01 09:00:00.000'
UNION ALL
SELECT 9, '2009-06-01 09:00:00.000'
UNION ALL
SELECT 10, '2009-05-27 05:00:00.000'
UNION ALL
SELECT 11, '2009-05-02 07:00:00.000'
UNION ALL
SELECT 12, '2008-06-01 09:00:00.000'
UNION ALL
SELECT 13, '2009-05-27 09:00:00.000'
UNION ALL
SELECT 14, '2008-07-02 09:00:00.000'
UNION ALL
SELECT 15, '2008-08-15 09:00:00.000'
UNION ALL
SELECT 16, '2007-06-05 09:00:00.000'
UNION ALL
SELECT 17, '2007-07-10 09:00:00.000'
UNION ALL
SELECT 18, '2007-12-31 10:00:00.000'
UNION ALL
SELECT 19, '2008-04-01 17:00:00.000'
UNION ALL
SELECT 20, '2008-05-02 19:00:00.000'

SELECT [LastMonth]
= SUM (CASE
WHEN [orderdate] >= dateadd(month,-1,@date)
and [orderdate] < @date
THEN 1
ELSE 0
END)
,[LastMonth_Of_LastYear]
= SUM (CASE
WHEN [orderdate] >= dateadd(month,datediff(month,0,@date) -13,0)
and [orderdate] < dateadd(month,datediff(month,0,@date) -12,0)
THEN 1
ELSE 0
END)
,[Last_12_Months]
= SUM (CASE
WHEN [orderdate] >= dateadd(year,-1,@date)
and [orderdate] < @date
THEN 1
ELSE 0
END)
,[LastYear_Last12Months]=
SUM (CASE
WHEN [orderdate] >= dateadd(year,-2,@date)
and [orderdate] < dateadd(year,-1,@date)
THEN 1
ELSE 0
END)

from @temp

Jim

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-06-18 : 15:29:10

you're almost there, just need to convert your input date to first of that month

DECLARE @DATE DATETIME
SET @DATE = '06/30/2009'

SELECT @DATE = DATEADD(MM, DATEDIFF(MM, 0, @DATE), 0)

SELECT @DATE

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-06-19 : 12:07:38

Thanks for the replies, 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 let me know better way of doing it. 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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 12:26:34

What datatypes are month and year?

Jim

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-06-19 : 13:00:58

Since month and year are passed as: March 2009
so month will be varchar datatype and for year lets go with varchar datatype too.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 13:19:55

DECLARE @yearin varchar(4),@monthin varchar(10), @Date datetime

set @yearin = '2009'
set @monthin = 'March'

set @date= @yearin +'/'+ @monthin +'/'+'1'

select @date

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 20:57:03

quote:
Originally posted by jimf

DECLARE @yearin varchar(4),@monthin varchar(10), @Date datetime

set @yearin = '2009'
set @monthin = 'March'

set @date= @yearin +'/'+ @monthin +'/'+'1'

select @date

Hi Jim,

SQL Server is very forgiving in such matters. It doesn't need to be nearly that complex...

DECLARE @yearin varchar(4),@monthin varchar(10), @Date datetime
set @yearin = '2009'
set @monthin = 'March'

SELECT @Date = @MonthIn+@YearIn
SELECT @Date

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 03:27:49

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
ID INT,
OrderDate DATETIME
)

INSERT @Sample
SELECT 1, '2009-05-27 09:00' UNION ALL
SELECT 2, '2009-05-01 11:00' UNION ALL
SELECT 3, '2009-05-12 07:00' UNION ALL
SELECT 4, '2009-05-02 06:00' UNION ALL
SELECT 5, '2008-05-15 11:00' UNION ALL
SELECT 6, '2008-05-27 14:00' UNION ALL
SELECT 7, '2009-05-30 09:00' UNION ALL
SELECT 8, '2008-05-01 09:00' UNION ALL
SELECT 9, '2009-06-01 09:00' UNION ALL
SELECT 10, '2009-05-27 05:00' UNION ALL
SELECT 11, '2009-05-02 07:00' UNION ALL
SELECT 12, '2008-06-01 09:00' UNION ALL
SELECT 13, '2009-05-27 09:00' UNION ALL
SELECT 14, '2008-07-02 09:00' UNION ALL
SELECT 15, '2008-08-15 09:00' UNION ALL
SELECT 16, '2007-06-05 09:00' UNION ALL
SELECT 17, '2007-07-10 09:00' UNION ALL
SELECT 18, '2007-12-31 10:00' UNION ALL
SELECT 19, '2008-04-01 17:00' UNION ALL
SELECT 20, '2008-05-02 19:00'

-- Simulate user supplied parameters
DECLARE @Month VARCHAR(9),
@Year CHAR(4)

SELECT @Month = 'March',
@Year = '2009'

-- Initialize internal variables
DECLARE @theDate DATETIME

SET @theDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Month + @Year), 0)

-- Show the result
SELECT SUM(CASE WHEN OrderDate >= @theDate AND OrderDate < DATEADD(MONTH, 1, @theDate) THEN 1 ELSE 0 END) AS [Case 1],
SUM(CASE WHEN OrderDate >= DATEADD(MONTH, -12, @theDate) AND OrderDate < DATEADD(MONTH, -11, @theDate) THEN 1 ELSE 0 END) AS [Case 2],
SUM(CASE WHEN OrderDate >= DATEADD(MONTH, -12, @theDate) AND OrderDate < @theDate THEN 1 ELSE 0 END) AS [Case 3],
SUM(CASE WHEN OrderDate >= DATEADD(MONTH, -24, @theDate) AND OrderDate < DATEADD(MONTH, -12, @theDate) THEN 1 ELSE 0 END) AS [Case 4]
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources