Author |
Topic |
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-19 : 13:19:00
|
Hi everyone , i would greatly appreciate anyones help on the following that i am trying to solve ... I am using SQL 2008 i have a database called ISCmetrics and a table called Meeting, the table meeting has 5 columns id ( int),TEAMNAME (varchar),DATECOMPLETE (varchar),STATUSNAME (varchar),STATUSLEVEL (varchar)We have around 20 different team names , and every team enters data into the database every day, and we have around 7 Different STATUSNAME they are always the same , the STATUSLEVEL is always a 1 or a 0 , so TEAMA enters a value every day for each of the 7 STATUSNAME, and the value is either a 1 or a 0 .The output for a Select * from ISCMetricslooks like thisid TEAMNAME DATECOMPLETE STATUSNAME STATUSLEVEL1 TeamA 20131022 STATCONTACT 12 TeamA 20131022 STATACTION 13 TeamA 20131022 STATABC 1 4 TeamB 20131022 STATCONTACT 15 TeamB 20131022 STATCTION 06 TeamB 20131022 STATABC 17 TeamA 20131021 STATCONTACT 08 TeamA 20131021 STATACTION 19 TeamA 20131021 STATABC 010 TeamB 20131021 STATCONTACT 111 TeamB 20131021 STATACTION 112 TeamB 20131021 STATABC 1What i am trying to achieve is this, i want the teams in one column then a column for each of the dates , and then sum of the STATUSLEVEL in each row as shown below for the day and team .....TEAMNAME 20131022 20131021 TeamA 3 1TeamB 2 3its mind boggling for me so any help Massively appreciated |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-20 : 01:55:55
|
[code]DECLARE @DateList varchar(5000)SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM ISCMetricslooks ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')SET @SQL='SELECT TEAMNAME,' + @DateList + 'FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM ISCMetricslooks )tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'EXEC (@SQL)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-20 : 04:25:18
|
Hi Visakh , thanks for your reply ,I received error that @SQL not defined , so I guess I need to DECLARE @SQL varchar(5000) ? is that correct ..you have a FROM ISCMetricslooks ... should this be from ISCMetrics.Meeting ( database and table name ) ? I did change both the above on the code to DECLARE @DateList varchar(5000)DECLARE @SQL varchar(5000)SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM ISCMetrics.E301836.[Log-TierMeeting] ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')SET @SQL='SELECT TEAMNAME,' + @DateList + 'FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM ISCMetrics.E301836.[Log-TierMeeting])tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'EXEC (@SQL)and received the errors Msg 1038, Level 15, State 4, Line 1An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.Msg 105, Level 15, State 1, Line 1Unclosed quotation mark after the character string ''.Msg 102, Level 15, State 1, Line 1Incorrect syntax near ''.Appreciate any further help you can give me |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-20 : 07:50:09
|
i dont think error is from posted part as i cant see where you're using SELECT INTO.Please check the rest of batch if you're using this in a batch------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-20 : 08:16:21
|
Hi Visakh16 , aah you are right ... sorry for that , just a syntax error now DECLARE @DateList varchar(5000)DECLARE @SQL varchar(5000)SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')SET @SQL='SELECT TEAMNAME,' + @DateList + 'FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836.[Log-TierMeeting])tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'EXEC (@SQL)Msg 102, Level 15, State 1, Line 5Incorrect syntax near '.'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-20 : 09:01:53
|
replace EXEC(@SQL) with PRINT(@SQL) and post back the result.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-20 : 09:24:23
|
Hi ... here it is :) SELECT TEAMNAME,[20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[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]FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-20 : 12:57:58
|
ok. i got the issue. It overshot the max length of @SQL so increase itDECLARE @DateList varchar(5000)DECLARE @SQL varchar(5000max)SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')SET @SQL='SELECT TEAMNAME,' + @DateList + 'FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836.[Log-TierMeeting])tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'EXEC (@SQL) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-21 : 04:22:10
|
Hi Visakh , seems so close just a syntax error still DECLARE @DateList varchar(5000)DECLARE @SQL varchar(MAX)SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')SET @SQL='SELECT TEAMNAME,' + @DateList + 'FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836.[Log-TierMeeting])tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'EXEC (@SQL)Msg 102, Level 15, State 1, Line 6Incorrect syntax near ')'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-21 : 06:55:13
|
show result of PRINT @SQL please------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-21 : 08:45:20
|
Hi , here you go ,,, if it helps I only need data from current year SELECT TEAMNAME,[20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[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],[20131FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836.[Log-TierMeeting])tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[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] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-21 : 08:52:27
|
its not showing full statement.. can you increase number of characters to be displayed for resuiltset in SSMS and try?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-21 : 09:14:23
|
Hi Increased to 1000 , think the results are the same SELECT TEAMNAME,[20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[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],[20131FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836.[Log-TierMeeting])tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[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] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-21 : 09:35:28
|
increase to maximum possible (8000)otherwise dump results to file and then copy and paste------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-21 : 10:06:47
|
hi increased to 8000 and sent to file ... it looks the same :(SELECT TEAMNAME,[20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[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],[20131FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836.[Log-TierMeeting])tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[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] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-22 : 05:48:36
|
do one more than restruict it to year and seeDECLARE @DateList varchar(5000)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 @SQL='SELECT TEAMNAME,' + @DateList + 'FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836.[Log-TierMeeting]WHERE DATECOMPLETE >=''20130101'' AND DATECOMPLETE < ''20140101'')tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'EXEC (@SQL) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-22 : 07:02:14
|
Hi Visakh16 , really appreciate your patience :) and help ...this time I get Msg 8117, Level 16, State 1, Line 1Operand data type varchar is invalid for sum operator.A print gives the followingSELECT 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]FROM(SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL FROM E301836.[Log-TierMeeting]WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101')tPIVOT (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]))p |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-22 : 07:08:09
|
whats the datatype of statuslevel? try this tooDECLARE @DateList varchar(5000)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 @SQL='SELECT TEAMNAME,' + @DateList + 'FROM(SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS int) AS STATUSLEVELFROM E301836.[Log-TierMeeting]WHERE DATECOMPLETE >=''20130101'' AND DATECOMPLETE < ''20140101'')tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'EXEC (@SQL) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-22 : 07:19:03
|
Ooooh getting close :) , I did get some results in the formTeamName Date1,Date2,Date3 etc ... but nothing populated ErrorMsg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '117.55' to data type int.table meeting has 5 columns id ( int),TEAMNAME (varchar),DATECOMPLETE (varchar),STATUSNAME (varchar),STATUSLEVEL (varchar) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-22 : 07:24:05
|
[code]DECLARE @DateList varchar(5000)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 @SQL='SELECT TEAMNAME,' + @DateList + 'FROM(SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS decimal(10,2)) AS STATUSLEVELFROM E301836.[Log-TierMeeting]WHERE DATECOMPLETE >=''20130101'' AND DATECOMPLETE < ''20140101'')tPIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'EXEC (@SQL)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
emailuser
Yak Posting Veteran
74 Posts |
Posted - 2013-11-22 : 10:40:39
|
Visakh16 5 STARS !!!! ... thank you !! ... this gives us a full date span ... one last thing if possible is it possible to have a rolling 30 day span , i.e where we show the latest DATECOMPLETE ( largest I guess as its just a number not a date )and then the preceeding 30 days ( DATECOMPLETE) , so in effect the WHERE is always WHERE DATECOMPLETE is the largest value AND Largest DATECOMPLETE -30 ???? hope that makes sense ( I guess you could order the results by DATECOMPLETE DESC and use TOP30 could you ??) |
|
|
Next Page
|
|
|