Last year comparable Business Day

/**
DESCRIPTION
Okay, I have an app that I need to compare information from two dates
in. The two dates are whatever date is passed to it, and its equivalent
business day from last year. For instance, today is January 10th,
2006. January 10th is the tuesday of the second w in 2006. I need to
be able to compare it the tuesday of the second FULL w in 2005 (Full
w being the catch).
So, if you give it 1/6/06, it will output 1/7/05
1/2/06 => 1/3/05
12/31/06 => 1/1/06
I have a function below that is almost there, but it runs into some
hiccups towards the end of the year. Try inputing 12/31/05, and you
will see what I mean.
That being said, here is my function. You wil have to doctor a few
lines due to word wrap.
FUNCTION
**/
CREATE FUNCTION fnGetLastYearComparableDate(@.datetime datetime)
RETURNS DATETIME
AS
BEGIN
DECLARE @.DATELY datetime,
@.WEEKTY int,
@.WEEKLY int,
@.WEEKDAYTY int,
@.WEEKDAYLY int,
@.WDSUNDAYLY datetime,
@.WDSATURDAYLY datetime,
@.BOY datetime,
@.BOLY datetime
SET @.DATELY = DATEADD("yyyy",-1,@.datetime)
SET @.BOY = CONVERT(datetime,'1/1/' +
CONVERT(varchar(4),datepart("yyyy",@.datetime)))
SET @.BOLY = CONVERT(datetime,'1/1/' +
CONVERT(varchar(4),datepart("yyyy",@.DATELY)))
SET @.WEEKTY = DATEPART("wk", @.datetime)
SET @.WEEKLY = DATEPART("wk", @.DATELY)
BEGIN
IF @.WEEKTY <> @.WEEKLY
IF @.WEEKTY > @.WEEKLY
SET @.DATELY = DATEADD("wk", (@.WEEKTY - @.WEEKLY), @.DATELY)
ELSE IF @.WEEKTY < @.WEEKLY
SET @.DATELY = DATEADD("wk", ((@.WEEKLY - @.WEEKTY) * -1), @.DATELY)
END
SET @.WDSUNDAYLY = DATEADD("dw",(DATEPART("dw",@.BOLY) - 1) * -1,@.BOLY)
SET @.WDSATURDAYLY = DATEADD("dw",((DATEPART("dw",@.BOLY) - 1) * -1 +
6),@.BOLY)
BEGIN
IF (DATEPART("yyyy",@.WDSUNDAYLY) < DATEPART("yyyy",@.WDSATURDAYLY)) OR
(@.WEEKTY <> @.WEEKLY)
BEGIN
SET @.DATELY = DATEADD("d",7,@.DATELY)
END
END
SET @.WEEKDAYTY = DATEPART("dw", @.datetime)
SET @.WEEKDAYLY = DATEPART("dw", @.DATELY)
BEGIN
IF @.WEEKDAYTY <> @.WEEKDAYLY
IF @.WEEKDAYTY > @.WEEKDAYLY
SET @.DATELY = DATEADD("dw", (@.WEEKDAYTY - @.WEEKDAYLY), @.DATELY)
ELSE IF @.WEEKDAYTY < @.WEEKDAYLY
SET @.DATELY = DATEADD("dw", ((@.WEEKDAYLY - @.WEEKDAYTY) * -1),
@.DATELY)
END
RETURN @.DATELY
ENDOn 10 Jan 2006 14:11:54 -0800, Brian Baumann wrote:

>/**
>DESCRIPTION
>Okay, I have an app that I need to compare information from two dates
>in. The two dates are whatever date is passed to it, and its equivalent
>business day from last year. For instance, today is January 10th,
>2006. January 10th is the tuesday of the second w in 2006. I need to
>be able to compare it the tuesday of the second FULL w in 2005 (Full
>w being the catch).
(snip)
Hi Brian,
I didn't take the time to disect your code. Instead of going over the
top with complex date calculations, why not simply create a Calendar
table? (See www.aspfaq.com/2519). Your problem can then be solved by a
simple join on same w, same wday, and year = year - 1.
(snip)
>I have a function below that is almost there, but it runs into some
>hiccups towards the end of the year. Try inputing 12/31/05, and you
>will see what I mean.
I didn't test it, but might this not be related to an omission in the
specification? After all, some years have a 53rd w and others don't.
Or they do, but with different wdays in it. Your spec doesn't say how
to handle that case.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
> I didn't take the time to disect your code. Instead of going over the
> top with complex date calculations, why not simply create a Calendar
> table? (See www.aspfaq.com/2519). Your problem can then be solved by a
> simple join on same w, same wday, and year = year - 1.
I don't think so. Brian said first full w. I interpret this as
that the corresponding day to 2007-01-08 will be 2006-01-09, but
the corresponding day to 2008-01-08, will be 2007-01-01, because
in 2008 New Years Day is on a Tuesday, so the first full w starts
on 2008-01-07. (Assuming here that you ws starts on Monday.)
In the two w-numbering system, w 1 is eiher the w that
Jan 1st falls on (this is what SQL Server uses) or the w that
Jan 4th falls on (this is the ISO standard for w numbering).
Of course Brian could number the ws in his calendar table after
his own liking, but then he still left with the problem that to number
them. There are also funny things, with New Years Eve mapping back
to New Years Day the same year. It would be a very strange calendar.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 10 Jan 2006 23:30:58 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
>I don't think so.
(snip)
>Of course Brian could number the ws in his calendar table after
>his own liking,
Hi Erland,
That's what I meant. I wanted to add this to my message, but forgot
about it and clicked Send.
Thanks for the addition!
Hugo Kornelis, SQL Server MVP|||So if it is not in the first full w (starting Monday?) then it is the
Monday of the next w?
So if Tues is the day in year x, and w 1 in year x-1 is Tues, then the
day you want is the next Monday?
What about holidays that land on business days?
William Stacey [MVP]
"Brian Baumann" <ferretworks@.gmail.com> wrote in message
news:1136931114.105675.287240@.g44g2000cwa.googlegroups.com...
> /**
> DESCRIPTION
> Okay, I have an app that I need to compare information from two dates
> in. The two dates are whatever date is passed to it, and its equivalent
> business day from last year. For instance, today is January 10th,
> 2006. January 10th is the tuesday of the second w in 2006. I need to
> be able to compare it the tuesday of the second FULL w in 2005 (Full
> w being the catch).
> So, if you give it 1/6/06, it will output 1/7/05
> 1/2/06 => 1/3/05
> 12/31/06 => 1/1/06
> I have a function below that is almost there, but it runs into some
> hiccups towards the end of the year. Try inputing 12/31/05, and you
> will see what I mean.
> That being said, here is my function. You wil have to doctor a few
> lines due to word wrap.
> FUNCTION
> **/
> CREATE FUNCTION fnGetLastYearComparableDate(@.datetime datetime)
> RETURNS DATETIME
> AS
> BEGIN
> DECLARE @.DATELY datetime,
> @.WEEKTY int,
> @.WEEKLY int,
> @.WEEKDAYTY int,
> @.WEEKDAYLY int,
> @.WDSUNDAYLY datetime,
> @.WDSATURDAYLY datetime,
> @.BOY datetime,
> @.BOLY datetime
>
> SET @.DATELY = DATEADD("yyyy",-1,@.datetime)
> SET @.BOY = CONVERT(datetime,'1/1/' +
> CONVERT(varchar(4),datepart("yyyy",@.datetime)))
> SET @.BOLY = CONVERT(datetime,'1/1/' +
> CONVERT(varchar(4),datepart("yyyy",@.DATELY)))
> SET @.WEEKTY = DATEPART("wk", @.datetime)
> SET @.WEEKLY = DATEPART("wk", @.DATELY)
> BEGIN
> IF @.WEEKTY <> @.WEEKLY
> IF @.WEEKTY > @.WEEKLY
> SET @.DATELY = DATEADD("wk", (@.WEEKTY - @.WEEKLY), @.DATELY)
> ELSE IF @.WEEKTY < @.WEEKLY
> SET @.DATELY = DATEADD("wk", ((@.WEEKLY - @.WEEKTY) * -1), @.DATELY)
> END
> SET @.WDSUNDAYLY = DATEADD("dw",(DATEPART("dw",@.BOLY) - 1) * -1,@.BOLY)
> SET @.WDSATURDAYLY = DATEADD("dw",((DATEPART("dw",@.BOLY) - 1) * -1 +
> 6),@.BOLY)
>
> BEGIN
> IF (DATEPART("yyyy",@.WDSUNDAYLY) < DATEPART("yyyy",@.WDSATURDAYLY)) OR
> (@.WEEKTY <> @.WEEKLY)
> BEGIN
> SET @.DATELY = DATEADD("d",7,@.DATELY)
> END
> END
>
> SET @.WEEKDAYTY = DATEPART("dw", @.datetime)
> SET @.WEEKDAYLY = DATEPART("dw", @.DATELY)
> BEGIN
> IF @.WEEKDAYTY <> @.WEEKDAYLY
> IF @.WEEKDAYTY > @.WEEKDAYLY
> SET @.DATELY = DATEADD("dw", (@.WEEKDAYTY - @.WEEKDAYLY), @.DATELY)
> ELSE IF @.WEEKDAYTY < @.WEEKDAYLY
> SET @.DATELY = DATEADD("dw", ((@.WEEKDAYLY - @.WEEKDAYTY) * -1),
> @.DATELY)
> END
>
> RETURN @.DATELY
> END
>|||This whole approach will fail when you get to lunar/solar holidays,
like Easter. Want to see the code for Easter after the Y2K
non-leapyear? Want to see the code for Easter before the Y2K
non-leapyear?
The best way is to build a Calendar table with a coumn for "equivalent
business day from last year" values. Build a decade or two with a
spreadsheet after talking to the accounting department.|||Not sure who to respond to on this, so I will respond to my original
post and hopefully it will get to everyone.
I figured out what to do, and might I add, it is way simpler than I had
ever imagined. Kinda makes me wanna kick myself in butt.
DATEADD("wk",-52,@.DATE)
Works like a champ and hasn't failed. Always gets me day for day of a
comparable look last year.|||Brian,
One thing you will want to take a look at is what happens in the last two
days of the year. Dec 31st will return Jan 1st of the current year in a
normal year, and Jan 2nd in a leap year. Dec 30th will return Jan 1st
during leap year.
Also, no matter what day it is you will never get back Jan 1st of the
previous year. Following a leap year you wont be able to get back Jan 2nd
either.
This may be perfectly acceptable for your business need. It is something
that needs to be understood before using this approach, however.
Jusat
"Brian Baumann" <ferretworks@.gmail.com> wrote in message
news:1137162876.033640.264260@.z14g2000cwz.googlegroups.com...
> Not sure who to respond to on this, so I will respond to my original
> post and hopefully it will get to everyone.
> I figured out what to do, and might I add, it is way simpler than I had
> ever imagined. Kinda makes me wanna kick myself in butt.
> DATEADD("wk",-52,@.DATE)
> Works like a champ and hasn't failed. Always gets me day for day of a
> comparable look last year.
>

Last Two Rows

I have a company table that lists the company name,
fiscal year, and fees. I am trying to compare the last
two years without specifically setting the criteria
because I want the data to drive the comparison between
the two.
Adding to the complexity is the fact that a company can
have two rows with the same year and different fees so I
would need to sum the fees for the year first and then do
the computation to display the percent change from year
to year.
An example of the data would be:
Microsoft 2005 2000000
Microsoft 2004 1750000
Dell 2004 1200000
Dell 2003 1100000
HP 2004 500000
HP 2004 250000
HP 2003 600000
I would need the output to look like this:
Microsoft 14.29%
Dell 9.09%
HP 25.00%
Thanks.How does the percentage calcuates from ?
"daniel" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0c3001c54100$6fa29aa0$a401280a@.phx.gbl...
>I have a company table that lists the company name,
> fiscal year, and fees. I am trying to compare the last
> two years without specifically setting the criteria
> because I want the data to drive the comparison between
> the two.
> Adding to the complexity is the fact that a company can
> have two rows with the same year and different fees so I
> would need to sum the fees for the year first and then do
> the computation to display the percent change from year
> to year.
> An example of the data would be:
> Microsoft 2005 2000000
> Microsoft 2004 1750000
> Dell 2004 1200000
> Dell 2003 1100000
> HP 2004 500000
> HP 2004 250000
> HP 2003 600000
> I would need the output to look like this:
> Microsoft 14.29%
> Dell 9.09%
> HP 25.00%
> Thanks.|||the % change is calculated by subtracting the latest date
fee from last years fee. Then divide the difference by
last years fee.
So in MSFT's case subtract 2000000-1750000 = 250000 and
then divide 250000/1750000.
>--Original Message--
>How does the percentage calcuates from ?
>
>"daniel" <anonymous@.discussions.microsoft.com> schrieb
im Newsbeitrag
>news:0c3001c54100$6fa29aa0$a401280a@.phx.gbl...
I
do
>
>.
>|||Using the following table structure and data:
CREATE TABLE CompanyFees (
Company varchar(50) NOT NULL,
FiscalYear smallint NOT NULL,
Fees money NOT NULL
)
INSERT INTO CompanyFees VALUES ('Microsoft', 2005, 2000000)
INSERT INTO CompanyFees VALUES ('Microsoft', 2004, 1750000)
INSERT INTO CompanyFees VALUES ('Dell', 2004, 1200000)
INSERT INTO CompanyFees VALUES ('Dell', 2003, 1100000)
INSERT INTO CompanyFees VALUES ('HP', 2004, 500000)
INSERT INTO CompanyFees VALUES ('HP', 2004, 250000)
INSERT INTO CompanyFees VALUES ('HP', 2003, 600000)
Use this query:
SELECT a.Company, a.FiscalYear, a.SumOfFees as ThisYearFees,
b.SumOfFees as PreviousYearFees,
(1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
FROM (
SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
FROM CompanyFees GROUP BY Company, FiscalYear
) a INNER JOIN (
SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
FROM CompanyFees GROUP BY Company, FiscalYear
) b ON a.Company=b.Company AND a.FiscalYear=b.FiscalYear+1
Razvan|||What is the primary key? Please help us to help you by including DDL
with future posts.
Try:
SELECT company, yr,
(SELECT (SUM(T.fee)-SUM(fee))
/CAST(SUM(fee) AS REAL)*100
FROM YourTable
WHERE company = T.company
AND yr = T.yr - 1) AS pct
FROM YourTable AS T
WHERE yr =
(SELECT MAX(yr)
FROM YourTable
WHERE company = T.company)
GROUP BY company, yr
David Portas
SQL Server MVP
--|||Try,
use northwind
go
create table t (
company varchar(25),
fiscal_year int,
fee int
)
go
insert into t values('Microsoft', 2005, 2000000)
insert into t values('Microsoft', 2004, 1750000)
insert into t values('Dell', 2004, 1200000)
insert into t values('Dell', 2003, 1100000)
insert into t values('HP', 2004, 500000)
insert into t values('HP', 2004, 250000)
insert into t values('HP', 2003, 600000)
insert into t values('YAHOO', 2005, 1600000)
go
select
a.company,
cast(max(((a.sum_fee * 100.00) / b.sum_fee) - 100.00) as decimal(5, 2)) as
percent_change
from
(
select
company,
fiscal_year,
sum(fee) as sum_fee
from
t
where
(select count(distinct c.fiscal_year) from t as c where c.company =
t.company and c.fiscal_year >= t.fiscal_year) <= 2
group by
company,
fiscal_year
) as a
inner join
(
select
company,
fiscal_year,
sum(fee) as sum_fee
from
t
where
(select count(distinct c.fiscal_year) from t as c where c.company =
t.company and c.fiscal_year >= t.fiscal_year) <= 2
group by
company,
fiscal_year
) as b
on a.company = b.company
and a.fiscal_year = b.fiscal_year + 1
group by
a.company
go
drop table t
go
AMB
"daniel" wrote:

> I have a company table that lists the company name,
> fiscal year, and fees. I am trying to compare the last
> two years without specifically setting the criteria
> because I want the data to drive the comparison between
> the two.
> Adding to the complexity is the fact that a company can
> have two rows with the same year and different fees so I
> would need to sum the fees for the year first and then do
> the computation to display the percent change from year
> to year.
> An example of the data would be:
> Microsoft 2005 2000000
> Microsoft 2004 1750000
> Dell 2004 1200000
> Dell 2003 1100000
> HP 2004 500000
> HP 2004 250000
> HP 2003 600000
> I would need the output to look like this:
> Microsoft 14.29%
> Dell 9.09%
> HP 25.00%
> Thanks.
>|||Thanks Razvan. I had to check for divide by zero but
other than that it works perfecty!
>--Original Message--
>Using the following table structure and data:
>CREATE TABLE CompanyFees (
> Company varchar(50) NOT NULL,
> FiscalYear smallint NOT NULL,
> Fees money NOT NULL
> )
>INSERT INTO CompanyFees VALUES ('Microsoft', 2005,
2000000)
>INSERT INTO CompanyFees VALUES ('Microsoft', 2004,
1750000)
>INSERT INTO CompanyFees VALUES ('Dell', 2004,
1200000)
>INSERT INTO CompanyFees VALUES ('Dell', 2003,
1100000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
500000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
250000)
>INSERT INTO CompanyFees VALUES ('HP', 2003,
600000)
>Use this query:
>SELECT a.Company, a.FiscalYear, a.SumOfFees as
ThisYearFees,
> b.SumOfFees as PreviousYearFees,
> (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>FROM (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) a INNER JOIN (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) b ON a.Company=b.Company AND
a.FiscalYear=b.FiscalYear+1
>Razvan
>.
>|||one question though. what does the 1. mean in the
Column: (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>--Original Message--
>Using the following table structure and data:
>CREATE TABLE CompanyFees (
> Company varchar(50) NOT NULL,
> FiscalYear smallint NOT NULL,
> Fees money NOT NULL
> )
>INSERT INTO CompanyFees VALUES ('Microsoft', 2005,
2000000)
>INSERT INTO CompanyFees VALUES ('Microsoft', 2004,
1750000)
>INSERT INTO CompanyFees VALUES ('Dell', 2004,
1200000)
>INSERT INTO CompanyFees VALUES ('Dell', 2003,
1100000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
500000)
>INSERT INTO CompanyFees VALUES ('HP', 2004,
250000)
>INSERT INTO CompanyFees VALUES ('HP', 2003,
600000)
>Use this query:
>SELECT a.Company, a.FiscalYear, a.SumOfFees as
ThisYearFees,
> b.SumOfFees as PreviousYearFees,
> (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
>FROM (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) a INNER JOIN (
> SELECT Company, FiscalYear, SUM(Fees) as SumOfFees
> FROM CompanyFees GROUP BY Company, FiscalYear
> ) b ON a.Company=b.Company AND
a.FiscalYear=b.FiscalYear+1
>Razvan
>.
>|||Making sure it is converted to a decimal instead of doing integer division
(which yields an integer, e.g. 45/100 = 0).
http://www.aspfaq.com/2483
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"daniel" <anonymous@.discussions.microsoft.com> wrote in message
news:025501c5410a$07c226d0$a501280a@.phx.gbl...
> one question though. what does the 1. mean in the
> Column: (1.*a.SumOfFees/b.SumOfFees-1)*100 as Variation
> 2000000)
> 1750000)
> 1200000)
> 1100000)
> 500000)
> 250000)
> 600000)
> ThisYearFees,
> a.FiscalYear=b.FiscalYear+1|||Another approach:
SELECT Company,
100 * ( MAX( CASE seq WHEN 0 THEN Fees END ) /
MAX( CASE seq WHEN 1 THEN Fees END ) - 1 )
FROM ( SELECT c1.Company, c1.FiscalYear, SUM( c1.Fees ),
( SELECT COUNT( DISTINCT c2.FiscalYear )
FROM CompanyFees c2
WHERE c2.Company = c1.Company
AND c2.FiscalYear > c1.FiscalYear )
FROM CompanyFees c1
GROUP BY c1.Company, c1.FiscalYear
) D ( Company, FiscalYear, Fees, seq )
GROUP BY Company ;
Anith