Help on TSQL Query to get formula based value from the below query

Question

Hi I have the below table.
]2

From the above table, I would like to get the results as below if productype for instance 'A', for producttype value should be
=(BeginingMembers from current month)-(BeginingMembers from currentmonth+NewADDS-Disenrolled)=335-(339+13-15)=4

Could you help me on it, i have to write the same query not only for profuctype with some other groups also...mainly need the query how to write it like the above one.

Thanks.

Answer 1

I prefer to use pivot for this type of operation.

DECLARE @tbl TABLE (Total2        int NOT NULL,
                  Member_Month  date NOT NULL,
                  New_Enrolled_Status varchar(200) NOT NULL,
                  Product_Type varchar(40) NOT NULL
)

INSERT @tbl(Total2, Member_Month, New_Enrolled_Status, Product_Type)
   VALUES
        (335, '20220101', 'Beginning Members', 'A'),
         (339, '20211201', 'Total included members', 'A'),
         (337, '20211201', 'Beginning Members', 'A'),
         (13,  '20211201', 'Disenrolled', 'A'),
         (15,  '20211201', 'New Adds', 'A'),

        (35, '20220101', 'Beginning Members', 'B'),
         (39, '20211201', 'Total included members', 'B'),
         (37, '20211201', 'Beginning Members', 'B'),
         (3,  '20211201', 'Disenrolled', 'B'),
         (5,  '20211201', 'New Adds', 'B')

;with prevtotals as (
    SELECT 
        Product_Type,
        Member_Month,
        [Beginning Members],
        LAG([Total included members], 1,0) OVER (PARTITION BY Product_Type ORDER BY Member_Month) as [Prev_Total included members],
        LAG([Beginning Members], 1,0) OVER (PARTITION BY Product_Type ORDER BY Member_Month) as [Prev_Beginning Members],
        LAG([Disenrolled], 1,0) OVER (PARTITION BY Product_Type ORDER BY Member_Month) as [Prev_Disenrolled],
        LAG([New Adds], 1,0) OVER (PARTITION BY Product_Type ORDER BY Member_Month) as [Prev_New Adds]
    FROM (
    SELECT 
        Product_Type, Member_Month, New_Enrolled_Status, Total2
    FROM @tbl
    ) as src
    PIVOT
    (
        MAX(Total2) FOR New_Enrolled_Status IN ([Beginning Members],[Disenrolled],[New Adds],[Total included members])
    ) as pvt
)
SELECT 
    [Beginning Members]-([Prev_Total included members]+[Prev_New Adds]-[Prev_Disenrolled]) as [Discrepancy_Value],
    [Product_Type],
    [Member_Month]
FROM prevtotals
ORDER BY Product_Type, Member_Month DESC

Answer 2

For this type of question, we recommend that you give the CREATE TABLE statement for your table(s) together with INSERT statements with the sample data. This time, I have typed this from the information in your screenshot, but there is no guarantee that I or anyone else will do this next time you have a question, so take this as a recommendation for the future. And having CREATE TABLE + INSERT is required to produce a tested solution.

Anyway, here is something you can try:

CREATE TABLE tbl (Total2        int NOT NULL,
                  Member_Month  date NOT NULL,
                  New_Enrolled_Status varchar(200) NOT NULL,
                  Product_Type varchar(40) NOT NULL,
                  CONSTRAINT pk_tbl PRIMARY KEY (Product_Type, Member_Month, New_Enrolled_Status)
)
go
INSERT tbl(Total2, Member_Month, New_Enrolled_Status, Product_Type)
   VALUES(335, '20220101', 'Beginning Members', 'A'),
         (339, '20211201', 'Total included members', 'A'),
         (337, '20211201', 'Beginning Members', 'A'),
         (13,  '20211201', 'Disenrolled', 'A'),
         (15,  '20211201', 'New Adds', 'A')
go
SELECT * FROM tbl
go
; WITH GetNext AS (
   SELECT Total2, Next = LEAD(Total2) OVER (PARTITION BY Product_Type, New_Enrolled_Status ORDER BY Member_Month),
          Member_Month, New_Enrolled_Status, Product_Type
   FROM   tbl
)
SELECT concat(MIN(CASE WHEN New_Enrolled_Status = 'Beginning Members' THEN Next    END), '-(', 
              MIN(CASE WHEN New_Enrolled_Status = 'Beginning Members' THEN Total2  END), '+', 
              MIN(CASE WHEN New_Enrolled_Status = 'New Adds'          THEN Total2  END), '-',
              MIN(CASE WHEN New_Enrolled_Status = 'Disenrolled'       THEN Total2  END), ')'),
       Product_Type
FROM   GetNext
WHERE  Member_Month = '20211201'
GROUP  BY Product_Type
go
DROP TABLE tbl

Answer 3

Updated one:
Hi I have the below table.

From the above table, I would like to get the results as below if productype for instance 'A', for producttype value should be
=(BeginingMembers from current month)-(BeginingMembers from previousmonth+NewADDS-Disenrolled)=335-(337+15-13)=4

Could you help me on it, i have to write the same query not only for profuctype with some other groups also...mainly need the query how to write it like the above one.

Thanks.