April 21, 2009 at 10:01 am
I have a query that I am trying to convert to from VBA for Access to T-sql. Here is the query listed below:
SELECT tblWO.WOnumber, tblWO.Created, tblWO.Completed, tblWO.Status, tblWO.Building, tblWO.Supervisor, tblWO.Property
FROM tblWO
WHERE (((tblWO.Property)<>"Leased") AND ((tblWO.Type)="preventive") AND ((tblWO.Task) Is Not Null) AND ((IIf(Month(Date())=1,Year([created])=Year(Date())-1 And Month([created])=12,Year([created])=Year(Date()) And Month([created])=Month(Date())-1))<>False)) and ((IIf(Month(Date())=1,Year([completed])=Year(Date())-1 And Month([completed])=12,Year([completed])=Year(Date()) And Month([completed])=Month(Date())-1))<>False);
I'verewritten the query so it now looks like this:
SELECT tblWO.WOnumber, tblWO.Created, tblWO.Completed, tblWO.Status, tblWO.Building, tblWO.Supervisor, tblWO.Property
FROM tblWO
WHERE (((tblWO.Property)<>'Leased')
AND ((tblWO.Type)='preventive')
AND ((tblWO.Task) Is Not Null))
and month([created]) = month(getdate())-1
and year([created]) = year(getdate())
and month([completed]) = month(getdate())-1
and year([completed]) = year(getdate())
So my query now returns the cases that were completed and created for the previous month, but it doesn't work when the month is January. The problem I am having is trying to figure out what to do about the iif statement in the where clause and how exactly to implement it. I understand that the purpose of this iif statement is when the month is January, then you subtract 1 year and go to the month number of 12, or month name december. Any body have any Ideas, because I'm all out?!?!
Thanks
Allen
April 21, 2009 at 10:23 am
Okay... let's rethink this in terms of SQL functionality. You are wanting to do month/year comparisons ignoring the date and time. There is no need for an IF or CASE statement to handle this in SQL. You can get an integer representing the current month by using the following expression
select datediff(mm,0,getdate())
The DATEDIFF function with the mm parameter counts the number of months between two dates. In this case, we count the number of months between the first date in the system (day 0) and the current month.
Obviously the number of the previous month, no matter what year, is given by simply subtracting one.
select datediff(mm,0,getdate()) - 1
So, to test whether the [created] column has a date from last month, we just have to see if the month count is equal to the month count for getdate(), minus one month.
and datediff(mm,0,[created]) = datediff(mm,0,getdate()) - 1
Here's a quick proof:
-- (It's April,2009 as I type this.)
;with test (created) as
(select '3/17/2009' union all
select '2/28/2009' union all
select '4/1/2009'
)
select getdate(),datediff(mm,0,getdate()),[created], datediff(mm,0,[created])
from test
where datediff(mm,0,[created]) = datediff(mm,0,getdate()) - 1
By using DATEDIFF to count months, we didn't have to think in terms of Years/Months. Because DATEDIFF also works for days, years, and even weeks, this same principle can be used to solve a lot of problems you may face in the future. For example, you can easily find the first day of the current month by
select DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
Please let me know if you have any questions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 21, 2009 at 11:18 am
I didn't even think about datediff, I was too busy looking at the dateadd function. Thank you so much, this was very helpful. My project Manager and I can now sleep 🙂 !
April 21, 2009 at 11:23 am
Bob has outlined one of the ways you can approach this, however - that solution won't utilize any indexes on the date columns if they exist. If all you are looking for is the data from last month, then we need to define the first of last month and the first of this month and perform a range check.
To get the first of this month we can use the following:
SELECT dateadd(month, datediff(month, 0, getdate()), 0);
To get the first of last month, we use the following:
SELECT dateadd(month, datediff(month, 0, getdate()) - 1, 0);
Now, we put that into our query, and we get:
SELECT {columns}
FROM {tables}
WHERE datecolumn >= dateadd(month, datediff(month, 0, getdate()) - 1, 0) -- beginning of last month
AND datecolumn < dateadd(month, datediff(month, 0, getdate()), 0) -- beginning of this month (non-inclusive)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 21, 2009 at 2:20 pm
Jeffrey is absolutely correct.
Because the date test appeared late in the WHERE clause, I assumed that there wasn't an index over the date(s).
(Don't ask me why. It is always a mistake to assume such things.)
My apologies for my carelessness. :w00t:
I took out the DATEADD for simplicity of explanation and just forgot about the consequences if an index over the date(s) did exist. If you don't have an index on the date(s) in question that the optimiser is using, the DATEDIFF by itself will run a nano or two faster. However, even if you don't currently have indexes over the date columns, it's better to write the expression anticipating they might be created in the future. DATEADD results in a date format and does just that.
Thanks for the save Jeffrey!! 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply