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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy