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 |
dave1816
Starting Member
9 Posts |
Posted - 2013-11-04 : 11:06:01
|
I have this bit of code below and would like to bring back stat_code's which have no value as 0...anyway of achiving this??ThanksregardsDaveselect stat_code, COUNT(stat_code)AS Stat_Count --INTO tableSroStatABDfrom fs_srowhere sro_stat ='O' and whse = 'ABD' group by stat_codehaving stat_code in ('1 QUOTE WN', '2 PR & DEF', '2.1 PRTORD', '3 PRTS AVL', '4 SCHEDULD', '5 LABB JBC', '6 CSTB JBC', '7 WRKS CMP', '8 CSH APP', '9 TO B INV')order by stat_code asc D.ARNOLD |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-04 : 11:37:11
|
You need to create a list of stat_codes you want to consider and do a left join to that. Something like shown below:SELECT b.stat_code , COUNT(fs_sro.stat_code) AS Stat_Count --INTO tableSroStatABDFROM (VALUES ( '1 QUOTE WN'),( '2 PR & DEF'),( '2.1 PRTORD'),( '3 PRTS AVL'), ('4 SCHEDULD'),( '5 LABB JBC'),( '6 CSTB JBC'),( '7 WRKS CMP'), ('8 CSH APP'),( '9 TO B INV' )) AS b(stat_code) LEFT JOIN fs_sro ON fs_sro.stat_Code = b.stat_codeWHERE sro_stat = 'O' AND whse = 'ABD'GROUP BY b.stat_codeORDER BY b.stat_code ASC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-04 : 14:04:35
|
quote: Originally posted by James K You need to create a list of stat_codes you want to consider and do a left join to that. Something like shown below:SELECT b.stat_code , COUNT(fs_sro.stat_code) AS Stat_Count --INTO tableSroStatABDFROM (VALUES ( '1 QUOTE WN'),( '2 PR & DEF'),( '2.1 PRTORD'),( '3 PRTS AVL'), ('4 SCHEDULD'),( '5 LABB JBC'),( '6 CSTB JBC'),( '7 WRKS CMP'), ('8 CSH APP'),( '9 TO B INV' )) AS b(stat_code) LEFT JOIN fs_sro ON fs_sro.stat_Code = b.stat_codeWHERE sro_stat = 'O' AND whse = 'ABD'GROUP BY b.stat_codeORDER BY b.stat_code ASC
This will still reduce it to an inner join and cause unmatched rows to be ignored I guess.You might need to tweak it asSELECT b.stat_code , COUNT(fs_sro.stat_code) AS Stat_Count --INTO tableSroStatABDFROM (VALUES ( '1 QUOTE WN'),( '2 PR & DEF'),( '2.1 PRTORD'),( '3 PRTS AVL'), ('4 SCHEDULD'),( '5 LABB JBC'),( '6 CSTB JBC'),( '7 WRKS CMP'), ('8 CSH APP'),( '9 TO B INV' )) AS b(stat_code) LEFT JOIN fs_sro ON fs_sro.stat_Code = b.stat_code AND sro_stat = 'O' AND whse = 'ABD'GROUP BY b.stat_codeORDER BY b.stat_code ASC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
dave1816
Starting Member
9 Posts |
Posted - 2013-11-05 : 11:35:28
|
Thank for the replies people, but I should of mentioned I have SQL 2005 so from values does not work...However I have a table listed with these status's which I have already tried but failed to work and is simlliar to James's code. visakh16 your script does work without using the where clause but it has comes back with a value of 1 instead of 0 :( no idea how to alter thisregardsD.ARNOLD |
|
|
dave1816
Starting Member
9 Posts |
Posted - 2013-11-05 : 11:41:21
|
spoke to soon :) solved withselect sc.stat_code, count(sro.stat_code)AS Stat_Count from fs_stat_code SC left outer join fs_sro SRO on SC.stat_code = SRO.stat_codeand SRO.sro_stat ='O' and SRO.whse = 'ABD' group by sc.stat_codehaving sc.stat_code in ('1 QUOTE WN', '2 PR & DEF', '2.1 PRTORD', '3 PRTS AVL', '4 SCHEDULD', '5 LABB JBC', '6 CSTB JBC', '7 WRKS CMP', '8 CSH APP', '9 TO B INV')order by sc.stat_code asc thanks everyone D.ARNOLD |
|
|
|
|
|
|
|