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)
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.
> 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 > 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)
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) <<
> 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 > > 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.
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 )
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)
> On Oct 4, 10:37 am, --CELKO-- <jcelko...@earthlink.net> wrote:
> > 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 > > > 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.
> 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 > )
> 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
I forgot to mention. Any given day may not include a row for any given Symbol
<macnoknifes...@noemailspam.com> wrote: > 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
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
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.
>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)
<james.underwood_nos...@fallonclinic.org> wrote: > 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.
> >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?
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 )
> On Oct 4, 12:41 pm, "Jim Underwood" > <james.underwood_nos...@fallonclinic.org> wrote: >> 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.
>> >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?
<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 > )
> > On Oct 4, 12:41 pm, "Jim Underwood" > > <james.underwood_nos...@fallonclinic.org> wrote: > >> 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.
> >> >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?
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.
|
