Query back 24 months from current date

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

General SQL Server Forums

New to SQL Server Programming

Query back 24 months from current date

Author

Topic

zhel04
Starting Member

38 Posts

Posted - 2011-03-22 : 04:17:10

Hi. I Badly need help. How can i get the 24 months from the current date? Need to have another field to see months 1-24.

Thanks in advance. God Speed.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-03-22 : 04:46:10

Please provide more information on your data structure and some example data, along with expected result set.

Just for the date though, try the DATEADD() function.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-22 : 05:40:32

Are you looking for something like -


SELECT	DATEADD(MONTH, 1*number , GETDATE()), 
	DATENAME(MONTH, DATEADD(MONTH, 1*number , GETDATE()))  
FROM	master.dbo.spt_values
WHERE	number BETWEEN 1 AND 24 AND type = 'P'

Vaibhav T

If I cant go back, I want to go fast...

zhel04
Starting Member

38 Posts

Posted - 2011-03-22 : 05:44:05

Thanks for the reply.. :) Yes.. however, the reult should be the previous month from the current date example if the current date = 03/11
Result: 03/11, 02/11, 01/11, 12/10, 11/10.....

quote:
Originally posted by vaibhavktiwari83

Are you looking for something like -

SELECT DATEADD(MONTH, 1*number , GETDATE()),
DATENAME(MONTH, DATEADD(MONTH, 1*number , GETDATE()))
FROM master.dbo.spt_values
WHERE number BETWEEN 1 AND 24 AND type = 'P'

Vaibhav T

If I cant go back, I want to go fast...

matty
Posting Yak Master

161 Posts

Posted - 2011-03-22 : 05:49:53

quote:
Originally posted by zhel04

Thanks for the reply.. :) Yes.. however, the reult should be the previous month from the current date example if the current date = 03/11
Result: 03/11, 02/11, 01/11, 12/10, 11/10.....

quote:
Originally posted by vaibhavktiwari83

Are you looking for something like -

SELECT DATEADD(MONTH, -1*number , GETDATE()),
DATENAME(MONTH, DATEADD(MONTH, -1*number , GETDATE()))
FROM master.dbo.spt_values
WHERE number BETWEEN 0 AND 23 AND type = 'P'

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-22 : 05:50:35

For this just put -1 in place of 1. See the red part.

quote:
Originally posted by zhel04

Thanks for the reply.. :) Yes.. however, the reult should be the previous month from the current date example if the current date = 03/11
Result: 03/11, 02/11, 01/11, 12/10, 11/10.....

quote:
Originally posted by vaibhavktiwari83

Are you looking for something like -

SELECT DATEADD(MONTH, -1*number , GETDATE()),
DATENAME(MONTH, DATEADD(MONTH, 1*number , GETDATE()))
FROM master.dbo.spt_values
WHERE number BETWEEN 1 AND 24 AND type = 'P'

Vaibhav T

If I cant go back, I want to go fast...

Vaibhav T

If I cant go back, I want to go fast...

zhel04
Starting Member

38 Posts

Posted - 2011-03-22 : 06:06:17

Oh thanks so much.. but I have another problem how will i include this in my query and I need to transpose this to columns.

quote:
Originally posted by vaibhavktiwari83

For this just put -1 in place of 1. See the red part.

quote:
Originally posted by zhel04

Thanks for the reply.. :) Yes.. however, the reult should be the previous month from the current date example if the current date = 03/11
Result: 03/11, 02/11, 01/11, 12/10, 11/10.....

quote:
Originally posted by vaibhavktiwari83

Are you looking for something like -

SELECT DATEADD(MONTH, -1*number , GETDATE()),
DATENAME(MONTH, DATEADD(MONTH, 1*number , GETDATE()))
FROM master.dbo.spt_values
WHERE number BETWEEN 1 AND 24 AND type = 'P'

Vaibhav T

If I cant go back, I want to go fast...

Vaibhav T

If I cant go back, I want to go fast...

zhel04
Starting Member

38 Posts

Posted - 2011-03-22 : 06:07:26

hey matty thanks for the reply.. :) please see my reply to vaibhavktiwari83.

quote:
Originally posted by matty

quote:
Originally posted by zhel04

Thanks for the reply.. :) Yes.. however, the reult should be the previous month from the current date example if the current date = 03/11
Result: 03/11, 02/11, 01/11, 12/10, 11/10.....

quote:
Originally posted by vaibhavktiwari83

Are you looking for something like -

SELECT DATEADD(MONTH, -1*number , GETDATE()),
DATENAME(MONTH, DATEADD(MONTH, -1*number , GETDATE()))
FROM master.dbo.spt_values
WHERE number BETWEEN 0 AND 23 AND type = 'P'

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-22 : 06:08:20

Please post table structure, sample data and expected output.

Vaibhav T

If I cant go back, I want to go fast...

zhel04
Starting Member

38 Posts

Posted - 2011-03-22 : 06:31:53

Here's a sample data:

Date Type Code Qty
12/22/2009 A 23546 8
2/22/2010 A 23546 6
7/22/2010 A 52453 8
3/22/2010 A 52453 5
1/22/2010 A 52453 9
12/22/2009 A 38675 12
9/22/2009 A 96878 1
7/22/2009 A 67868 9
7/22/2009 A 86569 6
6/22/2009 A 54596 2
5/22/2009 A 12356 1
10/22/2009 A 35689 2
2/22/2010 A 35689 1

Expected Output:

Example: Current date = 03/2010

Type Code Mar-10 Feb-10 Jan-10 Dec-09 Nov-09 Oct-09 Sep-09 Aug-09 Jul-09 Jun-09 May-09
A 23546 0 6 0 8 0 0 0 0 0 0 0
A 52453 3 0 9 0 0 0 0 0 0 0 0
A 38675 0 0 0 12 0 0 0 0 0 0 0
A 96878 0 0 0 0 0 0 1 0 0 0 0
A 67868 0 0 0 0 0 0 0 0 9 0 0
A 86569 0 0 0 0 0 0 0 0 6 0 0
A 54596 0 0 0 0 0 0 0 0 0 2 0
A 12356 0 0 0 0 0 0 0 0 0 0 1
A 35689 0 1 0 0 0 2 0 0 0 0 0

Note: It is just an example. But the output must look like that and it will always be 24 month. Is it possible in SQL?

Thanks in advance. I know I would be able to learn from this forum.

quote:
Originally posted by vaibhavktiwari83

Please post table structure, sample data and expected output.

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-22 : 09:12:24

[code]
CREATE TABLE #Test ( Date DATETIME, Type CHAR(1), Code VARCHAR(20), Qty INT )

INSERT INTO #Test
SELECT '12/22/2009','A','23546',8 UNION ALL
SELECT '2/22/2010','A','23546',6 UNION ALL
SELECT '7/22/2010','A','52453',8 UNION ALL
SELECT '3/22/2010','A','52453',5 UNION ALL
SELECT '1/22/2010','A','52453',9 UNION ALL
SELECT '12/22/2009','A','38675',12 UNION ALL
SELECT '9/22/2009','A','96878',1 UNION ALL
SELECT '7/22/2009','A','67868',9 UNION ALL
SELECT '7/22/2009','A','86569',6 UNION ALL
SELECT '6/22/2009','A','54596',2 UNION ALL
SELECT '5/22/2009','A','12356',1 UNION ALL
SELECT '10/22/2009','A','35689',2 UNION ALL
SELECT '2/22/2010','A','35689',1

DECLARE @CurrDate AS DATETIME, @Column_Name AS VARCHAR(MAX), @SQL AS VARCHAR(MAX)

SELECT @CurrDate = '22-Mar-2010', @Column_Name = '', @SQL = ''

SELECT @Column_Name = @Column_Name + '[' + SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + '],'
FROM master.dbo.spt_values
WHERE number BETWEEN 0 AND 11 AND type = 'P'

SELECT @Column_Name = LEFT(@Column_Name,LEN(@Column_name)-1)

SET @SQL =
'DECLARE @CurrDate AS DATETIME
SET @CurrDate = ''22-Mar-2010''' +
' SELECT Type, Code, ' + @Column_Name +
' FROM
(
SELECT SUBSTRING(CONVERT(VARCHAR(20), T.Date, 106), 4, LEN(CONVERT(VARCHAR(20), T.Date, 106))) PDate, T.Date,
Type, Code, QTY
FROM
(
SELECT DATEADD(MONTH, -1*number , @CurrDate)Date
FROM master.dbo.spt_values
WHERE number BETWEEN 0 AND 11 AND type = ''P''
) A
LEFT JOIN #Test T ON MONTH(A.Date) = MONTH(T.Date) AND YEAR(A.Date) = YEAR(T.Date)
) UP
PIVOT ( MAX( Qty ) FOR PDate IN ('+ @Column_Name +
')) AS PVT
WHERE Code IS NOT NULL'

EXEC ( @SQL )
[/code]

Vaibhav T

If I cant go back, I want to go fast...

zhel04
Starting Member

38 Posts

Posted - 2011-03-22 : 22:24:39

Thanks. I'll try this and check if I would be able to get the output.

Thanks. Thanks. :)

quote:

Originally posted by vaibhavktiwari83


CREATE TABLE #Test ( Date DATETIME, Type CHAR(1), Code VARCHAR(20), Qty INT )

INSERT INTO #Test 
SELECT '12/22/2009','A','23546',8 UNION ALL
SELECT '2/22/2010','A','23546',6 UNION ALL
SELECT '7/22/2010','A','52453',8 UNION ALL
SELECT '3/22/2010','A','52453',5 UNION ALL
SELECT '1/22/2010','A','52453',9 UNION ALL
SELECT '12/22/2009','A','38675',12 UNION ALL
SELECT '9/22/2009','A','96878',1 UNION ALL
SELECT '7/22/2009','A','67868',9 UNION ALL
SELECT '7/22/2009','A','86569',6 UNION ALL
SELECT '6/22/2009','A','54596',2 UNION ALL
SELECT '5/22/2009','A','12356',1 UNION ALL
SELECT '10/22/2009','A','35689',2 UNION ALL
SELECT '2/22/2010','A','35689',1

DECLARE @CurrDate AS DATETIME, @Column_Name AS VARCHAR(MAX), @SQL AS VARCHAR(MAX)

SELECT @CurrDate = '22-Mar-2010', @Column_Name = '', @SQL = ''

SELECT	@Column_Name = @Column_Name + '[' + SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + '],'
FROM	master.dbo.spt_values
WHERE	number BETWEEN 0 AND 11 AND type = 'P'

SELECT  @Column_Name = LEFT(@Column_Name,LEN(@Column_name)-1)

SET @SQL = 
'DECLARE @CurrDate AS DATETIME
SET @CurrDate = ''22-Mar-2010''' + 
' SELECT Type, Code, ' + @Column_Name +
' FROM 
(
SELECT	SUBSTRING(CONVERT(VARCHAR(20), T.Date, 106), 4, LEN(CONVERT(VARCHAR(20), T.Date, 106))) PDate, T.Date, 
		Type, Code, QTY 
FROM 
(
	SELECT	DATEADD(MONTH, -1*number , @CurrDate)Date
	FROM	master.dbo.spt_values
	WHERE	number BETWEEN 0 AND 11 AND type = ''P''
) A 
LEFT JOIN #Test T ON MONTH(A.Date) = MONTH(T.Date) AND YEAR(A.Date) = YEAR(T.Date)
) UP
PIVOT ( MAX( Qty ) FOR PDate IN ('+ @Column_Name +
')) AS PVT
WHERE Code IS NOT NULL'

EXEC ( @SQL )

Vaibhav T

If I cant go back, I want to go fast...

zhel04
Starting Member

38 Posts

Posted - 2011-03-22 : 23:24:27

Thanks a lot! thanks thanks.. :) But is it possible if I want to change Null into zero? and I want it to group by Code? Another is i also have to get the Quarterly and Year To Date is it possible from the code you have given me?

quote:

Originally posted by vaibhavktiwari83


CREATE TABLE #Test ( Date DATETIME, Type CHAR(1), Code VARCHAR(20), Qty INT )

INSERT INTO #Test 
SELECT '12/22/2009','A','23546',8 UNION ALL
SELECT '2/22/2010','A','23546',6 UNION ALL
SELECT '7/22/2010','A','52453',8 UNION ALL
SELECT '3/22/2010','A','52453',5 UNION ALL
SELECT '1/22/2010','A','52453',9 UNION ALL
SELECT '12/22/2009','A','38675',12 UNION ALL
SELECT '9/22/2009','A','96878',1 UNION ALL
SELECT '7/22/2009','A','67868',9 UNION ALL
SELECT '7/22/2009','A','86569',6 UNION ALL
SELECT '6/22/2009','A','54596',2 UNION ALL
SELECT '5/22/2009','A','12356',1 UNION ALL
SELECT '10/22/2009','A','35689',2 UNION ALL
SELECT '2/22/2010','A','35689',1

DECLARE @CurrDate AS DATETIME, @Column_Name AS VARCHAR(MAX), @SQL AS VARCHAR(MAX)

SELECT @CurrDate = '22-Mar-2010', @Column_Name = '', @SQL = ''

SELECT	@Column_Name = @Column_Name + '[' + SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + '],'
FROM	master.dbo.spt_values
WHERE	number BETWEEN 0 AND 11 AND type = 'P'

SELECT  @Column_Name = LEFT(@Column_Name,LEN(@Column_name)-1)

SET @SQL = 
'DECLARE @CurrDate AS DATETIME
SET @CurrDate = ''22-Mar-2010''' + 
' SELECT Type, Code, ' + @Column_Name +
' FROM 
(
SELECT	SUBSTRING(CONVERT(VARCHAR(20), T.Date, 106), 4, LEN(CONVERT(VARCHAR(20), T.Date, 106))) PDate, T.Date, 
		Type, Code, QTY 
FROM 
(
	SELECT	DATEADD(MONTH, -1*number , @CurrDate)Date
	FROM	master.dbo.spt_values
	WHERE	number BETWEEN 0 AND 11 AND type = ''P''
) A 
LEFT JOIN #Test T ON MONTH(A.Date) = MONTH(T.Date) AND YEAR(A.Date) = YEAR(T.Date)
) UP
PIVOT ( MAX( Qty ) FOR PDate IN ('+ @Column_Name +
')) AS PVT
WHERE Code IS NOT NULL'

EXEC ( @SQL )

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-23 : 02:51:14

I have been rewrite code for showing Zero instead of null -
and for quater or year just put -3 and -12 instead of -1 respectively and change where clause accordingly.


DROP TABLE #Test
CREATE TABLE #Test ( Date DATETIME, Type CHAR(1), Code VARCHAR(20), Qty INT )

INSERT INTO #Test 
SELECT '12/22/2009','A','23546',8 UNION ALL
SELECT '2/22/2010','A','23546',6 UNION ALL
SELECT '7/22/2010','A','52453',8 UNION ALL
SELECT '3/22/2010','A','52453',5 UNION ALL
SELECT '1/22/2010','A','52453',9 UNION ALL
SELECT '12/22/2009','A','38675',12 UNION ALL
SELECT '9/22/2009','A','96878',1 UNION ALL
SELECT '7/22/2009','A','67868',9 UNION ALL
SELECT '7/22/2009','A','86569',6 UNION ALL
SELECT '6/22/2009','A','54596',2 UNION ALL
SELECT '5/22/2009','A','12356',1 UNION ALL
SELECT '10/22/2009','A','35689',2 UNION ALL
SELECT '2/22/2010','A','35689',1

DECLARE @CurrDate AS DATETIME, @NULLColumn_Name AS VARCHAR(MAX), @Column_Name AS VARCHAR(MAX), @SQL AS VARCHAR(MAX)

SELECT @CurrDate = '22-Mar-2010', @Column_Name = '', @NULLColumn_Name = '', @SQL = ''

SELECT	@NULLColumn_Name = @NULLColumn_Name + 'ISNULL([' + SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + '],0)' +
		' AS '+ '['+ SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + ']' +','
FROM	master.dbo.spt_values
WHERE	number BETWEEN 0 AND 11 AND type = 'P'
		
SELECT	@Column_Name = @Column_Name + '['+ SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + ']' +','
FROM	master.dbo.spt_values
WHERE	number BETWEEN 0 AND 11 AND type = 'P'

SELECT  @Column_Name = LEFT(@Column_Name,LEN(@Column_name)-1)
SELECT  @NULLColumn_Name = LEFT(@NULLColumn_Name,LEN(@NULLColumn_Name)-1)

SET @SQL = 
'DECLARE @CurrDate AS DATETIME
SET @CurrDate = ''22-Mar-2010''' + 
' SELECT Type, Code, ' + @NULLColumn_Name +
' FROM 
(
SELECT	SUBSTRING(CONVERT(VARCHAR(20), T.Date, 106), 4, LEN(CONVERT(VARCHAR(20), T.Date, 106))) PDate, T.Date, 
		Type, Code, QTY 
FROM 
(
	SELECT	DATEADD(MONTH, -1*number , @CurrDate)Date
	FROM	master.dbo.spt_values
	WHERE	number BETWEEN 0 AND 11 AND type = ''P''
) A 
LEFT JOIN #Test T ON MONTH(A.Date) = MONTH(T.Date) AND YEAR(A.Date) = YEAR(T.Date)
) UP
PIVOT ( MAX( Qty ) FOR PDate IN ('+ @Column_Name +
')) AS PVT
WHERE Code IS NOT NULL'

EXEC ( @SQL )

Vaibhav T

If I cant go back, I want to go fast...

zhel04
Starting Member

38 Posts

Posted - 2011-03-23 : 03:24:45

Thanks.. :) But what if I want to change the column name example: March 2010 would be Qty1 then Feb 2010 would be Qty2.. and so on.. And what if I want to have a group by clause? is it possible?

quote:

Originally posted by vaibhavktiwari83

I have been rewrite code for showing Zero instead of null -
and for quater or year just put -3 and -12 instead of -1 respectively and change where clause accordingly.


DROP TABLE #Test
CREATE TABLE #Test ( Date DATETIME, Type CHAR(1), Code VARCHAR(20), Qty INT )

INSERT INTO #Test 
SELECT '12/22/2009','A','23546',8 UNION ALL
SELECT '2/22/2010','A','23546',6 UNION ALL
SELECT '7/22/2010','A','52453',8 UNION ALL
SELECT '3/22/2010','A','52453',5 UNION ALL
SELECT '1/22/2010','A','52453',9 UNION ALL
SELECT '12/22/2009','A','38675',12 UNION ALL
SELECT '9/22/2009','A','96878',1 UNION ALL
SELECT '7/22/2009','A','67868',9 UNION ALL
SELECT '7/22/2009','A','86569',6 UNION ALL
SELECT '6/22/2009','A','54596',2 UNION ALL
SELECT '5/22/2009','A','12356',1 UNION ALL
SELECT '10/22/2009','A','35689',2 UNION ALL
SELECT '2/22/2010','A','35689',1

DECLARE @CurrDate AS DATETIME, @NULLColumn_Name AS VARCHAR(MAX), @Column_Name AS VARCHAR(MAX), @SQL AS VARCHAR(MAX)

SELECT @CurrDate = '22-Mar-2010', @Column_Name = '', @NULLColumn_Name = '', @SQL = ''

SELECT	@NULLColumn_Name = @NULLColumn_Name + 'ISNULL([' + SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + '],0)' +
		' AS '+ '['+ SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + ']' +','
FROM	master.dbo.spt_values
WHERE	number BETWEEN 0 AND 11 AND type = 'P'
		
SELECT	@Column_Name = @Column_Name + '['+ SUBSTRING(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106), 4, LEN(CONVERT(VARCHAR(20), DATEADD(MONTH, -1*number , @CurrDate), 106))) + ']' +','
FROM	master.dbo.spt_values
WHERE	number BETWEEN 0 AND 11 AND type = 'P'

SELECT  @Column_Name = LEFT(@Column_Name,LEN(@Column_name)-1)
SELECT  @NULLColumn_Name = LEFT(@NULLColumn_Name,LEN(@NULLColumn_Name)-1)

SET @SQL = 
'DECLARE @CurrDate AS DATETIME
SET @CurrDate = ''22-Mar-2010''' + 
' SELECT Type, Code, ' + @NULLColumn_Name +
' FROM 
(
SELECT	SUBSTRING(CONVERT(VARCHAR(20), T.Date, 106), 4, LEN(CONVERT(VARCHAR(20), T.Date, 106))) PDate, T.Date, 
		Type, Code, QTY 
FROM 
(
	SELECT	DATEADD(MONTH, -1*number , @CurrDate)Date
	FROM	master.dbo.spt_values
	WHERE	number BETWEEN 0 AND 11 AND type = ''P''
) A 
LEFT JOIN #Test T ON MONTH(A.Date) = MONTH(T.Date) AND YEAR(A.Date) = YEAR(T.Date)
) UP
PIVOT ( MAX( Qty ) FOR PDate IN ('+ @Column_Name +
')) AS PVT
WHERE Code IS NOT NULL'

EXEC ( @SQL )

Vaibhav T

If I cant go back, I want to go fast...

matty
Posting Yak Master

161 Posts

Posted - 2011-03-23 : 04:48:06

[code]
CREATE TABLE #Test ( Date DATETIME, Type CHAR(1), Code VARCHAR(20), Qty INT )

INSERT INTO #Test
SELECT '12/22/2009','A','23546',8 UNION ALL
SELECT '2/22/2010','A','23546',6 UNION ALL
SELECT '7/22/2010','A','52453',8 UNION ALL
SELECT '3/22/2010','A','52453',5 UNION ALL
SELECT '1/22/2010','A','52453',9 UNION ALL
SELECT '12/22/2009','A','38675',12 UNION ALL
SELECT '9/22/2009','A','96878',1 UNION ALL
SELECT '7/22/2009','A','67868',9 UNION ALL
SELECT '7/22/2009','A','86569',6 UNION ALL
SELECT '6/22/2009','A','54596',2 UNION ALL
SELECT '5/22/2009','A','12356',1 UNION ALL
SELECT '10/22/2009','A','35689',2 UNION ALL
SELECT '2/22/2010','A','35689',1

DECLARE @CurrDate AS DATETIME, @Column_Name AS VARCHAR(MAX), @SQL AS VARCHAR(MAX),@Column_Name1 AS VARCHAR(MAX)
SELECT @CurrDate = '22-Mar-2010', @SQL = ''

SELECT
@Column_Name = COALESCE(@Column_Name + ',','') + '[' + SUBSTRING(DATENAME(MONTH, DATEADD(M, -1*number , @CurrDate)),1,3) + '-' + SUBSTRING(DATENAME(YY, DATEADD(MONTH, -1*number , @CurrDate)),3,2) + ']',
@Column_Name1 = COALESCE(@Column_Name1 + ',','') + 'ISNULL([' + SUBSTRING(DATENAME(MONTH, DATEADD(M, -1*number , @CurrDate)),1,3) + '-' + SUBSTRING(DATENAME(YY, DATEADD(MONTH, -1*number , @CurrDate)),3,2) + '],0) AS [Qty' + CONVERT(VARCHAR(30),number + 1) + ']'
FROM master.dbo.spt_values
WHERE number BETWEEN 0 AND 11 AND type = 'P'

SET @SQL =
'SELECT Type , Code,' + @Column_Name1 +
' FROM
(
SELECT Type, Code,Qty,SUBSTRING(Mon,1,3) + ''-'' + SUBSTRING(Yr,3,2) as Pdate
FROM
(
SELECT DATENAME(MONTH,T.Date) As Mon,DATENAME(YEAR,T.Date) AS Yr,
Type, Code, SUM(QTY) AS Qty
FROM
(
SELECT DATEADD(MONTH, -1*number , ''' + CONVERT(VARCHAR(10),@CurrDate,101) + ''') AS Date
FROM master.dbo.spt_values
WHERE number BETWEEN 0 AND 11 AND type = ''P''
) A
LEFT JOIN #Test T ON MONTH(A.Date) = MONTH(T.Date) AND YEAR(A.Date) = YEAR(T.Date)
WHERE t.Code IS NOT NULL
GROUP BY Code,Type,DATENAME(MONTH,T.Date),DATENAME(YEAR,T.Date)
)t
)t
pivot
(
MAX(Qty)
FOR PDate IN( ' + @Column_Name + ')
)p'

EXEC (@SQL)

drop table #Test
[/code]

zhel04
Starting Member

38 Posts

Posted - 2011-03-23 : 05:03:18

Thank you so much matty.. :) This is what i needed! :)
Thanks a lot!

Thank you for all who take time to read and most especially those who helped me out. I know I could learn a lot from this forum.

Thanks. :) God bless..

quote:

Originally posted by matty


CREATE TABLE #Test ( Date DATETIME, Type CHAR(1), Code VARCHAR(20), Qty INT )

INSERT INTO #Test 
SELECT '12/22/2009','A','23546',8 UNION ALL
SELECT '2/22/2010','A','23546',6 UNION ALL
SELECT '7/22/2010','A','52453',8 UNION ALL
SELECT '3/22/2010','A','52453',5 UNION ALL
SELECT '1/22/2010','A','52453',9 UNION ALL
SELECT '12/22/2009','A','38675',12 UNION ALL
SELECT '9/22/2009','A','96878',1 UNION ALL
SELECT '7/22/2009','A','67868',9 UNION ALL
SELECT '7/22/2009','A','86569',6 UNION ALL
SELECT '6/22/2009','A','54596',2 UNION ALL
SELECT '5/22/2009','A','12356',1 UNION ALL
SELECT '10/22/2009','A','35689',2 UNION ALL
SELECT '2/22/2010','A','35689',1

DECLARE @CurrDate AS DATETIME, @Column_Name AS VARCHAR(MAX), @SQL AS VARCHAR(MAX),@Column_Name1 AS VARCHAR(MAX)
SELECT @CurrDate = '22-Mar-2010',  @SQL = ''

SELECT	
		@Column_Name  = COALESCE(@Column_Name + ',','') + '[' + SUBSTRING(DATENAME(MONTH, DATEADD(M, -1*number , @CurrDate)),1,3) + '-' + SUBSTRING(DATENAME(YY, DATEADD(MONTH, -1*number , @CurrDate)),3,2)   + ']',
		@Column_Name1 = COALESCE(@Column_Name1 + ',','') + 'ISNULL([' + SUBSTRING(DATENAME(MONTH, DATEADD(M, -1*number , @CurrDate)),1,3) + '-' + SUBSTRING(DATENAME(YY, DATEADD(MONTH, -1*number , @CurrDate)),3,2)  + '],0) AS [Qty' + CONVERT(VARCHAR(30),number + 1) + ']'
FROM	master.dbo.spt_values
WHERE	number BETWEEN 0 AND 11 AND type = 'P'


SET @SQL = 
		'SELECT Type , Code,' + @Column_Name1 +
		' FROM
		(
			SELECT Type, Code,Qty,SUBSTRING(Mon,1,3) + ''-'' + SUBSTRING(Yr,3,2) as Pdate
			FROM
			(
				SELECT	DATENAME(MONTH,T.Date) As Mon,DATENAME(YEAR,T.Date) AS Yr,
						Type, Code,  SUM(QTY) AS Qty
				FROM 
				(
					SELECT	DATEADD(MONTH, -1*number , ''' + CONVERT(VARCHAR(10),@CurrDate,101) + ''') AS Date
					FROM	master.dbo.spt_values
					WHERE	number BETWEEN 0 AND 11 AND type = ''P''
				) A 
				LEFT JOIN #Test T ON MONTH(A.Date) = MONTH(T.Date) AND YEAR(A.Date) = YEAR(T.Date)
				WHERE t.Code IS NOT NULL
				GROUP BY Code,Type,DATENAME(MONTH,T.Date),DATENAME(YEAR,T.Date) 
			)t
		)t
		pivot
		(
			MAX(Qty)
			FOR PDate IN( ' + @Column_Name + ') 
		)p'


EXEC (@SQL)

drop table #Test

zhel04
Starting Member

38 Posts

Posted - 2011-03-23 : 05:57:48

I'm trying to add the Quarter and yearly but I'm having a hard time I honestly don't know will I add those though vaibhavktiwari83 thought me just to change -1 to -3 or -12 but then i don't know how will i come up another field or column for quarterly and yearly.

Hope you understand guys I'm a newbie to pivot.

Thanks in advance. :)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-23 : 07:13:37

Please clarify your requirement with sample data.
In your first post you asked for 24 months
and in sample data you have given for 12 months only.

Please give the sample data for quaterly and yearly columns.

Vaibhav T

If I cant go back, I want to go fast...

zhel04
Starting Member

38 Posts

Posted - 2011-03-23 : 23:05:50

same sample data.. I just need to add the quarter and the year-to-date. For Quarter needed to get the total qty for quarterly.

quote:
Originally posted by vaibhavktiwari83

Please clarify your requirement with sample data.
In your first post you asked for 24 months
and in sample data you have given for 12 months only.

Please give the sample data for quaterly and yearly columns.

Vaibhav T

If I cant go back, I want to go fast...

matty
Posting Yak Master

161 Posts

Posted - 2011-03-24 : 05:03:09

I dont understand your requirement.What should be the ouput with the sample data?

quote:
Originally posted by zhel04

same sample data.. I just need to add the quarter and the year-to-date. For Quarter needed to get the total qty for quarterly.

quote:
Originally posted by vaibhavktiwari83

Please clarify your requirement with sample data.
In your first post you asked for 24 months
and in sample data you have given for 12 months only.

Please give the sample data for quaterly and yearly columns.

Vaibhav T

If I cant go back, I want to go fast...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources