query.... - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

query....

Author

Topic

anilr499
Starting Member

18 Posts

Posted - 2012-05-01 : 22:08:48

queries and respective tables...

SELECT COUNT(*) as Cnt, RSK_STATUS,TD.TBD_DESC AS SCORE FROM RISK RSK INNER JOIN TSTABLEDETAILS TD ON TD.TBD_ID = RSK.RSK_SCORE AND
TD.TBL_ID = 'RSK_SCORE' WHERE RSK_REL_ID = 'PRJ0000673' AND RSK_ACTIVE = 'Y'
and TD.TBD_DESC ='HIGH' GROUP BY RSK_STATUS, TD.TBD_DESC

o/p table:

cnt rsk_status score

2 C High
24 O High

required table:

cnt rsk_status score

2 C High
24 O High

0 R 0

SELECT COUNT(*) as Cnt, RSK_STATUS,TD.TBD_DESC AS SCORE FROM RISK RSK INNER JOIN TSTABLEDETAILS TD ON TD.TBD_ID = RSK.RSK_SCORE AND
TD.TBL_ID = 'RSK_SCORE' WHERE RSK_REL_ID = 'PRJ0000673' AND RSK_ACTIVE = 'Y'
and TD.TBD_DESC ='low' GROUP BY RSK_STATUS, TD.TBD_DESC

o/p table:

cnt rsk_status score

1 C Low

required table:

cnt rsk_status score

2 C Low
0 O 0

0 R 0

i need to display the status C(closed),R(realised),o(open)

in my chart...

if i dont have c or R or O in my table then that should display 'zero'...

so that my chart will not be a mixed one...

thank you...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 22:46:17

[code]
SELECT COUNT(*) as Cnt, RSK_STATUS, COALESCE(TD.TBD_DESC,'0') AS SCORE FROM RISK RSK LEFT JOIN TSTABLEDETAILS TD ON TD.TBD_ID = RSK.RSK_SCORE AND
TD.TBL_ID = 'RSK_SCORE' WHERE RSK_REL_ID = 'PRJ0000673' AND RSK_ACTIVE = 'Y'
GROUP BY RSK_STATUS, COALESCE(TD.TBD_DESC,'0')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anilr499
Starting Member

18 Posts

Posted - 2012-05-01 : 23:29:10

HI vishak,

if i execute this query am getting the table like this.....

cnt rsk_status score
2 C High
24 O High
1 C Low
3 C Moderate
3 O Moderate

But my requirement is to get high,low, and moderate separately ...
with rsk_status field C,O,R as mandatory fields....
that means if i dont have the value then zero should be displayed

for example:
SELECT COUNT(*) as Cnt, RSK_STATUS, COALESCE(TD.TBD_DESC,'0') AS SCORE FROM RISK RSK LEFT JOIN TSTABLEDETAILS TD ON TD.TBD_ID = RSK.RSK_SCORE AND
TD.TBL_ID = 'RSK_SCORE' WHERE RSK_REL_ID = 'PRJ0000673' AND RSK_ACTIVE = 'Y' AND TD.TBD_DESC ='HIGH'
GROUP BY RSK_STATUS, COALESCE(TD.TBD_DESC,'0')

output table is

cnt rsk_status score
2 C High
24 O High

required output table should be like this:

cnt rsk_status score
2 C High
24 O High
0 r 0
please help me
thank you....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources