Return last amount larger than 0

  • Hi guys,
    I have a table of accounts by month.
    I need to return the last amount larger than 0
    e.g
    Unique_ID              DATE             CLOSING_CREDITS    CLOSING_DEBITS
    00522467364634    2017-03-01    0                                    0
    00522467364634    2017-04-01    0                                     0
    00522467364634    2017-05-01    140.94                             0
    00522467364634    2017-06-01    1665.96                         0
    00522467364634    2017-07-01    0                                   0
    00522467364634    2017-08-01    0                                     0
    00522467364634    2017-09-01    0                                   0 
    00522467364634    2017-10-01    0                                   0
    00522467364634    2017-11-01    0                                     0
    00522467364634    2017-12-01    0                                      0
    00522467364634    2018-01-01    0                                           0
    00522467364634    2018-02-01    0                                         0
    00522467449743    2017-03-01    9387869.9299999997    6316286.0499999998
    00522467449743    2017-04-01    6497821.9900000002    1875693.42
    00522467449743    2017-05-01    2463692.37                     11333614.84
    00522467449743    2017-06-01    5759757.1699999999    7368125.1799999997
    00522467449743    2017-07-01    3235571.06                    2464517.84
    00522467449743    2017-08-01    6090506.1399999997    3667257.76
    00522467449743    2017-09-01    7444508.8099999996    8474647.9100000001
    00522467449743    2017-10-01    8820797.25                    6863557.3399999999
    00522467449743    2017-11-01    11007685.23                  8979739.5899999999
    00522467449743    2017-12-01    8486954.3599999994   11224876.779999999
    00522467449743    2018-01-01    14320916.109999999   13263591.279999999
    00522467449743    2018-02-01    7789124.96                    3585965.79

    the result should be

    00522467364634    2017-06-01    1665.96                         0
    00522467449743    2018-02-01    7789124.96                    3585965.79

    I thought of creating a temp  table for each monthe and the comaring them. but that seems like alot  of overhead

    any ideas?
    cheers 
    thanks

    Ian Cockcroft
    MCITP BI Specialist

  • Ian C0ckcroft - Friday, June 15, 2018 4:56 AM

    Hi guys,
    I have a table of accounts by month.
    I need to return the last amount larger than 0
    e.g
    Unique_ID              DATE             CLOSING_CREDITS    CLOSING_DEBITS
    00522467364634    2017-03-01    0                                    0
    00522467364634    2017-04-01    0                                     0
    00522467364634    2017-05-01    140.94                             0
    00522467364634    2017-06-01    1665.96                         0
    00522467364634    2017-07-01    0                                   0
    00522467364634    2017-08-01    0                                     0
    00522467364634    2017-09-01    0                                   0 
    00522467364634    2017-10-01    0                                   0
    00522467364634    2017-11-01    0                                     0
    00522467364634    2017-12-01    0                                      0
    00522467364634    2018-01-01    0                                           0
    00522467364634    2018-02-01    0                                         0
    00522467449743    2017-03-01    9387869.9299999997    6316286.0499999998
    00522467449743    2017-04-01    6497821.9900000002    1875693.42
    00522467449743    2017-05-01    2463692.37                     11333614.84
    00522467449743    2017-06-01    5759757.1699999999    7368125.1799999997
    00522467449743    2017-07-01    3235571.06                    2464517.84
    00522467449743    2017-08-01    6090506.1399999997    3667257.76
    00522467449743    2017-09-01    7444508.8099999996    8474647.9100000001
    00522467449743    2017-10-01    8820797.25                    6863557.3399999999
    00522467449743    2017-11-01    11007685.23                  8979739.5899999999
    00522467449743    2017-12-01    8486954.3599999994   11224876.779999999
    00522467449743    2018-01-01    14320916.109999999   13263591.279999999
    00522467449743    2018-02-01    7789124.96                    3585965.79

    the result should be

    00522467364634    2017-06-01    1665.96                         0
    00522467449743    2018-02-01    7789124.96                    3585965.79

    I thought of creating a temp  table for each monthe and the comaring them. but that seems like alot  of overhead

    any ideas?
    cheers 
    thanks

    >2500 points and you haven't included DDL & sample data as INSERT statements? You should know better than that by now.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Ian C0ckcroft - Friday, June 15, 2018 4:56 AM

    Hi guys,
    I have a table of accounts by month.
    I need to return the last amount larger than 0
    e.g
    Unique_ID              DATE             CLOSING_CREDITS    CLOSING_DEBITS
    00522467364634    2017-03-01    0                                    0
    00522467364634    2017-04-01    0                                     0
    00522467364634    2017-05-01    140.94                             0
    00522467364634    2017-06-01    1665.96                         0
    00522467364634    2017-07-01    0                                   0
    00522467364634    2017-08-01    0                                     0
    00522467364634    2017-09-01    0                                   0 
    00522467364634    2017-10-01    0                                   0
    00522467364634    2017-11-01    0                                     0
    00522467364634    2017-12-01    0                                      0
    00522467364634    2018-01-01    0                                           0
    00522467364634    2018-02-01    0                                         0
    00522467449743    2017-03-01    9387869.9299999997    6316286.0499999998
    00522467449743    2017-04-01    6497821.9900000002    1875693.42
    00522467449743    2017-05-01    2463692.37                     11333614.84
    00522467449743    2017-06-01    5759757.1699999999    7368125.1799999997
    00522467449743    2017-07-01    3235571.06                    2464517.84
    00522467449743    2017-08-01    6090506.1399999997    3667257.76
    00522467449743    2017-09-01    7444508.8099999996    8474647.9100000001
    00522467449743    2017-10-01    8820797.25                    6863557.3399999999
    00522467449743    2017-11-01    11007685.23                  8979739.5899999999
    00522467449743    2017-12-01    8486954.3599999994   11224876.779999999
    00522467449743    2018-01-01    14320916.109999999   13263591.279999999
    00522467449743    2018-02-01    7789124.96                    3585965.79

    the result should be

    00522467364634    2017-06-01    1665.96                         0
    00522467449743    2018-02-01    7789124.96                    3585965.79

    I thought of creating a temp  table for each monthe and the comaring them. but that seems like alot  of overhead

    any ideas?
    cheers 
    thanks

    See the below illustration :


    create table credit
    (
    uniqueid bigint,
    coldate date,
    CLOSING_CREDITS float,
    CLOSING_DEBITS float
    );

    insert into credit values(00522467364634,'2017-05-01',140.94,0);
    insert into credit values(00522467364634,'2017-06-01',1665.96,0);
    insert into credit values(00522467364634,'2017-07-01',0,0);
    insert into credit values(00522467364634,'2017-08-01',0,0);

    select uniqueid,coldate,CLOSING_CREDITS,CLOSING_DEBITS from
    (select uniqueid,coldate,CLOSING_CREDITS,CLOSING_DEBITS,
    rank() over(partition by uniqueid order by coldate desc,CLOSING_CREDITS desc,uniqueid) as rnk
    from credit
    where CLOSING_CREDITS<>0)credit
    where rnk=1;

    1 rows (showing 1 to 1)

    Saravanan

  • Try this:
    CREATE TABLE #ClosingEntries (
        Unique_ID char(14) NOT NULL,
        TRAN_DATE date NOT NULL,
        CLOSING_CREDITS decimal(20,10) NOT NULL,
        CLOSING_DEBITS decimal(20,10) NOT NULL
    );
    INSERT INTO #ClosingEntries (Unique_ID, TRAN_DATE, CLOSING_CREDITS, CLOSING_DEBITS)
        VALUES    ('00522467364634', '2017-03-01', 0, 0),
                ('00522467364634', '2017-04-01', 0, 0),
                ('00522467364634', '2017-05-01', 140.94, 0),
                ('00522467364634', '2017-06-01', 1665.96, 0),
                ('00522467364634', '2017-07-01', 0, 0),
                ('00522467364634', '2017-08-01', 0, 0),
                ('00522467364634', '2017-09-01', 0, 0),
                ('00522467364634', '2017-10-01', 0, 0),
                ('00522467364634', '2017-11-01', 0, 0),
                ('00522467364634', '2017-12-01', 0, 0),
                ('00522467364634', '2018-01-01', 0, 0),
                ('00522467364634', '2018-02-01', 0, 0),
                ('00522467449743', '2017-03-01', 9387869.9299999997, 6316286.0499999998),
                ('00522467449743', '2017-04-01', 6497821.9900000002, 1875693.42),
                ('00522467449743', '2017-05-01', 2463692.37, 11333614.84),
                ('00522467449743', '2017-06-01', 5759757.1699999999, 7368125.1799999997),
                ('00522467449743', '2017-07-01', 3235571.06, 2464517.84),
                ('00522467449743', '2017-08-01', 6090506.1399999997, 3667257.76),
                ('00522467449743', '2017-09-01', 7444508.8099999996, 8474647.9100000001),
                ('00522467449743', '2017-10-01', 8820797.25, 6863557.3399999999),
                ('00522467449743', '2017-11-01', 11007685.23, 8979739.5899999999),
                ('00522467449743', '2017-12-01', 8486954.3599999994, 11224876.779999999),
                ('00522467449743', '2018-01-01', 14320916.109999999, 13263591.279999999),
                ('00522467449743', '2018-02-01', 7789124.96, 3585965.79);

    WITH NON_ZERO_DATA AS (

        SELECT *,
            ROW_NUMBER() OVER(PARTITION BY CE.Unique_ID ORDER BY CE.TRAN_DATE DESC) AS RowNum
        FROM #ClosingEntries AS CE
        WHERE CE.CLOSING_CREDITS + CE.CLOSING_DEBITS > 0
    )
    SELECT NZD.Unique_ID, NZD.TRAN_DATE, NZD.CLOSING_CREDITS, NZD.CLOSING_DEBITS
    FROM NON_ZERO_DATA AS NZD
    WHERE NZD.RowNum = 1
    ORDER BY NZD.TRAN_DATE DESC;

    DROP TABLE #ClosingEntries;

    Results:
    Unique_ID  TRAN_DATE CLOSING_CREDITS         CLOSING_DEBITS
    -------------- ---------- --------------------------------------- ---------------------------------------
    00522467449743 2018-02-01 7789124.9600000000        3585965.7900000000
    00522467364634 2017-06-01 1665.9600000000         0.0000000000

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • I'd check column separately rather than adding them, just in case negative amounts could appear now, or somehow do appear later:

    WHERE CE.CLOSING_CREDITS <> 0 AND CE.CLOSING_DEBITS <> 0

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks guys, gives me some direction to explore.
    Phil, its been a while. sorry

    Ian Cockcroft
    MCITP BI Specialist

  • sgmunson - Friday, June 15, 2018 8:26 AM


    WITH NON_ZERO_DATA AS (
        SELECT *,
            ROW_NUMBER() OVER(PARTITION BY CE.Unique_ID ORDER BY CE.TRAN_DATE DESC) AS RowNum
        FROM #ClosingEntries AS CE
        WHERE CE.CLOSING_CREDITS + CE.CLOSING_DEBITS > 0
    )
    SELECT NZD.Unique_ID, NZD.TRAN_DATE, NZD.CLOSING_CREDITS, NZD.CLOSING_DEBITS
    FROM NON_ZERO_DATA AS NZD
    WHERE NZD.RowNum = 1
    ORDER BY NZD.TRAN_DATE DESC;

    This solution works fine on small data sets but slows down quite quickly when the sets get larger.
    😎

    Here is an alternative solution that works better on larger sets

    WITH SAMPLE_DATA (Unique_ID, TRAN_DATE, CLOSING_CREDITS, CLOSING_DEBITS)
    AS
    (
        SELECT Unique_ID, TRAN_DATE, CLOSING_CREDITS, CLOSING_DEBITS FROM (
        VALUES    ('00522467364634', '2017-03-01', 0, 0),
                ('00522467364634', '2017-04-01', 0, 0),
                ('00522467364634', '2017-05-01', 140.94, 0),
                ('00522467364634', '2017-06-01', 1665.96, 0),
                ('00522467364634', '2017-07-01', 0, 0),
                ('00522467364634', '2017-08-01', 0, 0),
                ('00522467364634', '2017-09-01', 0, 0),
                ('00522467364634', '2017-10-01', 0, 0),
                ('00522467364634', '2017-11-01', 0, 0),
                ('00522467364634', '2017-12-01', 0, 0),
                ('00522467364634', '2018-01-01', 0, 0),
                ('00522467364634', '2018-02-01', 0, 0),
                ('00522467449743', '2017-03-01', 9387869.9299999997, 6316286.0499999998),
                ('00522467449743', '2017-04-01', 6497821.9900000002, 1875693.42),
                ('00522467449743', '2017-05-01', 2463692.37, 11333614.84),
                ('00522467449743', '2017-06-01', 5759757.1699999999, 7368125.1799999997),
                ('00522467449743', '2017-07-01', 3235571.06, 2464517.84),
                ('00522467449743', '2017-08-01', 6090506.1399999997, 3667257.76),
                ('00522467449743', '2017-09-01', 7444508.8099999996, 8474647.9100000001),
                ('00522467449743', '2017-10-01', 8820797.25, 6863557.3399999999),
                ('00522467449743', '2017-11-01', 11007685.23, 8979739.5899999999),
                ('00522467449743', '2017-12-01', 8486954.3599999994, 11224876.779999999),
                ('00522467449743', '2018-01-01', 14320916.109999999, 13263591.279999999),
                ('00522467449743', '2018-02-01', 7789124.96, 3585965.79)
    )X(Unique_ID, TRAN_DATE, CLOSING_CREDITS, CLOSING_DEBITS)
    )
    ,LATEST_VALUES AS
    (
    SELECT
        SD.Unique_ID
       ,MAX(SD.TRAN_DATE) AS LAST_DATE
    FROM    SAMPLE_DATA SD
    WHERE SD.CLOSING_CREDITS <> 0
    OR    SD.CLOSING_DEBITS  <> 0
    GROUP BY SD.Unique_ID
    )
    SELECT
        SD.Unique_ID
       ,SD.TRAN_DATE
       ,SD.CLOSING_CREDITS
       ,SD.CLOSING_DEBITS
    FROM        LATEST_VALUES   LV
    CROSS APPLY SAMPLE_DATA     SD
    WHERE       LV.Unique_ID    =   SD.Unique_ID
    AND         LV.LAST_DATE    =   SD.TRAN_DATE
    ;

    Quick comparison

    USE TEEST;
    GO
    SET NOCOUNT ON;
    -- /* -- UNCOMMENT THIS LINE TO SKIP THE SAMPLE DATA SET RECREATION
    IF OBJECT_ID(N'dbo.TBL_TEST_LAST_VALUE') IS NOT NULL DROP TABLE dbo.TBL_TEST_LAST_VALUE;
    CREATE TABLE dbo.TBL_TEST_LAST_VALUE
    (
      Unique_ID    VARCHAR(14NOT NULL
      ,TRAN_DATE    DATE    NOT NULL
      ,CLOSING_CREDITS  NUMERIC(15,8NOT NULL
      ,CLOSING_DEBITS  NUMERIC(15,8NOT NULL
      ,INDEX CLIDX_DBO_TBL_TEST_LAST_VALUE_Unique_ID_TRAN_DATE CLUSTERED (Unique_ID ASC,TRAN_DATE ASC)
    );
    DECLARE @ACCOUNT_COUNT INT  = 100000;
    DECLARE @DATE_COUNT  INT  = 48;
    DECLARE @FIRST_DATE  DATE  = '20150101';
    ;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
    , NUMS(N) AS (SELECT TOP(@ACCOUNT_COUNT) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
         FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
    ,ACCOUNTS(Unique_ID) AS
    (
      SELECT
       STUFF('00000000000000',15 - LEN(NM.N),LEN(NM.N),NM.N) AS Unique_ID
      FROM NUMS NM
    )
    ,DATE_LIST(TRAN_DATE) AS
    (
      SELECT
       TOP(@DATE_COUNT)
        DATEADD(MONTH,NM.N - 1,@FIRST_DATE) AS TRAN_DATE
      FROM  NUMS  NM
    )
    INSERT INTO dbo.TBL_TEST_LAST_VALUE(Unique_ID,TRAN_DATE,CLOSING_CREDITS,CLOSING_DEBITS)
    SELECT
      AC.Unique_ID
     ,DL.TRAN_DATE
     ,((ABS(CHECKSUM(NEWID())) % 10000) / 100.00) * ((ABS(CHECKSUM(NEWID())) % 2) * 1.0) AS CLOSING_CREDITS
     ,((ABS(CHECKSUM(NEWID())) % 10000) / 100.00) * ((ABS(CHECKSUM(NEWID())) % 2) * 1.0) AS CLOSING_DEBITS
    FROM   ACCOUNTS  AC
    CROSS APPLY DATE_LIST DL;
    -- */
    DECLARE @Unique_ID    VARCHAR(14= '' ;
    DECLARE @TRAN_DATE    DATE    = GETDATE();
    DECLARE @CLOSING_CREDITS  NUMERIC(15,8) = 0.0;
    DECLARE @CLOSING_DEBITS  NUMERIC(15,8) = 0.0;
    DECLARE @timer TABLE
    (
    T_TS DATETIME2(7NOT NULL DEFAULT (SYSDATETIME())
    ,T_TXT VARCHAR(50)  NOT NULL
    );
    INSERT INTO @timer(T_TXT) VALUES('DRY RUN')
    SELECT
      @Unique_ID   = LV.Unique_ID
     ,@TRAN_DATE   = LV.TRAN_DATE
     ,@CLOSING_CREDITS = LV.CLOSING_CREDITS
     ,@CLOSING_DEBITS = LV.CLOSING_DEBITS
    FROM dbo.TBL_TEST_LAST_VALUE  LV
    INSERT INTO @timer(T_TXT) VALUES('DRY RUN')
    INSERT INTO @timer(T_TXT) VALUES('ROW_NUMBER')
    ;WITH NON_ZERO_DATA AS (
      SELECT *,
       ROW_NUMBER() OVER(PARTITION BY CE.Unique_ID ORDER BY CE.TRAN_DATE DESC) AS RowNum
      FROM dbo.TBL_TEST_LAST_VALUE AS CE
      WHERE CE.CLOSING_CREDITS + CE.CLOSING_DEBITS > 0
    )
    SELECT
     @Unique_ID   = NZD.Unique_ID
    ,@TRAN_DATE   = NZD.TRAN_DATE
    ,@CLOSING_CREDITS = NZD.CLOSING_CREDITS
    ,@CLOSING_DEBITS = NZD.CLOSING_DEBITS
    FROM NON_ZERO_DATA AS NZD
    WHERE NZD.RowNum = 1;
    INSERT INTO @timer(T_TXT) VALUES('ROW_NUMBER')
    INSERT INTO @timer(T_TXT) VALUES('AGGREGATE')
    ;WITH LATEST_VALUES AS
    (
    SELECT
      SD.Unique_ID
     ,MAX(SD.TRAN_DATE) AS LAST_DATE
    FROM  dbo.TBL_TEST_LAST_VALUE  SD
    WHERE SD.CLOSING_CREDITS  <> 0
    OR  SD.CLOSING_DEBITS   <> 0
    GROUP BY SD.Unique_ID
    )
    SELECT
      @Unique_ID   = SD.Unique_ID
     ,@TRAN_DATE   = SD.TRAN_DATE
     ,@CLOSING_CREDITS = SD.CLOSING_CREDITS
     ,@CLOSING_DEBITS = SD.CLOSING_DEBITS
    FROM   LATEST_VALUES     LV
    CROSS APPLY dbo.TBL_TEST_LAST_VALUE  SD
    WHERE   LV.Unique_ID    = SD.Unique_ID
    AND   LV.LAST_DATE    = SD.TRAN_DATE
    ;
    INSERT INTO @timer(T_TXT) VALUES('AGGREGATE')
    -------------------------------------------------------------------------------
    -- CALCULATE AND DISPLAY THE RESULTS
    -------------------------------------------------------------------------------
    SELECT
    T.T_TXT
    ,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
    FROM @timer T
    GROUP BY T.T_TXT
    ORDER BY DURATION;

    Results

    T_TXT         DURATION
    DRY RUN       1092707
    AGGREGATE     2710711
    ROW_NUMBER    6159013

  • Hi guys, thanks for the replies. requirments have changed slightly.
    Initially, the data comes through from Oracle in 1 row on a yearly grane. I unpivot it to get dates on monthly grane.
    I am thinking rather than unpivot it first,  get the last available amount on a yearly level.(less overhead)Something like

    CREATE TABLE [dbo].[TEST](
        [Unique_ID] [int] IDENTITY(0,1) NOT NULL,
        [MAR_CREDITS] [decimal](18, 3) NULL,
        [APR_CREDITS] [decimal](18, 3) NULL,
        [MAY_CREDITS] [decimal](18, 3) NULL,
        [JUN_CREDITS] [decimal](18, 3) NULL,
        [JUL_CREDITS] [decimal](18, 3) NULL,
        [AUG_CREDITS] [decimal](18, 3) NULL,
        [SEP_CREDITS] [decimal](18, 3) NULL,
        [OCT_CREDITS] [decimal](18, 3) NULL,
        [NOV_CREDITS] [decimal](18, 3) NULL,
        [DEC_CREDITS] [decimal](18, 3) NULL,
        [JAN_CREDITS] [decimal](18, 3) NULL,
        [FEB_CREDITS] [decimal](18, 3) NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO [TEST] (    [MAR_CREDITS] ,    [APR_CREDITS],    [MAY_CREDITS],    [JUN_CREDITS],    [JUL_CREDITS],    [AUG_CREDITS],    [SEP_CREDITS],    [OCT_CREDITS],    [NOV_CREDITS],    [DEC_CREDITS],    [JAN_CREDITS],    [FEB_CREDITS])
      VALUES 
                (5211.32,4545.00,3874.25,4.321,5256.00,6879.654,7542.00,0,0,0,11412,135.32),
                (320.32,4747.00,3874.25,4.321,5256.00,6879.654,7542.00,0,0,0,0,0),
                (5.32,7425.00,3874.25,0,0,6879.654,7542.00,8963,9741,10753,11412,122.32),
                (1.00,5555.00,3874.25,4.321,5256.00,0,7542.00,8963,9741,10753,11412,4757.32),
                (598.32,85555.00,3874.25,4.321,5256.00,6879.654,7542.00,8963,9741,10753,11412,45464.32),
                (741.32,2547.00,3874.25,4.321,5256.00,6879.654,7542.00,8963,9741,10753,11412,0.32),
                (211.32,5451.00,3874.25,4.321,5256.00,6879.654,7542.00,8963,9741,10753,0,0),
                (311.32,222.00,3874.25,4.321,5256.00,6879.654,7542.00,8963,9741,10753,0,0),
                (55.32,85.00,3874.25,4.321,5256.00,0,0,0,0,0,0,0),
                (11.232,5420.00,3874.25,4.321,5256.00,6879.654,7542.00,8963,9741,0,0,0),
                (5211.32,7475.00,3874.25,4.321,0,0,0,0,0,0,0,0),
                (5211.32,2235.00,0,0,0,0,0,0,0,0,0,0)
        

    I thought of using a nested case statement. but the overhead is exorbatent and it  can only nest to the 10th degree

    SELECT
         [Unique_ID]
      ,[MAR_CREDITS]
      ,[APR_CREDITS]
      ,[MAY_CREDITS]
      ,[JUN_CREDITS]
      ,[JUL_CREDITS]
      ,[AUG_CREDITS]
      ,[SEP_CREDITS]
      ,[OCT_CREDITS]
      ,[NOV_CREDITS]
      ,[DEC_CREDITS]
      ,[JAN_CREDITS]
      ,[FEB_CREDITS]
         ,
         CASE
         WHEN [FEB_CREDITS] <> 0 THEN [FEB_CREDITS]
         ELSE (
                CASE
             WHEN [JAN_CREDITS] <> 0 THEN [JAN_CREDITS]
             ELSE (
                    CASE
                 WHEN [DEC_CREDITS] <> 0 THEN [DEC_CREDITS]
                 ELSE (
                        CASE
                     WHEN [NOV_CREDITS] <> 0 THEN [NOV_CREDITS]
                     ELSE (
                            CASE
                         WHEN [OCT_CREDITS] <> 0 THEN [OCT_CREDITS]
                         ELSE (
                                    CASE
                                 WHEN [SEP_CREDITS] <> 0 THEN [SEP_CREDITS]
                                 ELSE (
                                        CASE
                                     WHEN [AUG_CREDITS] <> 0 THEN [AUG_CREDITS]
                                     ELSE (
                                            CASE
                                         WHEN [JUL_CREDITS] <> 0 THEN [JUL_CREDITS]
                                         ELSE (
                                            CASE
                                         WHEN [JUN_CREDITS] <> 0 THEN [JUN_CREDITS]
                                         ELSE -9999
                                     END)
                                 END)
                             END)
                         END)
                     END)
                 END)
             END)
         END)
         END [LAST_CLOSING_CREDIT]
    FROM [dbo].[TEST]

    any better ideas?
    thanks 
    Ian

    Ian Cockcroft
    MCITP BI Specialist

  • You only need one CASE expression:
    CASE
        WHEN FEB_CREDITS <> 0 THEN FEB_CREDITS
        WHEN JAN_CREDITS <> 0 THEN JAN_CREDITS
        WHEN DEC_CREDITS <> 0 THEN DEC_CREDITS
        WHEN NOV_CREDITS <> 0 THEN NOV_CREDITS
        WHEN OCT_CREDITS <> 0 THEN OCT_CREDITS
        WHEN SEP_CREDITS <> 0 THEN SEP_CREDITS
        WHEN AUG_CREDITS <> 0 THEN AUG_CREDITS
        WHEN JUL_CREDITS <> 0 THEN JUL_CREDITS
        WHEN JUN_CREDITS <> 0 THEN JUN_CREDITS
        WHEN MAY_CREDITS <> 0 THEN MAY_CREDITS
        WHEN APR_CREDITS <> 0 THEN APR_CREDITS
        WHEN MAR_CREDITS <> 0 THEN MAR_CREDITS
    END

    What results are you expecting from your latest sample data?

    John

  • duh!!! dont I feel like a ....
    Thanks John
    works perfectly

    Ian Cockcroft
    MCITP BI Specialist

  • just one thing... please tell me you're not storing money / financial values as `float`s?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • NOTE: This method is about 10-15% slower than the CASE statement!
    Here is an alternative which brings the last non-zero value
    😎


    SELECT
      TW.Unique_ID
     ,TW.MAR_CREDITS
     ,TW.APR_CREDITS
     ,TW.MAY_CREDITS
     ,TW.JUN_CREDITS
     ,TW.JUL_CREDITS
     ,TW.AUG_CREDITS
     ,TW.SEP_CREDITS
     ,TW.OCT_CREDITS
     ,TW.NOV_CREDITS
     ,TW.DEC_CREDITS
     ,TW.JAN_CREDITS
     ,TW.FEB_CREDITS
     ,COALESCE(
      NULLIF(TW.FEB_CREDITS,0.0)
     ,NULLIF(TW.JAN_CREDITS,0.0)
     ,NULLIF(TW.DEC_CREDITS,0.0)
     ,NULLIF(TW.NOV_CREDITS,0.0)
     ,NULLIF(TW.OCT_CREDITS,0.0)
     ,NULLIF(TW.SEP_CREDITS,0.0)
     ,NULLIF(TW.AUG_CREDITS,0.0)
     ,NULLIF(TW.JUL_CREDITS,0.0)
     ,NULLIF(TW.JUN_CREDITS,0.0)
     ,NULLIF(TW.MAY_CREDITS,0.0)
     ,NULLIF(TW.APR_CREDITS,0.0)
     ,NULLIF(TW.MAR_CREDITS,0.0)) AS [LAST_CLOSING_CREDIT]
    FROM  dbo.TBL_TEST_LAST_VALUE_WIDE  TW;

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply