February 10, 2014 at 8:54 am
I have table as below.
create table name (id int, name varchar(10))
insert into name
values (1,'asd'),
(2,'asd'),
(2,'bcc'),
(3,'shd')
I have another table which has just one column month
DECLARE @CutOffDatedate
DECLARE @StartDtdate
DECLARE @EndDtdate
DECLARE @DocumentDt date = '10-1-2014'
DECLARE @CurrentDate datetime
SET @CurrentDate = @DocumentDt
SET @CutOffDate = DATEADD(MONTH,((YEAR(@DocumentDt)-1900)*12)+ 8, 0)
IF @CurrentDate >= @CutOffDate
BEGIN
SET @StartDt = DATEADD(YEAR, -1, @CutOffDate)
SET @EndDt = DATEADD(DAY, -1, DATEADD(Year, 2, @CutOffDate))
END
ELSE
BEGIN
SET @StartDt = DATEADD(YEAR, -2, @CutOffDate)
SET @EndDt = DATEADD(DAY, -1, DATEADD(YEAR, 1, @CutOffDate))
END
DECLARE @MonthDiff int;
DECLARE @counter int;
DECLARE @Monthstable ([month] date);
SET @counter = 0;
SELECT @MonthDiff = DATEDIFF(mm, @StartDt, @EndDt);
WHILE @counter <= @MonthDiff
BEGIN
INSERT @Months
SELECT (DATEADD(mm, @counter, @StartDt));
SET @counter = @counter + 1;
END
Now i need out put as first two columns from table name and after that number of months as columns.
for eg. @month has 24 months then i need id,name,jan-13,fet-13,mar-13..........
Thanks in advance 🙂
February 10, 2014 at 9:21 am
What are you going to put on the months columns? month names?
DECLARE @Startdate date
DECLARE @DocumentDt date = '20141001'
SET @Startdate = CASE WHEN MONTH( @DocumentDt) < 9
THEN DATEADD(MONTH,((YEAR(@DocumentDt)-1902)*12)+ 8, 0)
ELSE DATEADD(MONTH,((YEAR(@DocumentDt)-1901)*12)+ 8, 0) END
SELECT id,
name,
RIGHT( REPLACE( CONVERT( char(9), @Startdate, 6), ' ', '-'), 6) Month01,
RIGHT( REPLACE( CONVERT( char(9), DATEADD( MM, 1, @Startdate), 6), ' ', '-'), 6) Month02,
RIGHT( REPLACE( CONVERT( char(9), DATEADD( MM, 2, @Startdate), 6), ' ', '-'), 6) Month03,
RIGHT( REPLACE( CONVERT( char(9), DATEADD( MM, 3, @Startdate), 6), ' ', '-'), 6) Month04,
RIGHT( REPLACE( CONVERT( char(9), DATEADD( MM, 4, @Startdate), 6), ' ', '-'), 6) Month05,
--...
RIGHT( REPLACE( CONVERT( char(9), DATEADD( MM, 35, @Startdate), 6), ' ', '-'), 6) Month36
FROM name
February 10, 2014 at 9:33 am
Thanks for your reply, But i want output as id,name , sep-13,oct-13.......
months as a column name not as a value.
February 10, 2014 at 9:55 am
You will need some dynamic code for that. But before going there, what would go in the column values? You've given nothing to work with. Would it be just NULL values?
Check the following articles (in order) that might help you.
February 10, 2014 at 10:13 am
Thanks for your time, Data will be null as of now. we need to populate data from application. for that i need to provide them this as a template.
February 10, 2014 at 10:51 am
You still need to read the articles I posted to go further on this. I'm helping you with the template to create all the values. Your original query used 3 years (36 months) starting in september.
Please ask any question of the method used.
DECLARE @Startdate date
DECLARE @sql varchar(8000)
DECLARE @DocumentDt date = '20141001'
SET @Startdate = CASE WHEN MONTH( @DocumentDt) < 9
THEN DATEADD(MONTH,((YEAR(@DocumentDt)-1902)*12)+ 8, 0)
ELSE DATEADD(MONTH,((YEAR(@DocumentDt)-1901)*12)+ 8, 0) END
SET @sql =
'SELECT id
,name
'
SELECT @sql = @sql + ',NULL AS [' +
RIGHT( REPLACE( CONVERT( char(9), DATEADD( MM, N, @Startdate), 6), ' ', '-'), 6) + ']' + CHAR(13)
FROM (SELECT TOP (36) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 N
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))a(N),
(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))b(N))x
ORDER BY N
EXEC( @sql + 'FROM name')
February 10, 2014 at 11:26 am
Thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply