Question

Photo of Michael Garrison

0

Please review this SQL query, designed to provide DateTime to transactions with none stored

1

I have a need to routinely add a TransactionDateTime to our Credit Card transactions, as they're not automatically stored, and also not being inputted by our accounting team.

Since this is potentially so fraught with peril, I'd love for a few eyes to check my logic before I start attempting to run this. The idea is it finds transactions which belong to a closed batch, that doesn't have a TransactionDateTime. Then it grabs the StartDateTime from the batch itself and writes that data as the TransactionDateTime (almost all our batches are single day batches, so that is nearly always accurate- and in any case is better than a null TransactionDate).

Here's my first draft:

UPDATE [FinancialTransaction]
    SET [FinancialTransaction].[TransactionDateTime] = b.[BatchStartDateTime]
FROM (
    SELECT [Id],[BatchStartDateTime],[Status] FROM [FinancialBatch]
    ) b
WHERE
    b.[Id]=[FinancialTransaction].[BatchId]
    AND [FinancialTransaction].[TransactionDateTime] IS NULL
    AND b.[Status]=2

Thoughts? Thanks!

  • Michael Garrison

    Michael Garrison

    I ran this between
    BEGIN TRANSACTION; SELECT TOP 100 * FROM [FinancialTransaction]


    and


    SELECT TOP 100 * FROM [FinancialTransaction]; ROLLBACK TRANSACTION


    on my server and compared the results- it appears to have worked perfectly for this subset of data, but I still would appreciate some more experienced programmers and the devs to give this the once-over and gut check to see if there are any pitfalls I missed.
    Thanks!

    • Photo of David Turner

      0

      Looks good. Can you also open a github issue describing how these are getting created without a date ( transactions should probably always have a date ).

      Just for reference, this is how I'd write it (I tend to use JOINs more than linking tables with the where clause) ...

      UPDATE f SET [TransactionDateTime] = b.[BatchStartDateTime]
FROM [FinancialTransaction] f
INNER JOIN [FinancialBatch] b ON b.[Id] = f.[BatchId]
WHERE f.[TransactionDateTime] IS NULL
AND b.[Status] = 2
      • Michael Garrison

        Michael Garrison

        Thanks David- I use Joins for selects but honestly wasn't familiar with that particular syntax of Update. Looks cleaner, I think I'll take it =)


        Thanks again

        • Michael Garrison

          Michael Garrison

          Oh, and I'm not sure that a GitHub issue is necessary- the transactions don't have dates because our accounting team doesn't provide them- figuring that having put the date into the Batch is sufficient.