|
|
You're in the
sql server new users
group:Sales within the first year
sql server new users:
I also have a Sales table. There is a field called SaleDate. I'd like to run a query, and flag any Sales within one year of the Clients start date as New Business. I thought I'd just this would be easy - use DateDiff, but I was wrong! For example: Client Start Date = 12/04/06 - 12th April 2006 Sale Date = 04/04/06 DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 Sale Date = 13/04/06 DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 So I get 12 months in both cases. As it states in the T-SQL help: DateDiff - Returns the number of date and time boundaries crossed between two specified dates. So what should I use instead of DateDiff? Thanks Vayse
How about DAYs for the datediff? If <365, you have a new client.
-- TheSQLGuru President Indicium Resources, Inc. [quoted text, click to view] "Vayse" <vvv> wrote in message news:OOOyp9MgHHA.5044@TK2MSFTNGP05.phx.gbl... >I have a Client table, with a field StartDate > I also have a Sales table. There is a field called SaleDate. > I'd like to run a query, and flag any Sales within one year of the Clients > start date as New Business. > I thought I'd just this would be easy - use DateDiff, but I was wrong! > > For example: > Client Start Date = 12/04/06 - 12th April 2006 > > Sale Date = 04/04/06 > DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 > Sale Date = 13/04/06 > DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 > > So I get 12 months in both cases. As it states in the T-SQL help: > DateDiff - Returns the number of date and time boundaries crossed between > two specified dates. > > So what should I use instead of DateDiff? > > Thanks > Vayse >
That did occur to me, but I couldn't figure out how to deal with leap years.
Still can't! [quoted text, click to view] "TheSQLGuru" <kgboles@earthlink.net> wrote in message news:%23nzUrRQgHHA.4868@TK2MSFTNGP06.phx.gbl... > How about DAYs for the datediff? If <365, you have a new client. > > -- > TheSQLGuru > President > Indicium Resources, Inc. > > "Vayse" <vvv> wrote in message > news:OOOyp9MgHHA.5044@TK2MSFTNGP05.phx.gbl... >>I have a Client table, with a field StartDate >> I also have a Sales table. There is a field called SaleDate. >> I'd like to run a query, and flag any Sales within one year of the >> Clients start date as New Business. >> I thought I'd just this would be easy - use DateDiff, but I was wrong! >> >> For example: >> Client Start Date = 12/04/06 - 12th April 2006 >> >> Sale Date = 04/04/06 >> DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 >> Sale Date = 13/04/06 >> DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 >> >> So I get 12 months in both cases. As it states in the T-SQL help: >> DateDiff - Returns the number of date and time boundaries crossed >> between two specified dates. >> >> So what should I use instead of DateDiff? >> >> Thanks >> Vayse >> > >
[quoted text, click to view]
On Tue, 17 Apr 2007 10:04:49 +0100, Vayse wrote: >I have a Client table, with a field StartDate >I also have a Sales table. There is a field called SaleDate. >I'd like to run a query, and flag any Sales within one year of the Clients >start date as New Business. >I thought I'd just this would be easy - use DateDiff, but I was wrong! > >For example: >Client Start Date = 12/04/06 - 12th April 2006 > >Sale Date = 04/04/06 >DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 >Sale Date = 13/04/06 >DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 > >So I get 12 months in both cases. As it states in the T-SQL help: >DateDiff - Returns the number of date and time boundaries crossed between >two specified dates. > >So what should I use instead of DateDiff? Hi Vayse, You can use the formula below to calculate the difference in "real" years (found on http://classicasp.aspfaq.com/date-time-routines-manipulation/given-two-dates-how-do-i-determine-an-age.html) DECLARE @birthdate SMALLDATETIME, @endDate SMALLDATETIME SET @birthdate = '19740224' SET @endDate = GETDATE() SELECT DATEDIFF ( YEAR, @birthdate, @endDate ) - CASE WHEN 100 * MONTH(@endDate) + DAY(@endDate) < 100 * MONTH(@birthdate) + DAY(@birthdate) THEN 1 ELSE 0 END -- Hugo Kornelis, SQL Server MVP
That should do it, thanks.
[quoted text, click to view] "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message news:63ea23hgghbct6037nnec2srvbb6fk3lpk@4ax.com... > On Tue, 17 Apr 2007 10:04:49 +0100, Vayse wrote: > >>I have a Client table, with a field StartDate >>I also have a Sales table. There is a field called SaleDate. >>I'd like to run a query, and flag any Sales within one year of the Clients >>start date as New Business. >>I thought I'd just this would be easy - use DateDiff, but I was wrong! >> >>For example: >>Client Start Date = 12/04/06 - 12th April 2006 >> >>Sale Date = 04/04/06 >>DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 >>Sale Date = 13/04/06 >>DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 >> >>So I get 12 months in both cases. As it states in the T-SQL help: >>DateDiff - Returns the number of date and time boundaries crossed between >>two specified dates. >> >>So what should I use instead of DateDiff? > > Hi Vayse, > > You can use the formula below to calculate the difference in "real" > years (found on > http://classicasp.aspfaq.com/date-time-routines-manipulation/given-two-dates-how-do-i-determine-an-age.html) > > DECLARE @birthdate SMALLDATETIME, @endDate SMALLDATETIME > SET @birthdate = '19740224' > SET @endDate = GETDATE() > > SELECT DATEDIFF > ( > YEAR, > @birthdate, > @endDate > ) - CASE > WHEN 100 * MONTH(@endDate) + DAY(@endDate) > < 100 * MONTH(@birthdate) + DAY(@birthdate) > THEN 1 ELSE 0 END > > > -- > Hugo Kornelis, SQL Server MVP > My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Isn't there a simple formula for calculating if any given year is a leap
year? Something like divisible by 4 but not 100 or some such. Anyway, no matter what it is (and I KNOW it exists and is simple), just use a CASE statement to test the year of concern and factor it into the overall datediff math. -- TheSQLGuru President Indicium Resources, Inc. [quoted text, click to view] "Vayse" <vvv> wrote in message news:OhQ6zZQgHHA.4188@TK2MSFTNGP02.phx.gbl... > That did occur to me, but I couldn't figure out how to deal with leap > years. > Still can't! > > "TheSQLGuru" <kgboles@earthlink.net> wrote in message > news:%23nzUrRQgHHA.4868@TK2MSFTNGP06.phx.gbl... >> How about DAYs for the datediff? If <365, you have a new client. >> >> -- >> TheSQLGuru >> President >> Indicium Resources, Inc. >> >> "Vayse" <vvv> wrote in message >> news:OOOyp9MgHHA.5044@TK2MSFTNGP05.phx.gbl... >>>I have a Client table, with a field StartDate >>> I also have a Sales table. There is a field called SaleDate. >>> I'd like to run a query, and flag any Sales within one year of the >>> Clients start date as New Business. >>> I thought I'd just this would be easy - use DateDiff, but I was wrong! >>> >>> For example: >>> Client Start Date = 12/04/06 - 12th April 2006 >>> >>> Sale Date = 04/04/06 >>> DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 >>> Sale Date = 13/04/06 >>> DATEDIFF(m, dbo.Clients.Client_StartDate, dbo.Sales.SaleDate) = 12 >>> >>> So I get 12 months in both cases. As it states in the T-SQL help: >>> DateDiff - Returns the number of date and time boundaries crossed >>> between two specified dates. >>> >>> So what should I use instead of DateDiff? >>> >>> Thanks >>> Vayse >>> >> >> > >
[quoted text, click to view]
On Fri, 27 Apr 2007 23:38:23 -0500, TheSQLGuru wrote: >Isn't there a simple formula for calculating if any given year is a leap >year? Hi SQLGuru, Rule: If divisible by 4 --> leapyear Exception to rule: If divisible by 100 --> not a leapyear Exception to exception: If divisible by 400 --> not a leapyear Or in T-SQL: IF @Year % 400 = 0 OR (@Year % 4 = 0 AND @Year % 100 <> 0) SET @Leap = 'Yes'; ELSE SET @Leap = 'No'; -- Hugo Kornelis, SQL Server MVP
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |