question

robothedog avatar image
robothedog asked

Deciphering SQL code

Hello all. So I have been given a SQL code and I am fairly new to the language. I was given this code without access to the server or the database (which isn't ideal but it is what I have to work with). My task is to understand what the script is doing essentially. Any help at all 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 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
joe_justguessing avatar image
joe_justguessing answered

Does this help?

-- Create table
CREATE TABLE #Dates ( OneDate date ) ;
--Insert values
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' --create variable
declare @SettlementDate SmallDateTime; --Declare a cursor
declare Dates cursor for
select OneDate from #Dates open Dates -- get first records from cursor
fetch next from Dates into @SettlementDate -- Do this until the table #dates is empty
WHILE @@FETCH_STATUS = 0 BEGIN
select @SettlementDate ; -- create this CTE
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 ) -- create this CTE
, 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)
)
-- run this query over the two CTE's
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 ; -- get next record
fetch next from Dates into @SettlementDate -- end or while loop
end -- drop temp table
drop table #Dates -- close cursor
CLOSE Dates --DEALLOCATE cursor
DEALLOCATE Dates Formatting so I can read it. Looks to insert some values into a table.

10 |1200 characters needed characters left characters exceeded

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.