Return last amount larger than 0

Question

Return last amount larger than 0

Ian C0ckcroft

Hall of Fame

Points: 3373
More actions
June 15, 2018 at 4:56 am

#413883

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 2

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

sgmunson SSC Guru Points: 110640 More actions · Answer 3

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

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".

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 5

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

Ian Cockcroft
MCITP BI Specialist

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

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(14)  NOT NULL
  ,TRAN_DATE    DATE    NOT NULL
  ,CLOSING_CREDITS  NUMERIC(15,8) NOT NULL
  ,CLOSING_DEBITS  NUMERIC(15,8) NOT 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(7)  NOT 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

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 7

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

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

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 9

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

Ian Cockcroft
MCITP BI Specialist

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 10

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 11

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;