Help with SQL Statement - SQL Server Forums

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)

Help with SQL Statement

Author

Topic

SulersCS
Starting Member

2 Posts

Posted - 2010-06-04 : 11:29:02

Hi,

I'm trying what the best way I can write a sql statement that gets data from a few different tables one of which is a table that contains transactions and I only need to get the latest one. Look at the table structure I have below and you'll get a better understanding of what I'm dealing with.

[Groups]
-ID (GUID)
-Name (Varchar)

[Terminals]
-ID (GUID)
-TerminalID (Varchar)
-Name (Varchar)
-Address (Varchar)
-GroupID (GUID)

[Transactions]
-ID (GUID)
-TerminalID (Varchar)
-LastTransaction (DateTime)

[Notes]
-ID (GUID)
-AssociatedID (GUID)
-Note (Varchar)

What I need to do is to group all the terminals inside the Terminals table by the Group so I can display each set in its own group. After I do that, i need to get lets just say TerminalID, Name, and Address from the Terminals table, along with the lasttransaction field from Transactions table associated to that terminal. I also need to get the latest note attached to that transaction if there's one.

So to recap:
1. Go through each group in the [Groups] table.
2. Get a list of all terminals associated to that group ordered by name.
3. Get the latest lasttransaction associated to each of the terminal
4. Get the latest note associated to the transaction.

Any help on how to do something like this would be appreciated.

Thanks,
Suler Abou

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 15:23:51

1. Go through each group in the [Groups] table.
Use a select clause with column "Name" from the Groups table

2. Get a list of all terminals associated to that group ordered by name.
Do a join of GroupID column in the terminals table to that of ID column in groups table and put a order by clause

3. Get the latest lasttransaction associated to each of the terminal
In the same query put max(LastTransaction) in the select list and join TerminalID of Terminals table to TerminalID of Transactions table

4. Get the latest note associated to the transaction.
Put max(Notes) in the select clause and join TerminalId of Transactions to ID column of Notes and in the end put a group by clause for Name i.e from Groups table

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources