TSQL Challenge 76

Här är min lösning av TSQL pussel nummer 76 från Beyond Relational. Pusslet är tydligen inspirerat av Erland Sommarskogs problem från verkligheten. Erland har en väldigt trevlig sida med mycket matnyttig information om SQL Server.

• Erland Sommarskogs hemsida - www.sommarskog.se
• Beskrivning av pusslet hos Beyond Relational - TC76

;WITH base AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY AccountNo, InstrumentID, TradeDatesOrder) [TransactionID]
  , ROW_NUMBER() OVER (PARTITION BY AccountNo, InstrumentID ORDER BY AccountNo, InstrumentID) [AccountInstrumentNo]
  , AccountNo, InstrumentID, Qty, TotalQty, TradeDatesOrder
  FROM TC76
)
, rec AS
(
  SELECT TransactionID, AccountInstrumentNo, AccountNo, InstrumentID
  , CASE WHEN TotalQty = 0 THEN CONVERT(BIT,1) ELSE CONVERT(BIT,0) END [NewBlock]
  , CONVERT(INT,1) [BlockNo]
  , CONVERT(INT,1) [RowNoInBlock]
  FROM base 
  WHERE [AccountInstrumentNo] = 1 AND Qty < 0

  UNION ALL

  SELECT b.TransactionID, b.AccountInstrumentNo, b.AccountNo, b.InstrumentID
  , CASE WHEN b.TotalQty = 0 Or b.AccountInstrumentNo = 1 THEN CONVERT(BIT,1) ELSE CONVERT(BIT,0) END [NewBlock]
  , CASE WHEN r.NewBlock = 1 THEN r.BlockNo + 1 ELSE r.BlockNo END [BlockNo]
  , CASE WHEN r.NewBlock = 1 THEN 1 ELSE r.RowNoInBlock + 1 END [RowNoInBlock]
  FROM rec r
  INNER JOIN base b ON r.AccountNo = b.AccountNo 
    AND r.InstrumentID = b.InstrumentID 
    AND r.AccountInstrumentNo + 1 = b.AccountInstrumentNo
)
SELECT b.AccountNo, b.InstrumentID, b.Qty, b.TotalQty, 
b.TradeDatesOrder, r.BlockNo, r.RowNoInBlock
FROM rec r
INNER JOIN base b ON r.TransactionID = b.TransactionID
ORDER BY b.AccountNo, b.InstrumentID, b.TradeDatesOrder