Get a max value from one table base on date from another.

I have a problem where I need to join multiple tables. Part of the query needs me to make the following relationship in a join.

Say I have a table with items, prices, and dates.

PriceTable

ITEM PRICE DATE

A 1.00 5/5/2000

B 3.00 1/1/2000

A 2.50 6/5/2004

....

This table represents an items price from the date on. So Item 'A' costed 1.00 from 5/5/2000 through 6/5/2004, then it costed 2.50 from that day on.

Now say I have a table of transactions with items, amount bought, and dates.

TransactionTable

ITEM AMOUNT DATE

A 5 6/6/2003

A 1 8/5/2003

A 2 8/5/2004

The total for A should come out to be 11.00. There are multiple Items and multiple price changes.

If someone could point out how a inner join of this nature would work it will help me in my query. I guess the reason i say inner join is because I am joining multiple other tables to do my query.

My current Price Table has a start date and an end date and my query looks something like this. "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable Inner Join TransactionTable ON TransactionTable.Date Between PriceTable.StartDate and PriceTable.EndDate AND PriceTable.Item = TransactionTable.Item".

I want something like this because the tables need to be in the format above "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable INNER JOIN TransactionTable ON Max(PriceTable.Date) WHERE PriceTable.Date <= TransactionTable.Date AND PriceTable.Item = TransactionTable.Item".

Hope that made sense, thanks.

SELECT c.item, SUM(c.ItemAmount) AS "Total" FROM (SELECT a.item , a.amount*(SELECT TOP (1) b.price

FROM PriceTable AS b

WHERE (b.Date <= a.Date)

ORDER BY b.Date DESC) AS "ItemAmount"

FROM TransactionTable AS a) AS c

GROUP BY c.item

|||

I had to change it slightly:

SELECT c.item, SUM(c.ItemAmount) AS "Total" FROM (SELECT a.item , a.amount*(SELECT TOP (1) b.price

FROM PriceTable AS b

WHERE (b.Date <= a.Date) AND b.item = a.item

ORDER BY b.Date DESC) AS "ItemAmount"

FROM TransactionTable AS a) AS c

GROUP BY c.item

Thank you for your help. Now I just have to integrate this into my larger query.

sql

Get a list of changed records in a table

Is there a way to get a list of records that have changed since a specific
Date/Time?
I could create a trigger and a new table and work off that data but I have a
lot of tables I want to query so I don't necessarily want to create triggers
for each one.You cannot get this unless you have a datetime column on the table that you
update when data changes. or a triggering mechanism to store changed data.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>|||> Is there a way to get a list of records that have changed since a specific
> Date/Time?
No, SQL Server does not keep this information for you automagically.
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
If you allow ad hoc access to your tables, then this is how it's done, I'm
afraid. If you deny INSERT/UPDATE rights to the table and force those
actions through stored procedures, then you could perform the logging within
a stored procedure, which at least avoids some of the problems with doing
this in a trigger. But for DELETE you would have to log to a separate table
or use a trigger.
While it doesn't help you today, SQL Server 2008 will have several options
to make this easier... from change tracking and change data capture to the
extreme of audit all actions.
A|||You could use the new OUTPUT clause to automatically pump the pre-existing
data into another table and then query that 2nd table
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Bishop" <nospam@.nospam.com> wrote in message
news:e9zRyVwqIHA.3900@.TK2MSFTNGP05.phx.gbl...
> Is there a way to get a list of records that have changed since a specific
> Date/Time?
> I could create a trigger and a new table and work off that data but I have
> a lot of tables I want to query so I don't necessarily want to create
> triggers for each one.
>

german database on engl. server

How ca i use a english database on a german SQL Server ?
Wher can I find some tips and trick about this config?
I have many problems with e.g. date format, with . and , and so one.

Thanks

Read about COLLATION in Books Online.

Thanks,|||

Change the collation of the database to an english collation.

Denny

|||

Hello,

thanks for your answers, but how can i change this? Which comand can i use ?

Must I rebuild the master db ... it is a production server !

Regards

Thanks

|||We had a similar problem with English server.

In order to use German date format , etc. You must change the default language of Login name in SQL Server database.

For example,

ALTER LOGIN <Login-name> WITH DEFAULT_LANGUAGE = German;

(the login-name is the name in web.config where in <appSettings> exists)

I hope this helps.

|||What do you mean with "English Database", the content is in general international in many cases. (Including the D-English words :-))

Jens K. Suessmeyer:

http://www.sqlserver2005.de

german database on engl. server

How ca i use a english database on a german SQL Server ?
Wher can I find some tips and trick about this config?
I have many problems with e.g. date format, with . and , and so one.

Thanks

Read about COLLATION in Books Online.

Thanks,|||

Change the collation of the database to an english collation.

Denny

|||

Hello,

thanks for your answers, but how can i change this? Which comand can i use ?

Must I rebuild the master db ... it is a production server !

Regards

Thanks

|||We had a similar problem with English server.

In order to use German date format , etc. You must change the default language of Login name in SQL Server database.

For example,

ALTER LOGIN <Login-name> WITH DEFAULT_LANGUAGE = German;

(the login-name is the name in web.config where in <appSettings> exists)

I hope this helps.

|||What do you mean with "English Database", the content is in general international in many cases. (Including the D-English words :-))

Jens K. Suessmeyer:

http://www.sqlserver2005.de

Generating File names on the fly

Hi,

I want to create a package that can process a flat file based on the current data. i.e. name of the file contains current date and some predefined characters.

What is the best way to process it?Use a property expression on the ConnectionString property of your FlatFile connection manager to set it to the correct filename (containing the date).

-Jamie

generate real date time

hi, i need to create a function to do some like this...
update myTable set DateModify = getdate()
... but... i need to get a minimum difference bettwen records...
milisecons... or...
how can i get an array or cursor (SQL server side) with 500 records
with different datetime programatically?Hi,
I assume that you are asking about writing a procedure for this and not
a function. A function cannot update tables nor can it use getdate().
To do this in a procedure you can use the WAITFOR DELAY statement in
SQL.
Regards,
Kart
celerno wrote:
> hi, i need to create a function to do some like this...
> update myTable set DateModify = getdate()
> ... but... i need to get a minimum difference bettwen records...
> milisecons... or...
> how can i get an array or cursor (SQL server side) with 500 records
> with different datetime programatically?|||Hi Kart.. thanks for your response...
I try to use waitfor, but i can't find the way to do it.
sorry, i don't have good programming skills in sql server...
here is my problem:
i retrieve a bunch of data using the DateTime field to get the lastest
data using "select top N... ... where dateTimeNew>myDate"
after download the N rows, myDate gets the biggest dateTime value from
the N rows.
and when we have N+1 data with the same DateTime my new select Top is
equal to the previus sentence and i got the same data bunch.
in order to evade this problem i want to create a routine on store
procedure or any way on the server side to set different datetimes to
all data...
excuse my poor english... thanks in advance...!
On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:[vbcol=seagreen]
> Hi,
> I assume that you are asking about writing a procedure for this and not
> a function. A function cannot update tables nor can it use getdate().
> To do this in a procedure you can use the WAITFOR DELAY statement in
> SQL.
> Regards,
> Kart
> celerno wrote:
>
>
>|||yeah, this query it all that i need... it was so simple.!
reference to:
http://www.sqlteam.com/item.asp?ItemID=765
declare @.intCounter datetime
set @.intCounter = getdate()
update articulo
SET @.intCounter = fechahora = dateadd(s, 1, @.intCounter)
select fechahora from articulo
On 11 nov, 11:06, "celerno" <cele...@.gmail.com> wrote:[vbcol=seagreen]
> Hi Kart.. thanks for your response...
> I try to use waitfor, but i can't find the way to do it.
> sorry, i don't have good programming skills in sql server...
> here is my problem:
> i retrieve a bunch of data using the DateTime field to get the lastest
> data using "select top N... ... where dateTimeNew>myDate"
> after download the N rows, myDate gets the biggest dateTime value from
> the N rows.
> and when we have N+1 data with the same DateTime my new select Top is
> equal to the previus sentence and i got the same data bunch.
> in order to evade this problem i want to create a routine on store
> procedure or any way on the server side to set different datetimes to
> all data...
> excuse my poor english... thanks in advance...!
> On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
>
>
>
>
>
>
>
>

generate real date time

hi, i need to create a function to do some like this...
update myTable set DateModify = getdate()
... but... i need to get a minimum difference bettwen records...
milisecons... or...
how can i get an array or cursor (SQL server side) with 500 records
with different datetime programatically?Hi,
I assume that you are asking about writing a procedure for this and not
a function. A function cannot update tables nor can it use getdate().
To do this in a procedure you can use the WAITFOR DELAY statement in
SQL.
Regards,
Kart
celerno wrote:
> hi, i need to create a function to do some like this...
> update myTable set DateModify = getdate()
> ... but... i need to get a minimum difference bettwen records...
> milisecons... or...
> how can i get an array or cursor (SQL server side) with 500 records
> with different datetime programatically?|||Hi Kart.. thanks for your response...
I try to use waitfor, but i can't find the way to do it.
sorry, i don't have good programming skills in sql server...
here is my problem:
i retrieve a bunch of data using the DateTime field to get the lastest
data using "select top N... ... where dateTimeNew>myDate"
after download the N rows, myDate gets the biggest dateTime value from
the N rows.
and when we have N+1 data with the same DateTime my new select Top is
equal to the previus sentence and i got the same data bunch.
in order to evade this problem i want to create a routine on store
procedure or any way on the server side to set different datetimes to
all data...
excuse my poor english... thanks in advance...!
On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
> Hi,
> I assume that you are asking about writing a procedure for this and not
> a function. A function cannot update tables nor can it use getdate().
> To do this in a procedure you can use the WAITFOR DELAY statement in
> SQL.
> Regards,
> Kart
> celerno wrote:
> > hi, i need to create a function to do some like this...
> > update myTable set DateModify = getdate()
> > ... but... i need to get a minimum difference bettwen records...
> > milisecons... or...
> > how can i get an array or cursor (SQL server side) with 500 records
> > with different datetime programatically?|||yeah, this query it all that i need... it was so simple.!
reference to:
http://www.sqlteam.com/item.asp?ItemID=765
declare @.intCounter datetime
set @.intCounter = getdate()
update articulo
SET @.intCounter = fechahora = dateadd(s, 1, @.intCounter)
select fechahora from articulo
On 11 nov, 11:06, "celerno" <cele...@.gmail.com> wrote:
> Hi Kart.. thanks for your response...
> I try to use waitfor, but i can't find the way to do it.
> sorry, i don't have good programming skills in sql server...
> here is my problem:
> i retrieve a bunch of data using the DateTime field to get the lastest
> data using "select top N... ... where dateTimeNew>myDate"
> after download the N rows, myDate gets the biggest dateTime value from
> the N rows.
> and when we have N+1 data with the same DateTime my new select Top is
> equal to the previus sentence and i got the same data bunch.
> in order to evade this problem i want to create a routine on store
> procedure or any way on the server side to set different datetimes to
> all data...
> excuse my poor english... thanks in advance...!
> On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
> > Hi,
> > I assume that you are asking about writing a procedure for this and not
> > a function. A function cannot update tables nor can it use getdate().
> > To do this in a procedure you can use the WAITFOR DELAY statement in
> > SQL.
> > Regards,
> > Kart
> > celerno wrote:
> > > hi, i need to create a function to do some like this...
> > > update myTable set DateModify = getdate()
> > > ... but... i need to get a minimum difference bettwen records...
> > > milisecons... or...
> > > how can i get an array or cursor (SQL server side) with 500 records
> > > with different datetime programatically?

generate real date time

hi, i need to create a function to do some like this...
update myTable set DateModify = getdate()
... but... i need to get a minimum difference bettwen records...
milisecons... or...
how can i get an array or cursor (SQL server side) with 500 records
with different datetime programatically?
Hi,
I assume that you are asking about writing a procedure for this and not
a function. A function cannot update tables nor can it use getdate().
To do this in a procedure you can use the WAITFOR DELAY statement in
SQL.
Regards,
Kart
celerno wrote:
> hi, i need to create a function to do some like this...
> update myTable set DateModify = getdate()
> ... but... i need to get a minimum difference bettwen records...
> milisecons... or...
> how can i get an array or cursor (SQL server side) with 500 records
> with different datetime programatically?
|||Hi Kart.. thanks for your response...
I try to use waitfor, but i can't find the way to do it.
sorry, i don't have good programming skills in sql server...
here is my problem:
i retrieve a bunch of data using the DateTime field to get the lastest
data using "select top N... ... where dateTimeNew>myDate"
after download the N rows, myDate gets the biggest dateTime value from
the N rows.
and when we have N+1 data with the same DateTime my new select Top is
equal to the previus sentence and i got the same data bunch.
in order to evade this problem i want to create a routine on store
procedure or any way on the server side to set different datetimes to
all data...
excuse my poor english... thanks in advance...!
On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:[vbcol=seagreen]
> Hi,
> I assume that you are asking about writing a procedure for this and not
> a function. A function cannot update tables nor can it use getdate().
> To do this in a procedure you can use the WAITFOR DELAY statement in
> SQL.
> Regards,
> Kart
> celerno wrote:
>
|||yeah, this query it all that i need... it was so simple.!
reference to:
http://www.sqlteam.com/item.asp?ItemID=765
declare @.intCounter datetime
set @.intCounter = getdate()
update articulo
SET @.intCounter = fechahora = dateadd(s, 1, @.intCounter)
select fechahora from articulo
On 11 nov, 11:06, "celerno" <cele...@.gmail.com> wrote:[vbcol=seagreen]
> Hi Kart.. thanks for your response...
> I try to use waitfor, but i can't find the way to do it.
> sorry, i don't have good programming skills in sql server...
> here is my problem:
> i retrieve a bunch of data using the DateTime field to get the lastest
> data using "select top N... ... where dateTimeNew>myDate"
> after download the N rows, myDate gets the biggest dateTime value from
> the N rows.
> and when we have N+1 data with the same DateTime my new select Top is
> equal to the previus sentence and i got the same data bunch.
> in order to evade this problem i want to create a routine on store
> procedure or any way on the server side to set different datetimes to
> all data...
> excuse my poor english... thanks in advance...!
> On 10 nov, 13:08, "Kart" <skarthike...@.bigfoot.com> wrote:
>
>
>
>