SELECT certain records based on current date?

1

Apologies, I've done this in the past but am having a brain freeze.

SELECT CASE WHEN CONVERT (VARCHAR (6), GETDATE(), 112) = '201702' THEN 'FIN_CC1500-12-106-100-100', '201601', '10.24', 'FIN_FC1030-12-106-100-100', '201601', '35.66', WHEN CONVERT (VARCHAR (6), GETDATE(), 112) = '201703' THEN 'FIN_FC1120-12-106-100-100', '201602', '0.71', 'FIN_FC1120-12-164-100-100', '201602', '8.88' END
Depending on the month, I only wish to return values from the previous month so I've tried something similar to the above (which falls over with multiple errors).

Can someone unfreeze my brain with the right syntax?

Many thanks as always.

2

Please provide:

  • table definition in the form of create statement
  • sample data in the form of insert statement
  • expected output from the sample data you provide
3 
DECLARE @test AS TABLE
(
CustID VARCHAR (50),
TDate VARCHAR (6),
Value DECIMAL (15, 2)
)

INSERT INTO @test
VALUES 
(
'FIN_CC1500-12-106-100-100', '201701', '10.24'
)
INSERT INTO @test
VALUES 
(
'FIN_FC1030-12-106-100-100', '201701', '35.66'
) 
INSERT INTO @test
VALUES 
(
'FIN_FC1090-12-106-100-100', '201702', '9.55'
) 

SELECT * FROM @test

So as we're in 201702, I'd only want the first two records returned as they're 201701.

Next month when we're in 201703, I'd only want the third record returned as it's 201702.

4

Something like:

select custid
      ,tdate
      ,[value]
  from @test
 where tdate=convert(varchar(6),dateadd(month,-1,current_timestamp),112)
;
1 Like
5

Thanks.

I really don't know where I was going with the CASE thing.....

:disappointed: