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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query to be used in Crystal Reports

Author  Topic 

dchbiv
Starting Member

10 Posts
Posted - 2012-02-16 : 09:37:40
I have a problem.

I have a table that has missing transaction numbers (TX_NUM) in it. I need to identify the missing values in these breaks.

the table is laid out as such:


company_id Unit_ID Device_id TX_DT_TM TX_NUM
10 1 51 2/12/12 12:00 AM 789
10 1 51 2/12/12 12:10 AM 790
10 1 51 2/12/12 12:20 AM 791
10 1 51 2/12/12 12:30 AM 793
10 1 51 2/12/12 12:40 AM 794
10 1 51 2/12/12 12:50 AM 795
20 2 48 2/12/12 12:00 AM 594
20 2 48 2/12/12 12:10 AM 595
20 2 48 2/12/12 12:20 AM 596
20 2 48 2/12/12 12:30 AM 598
20 2 48 2/12/12 12:40 AM 599
20 2 48 2/12/12 12:50 AM 600
20 2 48 2/12/12 1:00 AM 602
20 2 48 2/12/12 1:10 AM 603
20 2 48 2/12/12 1:20 AM 604
20 2 48 2/12/12 1:30 AM 605
20 2 62 2/12/12 12:10 AM 9995
20 2 62 2/12/12 12:20 AM 9996
20 2 62 2/12/12 12:30 AM 9997
20 2 62 2/12/12 12:40 AM 9998
20 2 62 2/12/12 12:50 AM 9999
20 2 62 2/12/12 1:00 AM 1
20 2 62 2/12/12 1:20 AM 3
20 2 62 2/12/12 1:30 AM 4
20 2 62 2/12/12 1:40 AM 5
20 2 62 2/12/12 1:50 AM 6
20 2 62 2/12/12 2:00 AM 7


I need to query this table by TX_DT_TM and identify missing TX_NUM values for each combination of
Company_ID, Unit_IT and Device_ID.

NOTE: the max value for the TX_NUM for each device is 9999, it then rolls over to 1 and starts over again.

I am in an environment where there are no other DB people to bounce this off of. I hope someone can help.

I am seriously suffering from some caffeine overload and balancing 7 other reports to get completed in a very
short time.

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 09:42:23
you need to use [ code] [ /code] tags to retain formating (with out the space




company_id	Unit_ID	Device_id	TX_DT_TM		TX_NUM
10		1	51		2/12/12 12:00 AM	789
10		1	51		2/12/12 12:10 AM	790
10		1	51		2/12/12 12:20 AM	791
10		1	51		2/12/12 12:30 AM	793
10		1	51		2/12/12 12:40 AM	794
10		1	51		2/12/12 12:50 AM	795
20		2	48		2/12/12 12:00 AM	594
20		2	48		2/12/12 12:10 AM	595
20		2	48		2/12/12 12:20 AM	596
20		2	48		2/12/12 12:30 AM	598
20		2	48		2/12/12 12:40 AM	599
20		2	48		2/12/12 12:50 AM	600
20		2	48		2/12/12 1:00 AM		602
20		2	48		2/12/12 1:10 AM		603
20		2	48		2/12/12 1:20 AM		604
20		2	48		2/12/12 1:30 AM		605
20		2	62		2/12/12 12:10 AM	9995
20		2	62		2/12/12 12:20 AM	9996
20		2	62		2/12/12 12:30 AM	9997
20		2	62		2/12/12 12:40 AM	9998
20		2	62		2/12/12 12:50 AM	9999
20		2	62		2/12/12 1:00 AM		1
20		2	62		2/12/12 1:20 AM		3
20		2	62		2/12/12 1:30 AM		4
20		2	62		2/12/12 1:40 AM		5
20		2	62		2/12/12 1:50 AM		6
20		2	62		2/12/12 2:00 AM		7




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 09:43:28
I don'e see any missing numbers



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

dchbiv
Starting Member

10 Posts
Posted - 2012-02-16 : 09:58:09
Sorry, I left off the CODE tags

BTW the following are missing


10		1	51		2/12/12 12:20 AM	792
20		2	48		2/12/12 12:30 AM	597
20		2	48		2/12/12 1:00 AM		601
20		2	62		2/12/12 1:20 AM		2



I have a problem.

I have a table that has missing transaction numbers (TX_NUM) in it. I need to identify the missing values in these breaks.

the table is laid out as such:



company_id	Unit_ID	Device_id	TX_DT_TM		TX_NUM
10		1	51		2/12/12 12:00 AM	789
10		1	51		2/12/12 12:10 AM	790
10		1	51		2/12/12 12:20 AM	791
10		1	51		2/12/12 12:30 AM	793
10		1	51		2/12/12 12:40 AM	794
10		1	51		2/12/12 12:50 AM	795
20		2	48		2/12/12 12:00 AM	594
20		2	48		2/12/12 12:10 AM	595
20		2	48		2/12/12 12:20 AM	596
20		2	48		2/12/12 12:30 AM	598
20		2	48		2/12/12 12:40 AM	599
20		2	48		2/12/12 12:50 AM	600
20		2	48		2/12/12 1:00 AM		602
20		2	48		2/12/12 1:10 AM		603
20		2	48		2/12/12 1:20 AM		604
20		2	48		2/12/12 1:30 AM		605
20		2	62		2/12/12 12:10 AM	9995
20		2	62		2/12/12 12:20 AM	9996
20		2	62		2/12/12 12:30 AM	9997
20		2	62		2/12/12 12:40 AM	9998
20		2	62		2/12/12 12:50 AM	9999
20		2	62		2/12/12 1:00 AM		1
20		2	62		2/12/12 1:20 AM		3
20		2	62		2/12/12 1:30 AM		4
20		2	62		2/12/12 1:40 AM		5
20		2	62		2/12/12 1:50 AM		6
20		2	62		2/12/12 2:00 AM		7



I need to query this table by TX_DT_TM and identify missing TX_NUM values for each combination of
Company_ID, Unit_IT and Device_ID.

NOTE: the max value for the TX_NUM for each device is 9999, it then rolls over to 1 and starts over again.

I am in an environment where there are no other DB people to bounce this off of. I hope someone can help.
I am seriously suffering from some caffeine overload and balancing 7 other reports to get completed in a very
short time.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 10:10:37
I still don't see what's missing...what's missing?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

dchbiv
Starting Member

10 Posts
Posted - 2012-02-16 : 10:46:38
It was at the top of my second post
the following are missing rows:


10		1	51		2/12/12 12:20 AM	792
20		2	48		2/12/12 12:30 AM	597
20		2	48		2/12/12 1:00 AM		601
20		2	62		2/12/12 1:20 AM		2
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 11:37:05
How do you know they are "missing"



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

dchbiv
Starting Member

10 Posts
Posted - 2012-02-16 : 11:51:16
Its a break in the series. Imagine you are looking for missing checks from your check register. The TX_NUM could be thought of as a number of a check.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 12:04:31
Ahhh..I see said the blind man

That's find for the Transaction Number, but where do you get the other data from ??

And this is the bad thing about these "numbers"

How do you know they weren't purposely deleted an are not really a gap?

In any case, you will need a numbers table and join to it to see what's missing

SELECT * FROM n -- the numbers table
LEFT JOIN yourTable t
ON n.n = t.TX_NUM
WHERE t.TX_NUM IS NULL



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

dchbiv
Starting Member

10 Posts
Posted - 2012-02-16 : 12:29:27
The data cannot be arbitrarily deleted. This represents transmitted data from an external log. If there are missing tx_nums then we need to identify them and fix the data in the table. This is for auditing purposes. I cannot use a join to a numbers table because in the tx_num can roll over from 9999 to 1 during a given day. I need to step through the days data for each company,unit and device to identify a break in a sequence by comparing each record s value of tx_num to the previous records TX_NUM to see if it is indeed 1 greater. I also need to allow for the rollover from 9999 to 1 during that comparison
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 12:46:12
quote:
Originally posted by dchbiv

The data cannot be arbitrarily deleted.


OK

quote:

This represents transmitted data from an external log. If there are missing tx_nums then we need to identify them and fix the data in the table.


What is the Process to "Fix" Them

quote:


This is for auditing purposes. I cannot use a join to a numbers table because in the tx_num can roll over from 9999 to 1 during a given day.


OK, So then Just add the day to the query


quote:

I need to step through the days data for each company,unit and device to identify a break in a sequence by comparing each record s value of tx_num to the previous records TX_NUM to see if it is indeed 1 greater. I also need to allow for the rollover from 9999 to 1 during that comparison


If you say so, then use a CURSOR.

BUT, I still think you could come up with a set based operation. How many days are you looking at?

I imaging the most Current, because you would have fixed yesterday already..no?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

dchbiv
Starting Member

10 Posts
Posted - 2012-02-16 : 13:14:35
The method to fix would be identify the failed transmissions and resubmit them hence inserting the missing data.

How would a set based operation help me. The tx_num fieldsprobalby won't start at 1 for any given day, plus the likelyhood that they will roll over from 9999 to 1 in a given time period is very likely.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 13:19:47
OK, so what are your rules to know what's missing?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-02-16 : 13:27:04
So Rule #1

Which Day(S) does the missing sequences belong to in this Example?



CREATE TABLE myTable99(company_id int, Unit_ID int, Device_id int, TX_DT_TM datetime, TX_NUM int)
GO

INSERT INTO myTable99 (company_id, Unit_ID, Device_id, TX_DT_TM, TX_NUM)
SELECT 



SELECT 10,	 1,	51,	 '2/11/12 11:50 PM',	787 UNION ALL
SELECT 10,	 1,	51,	 '2/12/12 12:00 AM',	789 UNION ALL
SELECT 10,	 1,	51,	 '2/12/12 12:10 AM',	791 UNION ALL
SELECT 10,	 1,	51,	 '2/12/12 11:50 PM',	792 UNION ALL
SELECT 10,	 1,	51,	 '2/13/12 12:10 AM',	794
GO

DROP TABLE myTable99
GO




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

dchbiv
Starting Member

10 Posts
Posted - 2012-02-21 : 08:17:04
I will only be examining one day at a time, but for each combination of company_id,Unit_ID, and Device_id I am looking for records that do not have consecutive TX_NUM

so in my example the following would be logged as missing:

company_id	Unit_ID	Device_id	TX_DT_TM		TX_NUM
10		1	51		2/12/12 12:20 AM	792
20		2	48		2/12/12 12:30 AM	597
20		2	48		2/12/12 1:00 AM		601
20		2	62		2/12/12 1:20 AM		2
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2012-02-21 : 13:17:33
maybe this

SELECT *
FROM
	(
		SELECT
			[company_Id]
			, [Unit_Id]
			, [Device_id]
			, [TX_num]
			, ROW_NUMBER() OVER ( PARTITION BY [CompanyID], [Unit_ID], [Device_ID] ORDER BY [TX_Num] DESC ) AS [rnk]
		FROM
			<TABLE>
	)
	AS t1
WHERE
	t1.[rnk] > 1
	
	AND NOT EXISTS (
		SELECT 1
		FROM <TABLE> AS t2
		WHERE
			t2.[company_id] = t1.[Company_ID]
			AND t2.[Unit_ID] = t1.[Unit_ID]
			AND t2.[Device_id] = t1.[Device_id]
			AND t2.[TX_Num] = t1.[TX_Num] + 1
			)

Where you are looking at each row that isn't the latest for the combinations or company_id, unit_id, device_id and finding them if there is no consecutive match.

This will find the rows with no parents - you just need to add 1 to find the next.

it won't find all possible gaps -- just the ones immediately after the latest in a run.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

dchbiv
Starting Member

10 Posts
Posted - 2012-02-21 : 15:48:01
I need to report every missing record. Plus remember, I have a rollover event where TX_NUM rolls from 9999 to 1 during the day.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site