Hendrik Kleine Hendrik Kleine - 2 months ago 5
SQL Question

T-SQL Create UID based on Columns and Iterate over it

I have the following code which evaluates different time periods and takes the difference between them, for each group,

For example

TimePeriod Value1
201607 50
201608 80


The code subtracts 80-50 = 30 (Periodic).

I also have a UID created based on several columns in my table. I have to use this custom UID and can't use the PK on the table, or any particular column.

WITH cte
AS (SELECT

ISNULL(CAST(TransactionID AS nvarchar), '_nullTransactionId_') + ISNULL(Description, '_nullDescription_') + CAST(Account AS nvarchar) + Category + Currency + Entity + Scenario AS UID,

LEFT(TimePeriod, 6) Period,
SUM(Value1) Value1,
CAST(LEFT(TimePeriod, 6) + '01' AS date) ord_date
FROM MyTestTable
GROUP BY LEFT(TimePeriod, 6),
TransactionID,
Description,
Account,
Category,
Currency,
Entity,
Scenario,
TimePeriod)
SELECT
a.UID,
a.Period,
--a.Value1,
ISNULL(a.Value1, 0) - ISNULL(b.Value1, 0) Periodic
FROM cte a
LEFT JOIN cte b
ON a.ord_date = DATEADD(MONTH, 1, b.ord_date)
ORDER BY a.UID


The issue is that this code generates many more results than I have rows in my table. When I looked at it closely, I can see that the first (Periodic) result of any given UID is correct, but the subsequent ones wrong.

I'm not sure where to start to move towards a solution, I'm thinking the GROUP BY may be causing this, but I 'have' to list the fields in group by, otherwise I cannot make the UID.

Any guidance very much appreciated.

Answer

You probably want the evaluation made for each UID right? In this case you are missing the UID in the join clause.

The query should be:

WITH cte
AS (SELECT

  ISNULL(CAST(TransactionID AS nvarchar), '_nullTransactionId_') + ISNULL(Description, '_nullDescription_') + CAST(Account AS nvarchar) + Category + Currency + Entity + Scenario AS UID,

  LEFT(TimePeriod, 6) Period,
  SUM(Value1) Value1,
  CAST(LEFT(TimePeriod, 6) + '01' AS date) ord_date
FROM MyTestTable
GROUP BY LEFT(TimePeriod, 6),
         TransactionID,
         Description,
         Account,
         Category,
         Currency,
         Entity,
         Scenario,
         TimePeriod)
SELECT
  a.UID,
  a.Period,
  --a.Value1,
  ISNULL(a.Value1, 0) - ISNULL(b.Value1, 0) Periodic
FROM cte a
LEFT JOIN cte b
  ON a.UID = b.UID and a.ord_date = DATEADD(MONTH, 1, b.ord_date)
ORDER BY a.UID
Comments