question

robothedog avatar image
robothedog asked

Deciphering SQL code

Hello, I am currently reviewing a query without access to the databases on which the query is performed. (It's not ideal but that's what I am tasked with). I am not a SQL expert and trying to identify what the below code does as I cannot run the query is quite difficult. Any help would be appreciated!

CREATE TABLE #Dates ( OneDate date ) ; insert into #Dates select '01/23/2018' insert into #Dates select '01/26/2018' insert into #Dates select '02/20/2018' insert into #Dates select '03/07/2018' insert into #Dates select '03/19/2018' insert into #Dates select '03/21/2018' insert into #Dates select '03/22/2018' insert into #Dates select '04/03/2018' insert into #Dates select '04/18/2018' insert into #Dates select '05/03/2018' insert into #Dates select '05/10/2018' insert into #Dates select '06/04/2018' insert into #Dates select '06/14/2018' insert into #Dates select '07/20/2018' insert into #Dates select '08/06/2018' insert into #Dates select '08/14/2018' insert into #Dates select '08/23/2018' insert into #Dates select '08/28/2018' insert into #Dates select '09/21/2018' insert into #Dates select '10/10/2018' insert into #Dates select '11/21/2018' insert into #Dates select '11/26/2018' insert into #Dates select '11/29/2018' insert into #Dates select '12/12/2018' insert into #Dates select '12/21/2018' declare @SettlementDate SmallDateTime; declare Dates cursor for select OneDate from #Dates open Dates fetch next from Dates into @SettlementDate WHILE @@FETCH_STATUS = 0 BEGIN select @SettlementDate ; WITH DTC as ( SELECT CUSIP ,SUM(POS.Quantity) as Quantity FROM StockRecord.dbo.DTCC_PositionHistory POS WHERE POS.EntryDate = @SettlementDate AND POS.DTCC_AccountType <> '121' group by CUSIP ) , Lek as ( SELECT TD.CUSIP as CUSIP , SUM(TD.QuantityWithFraction) AS PositionAtLEK FROM StockRecord.dbo.TransactionDetail TD WHERE TD.SettlementDate <= @SettlementDate AND TD.AccountID LIKE '[_]DTC%' GROUP BY TD.CUSIP HAVING (ABS(Sum(TD.QuantityWithFraction)) > 0.99) ) select Lek.*,DTC.*,ISNULL(Lek.PositionAtLEK,0)+ISNULL(DTC.Quantity,0) as Difference from DTC full outer join Lek on Lek.CUSIP=DTC.CUSIP where ISNULL(Lek.PositionAtLEK,0)+ISNULL(DTC.Quantity,0)<>0 order by Lek.CUSIP,DTC.CUSIP ; fetch next from Dates into @SettlementDate end drop table #Dates CLOSE Dates DEALLOCATE Dates

sqlcode
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered

Step 1 would be to run it through a formater. If you check the web, you'll find quite a few.

There's a bunch of esoteric lingo that I'd don't know about but here's a 100,000 ft view of what the code is doing.

1. It creates a temporary table of dates that seem arbitrary and it does so in one of the worst ways possible.

2. It loads that date table into a cursor.

3. For each date in the date table, it creates a CTE on a history table and a CTE on a transaction table and then the outer query compares them and list them using a full outer join for every date less than the current date, possibly to create a "point-in-time" position for each date.

There are some filters that do things like make sure the quantity is at least 1 (a guess because of the comparison to > 0.99)

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.