Transactions question

[Bill Toth]

I am re-writing a .net web site for a fantasy baseball site I host out of my house and would like to be able to look back at rosters based on a given date. For example I want to be able to look at my roster 6 months ago so that I might see where I went wrong. :) I am already recording all my transactions in a transaction table that contains a date/time stamp but I am not sure how to stucture the table so that I might querry for the 40 man roster at a given date.

[NeoPa]

I have no idea what you're storing, as you share no details, but if the transactions have an associated after image somewhere (Can be stored in a separate table but timestamp critical), then you could write a view to display the position for any/all players at that date.

It's mainly about how you store your data. So far we have no information on that score.

Welcome to Bytes Bill!

[Bill Toth]

I currently am storing

transaction_details_id
transaction_id
manager_id
player_id (This would be a player in a given tranaction)
draft_id (this would be a draft pick that could be included in a trade)
destination_team
timestamp

the issue I am worried about is with the timestamp how would I querry for a 40 man roster at a given point due to the ever evolving changes to a roster the last 40 additions would have players that would since be cut. I am a sql noob but thought this would be an entertaining feature. Thanks for the help

[NeoPa]

It may be possible to do this with transactions alone. It probably is with a Stored Procedure.

What I would propose though, is an AfterImage logging table for your core data (not the transactions as you've shown details of here). That would be much easier to process through with a subquery or CTE (Common Table Expression) to identify which item of each player is the last saved before the cutoff point.

[Jerry Winston]

If I understand your question correctly, you can query based on the date and transaction type to find out what your roster looked like.

I think this is the CTE solution NeoPa was talking about:

WITH tblPlayerID AS 
(
SELECT TOP 40 player_id FROM myTransactionTable
WHERE
timestamp < cast('someDate' as datetime)
AND destination_team = 'MyTeam'
AND transaction_id = 'Signed'
ORDER BY timestamp
)
SELECT first_name,last_name FROM tbl_Players P
INNER JOIN
tblPlayerID ID
ON
P.player_id=ID.player_id

[NeoPa]

This is actually the more complicated approach that I was going to avoid :D

I'm sure it's possible to do, but my understanding of exactly how the data was stored has never been adequate for me to formulate sensible SQL for it.

One think I'd suggest is that this SQL, if I read it correctly with my limited understanding of the data structure, may return the same [Player_ID] twice, if that player moved twice within the last 40 transactions (priot to the cut-off date).