|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Year To Date?Hi,
I am using a query tool that only allows me to define "custom sql" in the WHERE clause. I want to specify that sales_date be between the first of the year and last day of the most recent full month. The query will run on the 3rd day of the month. I have so far sales_date > = convert (char(4), getdate(), 112) + '0101' AND sales_date < convert (char (6), getdate(), 112) + '01') but this won't work for the 3rd of January. Looking for some help. Thanks, Dan You can start with SELECT Dateadd(d, Day(current_timestamp) * -1
,current_timestamp) to get the last day of last month. Once you have that, calculating the first of that year should be pretty straight forward. Good luck. Payson Dan wrote: Show quote > Hi, > > I am using a query tool that only allows me to define "custom sql" in > the WHERE clause. I want to specify that sales_date be between the > first of the year and last day of the most recent full month. The query > will run on the 3rd day of the month. I have so far > > sales_date > = convert (char(4), getdate(), 112) + '0101' AND sales_date > < convert (char (6), getdate(), 112) + '01') > > but this won't work for the 3rd of January. Looking for some help. > > Thanks, > Dan Do away with those silly conversions to character format. Internally SQL
Server will process this stuff much faster, without any implicit or explicit conversions... WHERE Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())+1,0) Show quote "Dan" <dan@nospam.com> wrote in message news:ArZlf.20$AX6.257@news.uswest.net... > Hi, > > I am using a query tool that only allows me to define "custom sql" in the > WHERE clause. I want to specify that sales_date be between the first of > the year and last day of the most recent full month. The query will run > on the 3rd day of the month. I have so far > > sales_date > = convert (char(4), getdate(), 112) + '0101' AND sales_date < > convert (char (6), getdate(), 112) + '01') > > but this won't work for the 3rd of January. Looking for some help. > > Thanks, > Dan Cool!! I like this. It's much simpler than what I have been doing.
Payson Aaron Bertrand [SQL Server MVP] wrote: Show quote > Do away with those silly conversions to character format. Internally SQL > Server will process this stuff much faster, without any implicit or explicit > conversions... > > WHERE Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) > AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())+1,0) > > > > "Dan" <dan@nospam.com> wrote in message > news:ArZlf.20$AX6.257@news.uswest.net... > > Hi, > > > > I am using a query tool that only allows me to define "custom sql" in the > > WHERE clause. I want to specify that sales_date be between the first of > > the year and last day of the most recent full month. The query will run > > on the 3rd day of the month. I have so far > > > > sales_date > = convert (char(4), getdate(), 112) + '0101' AND sales_date < > > convert (char (6), getdate(), 112) + '01') > > > > but this won't work for the 3rd of January. Looking for some help. > > > > Thanks, > > Dan "Payson" <payso***@hotmail.com> wrote in message Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0)news:1134062473.766150.129310@o13g2000cwo.googlegroups.com... > Cool!! I like this. It's much simpler than what I have been doing. AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())+1,0) I have UDF()s for these that are in my SPs in the reporting system I'm doing currently. In my report spec sheet I have a a check off for the date range needed for the report. Last Month, YTD, Rolling Year, compare FYx to now. Notice that I don't allow users to pick dates? Bad Steve! __Stephen __Stephen wrote:
> I have UDF()s for these that are in my SPs in the reporting system I'm doing Interesting. I had some performance problems with something similar a> currently. while back. I was able to decrease processing time significantly (10% - 20%) by moving a dateadd function from a udf back into the main select. The UDF did the dateadd and nothing else. I suspect the optimizer can't look inside scalar udf's when he builds the plan, but he knows about and can see inside dateadd. However, I never proved that. Payson Can you explain this? What is 0 date/time?
On 12/8/2005 11:10 AM, Aaron Bertrand [SQL Server MVP] wrote: Show quote > Do away with those silly conversions to character format. Internally SQL > Server will process this stuff much faster, without any implicit or explicit > conversions... > > WHERE Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) > AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())+1,0) > > > > "Dan" <dan@nospam.com> wrote in message > news:ArZlf.20$AX6.257@news.uswest.net... >> Hi, >> >> I am using a query tool that only allows me to define "custom sql" in the >> WHERE clause. I want to specify that sales_date be between the first of >> the year and last day of the most recent full month. The query will run >> on the 3rd day of the month. I have so far >> >> sales_date > = convert (char(4), getdate(), 112) + '0101' AND sales_date < >> convert (char (6), getdate(), 112) + '01') >> >> but this won't work for the 3rd of January. Looking for some help. >> >> Thanks, >> Dan > > I'll give you a hint. :-)
SELECT CONVERT(SMALLDATETIME, 0) Typing 0 is much easier than typing in some string that needs interpretation/conversion/validation... A Show quote "Dan" <dan@nospam.com> wrote in message news:fv%lf.57$dm5.9934@news.uswest.net... > Can you explain this? What is 0 date/time? Ah, the MS SQL server base reference date?
On 12/8/2005 1:28 PM, Aaron Bertrand [SQL Server MVP] wrote: Show quote > I'll give you a hint. :-) > > SELECT CONVERT(SMALLDATETIME, 0) > > Typing 0 is much easier than typing in some string that needs > interpretation/conversion/validation... > > A > > > "Dan" <dan@nospam.com> wrote in message > news:fv%lf.57$dm5.9934@news.uswest.net... >> Can you explain this? What is 0 date/time? > > Based on Aaron's advice, I so far have
WHERE Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0) which works for today returning returning records with Sales_Date between 2005-01-01 and 2005-11-30. But on 2006-01-03 I don't think this will return records with Sales_Date between 2005-01-01 and 2005-12-31. How do I tweak this? Thanks, Dan On 12/8/2005 11:10 AM, Aaron Bertrand [SQL Server MVP] wrote: Show quote > Do away with those silly conversions to character format. Internally SQL > Server will process this stuff much faster, without any implicit or explicit > conversions... > > WHERE Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) > AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())+1,0) > > > > "Dan" <dan@nospam.com> wrote in message > news:ArZlf.20$AX6.257@news.uswest.net... >> Hi, >> >> I am using a query tool that only allows me to define "custom sql" in the >> WHERE clause. I want to specify that sales_date be between the first of >> the year and last day of the most recent full month. The query will run >> on the 3rd day of the month. I have so far >> >> sales_date > = convert (char(4), getdate(), 112) + '0101' AND sales_date < >> convert (char (6), getdate(), 112) + '01') >> >> but this won't work for the 3rd of January. Looking for some help. >> >> Thanks, >> Dan > > How about
WHERE Sales_Date >= DATEADD ( YEAR, DATEDIFF(YEAR,0,CURRENT_TIMESTAMP) -- if it's January, subtract one year - CASE MONTH(CURRENT_TIMESTAMP) WHEN 1 THEN 1 ELSE 0 END, 0 ) AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,CURRENT_TIMESTAMP),0) -- cutoff is still the beginning of the current month Of course, this would be more efficient if you could use a stored procedure, where you could create these variables up front. A Show quote "Dan" <dan@nospam.com> wrote in message news:0C2mf.70$dm5.12208@news.uswest.net... > Based on Aaron's advice, I so far have > > WHERE Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) > AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0) > > which works for today returning returning records with Sales_Date between > 2005-01-01 and 2005-11-30. But on 2006-01-03 I don't think this will > return records with Sales_Date between 2005-01-01 and 2005-12-31. How do I > tweak this? > > Thanks, > Dan > see my response ( or rather my corrected response...)
Dan wrote: Show quote > Based on Aaron's advice, I so far have > > WHERE Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) > AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()),0) > > which works for today returning returning records with Sales_Date > between 2005-01-01 and 2005-11-30. But on 2006-01-03 I don't think this > will return records with Sales_Date between 2005-01-01 and 2005-12-31. > How do I tweak this? > > Thanks, > Dan > > > On 12/8/2005 11:10 AM, Aaron Bertrand [SQL Server MVP] wrote: > >> Do away with those silly conversions to character format. Internally >> SQL Server will process this stuff much faster, without any implicit >> or explicit conversions... >> >> WHERE Sales_Date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()),0) >> AND Sales_Date < DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())+1,0) >> >> >> >> "Dan" <dan@nospam.com> wrote in message >> news:ArZlf.20$AX6.257@news.uswest.net... >> >>> Hi, >>> >>> I am using a query tool that only allows me to define "custom sql" in >>> the WHERE clause. I want to specify that sales_date be between the >>> first of the year and last day of the most recent full month. The >>> query will run on the 3rd day of the month. I have so far >>> >>> sales_date > = convert (char(4), getdate(), 112) + '0101' AND >>> sales_date < convert (char (6), getdate(), 112) + '01') >>> >>> but this won't work for the 3rd of January. Looking for some help. >>> >>> Thanks, >>> Dan >> >> >> well it is compliant with the rules you specified? it should not return rows
from december if run in january, it should return rows from january when run in february - that is the rule you have isn't it? Peter Show quote >I want to specify that sales_date be between the first of the year and last >day of the most recent full month. Ah, I missed that part.
So, on January 3rd, 2005, what is the "first of the year"? 2005-01-01, or 2004-01-01??? A Show quote "Rogas69" <rogas69@no_spamers.o2.ie> wrote in message news:%238HJTpB$FHA.3676@tk2msftngp13.phx.gbl... > well it is compliant with the rules you specified? it should not return > rows from december if run in january, it should return rows from january > when run in february - that is the rule you have isn't it? > > Peter >>I want to specify that sales_date be between the first of the year and >>last day of the most recent full month. > > On January 3rd 2005 the range would be 2005-01-01 to 2005-12-31
Thanks, Dan On 12/8/2005 11:17 AM, Aaron Bertrand [SQL Server MVP] wrote: Show quote > Ah, I missed that part. > > So, on January 3rd, 2005, what is the "first of the year"? 2005-01-01, or > 2004-01-01??? > > A > > > > "Rogas69" <rogas69@no_spamers.o2.ie> wrote in message > news:%238HJTpB$FHA.3676@tk2msftngp13.phx.gbl... >> well it is compliant with the rules you specified? it should not return >> rows from december if run in january, it should return rows from january >> when run in february - that is the rule you have isn't it? >> >> Peter >>> I want to specify that sales_date be between the first of the year and >>> last day of the most recent full month. >> > > > On January 3rd 2005 the range would be 2005-01-01 to 2005-12-31 So now the end of the current year is suddenly "the more recent full month"? Your requirements are tripping over each other... On 12/8/2005 1:27 PM, Aaron Bertrand [SQL Server MVP] wrote:
>> On January 3rd 2005 the range would be 2005-01-01 to 2005-12-31 Oops. I meant 2004-01-01 to 2004-12-31. And on January 3rd 2006 it > > So now the end of the current year is suddenly "the more recent full month"? > Your requirements are tripping over each other... > > would be 2005-01-01 to 2005-12-31. Dan what do you mean by "won't work"?
you mean you want December's numbers in January? if so, then you don't want from the first of the current year, you want the year of the previous month. sales_date >= dateadd(year, datediff(year, 0, datediff(month,-1,getdate())), 0) and sales_date < dateadd(month, datediff(month, 0, getdate()), 0) Dan wrote: Show quote > Hi, > > I am using a query tool that only allows me to define "custom sql" in > the WHERE clause. I want to specify that sales_date be between the > first of the year and last day of the most recent full month. The query > will run on the 3rd day of the month. I have so far > > sales_date > = convert (char(4), getdate(), 112) + '0101' AND sales_date > < convert (char (6), getdate(), 112) + '01') > > but this won't work for the 3rd of January. Looking for some help. > > Thanks, > Dan I think Dan typo'ed on his last reply.
According to his first post, the business rules are: If run on 12/03/2005, date range is 01/01/2005 thru 12/01/2005. If run on 01/03/2006, date range is 01/01/2005 thru 01/01/2006. If run on 02/03/2006, date range is 01/01/2006 thru 02/01/2006. In my opinion, Dan either needs a UDF or a case statement in his WHERE clause so that if the month is 1 (January) it does year - 1. Otherwise, it's 01/01/current year. Joe Show quote "Trey Walpole" wrote: > what do you mean by "won't work"? > you mean you want December's numbers in January? if so, then you don't > want from the first of the current year, you want the year of the > previous month. > > sales_date >= dateadd(year, datediff(year, 0, > datediff(month,-1,getdate())), 0) and > sales_date < dateadd(month, datediff(month, 0, getdate()), 0) > > > Dan wrote: > > Hi, > > > > I am using a query tool that only allows me to define "custom sql" in > > the WHERE clause. I want to specify that sales_date be between the > > first of the year and last day of the most recent full month. The query > > will run on the 3rd day of the month. I have so far > > > > sales_date > = convert (char(4), getdate(), 112) + '0101' AND sales_date > > < convert (char (6), getdate(), 112) + '01') > > > > but this won't work for the 3rd of January. Looking for some help. > > > > Thanks, > > Dan > oops, that should read
sales_date >= dateadd(year, datediff(year, 0, DATEADD(month,-1,getdate())), 0) and sales_date < dateadd(month, datediff(month, 0, getdate()), 0) Trey Walpole wrote: Show quote > what do you mean by "won't work"? > you mean you want December's numbers in January? if so, then you don't > want from the first of the current year, you want the year of the > previous month. > > sales_date >= dateadd(year, datediff(year, 0, > datediff(month,-1,getdate())), 0) and > sales_date < dateadd(month, datediff(month, 0, getdate()), 0) > > > Dan wrote: > >> Hi, >> >> I am using a query tool that only allows me to define "custom sql" in >> the WHERE clause. I want to specify that sales_date be between the >> first of the year and last day of the most recent full month. The >> query will run on the 3rd day of the month. I have so far >> >> sales_date > = convert (char(4), getdate(), 112) + '0101' AND >> sales_date < convert (char (6), getdate(), 112) + '01') >> >> but this won't work for the 3rd of January. Looking for some help. >> >> Thanks, >> Dan  |
|||||||||||||||||||||||
Other interesting topics