No idea where to start with Query

[Carca...@gmail.com]

I need some help with a query using SQL 2005 and I really don't know
where to start.
I have 3 columns: Date(dateTime), Name(varchar), Value(decimal)
I need to retireive 3 values, one query or all, or one query for
each. It makes no difference to me.

1)For each Name I need to get the difference of current day's value
and previous day's value
(TodayValue- PreviousDayValue)

2)For each Name I need to get the sum of difference of yesterday's
value and today's value for the current month
So something like (Day1Value - Day2Value) + (Day2Value - Day3Value)
+...+ (Day29Value - Day30Value)

3)Same as #2 but just for the current year.

Any help would be greatly appreciated.

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it. What you did post was a pile of vague names
and/or reserved words.

CREATE TABLE Foobar
(walk_date DATETIME

> I need some help with a query using SQL 2005 and I really don't know
> where to start.
> I have 3 columns: Date(dateTime), Name(varchar), Value(decimal)

> I need to retreive 3 values, one query or all, or one query for

[--CELKO--]

The best place to start is with DDL, so that people do not have to

guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are. If you know how, follow ISO-11179
data element naming conventions and formatting rules. Sample data is
also a good idea, along with clear specifications. It is very hard to
debug code when you do not let us see it. What you did post was a pile

of vague names and/or reserved words. Let's make it real and sensible:

CREATE TABLE DogSchedule
(walk_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
dog_name CHAR(15) NOT NULL,
walk_kilometers DECIMAL (5,2) NOT NULL,
PRIMARY KEY (walk_date, dog_name));

1)For each dog_name I need to get the difference of current day's

value and previous day's value <<

You did not tell us if you are sure that all days are represented in
the table? Only one walk per day? What constraint enforces that
business rule? My point is that SQL is an integrated whole -- you
cannot separate DDL and DML; they must work together!

Next, you are going to be using the delta from day to day, so let's
put that in a VIEW.

CREATE VIEW DeltaDogWalks (walk_date, dog_name, delta_kilometers)
AS
SELECT D2.walk_date, D2.dog_name,
(D2.walk_kilometers - D1.walk_kilometers)
FROM DogSchedule AS D1, DogSchedule AS D2
WHERE D1.dog_name = D2.dog_name
AND D2.walk_date = DATEADD(DD, 1, D1.walk_date);

To sum the deltas, set up a report periods table that you can adjust:

CREATE TABLE ReportPeriods
(period_name CHAR(15) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK(start_date < end_date));

Now use a "walk_date BETWEEN start_date AND end_date" to group on the
names of the reporting periods.

>> 2)For each dog_name I need to get the sum of difference of yesterday's value and today's value for the current month So something like (Day1Value - Day2Value) + (Day2Value - Day3Value) +...+ (Day29Value - Day30Value) <<

[Carca...@gmail.com]

I will do my best, I am not really a DB guy but the boss thinks I
should be able to pull it off :-)
This is a single stand alone table, and if this table breaks some kind
of standards, I didn't create it so it's not my fault :-)

CREATE TABLE [dbo].[position](
[SID] [integer] PRIMARY KEY,
[Date] [datetime] NOT NULL,
[Symbol] [varchar](6) NOT NULL,
[AcctValue] [decimal](18, 2) NOT NULL
)

Some Sample Data
[SID] [Date] [Symbol]
[AcctValue]
1142 2007-10-03 00:00:00.000 MSFT 300.00
1141 2007-10-03 00:00:00.000 AAPL 74062.50
1140 2007-10-03 00:00:00.000 RIMM 23200.00
1130 2007-10-02 00:00:00.000 MSFT -49218.75
1129 2007-10-02 00:00:00.000 AAPL -1843.75
1128 2007-10-02 00:00:00.000 RIMM 19937.50
1127 2007-10-01 00:00:00.000 MSFT 743.75
1126 2007-10-01 00:00:00.000 AAPL 100.00
1125 2007-10-01 00:00:00.000 RIMM -2812.50
.
.
.
3 2007-01-02 00:00:00.000 MSFT 130131.25
2 2007-01-02 00:00:00.000 AAPL 61875.00
1 2007-01-02 00:00:00.000 RIMM -131400.00

Just to restate the question representing the sample data/table
I need to retireive 3 values, one query or all, or one query for

each. It makes no difference to me.

1)For each Symbol need to get the difference of current day's
AcctValue
and previous day's AcctValue
(TodayAcctValue- PreviousDayAcctValue)

2)For each Symbol I need to get the sum of difference of yesterday's
AcctValue and today's AcctValue for the current month
So something like (Day1AcctValue- Day2AcctValue) + (Day2AcctValue -
Day3AcctValue)+...+ (Day29AcctValue - Day30AcctValue)

3)Same as #2 but just for the current year.

I hope this time I phrased things better.
Thanks

[Carca...@gmail.com]

I forgot to mention. Any given day may not include a row for any
given Symbol

[Michael MacGregor]

Final step you need to provide is to show what you expect as a result based
on the example data you have already provided.

Michael MacGregor
Database Architect

[Carca...@gmail.com]

On Oct 4, 11:49 am, "Michael MacGregor"

Fair enough
Here is what I would like to see as a result
1)One day difference (2007-10-03 00:00:00.000 through 2007-10-02
00:00:00.000)
[Symbol] [Delta]
MSFT -49518.75
AAPL 75906.25
RIMM 3262.50

2)Month difference (2007-10-03 00:00:00.000 through 2007-10-02
00:00:00.000)
[Symbol] [Delta]
MSFT -156.25
AAPL 73962.5
RIMM 26012.5

3)Year difference I can't do with the sample data provided, but I
think the idea is clear

[Jim Underwood]

First, I do not think you need a sum here. Can't you just find the
difference between the first day of the month and the last?

ie:
(a-b) + (b-c) + (c-d) = a - b + b - c + c - d = a-c

So, first you determine the first and last day that you want to compare, and
you only use those. You can join the table to itself with two different
date criteria and get the two dates you need, then just subtract the values.

A simplified example:

select a.Symbol
, a.AcctValue - b.AcctValue as difference
from dbo.position as a
inner join dbo.position as b
on a.Symbol = B.Symbol
where a.[date] = DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP) ,
'20040101')
and b.[date] = DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP) -
1, '20040101')

The DATEADD logic is in there to round the date to midnight and eliminate
differences in time. To see why it is needed and how it works, take a look
at Tibor Karaszi's article on dates:
http://www.karaszi.com/SQLServer/info_datetime.asp

Note that if your dates in your table specify the time then you will want to
apply the formular to your date columns as well.

If you put this into a stored procedure you can use variables to store the
dates and the code will be easier to read:

select a.Symbol
, a.AcctValue - b.AcctValue as difference
from dbo.position as a
inner join dbo.position as b
on a.Symbol = B.Symbol

where a.[date] = @EndDate
and b.[date] = @StartDate

Note that this uses an inner join, so if either the start or end dates do
not have an entry you will not get back a row. You didn't explain what you
wanted to do in this instance, so I just decided to exclude those rows.

<Carca...@gmail.com> wrote in message
news:1191511816....@g4g2000hsf.googlegroups.com...

[Carca...@gmail.com]

On Oct 4, 12:41 pm, "Jim Underwood"

> <Carcass...@gmail.com> wrote in message

>
> news:1191511816....@g4g2000hsf.googlegroups.com...
>
> >I need some help with a query using SQL 2005 and I really don't know
> > where to start.
> > I have 3 columns: Date(dateTime), Name(varchar), Value(decimal)
> > I need to retireive 3 values, one query or all, or one query for
> > each. It makes no difference to me.
>
> > 1)For each Name I need to get the difference of current day's value
> > and previous day's value
> > (TodayValue- PreviousDayValue)
>
> > 2)For each Name I need to get the sum of difference of yesterday's
> > value and today's value for the current month
> > So something like (Day1Value - Day2Value) + (Day2Value - Day3Value)
> > +...+ (Day29Value - Day30Value)
>
> > 3)Same as #2 but just for the current year.
>
> > Any help would be greatly appreciated.

Jim,
You have a point about not needing the sum. But it leads this
problem. I do not know what the first date that a record has been
inserted for a given month/year. So let's say I am doing September:
9-3-2007 could very well be the first record for a Symbol. Which means
my formula is (Sept30Value - Sep3Value). And other Symbol might be
(Sep29Value - Sep1 Value) This is where my limited SQL experience
becomes an issue. Maybe your explanation takes care of this in some
way that I am missing?

[Jim Underwood]

In that case, I would use a subquery to get the MAX or MIN date < or > the
date in question.

select a.Symbol
, a.AcctValue - b.AcctValue as difference
from dbo.position as a
inner join dbo.position as b
on a.Symbol = B.Symbol
where a.[date] =

(
select max(a1.[date]) from dbo.position as a1
where a1.symbol = a.symbol
and a1.[date] <= @EndDate

)
and b.[date] =

(
select min(b1.[date]) from dbo.position as b1
where b1.symbol = a.symbol
and b1.[date] >= @StartDate
)

<Carca...@gmail.com> wrote in message
news:1191520903....@r29g2000hsg.googlegroups.com...

[Carca...@gmail.com]

On Oct 4, 1:43 pm, "Jim Underwood"

<james.underwood_nos...@fallonclinic.org> wrote:
> In that case, I would use a subquery to get the MAX or MIN date < or > the
> date in question.
>
> select a.Symbol
> , a.AcctValue - b.AcctValue as difference
> from dbo.position as a
> inner join dbo.position as b
> on a.Symbol = B.Symbol
> where a.[date] =
> (
> select max(a1.[date]) from dbo.position as a1
> where a1.symbol = a.symbol
> and a1.[date] <= @EndDate
> )
> and b.[date] =
> (
> select min(b1.[date]) from dbo.position as b1
> where b1.symbol = a.symbol
> and b1.[date] >= @StartDate
> )
>

Seems like I finally got it. Thanks guys for all the help, I had been
pounding my head against the wall for a couple days now.