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.
| Author |
Topic |
|
whiterabbot
Starting Member
18 Posts |
 Posted - 2008-12-12 : 20:46:24
|
| My objective for this query is to isolate the data for year and months. In the following code I get the desired effects but it is really, really slow. Does anyone have any suggestions?sqlQuery(dbconnection,"drop view winter_20052")sqlQuery(dbconnection,"create view winter_20052 as select (case when Year(null_date) = '2005' and Month(null_date) = '9' then ws else null END) as ws1, (case when Year(null_date) = '2005' and Month(null_date) = '10' then ws else null END) as ws2,(case when Year(null_date) = '2005' and Month(null_date) = '11' then ws else null END) as ws3,(case when Year(null_date) = '2005' and Month(null_date) = '12' then ws else null END) as ws4,(case when Year(null_date) = '2005' and Month(null_date) = '1' then ws else null END) as ws5,(case when Year(null_date) = '2005' and Month(null_date) = '2' then ws else null END) as ws6,(case when Year(null_date) = '2005' and Month(null_date) = '3' then ws else null END) as ws7,(case when Year(null_date) ='2005' then null_date else null END) as null_date from two_weeks3 where (case when Year(null_date) ='2005' then null_date else null END) is not null")sqlQuery(dbconnection,"drop view nonulls_date1")sqlQuery(dbconnection,"create view nonulls_date1 as select ws1,ws2,ws3,ws4,ws5,ws6,ws7,(case when ws1 IS NOT NULL then null_date when ws2 IS NOT NULL then null_date when ws3 is not null then null_date when ws4 is not null then null_date when ws5 is not null then null_date when ws6 is not null then null_date when ws7 is not null then null_date else null END) as null_date from winter_20052")sqlQuery(dbconnection,"drop view winter_nonulls2")sqlQuery(dbconnection,"create view winter_nonulls2 as select * from nonulls_date1 where ws1 is not null or ws2 is not null or ws3 is not null or ws4 is not null or ws5 is not null or ws6 is not null or ws7 is not null or null_date is not null") d<-sqlQuery(dbconnection,"select * from winter_nonulls2")Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-12 : 23:38:05
|
| are you looking for crosstabbed result or just month, year grouped result vertically? |
 |
|
|
whiterabbot
Starting Member
18 Posts |
Posted - 2008-12-16 : 14:59:18
|
| If I use this code,sqlQuery(dbconnection,"drop view nolulls1")sqlQuery(dbconnection,"create view nolulls1 as select ws,null_date from two_weeks2 where ws is null and null_date is not null" )sqlQuery(dbconnection,"drop view winter_20051")sqlQuery(dbconnection,"create view winter_20051 as select (case when Year(null_date) = '2005' and Month(null_date) = '9' then '9' else null END) as ws1, (case when Year(null_date) = '2005' and Month(null_date) = '10' then '10' else null END) as ws2,(case when Year(null_date) = '2005' and Month(null_date) = '11' then '11' else null END) as ws3,(case when Year(null_date) = '2005' and Month(null_date) = '12' then '12' else null END) as ws4,(case when Year(null_date) = '2005' and Month(null_date) = '1' then '1' else null END) as ws5,(case when Year(null_date) = '2005' and Month(null_date) = '2' then '2' else null END) as ws6,(case when Year(null_date) = '2005' and Month(null_date) = '3' then '3' else null END) as ws7,(case when Year(null_date) ='2005' then null_date else null END) as null_date from two_week_nonulls where (case when Year(null_date) ='2005' then null_date else null END) is not null")sqlQuery(dbconnection,"drop view nonulls_date")sqlQuery(dbconnection,"create view nonulls_date as select ws1,ws2,ws3,ws4,ws5,ws6,ws7,(case when ws1 IS NOT NULL then null_date when ws2 IS NOT NULL then null_date when ws3 is not null then null_date when ws4 is not null then null_date when ws5 is not null then null_date when ws6 is not null then null_date when ws7 is not null then null_date else null END) as null_date from winter_20051")sqlQuery(dbconnection,"drop view winter_nonulls1")sqlQuery(dbconnection,"create view winter_nonulls1 as select * from nonulls_date where ws1 is not null or ws2 is not null or ws3 is not null or ws4 is not null or ws5 is not null or ws6 is not null or ws7 is not null or null_date is not null") c<-sqlQuery(dbconnection,"select * from winter_nonulls1")Instead of the code above my data returns much faster. Does anyone know why?Thanks. |
|
|
|
whiterabbot
Starting Member
18 Posts |
Posted - 2008-12-16 : 17:28:22
|
| visakh16,To answer your question I am looking for crosstabbed results. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-16 : 23:25:37
|
| but the code above doesnt do any aggregation, then how will you get crtosstabbed result? you will jhust get records transposed with lots of nulls in b/w |
|
|
|
|
|
|
|
|
Slow Query to seperate data by month and year