Primary Key and Foreign Key off by 1 second

[cblakley4]

I have 2 tables that are being populated from a .Net Application. The inserts are happening one after the other. Occasionally the 2nd falls 1 second after the 1st.

The Key for these 2 tables are LnId, UserId, and TransDate

I have accounted for the time difference with the following, but I am now being asked for the Date range twice. Is there a better way to accomplish this that won't ask me for my date range twice?

SELECT tblTransactions.LnNumber, tblTransactions.TransDate, tblCallCatValues.Level2, tblReRegTransactions.OldLoan, tblReRegTransactions.NewLoan, tblReRegTransactions.Reason
FROM (tblTransactions INNER JOIN tblCallCatValues ON tblTransactions.CallCategory = tblCallCatValues.CatValue) LEFT JOIN tblReRegTransactions ON (tblTransactions.LnNumber = tblReRegTransactions.LnId) AND (tblTransactions.PricerID = tblReRegTransactions.UserId) AND (datediff("s",tblTransactions.TransDate , tblReRegTransactions.TransDate) =1) OR (datediff("s",tblTransactions.TransDate , tblReRegTransactions.TransDate) =0)
WHERE (((tblTransactions.TransDate)>[StartDate] And (tblTransactions.TransDate)<[EndDateComp]) AND ((tblTransactions.CallCategory) In ('120','121','152','153','205','206','178','179','31','69','50','77','36','38','43','66','15','139','173','195','217','6','49','42','19','223','224','225','226','227','228','229','230','231','232')))
ORDER BY tblTransactions.LnNumber;

 

[xenou]

Unless one of your queries has an unused or forgotten parameter (which can account for a popup), you don't have the Date being asked for twice. You have two dates, each being asked for once:

WHERE (((tblTransactions.TransDate)>[StartDate]
And (tblTransactions.TransDate)<[EndDateComp]) AND ((tblTransactions.CallCategory) In

Note: are you sure the transdate is part of a primary key - that would be highly unusual (for one thing, if it's a key it wouldn't be generated as you describe, since it wouldn't be a key anymore).

 

[cblakley4]

Unless one of your queries has an unused or forgotten parameter (which can account for a popup), you don't have the Date being asked for twice. You have two dates, each being asked for once:

WHERE (((tblTransactions.TransDate)>[StartDate]
And (tblTransactions.TransDate)<[EndDateComp]) AND ((tblTransactions.CallCategory) In

Note: are you sure the transdate is part of a primary key - that would be highly unusual (for one thing, if it's a key it wouldn't be generated as you describe, since it wouldn't be a key anymore).

Ok, so you caught me a little bit. Technically it isn't defined as a primary key in the database, but the loanNumber, userId, and timestamp together is the only thing that uniquely identifies the record. I can't just change it to a Date, as Time is still very important in the data. Most records come in with the exact same time stamp, but a few come in a few milliseconds after the new second. Thus, the timestamp difference.

As for the query, I'm getting 4 pop ups. [StartDate], [EndDateComp],[StartDate],[EndDateComp]

 

[cblakley4]

Ok, so you caught me a little bit. Technically it isn't defined as a primary key in the database, but the loanNumber, userId, and timestamp together is the only thing that uniquely identifies the record. I can't just change it to a Date, as Time is still very important in the data. Most records come in with the exact same time stamp, but a few come in a few milliseconds after the new second. Thus, the timestamp difference.

As for the query, I'm getting 4 pop ups. [StartDate], [EndDateComp],[StartDate],[EndDateComp]

I've got a solution. Instead of having the column defaulting to now, I'll control the date/Time in the insert query on the application side. Now that I've thought it, I feel kind of silly for asking in the first place.


Thanks for any time I may have wasted.

Casey

 

[xenou]

Great. Not that it matters but some judicious rounding can also be a way to shave off a little variation in a time value to get values that fit into a certain tolerance.