SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Columns to Rows and a Sum
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/22/2013 :  16:38:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE @DateList VARCHAR(MAX),
	@SQL VARCHAR(MAX),
	@Today DATETIME = GETDATE(),
	@DateOption TINYINT = 1,
	@FromDate DATETIME,
	@ToDate DATETIME;

IF @DateOption = 1
	-- Latest date and calculate 30 days back
	SELECT TOP(1)	@ToDate = DateComplete,
			@FromDate = DATEADD(DAY, -30, DateComplete)
	FROM		E301836.[Log-TierMeeting]
	ORDER BY	DateComplete DESC;
ELSE IF @DateOption = 2
	-- Latest date and previous 29 unique days
	SELECT	@FromDate = MIN(DateComplete),
		@ToDate = MAX(DateComplete)
	FROM	(
			SELECT DISTINCT	TOP(30)
					DateComplete
			FROM		E301836.[Log-TierMeeting]
			ORDER BY	DateComplete DESC
		) AS d;
ELSE IF @DateOption = 3
	-- Current month MTD
	SELECT	@FromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @Today), '19000101'),
		@ToDate = @Today;
ELSE IF @DateOption = 4
	-- Current month FULL
	SELECT	@FromDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @Today), '19000101'),
		@ToDate = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @Today), '18991231');

-- Clear time portion
SELECT	@FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @FromDate), '19000101'),
	@ToDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', @ToDate), '19000101');

-- Build date list
SET	@DateList = STUFF((
				SELECT		', t.' + QUOTENAME(DateComplete)
				FROM		E301836.[Log-TierMeeting]
				WHERE		DateComplete >= '' + CONVERT(CHAR(8), @FromDate, 112) + ''
						AND DateComplete < '' + CONVERT(CHAR(8), @ToDate, 112) + ''
				GROUP BY	DateComplete
				ORDER BY	DateComplete
				FOR XML		PATH('')
			), 1, 2, '');

-- Build statement
SET	@SQL =	'	SELECT	p.TeamName, ' + 
				REPLACE(@DateList,' t.', ' p.') + '
			FROM	(
					SELECT	TeamName,
						DateComplete,
						CAST(StatusLevel AS DECIMAL(10,2)) AS StatusLevel
					FROM	E301836.[Log-TierMeeting]
					WHERE	DateComplete >= ''' + CONVERT(CHAR(8), @FromDate, 112) + '''
						AND DateComplete < ''' + CONVERT(CHAR(8), @ToDate, 112) + '''
				) AS t
			PIVOT	(
					SUM(t.StatusLevel)
					FOR DateComplete IN (' + @DateList + ')
				) AS p';
-- Execution statement
EXEC	(@SQL);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/25/2013 :  04:50:30  Show Profile  Reply with Quote
Morning SwePeso , thank you for your help ,

run the above but I get the following

Msg 254, Level 16, State 1, Line 12
Prefixed columns are not allowed in the column list of a PIVOT operator.
Msg 254, Level 16, State 1, Line 12
Prefixed columns are not allowed in the column list of a PIVOT operator.
Msg 254, Level 16, State 1, Line 12
Prefixed columns are not allowed in the column list of a PIVOT operator.
Msg 254, Level 16, State 1, Line 12
Prefixed columns are not allowed in the column list of a PIVOT operator.
Msg 254, Level 16, State 1, Line 12
Prefixed columns are not allowed in the column list of a PIVOT operator.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t.20131027" could not be bound.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/25/2013 :  05:11:47  Show Profile  Reply with Quote

DECLARE @DateList varchar(5000),@MaxDate varchar(11)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101' ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')

SET @MaxDate = CONVERT(varchar(11),MAX(DATECOMPLETE),112)
FROM E301836.[Log-TierMeeting]

SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL  AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >=''' + @MaxDate + ''' AND DATECOMPLETE < ''' + DATEADD(dd,-30,@MaxDate) + '''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/25/2013 :  07:09:57  Show Profile  Reply with Quote
Hi Visakh16 , again much appreciate your response ...I get the following

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/25/2013 :  09:26:03  Show Profile  Reply with Quote
print (@SQL) result please

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/26/2013 :  06:15:33  Show Profile  Reply with Quote
Hi ... when I do a print (@sql) I get same error message :(

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/26/2013 :  06:57:18  Show Profile  Reply with Quote
tht was a typo. sorry for that


DECLARE @DateList varchar(5000),@MaxDate varchar(11)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101' ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')

SELECT @MaxDate = CONVERT(varchar(11),MAX(DATECOMPLETE),112)
FROM E301836.[Log-TierMeeting]

SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL  AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >=''' + @MaxDate + ''' AND DATECOMPLETE < ''' + DATEADD(dd,-30,@MaxDate) + '''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/26/2013 :  07:05:33  Show Profile  Reply with Quote
Hi Visakh16 ... just tried it

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/26/2013 :  07:45:20  Show Profile  Reply with Quote
is DATECOMPLETE a datetime field? if not, first check for any spurious values in it

SELECT DATECOMPLETE
FROM E301836.[Log-TierMeeting]
WHERE ISDATE(DATECOMPLETE) = 0




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/26/2013 :  08:04:03  Show Profile  Reply with Quote
Hi DATECOMPLETE is a varchar , run the above command .. 0 rows affected , all DATECOMPLETE entries are in the format 20130620 etc hope this helps
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 11/26/2013 :  19:39:53  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

tht was a typo. sorry for that


DECLARE @DateList varchar(5000),@MaxDate varchar(11)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101' ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')

SELECT @MaxDate = CONVERT(varchar(11),MAX(DATECOMPLETE),112)
FROM E301836.[Log-TierMeeting]

SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL  AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >=''' + @MaxDate + ''' AND DATECOMPLETE < ''' + convert(varchar, DATEADD(dd,-30,@MaxDate), 112) + '''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)




Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/27/2013 :  03:44:33  Show Profile  Reply with Quote
Hi waterduck thanks for your input , I run this code, but I get no results in the TEAMNAME column and the dates are not truncated to today- 30 , a print@sql is shown below

SELECT TEAMNAME,[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122],[20131124],[20131125],[20131126],[20131127]
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >='20131127' AND DATECOMPLETE < '20131028'
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122],[20131124],[20131125],[20131126],[20131127]))p
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  04:24:06  Show Profile  Reply with Quote
quote:
Originally posted by emailuser

Hi DATECOMPLETE is a varchar , run the above command .. 0 rows affected , all DATECOMPLETE entries are in the format 20130620 etc hope this helps


if they are all in same format and are valid date values then this problem shouldnt occur at all.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/27/2013 :  12:12:52  Show Profile  Reply with Quote
Hi Visakh16 ,

I have made a copy of the table and shrunk the available dates down to just November , this is now only around 800 rows, I doublechecked all the DATECOMPLETE entries and they are all varchar in the format 20131127 ( yyyymmdd) , I run the script again and get same message

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.


If I change the line back to
WHERE DATECOMPLETE >=''20130101'' AND DATECOMPLETE < ''20140101''
it works , ??????
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  12:26:02  Show Profile  Reply with Quote
what does this return you


DECLARE @MaxDate varchar(11)

SELECT @MaxDate = CONVERT(varchar(11),MAX(DATECOMPLETE),112)
FROM E301836.[Log-TierMeeting]
SELECT @MaxDate


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/27/2013 :  12:36:49  Show Profile  Reply with Quote
Hi Visakh16 , hi this brings back the value of today 20131127 ... this is what I would expect as there is data for each team from today ..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  12:58:51  Show Profile  Reply with Quote
can you try this then?

DECLARE @DateList varchar(5000),@MaxDate varchar(11),@EndDate varchar(11)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101' ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')

SELECT @MaxDate = CONVERT(varchar(11),MAX(DATECOMPLETE),112),
@EndDate = CONVERT(varchar(11),DATEADD(dd,-30,MAX(DATECOMPLETE)),112)
FROM E301836.[Log-TierMeeting]

SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL  AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >=''' + @MaxDate + ''' AND DATECOMPLETE < ''' + @EndDate + '''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 11/28/2013 :  09:09:53  Show Profile  Reply with Quote
Hi Visakh , run the code , it runs no errors :) , there are dates as columns, but no data in the rows

a print @ sql gives
SELECT TEAMNAME,[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122],[20131124],[20131125],[20131126],[20131127],[20131128]
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >='20131128' AND DATECOMPLETE < '20131029'
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122],[20131124],[20131125],[20131126],[20131127],[20131128]))p

I changed WHERE DATECOMPLETE >='20131128' AND DATECOMPLETE < '20131029'
as the > and < look incorrect as we want it where DATECOMPLETE is > 20131029 and DATECOMPLETE <=20131128 , but this gave a strange result
print sql below :
SELECT TEAMNAME,[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122],[20131124],[20131125],[20131126],[20131127],[20131128]
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >='20131029' AND DATECOMPLETE < '20131128'
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122],[20131124],[20131125],[20131126],[20131127],[20131128]))p
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/28/2013 :  23:38:15  Show Profile  Reply with Quote
we want it where DATECOMPLETE is > 20131029 and DATECOMPLETE <=20131128
then do change accordingly in where

like

..
WHERE DATECOMPLETE >''' + @EndDate + ''' AND DATECOMPLETE <= ''' + @MaxDate + '''
..


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

72 Posts

Posted - 12/02/2013 :  04:39:53  Show Profile  Reply with Quote
HI Visakh16 , been out of the office for a few days .. as always appreciating your help here ... I tried the with amendment
DECLARE @DateList varchar(5000),@MaxDate varchar(11),@EndDate varchar(11)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101' ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')

SELECT @MaxDate = CONVERT(varchar(11),MAX(DATECOMPLETE),112),
@EndDate = CONVERT(varchar(11),DATEADD(dd,-30,MAX(DATECOMPLETE)),112)
FROM E301836.[Log-TierMeeting]

SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >''' + @EndDate + ''' AND DATECOMPLETE <= ''' + @MaxDate + '''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)

I get some output , but the dates are not truncated to 30 days , and there is no values for each team , a print@sql is shown below ;

SELECT TEAMNAME,[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122],[20131124],[20131125],[20131126],[20131127],[20131128],[20131129],[20131202]
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >'20131102' AND DATECOMPLETE <= '20131202'
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122],[20131124],[20131125],[20131126],[20131127],[20131128],[20131129],[20131202]))p

Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000