SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get missing records


Get missing records

Author
Message
PSB
PSB
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 1536
Hi,

I have a table #TransDetail

CREATE TABLE #TransDetail
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Number INT,
TransDate SMALLDATETIME,
TransTime INT,
Site VARCHAR(100),
Company VARCHAR(15)


)

INSERT INTO #TransDetail (Number,TransDate,Transtime,site,company)

SELECT 1,'12/1/2012',0757,'MyCompany','COMP'
UNION
SELECT 2,'12/1/2012',0757,'MyCompany','COMP'
UNION
SELECT 3,'12/1/2012',0757,'MyCompany','COMP'
UNION
SELECT 5,'12/1/2012',0757,'MyCompany','COMP'
UNION
SELECT 1,'12/2/2012',0957,'MyNewCompany','COMPUSA'
UNION
SELECT 2,'12/2/2012',0957,'MyNewCompany','COMPUSA'
UNION
SELECT 5,'12/2/2012',0957,'MyNewCompany','COMPUSA'

I want to to know the missing numbers for a particular TransDate,Site and company
Example For 'MyCompany' , Number 4 is missing.

So I want 4,

Date and Time of prev number imported , That is '12/1/2012',0757

Date and Time of next number imported , That is '12/1/2012',0757,
Site (Mycompany)
AND
Company(COMP) returned

Similarly for 'MyNewCompany' Number 3 and 4 are missing


So I want Nunbers 3 ,4 returned along with

Date and Time of prev number imported , That is '12/2/2012',0957

Date and Time of next number imported , That is '12/2/2012',0957,
Site (MyNewcompany)
AND
Company(COMPUSA) returned

Please help
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85042 Visits: 41074
What is the domain of all possible numbers for the "Number column"?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5188 Visits: 4076
declare @max int , @cnt int
declare @t table (id int ,Company varchar(50),Site nvarchar(20),TransDate Datetime)
set @cnt =1
--select * from #TransDetail
select @max = MAX(Number) from #TransDetail
while ( @max > = @cnt)
begin
insert into @t select @cnt, 'COMP','MyCompany','2012-12-01 00:00:00'
insert into @t select @cnt, 'COMPUSA','MyNewCompany','2012-12-02 00:00:00'
set @cnt = @cnt + 1
end

select
a.*
from #TransDetail b
right join @t a
on a.id = b.number and a.Company = b.Company
where b.number is null



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16071 Visits: 19532
The #temp table isn't strictly necessary but may improve performance:
;WITH MassagedData AS (
SELECT Company,
MIN_Number = MIN(Number),
MAX_Number = MAX(Number),
rn = ROW_NUMBER() OVER(PARTITION BY Company ORDER BY MIN(Number))
FROM (
SELECT Company, Number,
gp = Number - ROW_NUMBER() OVER(PARTITION BY Company ORDER BY Number)
FROM #TransDetail
) d
GROUP BY Company, gp
)
SELECT a.Company, PriorNumber = a.MAX_Number, NextNumber = b.MIN_Number
INTO #RangeBoundaries
FROM MassagedData a
INNER JOIN MassagedData b
ON b.Company = a.Company AND b.rn = a.rn + 1

SELECT r.Company, p.[Site],
r.PriorNumber, PriorDate = p.TransDate, PriorTime = p.TransTime,
r.NextNumber, NextDate = n.TransDate, NextTime = n.TransTime
FROM #RangeBoundaries r
LEFT JOIN #TransDetail p ON p.Company = r.Company AND p.Number = r.PriorNumber
LEFT JOIN #TransDetail n ON n.Company = r.Company AND n.Number = r.NextNumber
ORDER BY r.Company, r.PriorNumber



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7217 Visits: 6431
Retraction of solution posted.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7217 Visits: 6431
Here's my shot at this:


;
WITH Tally (n) AS (
SELECT TOP (SELECT MAX(Number) FROM #TransDetail)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns),
IntialGrouping AS (
SELECT Site, Company, TransDate, TransTime, StartNumber=MIN(Number), EndNumber=MAX(Number)
FROM (
SELECT ID, Number, TransDate, TransTime, Site, Company
,rn=Number-ROW_NUMBER() OVER (PARTITION BY TransDate, Site, Company ORDER BY Number)
FROM #TransDetail) a
GROUP BY TransDate,TransTime,Site,Company,rn)
SELECT Site, Company, TransDate, TransTime, Number=n
FROM (
SELECT Site, Company, TransDate, TransTime, StartNumber=MIN(Number), EndNumber=MAX(Number)
FROM (
SELECT Site, Company, TransDate, TransTime, Number, rn
FROM (
SELECT Site, Company, TransDate, TransTime, Number
,rn=ROW_NUMBER() OVER (PARTITION BY TransDate, Site, Company ORDER BY Number)/2
FROM IntialGrouping a
CROSS APPLY (VALUES (StartNumber-1),(EndNumber+1)) b(Number)
) a
) a
GROUP BY Site, Company, TransDate, TransTime, rn
HAVING COUNT(*) = 2) a
CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN StartNumber AND EndNumber) b;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85042 Visits: 41074
Bhuvnesh (3/15/2013)
declare @max int , @cnt int
declare @t table (id int ,Company varchar(50),Site nvarchar(20),TransDate Datetime)
set @cnt =1
--select * from #TransDetail
select @max = MAX(Number) from #TransDetail
while ( @max > = @cnt)
begin
insert into @t select @cnt, 'COMP','MyCompany','2012-12-01 00:00:00'
insert into @t select @cnt, 'COMPUSA','MyNewCompany','2012-12-02 00:00:00'
set @cnt = @cnt + 1
end

select
a.*
from #TransDetail b
right join @t a
on a.id = b.number and a.Company = b.Company
where b.number is null



I'm a bit surprised at that, Bhuvnesh. I thought you've been around long enough to know not to use such RBAR solutions. Try your solution on the following test data and let me know how long it takes. And, yeah... this is a real life test. I've been through such a situation before.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7217 Visits: 6431
Jeff Moden (3/17/2013)
Try your solution on the following test data and let me know how long it takes.


Test data?


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search