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

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind Regards
Here is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Base.
have
>
|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Sorry, my methods don't copy the data.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the[vbcol=seagreen]
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
Hopefully
> best
> ----
> --
> ----
> --
> have
>
|||Check out the free data and schema scripter from Innovartis - the makers of DB Ghost. www.dbghost.com
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
sql

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind RegardsHere is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> > Hi All
> >
> > I have SQL Data Base . I am using asp application to access this Data
> Base.
> >
> > I want to create a script to generate Data Base on other machines. I
have
> > some data in that Data Base, which I required to run my application.
> >
> > So the final SCRIPT should contain all relations, default values ,
> > Identities and Data.
> >
> > Which method you advise to achieve these.
> >
> >
> > Kind Regards
> >
> >
> >
> >
> >
>|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Sorry, my methods don't copy the data.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> > Here is an article I wrote about generating SQL Server scripts.
Hopefully
> > this will provide you with enough information to help you determine the
> best
> > way to generate your script.
> >
> > http://www.dbazine.com/larsen4.shtml
> >
> > --
> >
> ----
> --
> ----
> --
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> > news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> > > Hi All
> > >
> > > I have SQL Data Base . I am using asp application to access this Data
> > Base.
> > >
> > > I want to create a script to generate Data Base on other machines. I
> have
> > > some data in that Data Base, which I required to run my application.
> > >
> > > So the final SCRIPT should contain all relations, default values ,
> > > Identities and Data.
> > >
> > > Which method you advise to achieve these.
> > >
> > >
> > > Kind Regards
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:
> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind RegardsHere is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Base.
have[vbcol=seagreen]
>|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Sorry, my methods don't copy the data.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
Hopefully[vbcol=seagreen]
> best
> ----
> --
> ----
> --
> have
>|||Check out the free data and schema scripter from Innovartis - the makers of DB Ghost. [
url]www.dbghost.com[/url]
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base
.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base
.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>

Generating Script

Hi,

I want generated script file of all data base objects as well as i want to keep the generated script file in CVS which will reflect the changes.

Thanks in Advance

Sathik, Take a look at the Generate Script wizard in Management Studio. You can read more about it in Books Online: http://msdn2.microsoft.com/en-us/library/ms178078(SQL.90).aspx

Cheers,
Dan

sql

Generate SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind Regards
What version of SQL Server?
BACKUP and RESTORE
or
sp_detach_db and sp_attach_db
are the easiest methods to move a database
If you truly want to script the database you can use Enterprise Manager or
Query Analyzer to generate the appropriate scripts (to create the tables,
views, stored procedures, and so on). Scripting the data is a bit more
challenging, as you will need to use a tool that will script the data for
you. Some such tools are (in no particular order):
Largo SQL Tools -- http://www.largosqltools.com/
ObjectScripter -- http://www.rac4sql.net/
QALite -- http://www.rac4sql.net/
Lockwood Tech -- http://www.lockwoodtech.com/
Keith
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:uXQYlzCaEHA.3944@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
|||I think sp_detach_db and sp_attach_db are best.
Just one more query can I use sp_attach_db in installshield as if I have
..ldf and .mdf files in my pakcge.
Kind Regards
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u0itCKDaEHA.1764@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> What version of SQL Server?
> BACKUP and RESTORE
> or
> sp_detach_db and sp_attach_db
> are the easiest methods to move a database
> If you truly want to script the database you can use Enterprise Manager or
> Query Analyzer to generate the appropriate scripts (to create the tables,
> views, stored procedures, and so on). Scripting the data is a bit more
> challenging, as you will need to use a tool that will script the data for
> you. Some such tools are (in no particular order):
> Largo SQL Tools -- http://www.largosqltools.com/
> ObjectScripter -- http://www.rac4sql.net/
> QALite -- http://www.rac4sql.net/
> Lockwood Tech -- http://www.lockwoodtech.com/
> --
> Keith
>
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:uXQYlzCaEHA.3944@.tk2msftngp13.phx.gbl...
> Base.
have
>
|||I don't see why not.
Keith
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e4VjsvLaEHA.2388@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I think sp_detach_db and sp_attach_db are best.
> Just one more query can I use sp_attach_db in installshield as if I have
> .ldf and .mdf files in my pakcge.
>
> Kind Regards
>
>
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u0itCKDaEHA.1764@.TK2MSFTNGP10.phx.gbl...
or[vbcol=seagreen]
tables,[vbcol=seagreen]
for
> have
>