Compare data in the same table

Question

I have a table that stores monthly data and I would like to create a comparison between the quantity movement within a period.

Here is an example of the table

.

The SQL statement below is meant to return any changes that has happened within a period - any fund/policy partial or total loss as well as partial or total gain. I have been battling with it for a while - any help would be well appreciated.

I currently have 5 sets of unions - (where the policies and funds match and there's a difference in quantities held, where the policies exist in the previous and not in the current and vice versa and where the securities exist in the previous and not in the current and vice versa) but the other unions work save for the last couple (where the securities exist in the previous and not in the current and vice versa). It doesn't seem to return every occurrence.

 SELECT distinct pc.[Client]
      ,pc.Policy
      ,cast(pc.Qty as decimal) AS CurrQ
      ,0 AS PrevQ
      ,cast(pc.Qty as decimal) - 0 AS QtyDiff
      ,CASE WHEN cast(pc.Qty as decimal) - 0 > 0 THEN 'Bought Units'
            WHEN cast(pc.Qty as decimal) - 0 < 0 THEN 'Sold Units'
          ELSE 'Unknown'
       END AS TransactionType
       ,convert(varchar,cast(pc.[ValDate] as date),103) AS CurrValDate
       ,'' AS PrevValDate
  FROM table pc
  WHERE convert(varchar,cast(pc.[ValDate] as date),103) = convert(varchar,getdate(),103)
  AND pc.Policy IN (SELECT policy
                    FROM table
                    WHERE convert(varchar(10),[ValDate],103) = convert(varchar(10),getdate()-1,103)

  AND pc.[Fund] NOT IN (SELECT PM.[Fund]
                        FROM table pc
                        LEFT JOIN table pm ON pc.policy = pm.policy
                        WHERE convert(varchar,cast(pc.[ValDate] as date),103) = convert(varchar,getdate(),103))
                        AND convert(varchar,cast(pm.[ValDate] as date),103) = convert(varchar,getdate()-1,103))

Answer 1

As @Larnu rightly mentioned in the comment section, the extra conditions in the query changed the run from a LEFT JOIN to an INNER JOIN. I changed the code to have policy, fund and date in the ON clause:

FROM table pc
LEFT JOIN table pm ON (pc.policy = pm.policy
                       AND pc.fund = pm.fund
                       AND pc.[ValDate]-1 = pm.[ValDate])

and got rid of the sub queries.

Thanks again Larnu.