question

VivaanShresth-9968 avatar image
0 Votes"
VivaanShresth-9968 asked VivaanShresth-9968 commented

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

Hi I have the below table.
![205912-image.png][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
205880-image.png


[2]: /answers/storage/attachments/205760-image.png


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.

sql-server-transact-sql
image.png (85.5 KiB)
image.png (2.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered VivaanShresth-9968 commented

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My query was also a pivoting query. It did just not use the PIVOT operator, that I find useless. Anything you can do with PIVOT, you can do with the technique that I used, but the reverse does not apply.

1 Vote 1 ·

Thank you so much which is a very useful :)

0 Votes 0 ·
VivaanShresth-9968 avatar image
0 Votes"
VivaanShresth-9968 answered

Updated one:
Hi I have the below table.
205912-image.png


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
205945-image.png

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.



image.png (2.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered VivaanShresth-9968 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you sure I will do it.

0 Votes 0 ·

Thank you that really helped a lot. I appreciate it for the quick response.

0 Votes 0 ·