Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trying to use a Case Statement in Where clause

Author  Topic 

bconner
Starting Member

48 Posts

Posted - 2013-01-30 : 14:07:32
I am trying to grab the previous Import Date.

Example the last file Import Date was '2013-30-01'
The code should return '2012-12-29' this was the file loaded previous to the '2013-30-01' file.


Below is the code I am using but it doesn't return anything..



DECLARE @LastImportDate DATE
SET @LastImportDate = (SELECT MAX([IMPORT_DATE]) AS [IMPORT_DATE] FROM dbo.ATB_Credits_Trending)


SELECT [GROUP_ID]
,[INVOICE_NUMBER]
,[DATE_OF_SERVICE]
,[INVOICE_CREATE_DATE]
,[INVOICE_BALANCE]
,[DOS_AGE]
,[MONTH]
,[YEAR]
,[AGED_BUCKET]
,[IMPORT_DATE]

FROM [AdHoc].[dbo].[ATB_Credits_Trending]

WHERE
MONTH([IMPORT_DATE]) = MONTH(DATEADD(m, -1, @LastImportDate))

AND

YEAR([IMPORT_DATE]) =

CASE
WHEN YEAR(DATEADD(y, -1, @LastImportDate)) = YEAR(GETDATE())
THEN YEAR(DATEADD(y, -1, @LastImportDate))

WHEN YEAR(DATEADD(y, -1, @LastImportDate)) <> YEAR(GETDATE())
THEN YEAR(DATEADD(y, -1, @LastImportDate))-1 END




Any help is greatly appreciated...

Brian

tm
Posting Yak Master

160 Posts

Posted - 2013-01-30 : 14:42:28
quote:


DECLARE @LastImportDate DATE
SET @LastImportDate = (SELECT MAX([IMPORT_DATE]) AS [IMPORT_DATE] FROM dbo.ATB_Credits_Trending)


SELECT [GROUP_ID]
,[INVOICE_NUMBER]
,[DATE_OF_SERVICE]
,[INVOICE_CREATE_DATE]
,[INVOICE_BALANCE]
,[DOS_AGE]
,[MONTH]
,[YEAR]
,[AGED_BUCKET]
,[IMPORT_DATE]

FROM [AdHoc].[dbo].[ATB_Credits_Trending]

WHERE
MONTH([IMPORT_DATE]) = MONTH(DATEADD(m, -1, @LastImportDate))

AND

YEAR([IMPORT_DATE]) =

CASE
WHEN YEAR(DATEADD(y, -1, @LastImportDate)) = YEAR(GETDATE())
THEN YEAR(DATEADD(y, -1, @LastImportDate))

WHEN YEAR(DATEADD(y, -1, @LastImportDate)) <> YEAR(GETDATE())
THEN YEAR(DATEADD(y, -1, @LastImportDate))-1 END







Is this what you are looking for?


DECLARE @LastImportDate DATE
SET @LastImportDate = (SELECT MAX(DATEADD(m,-1,[IMPORT_DATE])) AS [IMPORT_DATE] FROM dbo.ATB_Credits_Trending)


SELECT [GROUP_ID]
,[INVOICE_NUMBER]
,[DATE_OF_SERVICE]
,[INVOICE_CREATE_DATE]
,[INVOICE_BALANCE]
,[DOS_AGE]
,[MONTH]
,[YEAR]
,[AGED_BUCKET]
,[IMPORT_DATE]

FROM [AdHoc].[dbo].[ATB_Credits_Trending]

WHERE
MONTH([IMPORT_DATE]) = MONTH(@LastImportDate)

AND

YEAR([IMPORT_DATE]) = YEAR(@LastImportDate)



Go to Top of Page

bconner
Starting Member

48 Posts

Posted - 2013-01-30 : 15:32:41
TM, that works! Thank you very much...

Brian
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 01:22:16
make sure you read this and understand the performance implications of way date range filters are written

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tm
Posting Yak Master

160 Posts

Posted - 2013-01-31 : 13:57:03
Thanks Visakh for the link on date performance.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 13:58:14
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -