473,554 Members | 5,383 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Transactions question

2 New Member
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 1192
NeoPa
32,564 Recognized Expert Moderator MVP
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
Bill Toth
2 New Member
I currently am storing

transaction_det ails_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_tea m
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,564 Recognized Expert Moderator MVP
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 Recognized Expert New Member
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,564 Recognized Expert Moderator MVP
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

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

Similar topics

11
12675
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to read the new date. So process B starts "select ..." but does not get the previously inserted row. The timespan between commit and select is very short....
11
4232
by: Mark Yudkin | last post by:
The documentation is unclear (at least to me) on the permissibility of accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, with separate transactions scope, from separate threads of a multithreaded program using embedded SQL. Since the threads do not need to share transaction scopes, the sqleAttachToCtx family of APIs do...
15
1847
by: designconcepts | last post by:
bo'jour, bo'jour, So I have question to present to the forum about OOD. This is a Csharp forum, but C# is the lang of choice and the question is an exercise based on some comments by the chief designer of C#. Those of you who are junkies for design principle might be interested in contributing to this thread. I was recently reading some...
9
3205
by: David Eades | last post by:
Hi all Complete newbie here, so apologies if this is the wrong forum. I've been asked to use mysql and asp to make a simple bidding system (rather like a simple ebay), whereby users can use a web browser to view a highest bid and can make a bid. My question is; how can I be sure that when a user submits a bid, that another user isn't...
2
1125
by: groups.james | last post by:
I have basically inherited an old classic ASP 3.0 application. Overall it's design was a n-tier design (not always adhered to...) VB6 COM objects for the Business Layer - hosted in COM+ VB6 COM objects for the Data Layer - hosted in COM+ ASP/HTML for Presentation Layer It utilizes a SQL Server 2000 backend and makes a pretty strong use of...
12
2022
by: Rami | last post by:
I have some requirement for an automated payment system. The system has four machines setup as follows: 1- Two machines have a clustered database. 2- Two machines have a .net business logic component that will handle payment transactions with certain external component (Payment Gateway) My query is regarding transaction handling inside...
10
1783
by: e_matthes | last post by:
Hello everyone, I have read many threads about concurrency issues, and I think I understand some of the pieces, but not the whole picture. I believe I am like many people using php: developing a personal website on a windows machine, that will be hosted on a shared linux server. Financial reality makes it impossible to think of a...
3
1364
by: Ken | last post by:
I used the classes (SqlCommand, SqlConnection) of part of System.Data namespace. I got an error which mentioned "Could not load file or assembly assembly 'System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089 or noe of its dependencies. What should I do to fix this issue? Strange enough, this...
3
1094
by: kerry2807 | last post by:
Hi, Am starting to teach myself SQL. I cannot figure out how to do the most simple of queries, and it is driving me nuts. Here's the basic scenario: Table: Customer custid firstname lastname Table: Transactions custid transdate
3
5164
by: Paul H | last post by:
I have a transactions table and a balance table that look something like this: tblTransactions TransactionID (PK Autonumber) ClientID TransactionDate TransactionAmount (currency field, values must be >0) CRDR (indicates whether the transaction is a (credit or debit) StatementDate (Date stamp applied when the “Statement” report is run)
0
7580
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7503
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7781
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8017
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7538
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5140
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3543
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2003
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1113
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.