Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select all records in current month Expand / Collapse
Author
Message
Posted Wednesday, December 05, 2012 6:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:32 PM
Points: 69, Visits: 180
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()).
Post #1392972
Posted Wednesday, December 05, 2012 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #1392977
Posted Wednesday, December 05, 2012 6:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:06 AM
Points: 2,404, Visits: 7,311
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1392978
Posted Wednesday, December 05, 2012 6:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:32 PM
Points: 69, Visits: 180
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.
Post #1392987
Posted Wednesday, December 05, 2012 6:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:32 PM
Points: 69, Visits: 180
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.
Post #1392992
Posted Wednesday, December 05, 2012 7:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
-- 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
Post #1393006
Posted Wednesday, December 05, 2012 7:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:32 PM
Points: 69, Visits: 180
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

Post #1393011
Posted Wednesday, December 05, 2012 8:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:06 AM
Points: 2,404, Visits: 7,311
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1393060
Posted Tuesday, January 29, 2013 4:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 4:10 PM
Points: 16, 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.
Post #1412896
Posted Thursday, February 14, 2013 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:02 AM
Points: 29, Visits: 107
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 :)
Post #1420060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse