December 5, 2012 at 6:29 am
I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).
December 5, 2012 at 6:36 am
Can you post your original query, George? The last two weeks' version? Cheers.
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
December 5, 2012 at 6:37 am
george.greiner (12/5/2012)
I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).
Firstly, when looking for a month of data you're better off with this: -
WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
Which allows the query optimiser to seek on any index that may be on your DateFinished column.
Secondly, define what "does not work" means. You get a syntax error? You have no results? You have incorrect results?
Thirdly, have a read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which is all about how best to ask a question and expect to get an answer. If you follow the advise, then someone will be able to give you a fully coded working example for you to adapt for use in your particular environment.
December 5, 2012 at 6:52 am
ChrisM@Work (12/5/2012)
Can you post your original query, George? The last two weeks' version? Cheers.
My original query was:
SELECT PropertyInformation.BRTNumber, PropertyInformation.ClientsKey, PropertyInformation.ProductKey, PropertyInformation.DateFinished,
PropertyInformation.Finished, PropertyInformation.ReportType, PropertyInformation.Premises, PropertyInformation.OrderDate,
PropertyInformation.ClientKey, PropertyInformation.Invoiced, Fees.CaseNumberKey, Fees.Total, Fees.Summary, ClientTable.ClientKey AS Expr1
FROM PropertyInformation INNER JOIN
Fees ON PropertyInformation.CaseNumberKey = Fees.CaseNumberKey INNER JOIN
ClientTable ON PropertyInformation.ClientKey = ClientTable.ClientKey
WHERE (PropertyInformation.Finished = - 1) AND (PropertyInformation.ClientKey = 2) AND (PropertyInformation.DateFinished >= DATEADD(hour, - 336,
GETDATE())) AND (PropertyInformation.OrderDate > CONVERT(DATETIME, '2011-06-30 00:00:00', 102))
ORDER BY PropertyInformation.ClientsKey
Obviously this does not translate though as 1 month is not always a set amount of time.
December 5, 2012 at 6:58 am
Cadavre (12/5/2012)
george.greiner (12/5/2012)
I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).Firstly, when looking for a month of data you're better off with this: -
WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)Which allows the query optimiser to seek on any index that may be on your DateFinished column.
Secondly, define what "does not work" means. You get a syntax error? You have no results? You have incorrect results?
Thirdly, have a read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which is all about how best to ask a question and expect to get an answer. If you follow the advise, then someone will be able to give you a fully coded working example for you to adapt for use in your particular environment.
When I use the code I provided I get no results and as of today there should be 7.
When I use your code I get a syntax error.
December 5, 2012 at 7:36 am
-- run this and check DateFinished in the output to see how many
-- rows you should expect to return for December 2012, the current month.
-- The oldest rows will be from the last half hour or so of November.
SELECT
p.BRTNumber,
p.ClientsKey,
p.ProductKey,
p.DateFinished,
p.Finished,
p.ReportType,
p.Premises,
p.OrderDate,
p.ClientKey,
p.Invoiced,
f.CaseNumberKey,
f.Total,
f.Summary,
c.ClientKey AS Expr1
FROM PropertyInformation p
INNER JOIN Fees f
ON p.CaseNumberKey = f.CaseNumberKey
INNER JOIN ClientTable c
ON p.ClientKey = c.ClientKey
WHERE p.Finished = - 1
AND p.ClientKey = 2
AND p.DateFinished >= DATEADD(hour, -135, GETDATE())
AND p.OrderDate > CONVERT(DATETIME, '2011-06-30 00:00:00', 102)
ORDER BY p.DateFinished DESC --p.ClientsKey
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
December 5, 2012 at 7:43 am
I should have 7 rows. I already have a query that my business partner always asks me to run to check on client volume by whatever he feels like it that day.
USE newCityCollection
SELECT CaseNumberKey, DateFinished
FROM PropertyInformation
WHERE DateFinished between '12/1/2012' AND '12/06/2012' AND ClientKey = 3
Results:
CaseNumberKeyDateFinished
0024282012-12-03 09:24:45.093
0024292012-12-03 12:32:29.687
0024302012-12-04 10:39:20.280
0024362012-12-04 10:43:55.640
0024382012-12-04 11:10:59.877
0024392012-12-04 12:11:24.377
0024402012-12-04 12:37:57.267
December 5, 2012 at 8:51 am
george.greiner (12/5/2012)
Cadavre (12/5/2012)
george.greiner (12/5/2012)
I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).Firstly, when looking for a month of data you're better off with this: -
WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)Which allows the query optimiser to seek on any index that may be on your DateFinished column.
Secondly, define what "does not work" means. You get a syntax error? You have no results? You have incorrect results?
Thirdly, have a read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which is all about how best to ask a question and expect to get an answer. If you follow the advise, then someone will be able to give you a fully coded working example for you to adapt for use in your particular environment.
When I use the code I provided I get no results and as of today there should be 7.
When I use your code I get a syntax error.
Yes, for some reason the forum changed < to & l t ; and > to & g t ;
It should read: -
WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
This is irrelevant though. Without the sample data showing your issue so that we can have a look, it's pretty impossible to know why you aren't getting the results you require. As in my previous post, please take a look at this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which explains how to post sample data.
January 29, 2013 at 4:18 am
I think the comparison operators in the example above were the wrong way round. I have used the following against a date column in a test system of mine and get the expected answer.
where DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and
DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
In my system this shows all orders for January 2013.
Hope this helps.
February 14, 2013 at 7:35 am
Grasshopper:
I think your query is wrong
where DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and
DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0
it will display from Jan1 to Feb1 in this logic
It has to be
where DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and
DateFinished < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0
I guess 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply