Maximum value from different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Maximum value from different tables

Author

Topic

firevic@hotmail.com
Starting Member

2 Posts

Posted - 2009-05-27 : 19:00:02

Hello everyone:

I am looking for a SQL code (using SQL Server 2005) where we can filter through Crystal Parameters a date. The issue is that the table is related to other table and all of the events from second table are listed in the SQL. We want to have the most recent date from the first table and all of the available dates from the second one one:

This is the unwanted result:
Transfer date Observation Date
F2 525 2631 85 5.01 0 200502 01 3883 2006-10-09 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 29 2006-08-21 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 30 2006-03-13 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 32 2006-02-27 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 34 2006-02-20 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 60 2006-02-13 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 26 2006-02-06 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 22 2006-01-30 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 19 2006-01-23 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 7 2006-01-09 00:00:00.000 2005-01-03 00:00:00.000 0 0
F2 525 2631 85 5.01 0 200502 01 24 2006-01-02 00:00:00.000 2005-01-03 00:00:00.000 0 0

The date from the second date table is hardcoded into the SQL but there will be more than that one.

We tried with TOP1 but only shows the top results, not all the available dates.

Here is the SQL:

SELECT Tank.TankName, StatusEvent.FishNo, StatusEvent.BioMass, StatusEvent.Density, StatusEvent.AvKg,
StatusEventSummary.FeedPerDayKgNew, StatusEvent.WeekYear,
YearClassMain.YearClass, YearClassMain.Source,
TransferEvent.TransferCount, TransferEvent.TransferDate,
StatusEvent.DateObservation, StatusEvent.FeedSize,
FoodPerDayLiters = StatusEventSummary.FeedPerDayKgNew * StatusEventSummary.ConvLiters

FROM StatusEvent, StatusEventSummary, Tank, System, YearClassMain, TransferEvent

WHERE StatusEvent.StatusEventID = StatusEventSummary.StatusEventID AND Tank.TankID = StatusEvent.TankID AND Tank.SystemID = System.SystemID
AND YearClassMain.YearClassMainID = StatusEvent.YearClassMainID AND Tank.TankName = 'F2' AND TransferEvent.NewTankID = Tank.TankID
AND StatusEvent.DateObservation = '2005-01-03'

Again: I want the maximum date from the first column, but not using TOP1, where multiple dates from the second column will apear.

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 20:01:46

[code]
SELECT
Tank.TankName,
StatusEvent.FishNo,
StatusEvent.BioMass,
StatusEvent.Density,
StatusEvent.AvKg,
StatusEventSummary.FeedPerDayKgNew,
StatusEvent.WeekYear,
YearClassMain.YearClass,
YearClassMain.Source,
TransferEvent.TransferCount,
TransferEvent.TransferDate,
StatusEvent.DateObservation,
StatusEvent.FeedSize,
FoodPerDayLiters = StatusEventSummary.FeedPerDayKgNew * StatusEventSummary.ConvLiters
FROM
StatusEvent
INNER JOIN StatusEventSummary ON StatusEvent.StatusEventID = StatusEventSummary.StatusEventID
INNER JOIN Tank ON StatusEvent.TankID = Tank.TankID
INNER JOIN [System] ON Tank.SystemID = [System].SystemID
INNER JOIN YearClassMain ON StatusEvent.YearClassMainID = YearClassMain.YearClassMainID
INNER JOIN
(
SELECT NewTankID, TransferCount, TransferDate,
seq_no = row_number() OVER (PARTITION BY NewTankID ORDER BY TransferDate DESC)
FROM TransferEvent
) AS TransferEvent ON Tank.TankID = TransferEvent.NewTankID
AND TransferEvent.seq_no = 1
WHERE
Tank.TankName = 'F2'

[/code]

KH
[spoiler]Time is always against us[/spoiler]

firevic@hotmail.com
Starting Member

2 Posts

Posted - 2009-05-27 : 21:24:23

Wow,thank for the effort. When I run it, it shows the following:

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'ON'.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'AS'.

Could you take a look?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 21:29:28

see edited query

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources