SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select all records in current month


Select all records in current month

Author
Message
george.greiner
george.greiner
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 184
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()).
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16506 Visits: 19557
Can you post your original query, George? The last two weeks' version? Cheers.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3936 Visits: 8472
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/, 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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
george.greiner
george.greiner
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 184
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.
george.greiner
george.greiner
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 184
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/, 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.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16506 Visits: 19557
-- 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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
george.greiner
george.greiner
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 184
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:

CaseNumberKey DateFinished
002428 2012-12-03 09:24:45.093
002429 2012-12-03 12:32:29.687
002430 2012-12-04 10:39:20.280
002436 2012-12-04 10:43:55.640
002438 2012-12-04 11:10:59.877
002439 2012-12-04 12:11:24.377
002440 2012-12-04 12:37:57.267
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3936 Visits: 8472
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/, 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/, which explains how to post sample data.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Fear Naught
Fear Naught
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 65
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.
jeganbenitto.francis
jeganbenitto.francis
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 113
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search