Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get missing records Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 5:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:21 AM
Points: 296, Visits: 1,058
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
Post #1431305
Posted Thursday, March 14, 2013 6:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1431311
Posted Friday, March 15, 2013 6:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1431488
Posted Friday, March 15, 2013 7:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
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
Post #1431542
Posted Sunday, March 17, 2013 7:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 3,417, Visits: 5,332
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!
Post #1432057
Posted Sunday, March 17, 2013 7:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 3,417, Visits: 5,332
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!
Post #1432059
Posted Sunday, March 17, 2013 7:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 35,216, Visits: 31,672
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432063
Posted Sunday, March 17, 2013 7:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 3,417, Visits: 5,332
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!
Post #1432064
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse