How solve this sql query

Posted by Cpatil1000 under Sql Server on 1/18/2014 | Points: 10 | Views : 1052 | Status : [Member] | Replies : 0
Hi,
I have written the following the sql query. But problem is that my sub_division is 0. So it is not getting count of that rows. I have use inner join on that table which is matching id. Some our division don’t have sub division So I consider as a ‘0’. if there is not subdivision , but having organision,circle and division it is must display count that assets..
ITASSETS_DETAILS DATA
ASSET_DETAILS_ID ORGANISATION CIRCLE_ID DIV_ID UNIT_ID LAST_MODIFIED MODIFIED_BY
1 1 1 27 5 2014-01-18 21:12:25.010 8
2 1 1 27 7 2014-01-18 21:12:50.263 8
3 4 12 22 0 2014-01-18 21:13:43.353 8
4 7 27 61 142 2014-01-18 21:14:26.340 8

Alter Procedure SELECT_ITASSETS_TYPE_SUMMARY(@ORGANISATION int, @CIRCLE int, @DIVISION int, @SUB_SUBDIVISION int)
AS

Begin
SELECT IsNull(ITD.ORGANISATION,0) As 'ORGANISATION_ID',
'CIRCLE' = (Case When ITC.NAME IS NULL Then '-' Else ITC.NAME End),
'DIVISION' = (Case When DIV.NAME IS NULL Then '-' Else DIV.NAME End),
'SUB_DIVISION' = (Case When SDIV.NAME IS NULL Then '-' Else SDIV.NAME End),
'SOFTWARE' = (Case When COUNT(SW.ASSETS_DETAILS_ID) = 0 Then 0 Else COUNT(SW.ASSETS_DETAILS_ID) End),
'DESKTOP' = (Case When COUNT(DS.ASSETS_DETAILS_ID) = 0 Then 0 Else COUNT(DS.ASSETS_DETAILS_ID) End),
'PRINTER' = (Case When COUNT(PNT.ASSETS_DETAILS_ID) = 0 Then 0 Else COUNT(PNT.ASSETS_DETAILS_ID) End),
'PLOTTER' = (Case When COUNT(PL.ASSETS_DETAILS_ID) = 0 Then 0 Else COUNT(PL.ASSETS_DETAILS_ID) End),
'MISC' = (Case When COUNT(MS.ASSETS_DETAILS_ID) = 0 Then 0 Else COUNT(MS.ASSETS_DETAILS_ID) End),
'LAPTOP' = (Case When COUNT(LP.ASSETS_DETAILS_ID) = 0 Then 0 Else COUNT(LP.ASSETS_DETAILS_ID) End)
FROM ITASSETS_DETAILS ITD
INNER JOIN ITASSETS_ORGANISATION ITO ON ITD.ORGANISATION = ITO.ID
INNER JOIN ITASSETS_CIRCLE ITC ON ITD.CIRCLE_ID = ITC.ID
INNER JOIN ITASSETS_DIVISION DIV ON ITD.DIV_ID = DIV.ID
INNER JOIN ITASSETS_SUB_DIVISION SDIV ON ITD.UNIT_ID = SDIV.ID
LEFT JOIN ITASSETS_SOFTWARE SW ON SW.ASSETS_DETAILS_ID = ITD.ASSET_DETAILS_ID
lEFT JOIN ITASSETS_DESKTOP DS ON DS.ASSETS_DETAILS_ID = ITD.ASSET_DETAILS_ID
LEFT JOIN ITASSETS_LAPTOP LP ON LP.ASSETS_DETAILS_ID = ITD.ASSET_DETAILS_ID
LEFT JOIN ITASSETS_MISC MS ON MS.ASSETS_DETAILS_ID= ITD.ASSET_DETAILS_ID
LEFT JOIN ITASSETS_PRINTER PNT ON PNT.ASSETS_DETAILS_ID = ITD.ASSET_DETAILS_ID
LEFT JOIN ASSETS_PLOTTER PL ON PL.ASSETS_DETAILS_ID= ITD.ASSET_DETAILS_ID
Where ITO.ID = (Case When @ORGANISATION = 0 Then ITO.ID Else @ORGANISATION End) And
ITC.ID = (Case When @CIRCLE = 0 Then ITC.ID Else @CIRCLE End) And
DIV.ID = (Case When @DIVISION = 0 Then DIV.ID Else @DIVISION End) And
SDIV.ID = (Case When @SUB_SUBDIVISION = 0 Then SDIV.ID Else @SUB_SUBDIVISION End)
Group by ITD.ORGANISATION,ITC.NAME,DIV.NAME,SDIV.NAME
Order bY ITD.ORGANISATION,ITC.NAME,DIV.NAME,SDIV.NAME
End

OUTPUT :
ORGANISATION_ID CIRCLE DIVISION SUB_DIVISION SOFTWARE DESKTOP PRINTER PLOTTER MISC LAPTOP
1 THANE MANGAON MANGAON 1 0 0 0 0 0
1 THANE MANGAON PANVEL-1 1 0 0 0 0 0
7 FINANCE Finance FINANCE 0 0 1 0 0 0




Responses

(No response found.)

Login to post response