October 25, 2010 at 8:43 am
I have an existing query in place that I use to sum totals for various codes we do business under. So for example any code in the 600 range belongs under one area, any code in the 700 range belongs in another area. Here is the existing query:
SELECT SUM(W.Estimate) FROM WorkOrders W
LEFT OUTER JOIN IntWOSO I ON W.WorkOrder = I.WorkOrder
LEFT OUTER JOIN ServiceOrders S ON I.ServiceOrder = S.ServiceOrder
WHERE W.RUSCode >= @Start AND
W.RUSCode <= @Stop AND W.Status = @status
AND S.Assigned >= @StartDate AND S.Assigned < @EndDate
where @Start = 600 and @Stop would equal 699
Well they have come along and decided they want to start using alpha numeric codes, e.g. 600A, or 600-B (they also didn't standardize their layout). Anybody have any thoughts on how I would rewrite the conditional operators to handle the alpha numeric but still understand that 600A is between 600 and 699.
October 25, 2010 at 9:08 am
Can you Specify what kind of Result You are exactly looking?
October 25, 2010 at 9:12 am
I am trying to get the SUM of a field for all records with a code between two values, e.g. 600 and 699. Those values however can be alphanumberic, e.g. 605a.
October 25, 2010 at 11:32 am
Thanks for the response. I don't know what the valid codes are as they can be added dynamically, so I'm not sure how well an IN will work. Let me see if the between gets the job done.
October 25, 2010 at 11:56 am
Sean Grebey-262535 (10/25/2010)
I have an existing query in place that I use to sum totals for various codes we do business under. So for example any code in the 600 range belongs under one area, any code in the 700 range belongs in another area. Here is the existing query:SELECT SUM(W.Estimate) FROM WorkOrders W
LEFT OUTER JOIN IntWOSO I ON W.WorkOrder = I.WorkOrder
LEFT OUTER JOIN ServiceOrders S ON I.ServiceOrder = S.ServiceOrder
WHERE W.RUSCode >= @Start AND
W.RUSCode <= @Stop AND W.Status = @status
AND S.Assigned >= @StartDate AND S.Assigned < @EndDate
where @Start = 600 and @Stop would equal 699
Well they have come along and decided they want to start using alpha numeric codes, e.g. 600A, or 600-B (they also didn't standardize their layout). Anybody have any thoughts on how I would rewrite the conditional operators to handle the alpha numeric but still understand that 600A is between 600 and 699.
As you don't know teh format of the codes, you presumably don't know the stop code so you can't use between. But if you set @start = "600" and @toofar="700" (and these variables have the same data type as the RUSCode column) then
WHERE W.RUSCode >= @start AND W.RUSCode < @toofar
will give you everything between 600 and 699ZZZZZZZZZZZZZ....
The one worry I would have would be if you have areas higher than the 900 area, because with the textual comparison location 6999 (presumably in the 6000 area) would unfortunately be counted in the 600 area as well. You can of course code around this by translating however many initial numbers there are in the code and doing an integer comparison on those; but you will still have to stop using an @stop value and use an @toofar value instead (with < instead of <=).
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply