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 |
|
navbingo20
Starting Member
12 Posts |
 Posted - 2011-05-03 : 07:27:32
|
| here is my sql query, drop table #tempi CREATE TABLE #tempi ( nav1 VARCHAR(50), nav2 int, nav3 int, nav4 int, nav5 int ) insert into #tempi select coalesce(cast(machinename as varchar(28)), 'Grand Total:') as machinename, sum(case when vfrm.job_id = '1001' then DateDiff(mi, 0, total_time) end) as CRATE_SMALL , sum(case when vfrm.job_id = '1002' then DateDiff(mi, 0, total_time) end) as CRATE_MEDIUM , sum(case when vfrm.job_id = '1014' then DateDiff(mi, 0, total_time) end) as FRONT_AIR_DEFLECTOR , sum(case when vfrm.job_id = '9999' then DateDiff(mi, 0, total_time) end) as NO_SCHEDULE from ven_fullreportmaster vfrm INNER JOIN ven_descriptionmaster VDM ON VDM.description_id = vfrm..description_id inner join ven_machinemaster vm on vm.machine_id = vfrm..machine_id where vfrm.entry_date = convert(varchar, getdate()-7, 105) --and vfrm.shift_id =1 and vfrm.is_task_completed ='Y' group by machinename with rollupthe out put in tempi table :---------------------------- nav1 nav2 nav3 nav4 nav5 abc 0:0 0:0 0:0 1:0 def 0:0 1:0 2:0 1:0 ghi 0:0 0:0 0:0 1:0 grand 0:0 1:0 2:0 3:0 totali want to select the columns whose grand total is greater than 0..how to achieve it plz help me soon.. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-03 : 07:30:58
|
If possible then do that in your front end.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-03 : 07:40:29
|
| You want to change the structure of the resultset so only with dynamic sql.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
navbingo20
Starting Member
12 Posts |
Posted - 2011-05-03 : 22:42:11
|
i need to do it in the back end itself..i am saving the result set in an .xls sheetquote:
Originally posted by webfred
If possible then do that in your front end.
No, you're never too old to Yak'n'Roll if you're too young to die.
|
|
|
|
|
|
|
|
|
sql query formation problem