472,146 Members | 1,342 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,146 software developers and data experts.

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 1084
32,499 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

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)

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
32,499 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
  12. tblPlayerID ID
  13. ON
  14. P.player_id=ID.player_id
Aug 10 '10 #5
32,499 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
reply views Thread by Saiars | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.