SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trying to use a Case Statement in Where clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bconner
Starting Member

USA
48 Posts

Posted - 01/30/2013 :  14:07:32  Show Profile  Reply with Quote
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

147 Posts

Posted - 01/30/2013 :  14:42:28  Show Profile  Reply with Quote
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

USA
48 Posts

Posted - 01/30/2013 :  15:32:41  Show Profile  Reply with Quote
TM, that works! Thank you very much...

Brian
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/31/2013 :  01:22:16  Show Profile  Reply with Quote
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

147 Posts

Posted - 01/31/2013 :  13:57:03  Show Profile  Reply with Quote
Thanks Visakh for the link on date performance.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/31/2013 :  13:58:14  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000