SQL statement increase question

12 Sep 2006 12:47 PM

Pauljh

Hi All,
   Firstly appologies if this is the wrong group but I couldn't find a
comp.lang.sql or any such one similar.
   My question is probably a simple SQL question for experts among you,
but come from a background of most my SQL being SELECT x FROM y I can't
fathom how to do it.
   The problem I have a "table" which is created and persistent no
problems, but I want to retrieve data from the table (again not a
problem) but I also want this query to return the % and amount
difference to the previous record (if there is one (else 100% and value
would be the current value)), Hope that makes sense?.

An example of what I think I'm trying to achieve

SELECT Employee,Cost,DateEffective, (SELECT Cost
FROM EmployeeCost WHERE DateEffective<???
and Employee=????) AS PreviousCost FROM EmployeeCost

So I assume somewhere has to be a sub query which "some how" selects
the previous record (base on employee and Date Effective) and gets the
cost and then returns a CALC for % and £.

   I hope that makes sense, any help much appreciated.

12 Sep 2006 12:56 PM

Tracy McKibben

Pauljh wrote:

Show quote

> Hi All,
>    Firstly appologies if this is the wrong group but I couldn't find a
> comp.lang.sql or any such one similar.
>    My question is probably a simple SQL question for experts among you,
> but come from a background of most my SQL being SELECT x FROM y I can't
> fathom how to do it.
>    The problem I have a "table" which is created and persistent no
> problems, but I want to retrieve data from the table (again not a
> problem) but I also want this query to return the % and amount
> difference to the previous record (if there is one (else 100% and value
> would be the current value)), Hope that makes sense?.
>
>   An example of what I think I'm trying to achieve
>
> SELECT Employee,Cost,DateEffective, (SELECT Cost
> FROM EmployeeCost WHERE DateEffective<???
> and Employee=????) AS PreviousCost FROM EmployeeCost
>
> So I assume somewhere has to be a sub query which "some how" selects
> the previous record (base on employee and Date Effective) and gets the
> cost and then returns a CALC for % and £.
>
>    I hope that makes sense, any help much appreciated.
>

There is an excellent article in this month's SQL Server Magazine
written by Itzik Ben-Gan on this very topic. If you're a subscriber,
the article can be read here:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=92743&DisplayTab=Article

If you're not, I'd suggest subscribing!

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

12 Sep 2006 1:01 PM

Razvan Socol

Try something like this:

SELECT Employee,Cost,DateEffective, (
SELECT Cost FROM EmployeeCost b
WHERE b.Employee=a.Employee
AND b.DateEffective=(
SELECT MAX(DateEffective) FROM EmployeeCost c
WHERE c.Employee=a.Employee
)
) AS PreviousCost
FROM EmployeeCost a

The above query is assuming that you have a unique key on
(Employee,DateEffective) in the EmployeeCost table.

Razvan

PS. The groups related to SQL in the comp.* hierarchy have a
comp.databases.* prefix, for example comp.databases.ms-sqlserver.

12 Sep 2006 1:35 PM

Pauljh

Razvan Socol wrote:

> Try something like this:
>
> <snip>
>
> PS. The groups related to SQL in the comp.* hierarchy have a
> comp.databases.* prefix, for example comp.databases.ms-sqlserver.

Slightly modified and worked a treat. Thanks

13 Sep 2006 6:37 AM

Razvan Socol

Pauljh wrote:

> Slightly modified and worked a treat. Thanks

I guess you observed that I forgot a condition in the subquery.
The corrected query would be:

SELECT Employee,Cost,DateEffective, (
SELECT Cost FROM EmployeeCost b
WHERE b.Employee=a.Employee
AND b.DateEffective=(
   SELECT MAX(c.DateEffective) FROM EmployeeCost c
   WHERE c.Employee=a.Employee
   AND c.DateEffective<a.DateEffective
)
) AS PreviousCost
FROM EmployeeCost a

Another version, which is probably faster, but uses the proprietary TOP
extension, is this:

SELECT Employee,Cost,DateEffective, (
SELECT TOP 1 Cost FROM EmployeeCost b
WHERE b.Employee=a.Employee
AND b.DateEffective<a.DateEffective
ORDER BY DateEffective
) AS PreviousCost
FROM EmployeeCost a

I guess that for both queries, having a unique index on (Employee,
DateEffective) will probably work better than having a unique index on
(DateEffective, Employee).

