Author |
Topic |
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 2007-06-26 : 15:31:59
|
Please help me in writing the query to get the desired output.Table:Column1 Column2------- -------250 5254 6255 6Output:Desc Count---- -----Test5 1Test6 2Test1 0Test2 0Test3 0 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-26 : 15:36:31
|
Do you have a table that has the list of Testn values?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 2007-06-26 : 15:57:41
|
No I dont have table for testvalues |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-26 : 15:59:10
|
Then how would you know which values should show 0 for Count?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 2007-06-26 : 16:34:32
|
DECLARE @MT TABLE ( TNO Int, TID Int, Dt datetime ) INSERT INTO @MT (TNO, TID,dt) ( Select '256' , '5','5-4-2007' Union All Select '257', '5','5-4-2007' Union All Select '258', '6','6-4-2007' Union All Select '259', '7','7-4-2007' ) DECLARE @HIFRAP TABLE ( TID Int, DSCRPTR VARCHAR(50) ) INSERT INTO @HIFRAP (TID, DSCRPTR) ( Select '5' , 'Test5' Union All Select '6', 'Test6' Union All Select '7', 'Test7' Union All Select '8', 'Test8' ) select B.DSCRPTR as DESCR ,Count(A.TID) as Count from @MT A Right outer join @HIFRAP Bon A.TID=B.TID where A.dt between '5-4-2007' and '5-4-2007'Group by B.DSCRPTRWith the above query i got the below outputDESCR Count----- -----Test5 2I want the output:DESCR Count----- -----Test5 2Test6 0Test7 0Test8 0 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-26 : 17:12:22
|
So your table does contain the data, unlike what your second post said. Next time, please be very clear on what you want.This provides the expected output:select DISTINCT B.DSCRPTR as DESCR, Count = CASE WHEN Count IS NULL THEN 0 ELSE Count ENDfrom @HIFRAP Bleft outer join( select B.DSCRPTR as DESCR ,Count(A.TID) as Count from @HIFRAP B left outer join @MT A on A.TID=B.TID where A.dt between '5-4-2007' and '5-4-2007' Group by B.DSCRPTR) ton b.DSCRPTR = t.DESCR Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 2007-06-26 : 21:30:49
|
Thanks Tara Kizer!Sorry for the confusion..There is no table for the value description codes of 5,6,7,8 in the database.Since it was not being stored or there is no master table.As per code book we know the value desc since it needs to hardcode if based on the value codes.There are only 4 vaules codes (5,6,7,8)being stored in the transaction table.How can we modify the query if value code is 5 then desc test5,6 test6,7 test7,8 test8DESCR Count----- -----Test5 2Test6 0Test7 0Test8 0 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-27 : 13:19:52
|
I don't understand your new problem. The solution that I provided shows the exact output that you want, so I don't understand what part isn't working. Please provide new sample data that illustrates your issue.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 2007-06-27 : 21:45:51
|
Thanks !!I got the desired output with your query... |
|
|
|