February 16, 2012 at 11:21 am
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_idUnit_IDDevice_idTX_DT_TMTX_NUM
101512/12/12 12:00 AM789
101512/12/12 12:10 AM790
101512/12/12 12:20 AM791
101512/12/12 12:30 AM793
101512/12/12 12:40 AM794
101512/12/12 12:50 AM795
202482/12/12 12:00 AM594
202482/12/12 12:10 AM595
202482/12/12 12:20 AM596
202482/12/12 12:30 AM598
202482/12/12 12:40 AM599
202482/12/12 12:50 AM600
202482/12/12 1:00 AM602
202482/12/12 1:10 AM603
202482/12/12 1:20 AM604
202482/12/12 1:30 AM605
202622/12/12 12:10 AM9995
202622/12/12 12:20 AM9996
202622/12/12 12:30 AM9997
202622/12/12 12:40 AM9998
202622/12/12 12:50 AM9999
202622/12/12 1:00 AM1
202622/12/12 1:20 AM3
202622/12/12 1:30 AM4
202622/12/12 1:40 AM5
202622/12/12 1:50 AM6
202622/12/12 2:00 AM7
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.
February 16, 2012 at 12:13 pm
Provided the "missing" values are represented by NULLs in your db, something like this will identify those rows:
select company_id, Unit_ID, Device_id, TX_DT_TM from theTable where TX_NUM is NULL
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
February 16, 2012 at 12:41 pm
they are not nulls. the table appears just as shown in the example. I need to scan the table for missing sequences in the TX_NUM column
February 16, 2012 at 12:48 pm
Sorry, I misunderstood your question, and ignored the issue of sequence.
I leave this to the actual experts.
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
February 16, 2012 at 12:56 pm
Hey Lost. Easiest ways are either using a Tally Table if you had one handy, or a quick and dirty Row_Number() function.
Is it possible you can setup that sample data as you'll find in the first link in my signature? The benefit of doing that is we can hand you tested, directly usable code that you can see the results of and we can tweak further if needed instead of flailing at it helplessly.
It's not that hard to locate gaps, just need to be familiar with the pattern in T-SQL. The more you help us though the easier we can help you.
Edit: I should mention the rollover piece is what's going to make life interesting here. What was simple will get complex.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2012 at 1:22 pm
As Craig said, without tables and sample data this is pretty tough for us to help.
I tossed this quick example together to show you how you could use a tally table for this. You would just need to add some grouping logic into this and you should be good to go.
create table #MyTable
(
ID int,
SomeValue varchar(50)
)
insert #MyTable(ID, SomeValue)
Values (1, 'Value 1'), (2, 'Value2'), (4, 'Value4'), (7, 'Value 7')
select * from Tally t
left join #MyTable tb on t.N = tb.ID
where t.N <= (select Max(ID) from #MyTable)
and tb.ID is null
drop table #MyTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 16, 2012 at 1:39 pm
IF OBJECT_ID('TempDB..#SampleData','U') IS NOT NULL
DROP TABLE #SampleData
CREATE TABLE [dbo].[SampleData](
[Company_ID] [nvarchar](50) NOT NULL,
[Unit_ID] [nvarchar](20) NOT NULL,
[Device_id] [int] NOT NULL,
[TX_DT_TM] [datetime] NOT NULL,
[TXN_NUMBER] [int] NOT NULL,
) ON [PRIMARY]
GO
--===== Insert the test data into the test table
INSERT INTO SampleData
([Company_ID]
,[Unit_ID]
,[Device_id]
,[TX_DT_TM])
,[TXN_NUMBER])
SELECT '20','1','51','Feb 12 2012 1:12AM','9370' UNION ALL
SELECT '20','1','51','Feb 12 2012 4:12AM','9371' UNION ALL
SELECT '20','1','51','Feb 12 2012 6:12AM','9372' UNION ALL
SELECT '20','1','51','Feb 12 2012 7:12AM','9373' UNION ALL
SELECT '20','1','51','Feb 12 2012 7:12AM','9374' UNION ALL
SELECT '20','1','51','Feb 12 2012 7:12AM','9375' UNION ALL
SELECT '20','1','51','Feb 12 2012 8:12AM','9376' UNION ALL
SELECT '20','1','51','Feb 12 2012 8:12AM','9377' UNION ALL
SELECT '20','1','51','Feb 12 2012 9:12AM','9380' UNION ALL
SELECT '20','1','51','Feb 12 2012 9:12AM','9381' UNION ALL
SELECT '20','1','51','Feb 12 2012 9:12AM','9382' UNION ALL
SELECT '20','1','51','Feb 12 2012 9:12AM','9383' UNION ALL
SELECT '20','1','51','Feb 12 2012 9:12AM','9384' UNION ALL
SELECT '20','1','51','Feb 12 2012 10:12AM','9385' UNION ALL
SELECT '20','1','51','Feb 12 2012 10:12AM','9386' UNION ALL
SELECT '20','1','51','Feb 12 2012 10:12AM','9387' UNION ALL
SELECT '20','1','51','Feb 12 2012 10:12AM','9388' UNION ALL
SELECT '20','1','51','Feb 12 2012 10:12AM','9389' UNION ALL
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #SampleData ON
February 16, 2012 at 1:40 pm
in order to help us help you....this is the type of set up script that easy for anyone to cut and paste into SSMS and start working on.
if you can provide this, preferably with your initial question, you will be very pleased with the response that this friendly forum provides ...
--===== here are some helpful links on creating tables/data
--==== http://www.sqlservercentral.com/articles/Best+Practices/61537/
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABLEDATA]') AND type in (N'U'))
DROP TABLE [dbo].[TABLEDATA]
GO
CREATE TABLE [dbo].[TABLEDATA](
[company_id] [int] NULL,
[Unit_ID] [int] NULL,
[Device_id] [int] NULL,
[TX_DT_TM] [datetime] NULL,
[TX_NUM] [int] NULL
)
GO
INSERT INTO [dbo].[TABLEDATA]([company_id], [Unit_ID], [Device_id], [TX_DT_TM], [TX_NUM])
SELECT 10, 1, 51, '20121202 00:00:00.000', 789 UNION ALL
SELECT 10, 1, 51, '20121202 00:10:00.000', 790 UNION ALL
SELECT 10, 1, 51, '20121202 00:20:00.000', 791 UNION ALL
SELECT 10, 1, 51, '20121202 00:30:00.000', 793 UNION ALL
SELECT 10, 1, 51, '20121202 00:40:00.000', 794 UNION ALL
SELECT 10, 1, 51, '20121202 00:50:00.000', 795 UNION ALL
SELECT 20, 2, 48, '20121202 00:00:00.000', 594 UNION ALL
SELECT 20, 2, 48, '20121202 00:10:00.000', 595 UNION ALL
SELECT 20, 2, 48, '20121202 00:20:00.000', 596 UNION ALL
SELECT 20, 2, 48, '20121202 00:30:00.000', 598 UNION ALL
SELECT 20, 2, 48, '20121202 00:40:00.000', 599 UNION ALL
SELECT 20, 2, 48, '20121202 00:50:00.000', 600 UNION ALL
SELECT 20, 2, 48, '20121202 01:00:00.000', 602 UNION ALL
SELECT 20, 2, 48, '20121202 01:10:00.000', 603 UNION ALL
SELECT 20, 2, 48, '20121202 01:20:00.000', 604 UNION ALL
SELECT 20, 2, 48, '20121202 01:30:00.000', 605 UNION ALL
SELECT 20, 2, 62, '20121202 00:10:00.000', 9995 UNION ALL
SELECT 20, 2, 62, '20121202 00:20:00.000', 9996 UNION ALL
SELECT 20, 2, 62, '20121202 00:30:00.000', 9997 UNION ALL
SELECT 20, 2, 62, '20121202 00:40:00.000', 9998 UNION ALL
SELECT 20, 2, 62, '20121202 00:50:00.000', 9999 UNION ALL
SELECT 20, 2, 62, '20121202 01:00:00.000', 1 UNION ALL
SELECT 20, 2, 62, '20121202 01:20:00.000', 3 UNION ALL
SELECT 20, 2, 62, '20121202 01:30:00.000', 4 UNION ALL
SELECT 20, 2, 62, '20121202 01:40:00.000', 5 UNION ALL
SELECT 20, 2, 62, '20121202 01:50:00.000', 6 UNION ALL
SELECT 20, 2, 62, '20121202 02:00:00.000', 7
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 16, 2012 at 2:44 pm
Ah, thanks J.
Lost, just an FYI, your code doesn't run well. You've mixed and matched #table and hard tables in there. No worries, I realize you're new, we won't beat you up for it. 🙂 Just pointing it out.
So, let's get to the code. First, we need to find gaps. I realize Device_ID 62 will break this chain because of the rollover, but bear with me. I'll walk you through the code build.
First thing, we need to assign the Row_number function:
SELECT
company_id, Unit_id, device_id, TX_NUM,
ROW_NUMBER() OVER ( PARTITION BY company_id, unit_id, device_id ORDER BY TX_DT_TM) AS rn
FROM
TableData
That gives us a value per row. I know, it's not handy yet, but it'll help us group contiguous sections.
From there, you create groups:
;WITH cte AS
(SELECT
company_id, Unit_id, device_id, TX_NUM,
ROW_NUMBER() OVER ( PARTITION BY company_id, unit_id, device_id ORDER BY TX_DT_TM) AS rn
FROM
TableData
)
SELECT
*, TX_Num - rn AS rnGroup
FROM
cte
What those groups give us are underlying islands of data. If you want to read a more thorough discussion on this technique, read this article by Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/71550/
Finally, this will give you a list, by company, unit, and device, of the contiguous sections. Since you don't need fully automatable results (yet) and need something to review, it does not pick up the 9999 - 1 rollover as a contiguous section. Coding that is outside of my available time at the moment. However, this will get you human usable results, from the TABLEDATA table build above by J Livingston.
;WITH cte AS
(SELECT
company_id, Unit_id, device_id, TX_NUM,
ROW_NUMBER() OVER ( PARTITION BY company_id, unit_id, device_id ORDER BY TX_DT_TM) AS rn
FROM
TableData
)
-- SELECT * FROM cte
, cte2 AS
(SELECT
*, TX_Num - rn AS rnGroup
FROM
cte)
SELECT
company_id, Unit_id, device_id, /*rnGroup,*/
MIN( TX_Num) AS MinTX,
MAX( TX_Num) AS MaxTX
FROM
cte2
GROUP BY
company_id, Unit_id, device_id,rnGroup
Now, armed with that, what else do you need to do with this data once you have a list of the contiguous sections? Any automation of some kind?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 21, 2012 at 6:18 am
In my "live data" that does not give me the proper break in sequences.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply