| Author |
 Topic  |
|
|
bconner
Starting Member
USA
47 Posts |
 Posted - 01/30/2013 : 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
137 Posts |
Posted - 01/30/2013 : 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)
|
 |
|
|
bconner
Starting Member
USA
47 Posts |
Posted - 01/30/2013 : 15:32:41
|
TM, that works! Thank you very much...
Brian |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
|
|
tm
Posting Yak Master
137 Posts |
Posted - 01/31/2013 : 13:57:03
|
| Thanks Visakh for the link on date performance. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 01/31/2013 : 13:58:14
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Trying to use a Case Statement in Where clause
