June 7, 2011 at 4:10 am
Got simple query:
SELECT Department, Tax_Year, Tax_Period, Total_Bill, Total_Hours, Purchase_Order_Number
FROM Timesheets
WHERE (Department = '971') AND (Tax_Year = '2010') AND (Tax_Period > '41')
I want to change this so it does a total_bill for each different Purchase_Order_Number Number for all timesheets between Tax_Week 41 and Tax_Year 2009
and Tax_Week 40 and Tax_Year 2010 for Department 971
When i do it i am only getting data > tax week 41 and less than week 40. It mjisses out tax year 1,2,3 ext for the higher year.
Sorry this is so basic but teaching myself.
Thanks for any help
June 7, 2011 at 7:47 am
The problem here is that the tax week uses modulo arithmetic and the comparison operators don't. So when the tax week reaches 52 (or 53) it cycles back to 1, so in this particular modulo arithmetic 52 + 1 = 1 and 1 is greater than 52, but for the comparison operator 1 is ALWAYS less than 52.
As a simple example, say we want Dec 2009 to Jan 2010. If we write this using DateParts we get the wrong results.
WHERE Mon >= 12 AND Yr >= 2009
AND Mon <= 1 AND Yr <= 2010
Which is equivalent to
WHERE Mon BETWEEN 12 and 1 -- that is never
AND Yr BETWEEN 2009 and 2010
If you want to do comparisons or rollups on dates, you need to start with ACTUAL dates.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 7, 2011 at 7:59 am
Another problem is where Tax_Period is a stored as a string (which it appears to be), and not a numeric, as exemplified below:
DECLARE @Tx1 VARCHAR(5), @tx2 VARCHAR(5)
SET @tx1 = '21'
SET @tx2 = '4'
SELECT 'Dodgy Comparison'
WHERE @tx1 < @tx2
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply