October 16, 2012 at 8:49 am
Hi I have this query -
SELECT *
FROM [GATE].[dbo].[DATA_CLOB]
WHERE RAW_CLOB LIKE 'AAA|E0221002|_|'+CONVERT(VARCHAR(8),GETDATE(),112)+'%ecp%';
GO
I use this in an SQL Agent job to run twice daily as this code pulls records sent in to us twice daily.
It pulls one record in the morning that comes into the db at around 8am but then another one comes in at around 1pm and it pulls that when it runs again in the afternoon.. I output the results of the query to a CSV file and set the job to run at a time after I know that each one will have come in.
However, when the job runs and the output writes to the CSV file in the afternoon run it still includes the data from the one that comes in at 8am. Is there any way of only taking the most recent record and writing it to the CSV file?
I figure this would be something I change in the code but I'm not sure.
Here is my table and sample data -
USE [GATE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DATA_CLOB](
[DC_INDEX_FK] [numeric](15, 0) NOT NULL,
[RAW_CLOB] [text] NULL,
[RAW_BLOB] [text] NULL,
CONSTRAINT [UNQDFCL] UNIQUE NONCLUSTERED
(
[DC_INDEX_FK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [GATE]
GO
INSERT INTO [dbo].[DATA_CLOB] (DC_INDEX_FK, RAW_CLOB, RAW_BLOB)
VALUES
(1, 'AAA|E0221002|D|20120909080458|EC|UK|BP|0001|6768|TR01|', NULL),
(2, 'AAA|E0221002|D|20120909130512|EC|UK|BP|0001|6768|TR01|', NULL)
GO
October 16, 2012 at 9:01 am
You Can do a select top 1 order by DC_INDEX_FK desc.
What if you get 3 in one day? how do you make sure they all process?
October 16, 2012 at 9:10 am
Thanks, that worked.
We don't get three in a day, we only get two sent to us daily.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply