March 14, 2013 at 5:36 pm
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
March 14, 2013 at 6:24 pm
What is the domain of all possible numbers for the "Number column"?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2013 at 6:06 am
declare @max-2 int , @cnt int
declare @t table (id int ,Company varchar(50),Site nvarchar(20),TransDate Datetime)
set @cnt =1
--select * from #TransDetail
select @max-2 = MAX(Number) from #TransDetail
while ( @max-2 > = @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;-)
March 15, 2013 at 7:53 am
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
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
March 17, 2013 at 7:04 pm
Retraction of solution posted.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 17, 2013 at 7:21 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 17, 2013 at 7:31 pm
Bhuvnesh (3/15/2013)
declare @max-2 int , @cnt intdeclare @t table (id int ,Company varchar(50),Site nvarchar(20),TransDate Datetime)
set @cnt =1
--select * from #TransDetail
select @max-2 = MAX(Number) from #TransDetail
while ( @max-2 > = @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
Change is inevitable... Change for the better is not.
March 17, 2013 at 7:34 pm
Jeff Moden (3/17/2013)
Try your solution on the following test data and let me know how long it takes.
Test data?
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply