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 Multiple Reports

I have a report that I pass parameters of a clientID. I run the report and export it to a .pdf file. I would like to be able to do this for multiple clients without manually having to enter the ID each time and exporting it.

I have thought about creating a report that calls this report so that I can pass in the clientID one at a time. The problem is trying to export each one.

Does anyone have any ideas? Thanks in advance!

I beleive you can use the reportviewer.render method to export directly to a PDF. I've done a similar app going straight to printer.|||

Let me quote myself from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2048825&SiteID=1

Two methods come to mind:

1. You can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50Jqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Within the VB script that you run, query your tables and feed the parameters.

2. You could use a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

http://msdn2.microsoft.com/en-us/library/ms169673.aspx

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

and I believe either method will work for you here. If you need a more specific answer, let me know.

Good luck,

Larry Smithmier

Generate Multiple Tables from Column data

I have a large table with 30000+ rows. Most of the rows can be group by Column 1.

Say you had 10000 people and numbers 1 through 3. Every person would be linked to 1, 2 & 3. hence 30000 rows.

What i need to do is create a table for each number. i.e. Table 1 where column 1 is 1 (then it lists 10000 rows)

Table 2 where column 1 is 2 (another 10000 rows) and so on.

I need to create just under 300 tables. Is there a quicker way of doing that than this 300 times?

I'll try and put it into an example of what i'm looking for

Original table

Column 1 Column 2 Column 3 etc...

-

1 a b .....

2 a b .....

1 c d .....

2 c d .....

AND THEN TURN IT INTO

Table name '1'

Column 1 Column 2 Column 3 etc...

-

1 a b .....

1 c d .....

Table name '2'

Column 1 Column 2 Column 3 etc...

-

2 a b .....

2 c d .....

Using one piece of code and the data in Column 1 as the table names?

Thanks

it worked like this

Select *into 1 from Original Table where column1 = 1

Select *into 2 from Original Table where column1 = 2

All i did was copy the column names into excell and duplicate the rest and run seperate select queries.

Generate multiple rows for insert from single row

Dear all,

I have a package in which, when a Cost Center has X as a value, I must insert not X but many different Y value, which are associated with X. How can I gather and treat those Y values? Only using a Script Component?

Regards,

Pedro Martins

Are the Y values stored anywhere? Can you not merge the two data sets?