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.
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 DATESET @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))ANDYEAR([IMPORT_DATE]) = CASEWHEN 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 DATESET @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))ANDYEAR([IMPORT_DATE]) = CASEWHEN 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 DATESET @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)ANDYEAR([IMPORT_DATE]) = YEAR(@LastImportDate) |
|
|
bconner
Starting Member
48 Posts |
Posted - 2013-01-30 : 15:32:41
|
TM, that works! Thank you very much...Brian |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
tm
Posting Yak Master
160 Posts |
Posted - 2013-01-31 : 13:57:03
|
Thanks Visakh for the link on date performance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 13:58:14
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|