November 2, 2009 at 6:38 am
SELECT OrgCode AS UnitState , OrgCode As State, IsNull(Count(*), 0) As [SLRP Approved]
FROM tLookup_StateTable LKState Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL on LKState.OrgCode = TBL.UnitState Inner Join
dbo.tblContracts C ON C.Contract_ID = TBL.TrackingID
LEFT OUTER JOIN dbo.tLookup_Incentive Lkp ON C.IT_CODE = Lkp.IT_ID
Where (TBL.APPROVED > 0) AND (TBL.VERIFIED = 0) AND (TBL.[VERIFICATION FAILED] = 0) AND (TBL.[SYS VERIF - LOSS] = 0) AND (C.ContractStatus = 'A') AND C.IT_Code = 'S'
Group By OrgCode
I expect the above code to list all the states and when the count for a particular state is null replace it with zero, but this is not working
July 21, 2011 at 4:16 am
Just for giggles, try using the caolescle(ltlrim(rtrim(value)), 0) instead of isnull(value, 0)
That might work in this case..
Aaron
July 21, 2011 at 4:53 am
Not much information to go on.
Run the following code and post back comments.
SELECT
OrgCode AS UnitState ,
OrgCode As State,
COUNT(*) AS Rows_Per_OrgCode
--IsNull(Count(*), 0) As [SLRP Approved]
FROM tLookup_StateTable LKState
Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL on LKState.OrgCode = TBL.UnitState
Inner Join dbo.tblContracts C ON C.Contract_ID = TBL.TrackingID
LEFT OUTER JOIN dbo.tLookup_Incentive Lkp ON C.IT_CODE = Lkp.IT_ID
Where (TBL.APPROVED > 0) AND (TBL.VERIFIED = 0) AND (TBL.[VERIFICATION FAILED] = 0) AND (TBL.[SYS VERIF - LOSS] = 0)
AND (C.ContractStatus = 'A') AND C.IT_Code = 'S'
Group By OrgCode
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2011 at 6:42 am
There is an Inner join with a outer joined object, so the inner join on NULL may be your issue .
Inner Join dbo.tblContracts C
ON C.Contract_ID = TBL.TrackingID
So, this may be avoided by altering the inner join to another left join
or maybe better a nested inner join, by shifting the left joined on clause like this.
SELECT OrgCode AS UnitState
, OrgCode As State
, IsNull(Count(*), 0) As [SLRP Approved]
FROM tLookup_StateTable LKState
Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL
on LKState.OrgCode = TBL.UnitState
LEFT Join dbo.tblContracts C ------ altered inner to left join (consequences)
ON C.Contract_ID = TBL.TrackingID
LEFT OUTER JOIN dbo.tLookup_Incentive Lkp
ON C.IT_CODE = Lkp.IT_ID
Where ( TBL.APPROVED > 0 )
AND ( TBL.VERIFIED = 0 )
AND ( TBL.[VERIFICATION FAILED] = 0 )
AND ( TBL.[SYS VERIF - LOSS] = 0 )
AND ( C.ContractStatus = 'A' )
AND C.IT_Code = 'S'
Group By OrgCode
SELECT OrgCode AS UnitState
, OrgCode As State
, IsNull(Count(*), 0) As [SLRP Approved]
FROM tLookup_StateTable LKState
Left Join dbo.tbl_Status_Of_ContractRequests_By_State TBL
Inner Join dbo.tblContracts C
ON C.Contract_ID = TBL.TrackingID
on LKState.OrgCode = TBL.UnitState --- nested inner join with left join
LEFT OUTER JOIN dbo.tLookup_Incentive Lkp
ON C.IT_CODE = Lkp.IT_ID
Where ( TBL.APPROVED > 0 )
AND ( TBL.VERIFIED = 0 )
AND ( TBL.[VERIFICATION FAILED] = 0 )
AND ( TBL.[SYS VERIF - LOSS] = 0 )
AND ( C.ContractStatus = 'A' )
AND C.IT_Code = 'S'
Group By OrgCode
Please test it and let us know your findings !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 21, 2011 at 8:25 am
this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.
July 21, 2011 at 8:35 am
Faye Fouladi (7/21/2011)
this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.
You mean you don't work on the same issue for a year and a half? :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 21, 2011 at 12:19 pm
Faye Fouladi (7/21/2011)
this problem was posted long time ago. I no longer work on this project, therefore, I cannot test it.
Oops, I didn't see the original date is somewhere in 2009.:crazy:
Never mind. Such things happen.;-)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply