| Author |
 Topic  |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
 Posted - 06/26/2007 : 15:31:59
|
Please help me in writing the query to get the desired output.
Table:
Column1 Column2
------- -------
250 5
254 6
255 6
Output:
Desc Count
---- -----
Test5 1
Test6 2
Test1 0
Test2 0
Test3 0 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 06/26/2007 : 15:57:41
|
| No I dont have table for testvalues |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 06/26/2007 : 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 B
on A.TID=B.TID where A.dt between '5-4-2007' and '5-4-2007'
Group by B.DSCRPTR
With the above query i got the below output
DESCR Count
----- -----
Test5 2
I want the output:
DESCR Count
----- -----
Test5 2
Test6 0
Test7 0
Test8 0 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 06/26/2007 : 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 END
from @HIFRAP B
left 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
) t
on b.DSCRPTR = t.DESCR
Tara Kizer
http://weblogs.sqlteam.com/tarad/ |
Edited by - tkizer on 06/26/2007 17:12:44 |
|
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 06/26/2007 : 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 test8
DESCR Count
----- -----
Test5 2
Test6 0
Test7 0
Test8 0
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 06/27/2007 : 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 Kizer
http://weblogs.sqlteam.com/tarad/ |
|
|
|
sqldba2k6
Posting Yak Master
176 Posts |
Posted - 06/27/2007 : 21:45:51
|
Thanks !!
I got the desired output with your query... |
|
|
| |
Topic |
|
count query help
