469,344 Members | 6,428 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Transactions question

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.
Aug 6 '10 #1
5 996
NeoPa
32,183 Expert Mod 16PB
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!
Aug 6 '10 #2
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
Aug 6 '10 #3
NeoPa
32,183 Expert Mod 16PB
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.
Aug 6 '10 #4
Jerry Winston
145 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. WITH tblPlayerID AS 
  2. (
  3. SELECT TOP 40 player_id FROM myTransactionTable
  4. WHERE
  5. timestamp < cast('someDate' as datetime)
  6. AND destination_team = 'MyTeam'
  7. AND transaction_id = 'Signed'
  8. ORDER BY timestamp
  9. )
  10. SELECT first_name,last_name FROM tbl_Players P
  11. INNER JOIN
  12. tblPlayerID ID
  13. ON
  14. P.player_id=ID.player_id
Aug 10 '10 #5
NeoPa
32,183 Expert Mod 16PB
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).
Aug 10 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by Markus Breuer | last post: by
15 posts views Thread by designconcepts | last post: by
9 posts views Thread by David Eades | last post: by
2 posts views Thread by groups.james | last post: by
12 posts views Thread by Rami | last post: by
10 posts views Thread by e_matthes | last post: by
3 posts views Thread by Ken | last post: by
3 posts views Thread by kerry2807 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.