InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:32:28 GMT20http://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspxGrasshopper:I think your query is wrongwhere DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0it will display from Jan1 to Feb1 in this logicIt has to bewhere DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and DateFinished < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0I guess :)Thu, 14 Feb 2013 07:35:11 GMTjeganbenitto.francishttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspxI 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.[code="sql"]where DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)[/code]In my system this shows all orders for January 2013.Hope this helps.Tue, 29 Jan 2013 04:18:35 GMTFear Naughthttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspx[quote][b]george.greiner (12/5/2012)[/b][hr][quote][b]Cadavre (12/5/2012)[/b][hr][quote][b]george.greiner (12/5/2012)[/b][hr]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()).[/quote]Firstly, when looking for a month of data you're better off with this: -[code="sql"]WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)[/code]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 [url=http://www.sqlservercentral.com/articles/Best+Practices/61537/]read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url], 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.[/quote]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.[/quote]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 [url=http://www.sqlservercentral.com/articles/Best+Practices/61537/]this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url], which explains how to post sample data.Wed, 05 Dec 2012 08:51:48 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspxI 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. [code] USE newCityCollectionSELECT CaseNumberKey, DateFinishedFROM PropertyInformationWHERE DateFinished between '12/1/2012' AND '12/06/2012' AND ClientKey = 3 [/code]Results:CaseNumberKey DateFinished002428 2012-12-03 09:24:45.093002429 2012-12-03 12:32:29.687002430 2012-12-04 10:39:20.280002436 2012-12-04 10:43:55.640002438 2012-12-04 11:10:59.877002439 2012-12-04 12:11:24.377002440 2012-12-04 12:37:57.267Wed, 05 Dec 2012 07:43:53 GMTgeorge.greinerhttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspx[code="sql"]-- 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 Expr1FROM PropertyInformation p INNER JOIN Fees f ON p.CaseNumberKey = f.CaseNumberKey INNER JOIN ClientTable c ON p.ClientKey = c.ClientKeyWHERE 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[/code]Wed, 05 Dec 2012 07:36:01 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspx[quote][b]Cadavre (12/5/2012)[/b][hr][quote][b]george.greiner (12/5/2012)[/b][hr]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()).[/quote]Firstly, when looking for a month of data you're better off with this: -[code="sql"]WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)[/code]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 [url=http://www.sqlservercentral.com/articles/Best+Practices/61537/]read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url], 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.[/quote]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.Wed, 05 Dec 2012 06:58:41 GMTgeorge.greinerhttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspx[quote][b]ChrisM@Work (12/5/2012)[/b][hr]Can you post your original query, George? The last two weeks' version? Cheers.[/quote]My original query was:[code]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 Expr1FROM PropertyInformation INNER JOIN Fees ON PropertyInformation.CaseNumberKey = Fees.CaseNumberKey INNER JOIN ClientTable ON PropertyInformation.ClientKey = ClientTable.ClientKeyWHERE (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[/code]Obviously this does not translate though as 1 month is not always a set amount of time.Wed, 05 Dec 2012 06:52:51 GMTgeorge.greinerhttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspx[quote][b]george.greiner (12/5/2012)[/b][hr]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()).[/quote]Firstly, when looking for a month of data you're better off with this: -[code="sql"]WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)[/code]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 [url=http://www.sqlservercentral.com/articles/Best+Practices/61537/]read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url], 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.Wed, 05 Dec 2012 06:37:07 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspxCan you post your original query, George? The last two weeks' version? Cheers.Wed, 05 Dec 2012 06:36:55 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1392972-1063-1.aspxI 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()).Wed, 05 Dec 2012 06:29:53 GMTgeorge.greiner