How to find out the missing transaction from a table
-
mardi 29 mai 2012 11:15
Hi,
I have a table which filled up 86400 (daily transaction in second) and i have a vb code which run daily to import those transactions in one goes.
I would like to find out the missing record(s) in which second so I can only insert/add back those transaction to the table. I could create a dummy table with 86400 transaction and compare with the daily transaction but what is the sql statement on this...
Thanks
Toutes les réponses
-
mardi 29 mai 2012 11:17
Use the EXCEPT operator:
select * from dummytable EXCEPT select * from originaltable
- Proposé comme réponse skc_chat mardi 29 mai 2012 11:43
-
mardi 29 mai 2012 11:39
Assuming the transaction table timestamp is rounded to the second, you could use the CTE below to identify transactions missing for a given date.
WITH t16(n) AS ( SELECT 0 FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS t(n)) ,t1M(n) AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM (SELECT 0 FROM t16 AS a, t16 AS b, t16 AS c, t16 AS d, t16 AS e) AS t(n)) SELECT DATEADD(second, n-1, '20120528') AS TransactionTime FROM t1M LEFT JOIN dbo.TransactionTable AS t ON t.TransactionTime = DATEADD(second, n-1, '20120528') WHERE n <= 84600 AND t.TransactionTime IS NULL;
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
- Marqué comme réponse Naomi NMicrosoft Community Contributor, Moderator mercredi 30 mai 2012 02:15
-
mercredi 30 mai 2012 00:59
Hi, Dan
I would like to have a SQL query so I can call it from my VB program. BTW, my raw table layout as per below
CREATE TABLE [dbo].[DLA_A](
[Date] [smalldatetime] NOT NULL, <-- this is stored as 2012-05-30 for all 86400 daily raw records
[Time] [datetime2](7) NOT NULL, <-- this is stored as 2012-05-30 00:00:01, 2012-05-30 00:00:02 ,2012-05-30 00:00:03 ... etc
[DateImp] [smalldatetime] NOT NULL,
[IDKey] [nchar](14) NOT NULL,
[FID] [smallint] NOT NULL,
[C1] [float] NULL,
[C2] [float] NULL,
[C3] [float] NULL,
[C4] [float] NULL,
[C5] [float] NULL,
[C6] [float] NULL,
[C7] [float] NULL,
[C8] [float] NULL,
[C9] [float] NULL,
[C10] [float] NULL,
[C11] [float] NULL,
CONSTRAINT [PK_DLA_A] PRIMARY KEY CLUSTERED -
mercredi 30 mai 2012 01:20ModérateurInstead of dbo.TransactionTable it should be dbo.DLA_A then. And also TransactionTime should be [Time] as this is the field's name.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
mercredi 30 mai 2012 01:33
All you need to do is customize the script I posted earlier and execute from your VB code. The statement below takes parameter @Date. Pass the desired date value as a parameter from your code and customize the INSERT...SELECT with the values you want for the missing not-null columns. The nullable columns will be NULL.
WITH t16(n) AS ( SELECT 0 FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS t(n)) ,t1M(n) AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM (SELECT 0 FROM t16 AS a, t16 AS b, t16 AS c, t16 AS d, t16 AS e) AS t(n)) INSERT INTO [dbo].[DLA_A] ( [Date] ,[Time] ,[DateImp] ,[IDKey] ,[FID] ) SELECT @Date ,DATEADD(second, n-1, @Date) ,NULL --specify your desired [DateImp] value ,NULL --specify your desired [IDKey] value ,NULL --specify your desired [[FID]] value FROM t1M LEFT JOIN dbo.DLA_A AS t ON t.[Time] = DATEADD(second, n-1, @Date) WHERE n <= 84600 AND t.[Time] IS NULL;
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
- Modifié Dan GuzmanMVP mercredi 30 mai 2012 01:34 corrected comment
-
mercredi 30 mai 2012 01:34
Hi, Naomi
Anything I did wrong ???
-
mercredi 30 mai 2012 01:49
This error message means that there are other SQL statements preceeding the WITH statement. The previous statement must be terminated with a semi-colon.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
- Proposé comme réponse Peja TaoModerator mercredi 30 mai 2012 02:09
-
mercredi 30 mai 2012 01:59Modérateur As Dan said, add ; before WITH
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Marqué comme réponse kkcci88888 mercredi 30 mai 2012 02:12
-
mercredi 30 mai 2012 02:13
Thanks, Naomi
It's work very well !!
-
mercredi 30 mai 2012 07:57
Hi, Dan
I have another table which store the record in 10 minutes interval so there would be 144 daily (2012-05-30 00:00:00, 2012-05-30 00:10:00...2012-05-30 23:50:00) etc. Could you change the CTE for me, please !!
Thanks
-
mercredi 30 mai 2012 12:04
I have another table which store the record in 10 minutes interval so there would be 144 daily (2012-05-30 00:00:00, 2012-05-30 00:10:00...2012-05-30 23:50:00) etc. Could you change the CTE for me, please !!
Here is another version with 10-minute intervals. Just change the references to "YourTable" to your actual table name along with columns as needed.
WITH t4(n) AS ( SELECT 0 FROM (VALUES(0),(0),(0),(0)) AS t(n)) ,t256(n) AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM (SELECT 0 FROM t4 AS a, t4 AS b, t4 AS c, t4 AS d) AS t(n)) INSERT INTO dbo.YourTable ( [Date] ,[Time] ,[DateImp] ,[IDKey] ,[FID] ) SELECT @Date ,DATEADD(minute, (n-1)*10, @Date) ,NULL --specify your desired [DateImp] value ,NULL --specify your desired [IDKey] value ,NULL --specify your desired [[FID]] value FROM t256 LEFT JOIN dbo.YourTable AS t ON t.[Time] = DATEADD(minute, (n-1)*10, @Date) WHERE n <= 144 AND t.[Time] IS NULL;
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
- Marqué comme réponse kkcci88888 jeudi 31 mai 2012 00:39
.png)