Razvan

12 Sep 2006 1:41 PM

Jim Underwood

You can also do this with a self join, and very similar code to what Razvan
posted.

<UNTESTED>

First, join the tables on employee and dateeffective, with the subquery to
get the max date less than the current date (from table a).

SELECT a.Employee
, a.Cost
, a.DateEffective
, b.Cost
FROM EmployeeCost a
left outer join employeeCost b
on b.Employee=a.Employee
and b.DateEffective =
(
   SELECT MAX(c.DateEffective)
   FROM EmployeeCost c
   WHERE c.Employee = b.Employee
   and c.DateEffective < a.DateEffective
)

Now you can make this into a view and work with that for your calculations,
or work directly with this (remember to handle nulls for table B).

SELECT a.Employee
, a.Cost as "NewCost"
, a.DateEffective
, coalesce(b.Cost,0) as "OldCost"
, (a.cost - coalesce(b.Cost,0)) as "Difference"
, (a.cost - coalesce(b.Cost,0))/a.cost as "Percent"
FROM EmployeeCost as a
left outer join employeeCost as b
on b.Employee=a.Employee
and b.DateEffective =
(
   SELECT MAX(c.DateEffective)
   FROM EmployeeCost c
   WHERE c.Employee = b.Employee
   and c.DateEffective < a.DateEffective
)

Show quote

"Razvan Socol" <rso***@gmail.com> wrote in message
news:1158066064.923388.204560@p79g2000cwp.googlegroups.com...
> Try something like this:
>
> SELECT Employee,Cost,DateEffective, (
> SELECT Cost FROM EmployeeCost b
> WHERE b.Employee=a.Employee
>   AND b.DateEffective=(
>    SELECT MAX(DateEffective) FROM EmployeeCost c
>    WHERE c.Employee=a.Employee
>   )
> ) AS PreviousCost
> FROM EmployeeCost a
>
> The above query is assuming that you have a unique key on
> (Employee,DateEffective) in the EmployeeCost table.
>
> Razvan
>
> PS. The groups related to SQL in the comp.* hierarchy have a
> comp.databases.* prefix, for example comp.databases.ms-sqlserver.
>

12 Sep 2006 1:46 PM

Pauljh

Hi All

> Try something like this:
>
> SELECT Employee,Cost,DateEffective, (
> SELECT Cost FROM EmployeeCost b
> WHERE b.Employee=a.Employee
>   AND b.DateEffective=(
>    SELECT MAX(DateEffective) FROM EmployeeCost c
>    WHERE c.Employee=a.Employee
>   )
> ) AS PreviousCost
> FROM EmployeeCost a
>

One further (hopefully quick question) how can I now reference the
PreviousCost to do a PreviousCost-Cost without having to repeat the sub
query again?

Ta!

12 Sep 2006 3:18 PM

Jim Underwood

In this case you would enclose the entire query in parenthesis to make it an
inline view. However I think you need an extraq criteria in the subquery
for c.dateeffective < 1.dateeffective.

select CostCompare.employee
, CostCompare.cost
, CostCompare.DateEffective
, CostCompare.PreviousCost
, CostCompare.cost - CostCompare.PreviousCost
From
(
SELECT Employee,Cost,DateEffective, (
SELECT Cost FROM EmployeeCost b
WHERE b.Employee=a.Employee
AND b.DateEffective=(
SELECT MAX(DateEffective) FROM EmployeeCost c
WHERE c.Employee=a.Employee
)
) AS PreviousCost
FROM EmployeeCost a
) as CostCompare

Show quote

"Pauljh" <Pau***@hushmail.com> wrote in message
news:1158068779.614015.288820@i42g2000cwa.googlegroups.com...
> Hi All
> > Try something like this:
> >
> > SELECT Employee,Cost,DateEffective, (
> > SELECT Cost FROM EmployeeCost b
> > WHERE b.Employee=a.Employee
> >   AND b.DateEffective=(
> >    SELECT MAX(DateEffective) FROM EmployeeCost c
> >    WHERE c.Employee=a.Employee
> >   )
> > ) AS PreviousCost
> > FROM EmployeeCost a
> >
>
> One further (hopefully quick question) how can I now reference the
> PreviousCost to do a PreviousCost-Cost without having to repeat the sub
> query again?
>
> Ta!
>