locked
re: Output "Copies" of rows RRS feed

  • Question

  • Hi All

    I have a table which outputs a running total in SQL 2012 and works very nicely. Here is the insertion code for a test sample of the data:

    CREATE TABLE [dbo].[vwtest](
    	[MSReportingCatID] [int] NULL,
    	[DevMonthId] [int] NULL,
    	[YearOfAccount] [int] NULL,
    	[SettlementCurrency] [varchar](3) NULL,
    	[GrossPremiumMovt] [money] NULL
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    
    
    
    USE [UDC_STAGING]
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 44, 1993, N'GBP', 0.0000)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 2, 1993, N'GBP', 1222.9400)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 133, 1993, N'GBP', 0.0000)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 133, 1993, N'GBP', 0.0000)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 133, 1993, N'GBP', 0.0000)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 133, 1993, N'GBP', 0.0000)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 133, 1993, N'GBP', 0.0000)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 15, 1993, N'GBP', -1060.7100)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 133, 1993, N'GBP', 0.0000)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 133, 1993, N'GBP', 0.0000)
    GO
    INSERT [dbo].[vwtest] ([MSReportingCatID], [DevMonthId], [YearOfAccount], [SettlementCurrency], [GrossPremiumMovt]) VALUES (1, 2, 1993, N'GBP', 4466.9100)
    GO
    

     a piece of code which outputs when the value needs to change (imagine you need one row for each devmonthid) even if that devmonthid does not exist .. so if no value comes in then we need to report it as the same value as the previous devmonthid as shown if you run this code on the table:

    				Declare @Currency varchar(3), @CurrentMonths int
    				Set @Currency = 'GBP'
    				Set @CurrentMonths =( Select DateDiff(mm,'1993-01-01', DateAdd(mm, 1,DATEFROMPARTS(YEAR(Getdate()),MONTH(GetDate()),1))) )-1
    				Select @CurrentMonths
    				;With SourceData as(
    				Select 
    						MSReportingCatID,
    						YearOfAccount,
    						(Year(GetDate())-YearOfAccount)*12+3 as MaxDevMonth,
    						convert(int,devmonthid) as Triangle_Start_Position,
    						LEAD(DevMonthID) OVER(Partition by MSReportingCatID, YearOfAccount, SettlementCurrency Order by DevMonthID) As NextValue,
    						SettlementCurrency,
    						GrossPremiumMovt,
    						Sum(GrossPremiumMovt) OVER (Partition by MSReportingCatID, YearOfAccount, SettlementCurrency Order by DevMonthID asc) as RunningTot
    						FROM dbo.vwtest
    						where
    						settlementcurrency = @Currency
    						AND
    						YearOfAccount > 1992
    				)
    
    				
    				Select distinct
    				
    				MSReportingCatID,
    				Convert(varchar,MSReportingCatID) + '_RESIDUAL' as ClassCustom,
    				YearOfAccount,
    				Triangle_Start_Position,
    				CASE WHEN Triangle_Start_Position > COALESCE(MAX(NextValue)-1,MaxDevMonth) then Triangle_Start_Position ELSE COALESCE(MAX(NextValue)-1,MaxDevMonth) END as Triangle_END_Position,
    				SettlementCurrency,
    				RunningTot as GrossPremiumMovt
    
    				From SourceData S
    				WHERE MSReportingCatID = 1 and YearOfAccount = 1994
    				GROUP BY MSReportingCatID, YearOfAccount, Triangle_Start_Position, MaxDevMonth, SettlementCurrency, RunningTot
    				Order by MSReportingCatID, YearOfAccount, Triangle_Start_Position,  SettlementCurrency

    Apologies the 60000 char limit wouldn't let me provide a full insertion of data. if all the rows are present, it builds me an output of 39 rows example - row 5-8 has the same value and at position 9 the value changes

    What I need is all 273 rows outputted with the grosspremmovt - is this possible?

    Thanks in advance


    J


    • Edited by James OHara Monday, April 25, 2016 1:00 PM add picture
    Monday, April 25, 2016 12:59 PM

All replies

  • Something like this?

    DECLARE @vwtest TABLE (MSReportingCatID int NULL, DevMonthId int NULL, YearOfAccount int NULL, SettlementCurrency varchar(3) NULL, GrossPremiumMovt money NULL) 
    
    INSERT @vwtest (MSReportingCatID, DevMonthId, YearOfAccount, SettlementCurrency, GrossPremiumMovt) VALUES 
    (1, 44, 1993, N'GBP', 0.0000),(1, 2, 1993, N'GBP', 1222.9400),(1, 133, 1993, N'GBP', 0.0000),(1, 133, 1993, N'GBP', 0.0000),
    (1, 133, 1993, N'GBP', 0.0000),(1, 133, 1993, N'GBP', 0.0000),(1, 133, 1993, N'GBP', 0.0000),(1, 15, 1993, N'GBP', -1060.7100),
    (1, 133, 1993, N'GBP', 0.0000),(1, 133, 1993, N'GBP', 0.0000),(1, 2, 1993, N'GBP', 4466.9100)
    
    ;WITH base AS (
    SELECT MSReportingCatID, devmonthID, ROW_NUMBER() OVER (PARTITION BY MSReportingCatID ORDER BY yearofAccount, devmonthID) AS seq, YearOfAccount, SettlementCurrency, SUM(GrossPremiumMovt) AS sumGrossPremiumMovt
      FROM @vwtest
     GROUP BY MSReportingCatID, devmonthID, YearOfAccount, SettlementCurrency
    )
    
    SELECT b.MSReportingCatID, b.DevMonthId, b.seq, b.YearOfAccount, b.SettlementCurrency, b.sumGrossPremiumMovt, b.sumGrossPremiumMovt+COALESCE(SUM(b2.sumGrossPremiumMovt),0) AS runningTotal
      FROM base b
        LEFT OUTER JOIN base b2
    	  ON b.MSReportingCatID = b2.MSReportingCatID
    	  AND b.seq > b2.seq
     GROUP BY b.MSReportingCatID, b.DevMonthId, b.seq, b.YearOfAccount, b.SettlementCurrency, b.sumGrossPremiumMovt


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, April 25, 2016 3:00 PM