I have a records of file run in an ETL. The problem statement is the get only the status of pervious file based on the date for the current file.
| TransactionID | FileName | Status | FileDate | TransactionDate | SrcFile |
|---|---|---|---|---|---|
| 1 | CallData_20200731 | 5 | 2020-07-31 | 2021-04-12 21:11:42.967 | CallData |
| 2 | CallData_20200831 | 5 | 2020-08-31 | 2021-04-12 21:12:37.387 | CallData |
| 3 | CallData_20200930 | 6 | 2020-09-30 | 2021-04-12 21:12:51.437 | CallData |
| 4 | CallData_20201031 | 6 | 2020-10-31 | 2021-04-12 21:13:52.487 | CallData |
| 5 | CallData_20201130 | 6 | 2020-11-30 | 2021-04-12 21:15:06.213 | CallData |
| 6 | CallData_20201231 | 6 | 2020-12-31 | 2021-04-12 21:16:16.870 | CallData |
If I input 'CallData_20200930' then I should get the record for TransactionID 2 only, i.e. the pervious date. The date is also not continuous, so I could subtract 1 day from the current file date. I tried using LAG() but it was of not help.
Currently I am able to slice and get the date and filename separated. I am not sure if its the efficient way or there is a better way all together. and current I am getting all the previous record too.
declare @filename nvarchar(20),
@srcfile nvarchar(20),
@filedate date
set @filename = 'CallData_20200930';
set @srcfile = LEFT(@filename,CHARINDEX('_',@filename)-1)
set @filedate = SUBSTRING(@filename,CHARINDEX('_',@filename)+1,8)
;with cte as
(select *,ROW_NUMBER() over(order by FileDate) as RN from tblFileStatusLog where SrcFile = @srcfile )
select * from cte where FileDate<@filedate ;
Below is the schema and data to help anyone out
CREATE TABLE [dbo].[tblFileStatusLog](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [nvarchar](50) NOT NULL,
[Status] [int] NOT NULL,
[FileDate] [date] NULL,
[TransactionDate] [datetime] NOT NULL,
[SrcFile] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblFileStatusLog] ON
INSERT [dbo].[tblFileStatusLog] ([TransactionID], [FileName], [Status], [FileDate], [TransactionDate], [SrcFile]) VALUES (1, N'CallData_20200731', 5, CAST(N'2020-07-31' AS Date), CAST(N'2021-04-12T21:11:42.967' AS DateTime), N'CallData')
INSERT [dbo].[tblFileStatusLog] ([TransactionID], [FileName], [Status], [FileDate], [TransactionDate], [SrcFile]) VALUES (2, N'CallData_20200831', 5, CAST(N'2020-08-31' AS Date), CAST(N'2021-04-12T21:12:37.387' AS DateTime), N'CallData')
INSERT [dbo].[tblFileStatusLog] ([TransactionID], [FileName], [Status], [FileDate], [TransactionDate], [SrcFile]) VALUES (3, N'CallData_20200930', 6, CAST(N'2020-09-30' AS Date), CAST(N'2021-04-12T21:12:51.437' AS DateTime), N'CallData')
INSERT [dbo].[tblFileStatusLog] ([TransactionID], [FileName], [Status], [FileDate], [TransactionDate], [SrcFile]) VALUES (4, N'CallData_20201031', 6, CAST(N'2020-10-31' AS Date), CAST(N'2021-04-12T21:13:52.487' AS DateTime), N'CallData')
INSERT [dbo].[tblFileStatusLog] ([TransactionID], [FileName], [Status], [FileDate], [TransactionDate], [SrcFile]) VALUES (5, N'CallData_20201130', 6, CAST(N'2020-11-30' AS Date), CAST(N'2021-04-12T21:15:06.213' AS DateTime), N'CallData')
INSERT [dbo].[tblFileStatusLog] ([TransactionID], [FileName], [Status], [FileDate], [TransactionDate], [SrcFile]) VALUES (6, N'CallData_20201231', 6, CAST(N'2020-12-31' AS Date), CAST(N'2021-04-12T21:16:16.870' AS DateTime), N'CallData')
SET IDENTITY_INSERT [dbo].[tblFileStatusLog] OFF
ALTER TABLE [dbo].[tblFileStatusLog] ADD DEFAULT (getdate()) FOR [TransactionDate]
GO