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

Obtaining Before & After Record & Missing Record. Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 6:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 12:22 PM
Points: 24, Visits: 105
I have a database that has two tables. On table is a date table that has all of the possible dates for a year within it. Within this table is a field ("concatenate") that contains a varchar data type specification that contains the date in the following format 201201010510. This represents the years, months, days, hours and minutes. This table is joined to a data table that contains the same varchar field named "concatenate". How do I query the data table to obtain the missing dates as well as the first prior matched date and the following matched date. Listed below is the format of the data.

Record Concatenate
1 201201010510
2 201201010511
3 201201010514
4 201201010515
5 201201010517

The query would report
201201010511
201201010512
201201010513
201201010514

201201010515
201201010516
201201010517
Post #1431854
Posted Monday, March 18, 2013 4:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
I would imagine your going to need to use the substring function as well as datepart function and possibly come CAST/COnvert functions in the mix. If you can supply a script that exposes mocked up data i will gladly show you how


thanks.
Post #1432375
Posted Tuesday, March 19, 2013 2:22 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:41 PM
Points: 544, Visits: 1,053
Unless I'm misunderstanding what you want it sounds like you want to use a left join..

Something like..

select t1.concatenate, t2.concatenate 
from table1 t1
left join table2 t2
on t1.concatenate = t2.concatenate

Maybe? You're trying to find nulls on a match so that's the first thing I think of.
Post #1432896
Posted Tuesday, March 19, 2013 6:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 12:22 PM
Points: 24, Visits: 105
Erin,

Thank you for your response. How do you chose the record before and after the record that is selected with this algorithm?

Mike
Post #1432959
Posted Wednesday, March 20, 2013 3:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 6,748, Visits: 13,885
-- make some sample data
DROP TABLE #Sample
CREATE TABLE #Sample (Record INT, [Concatenate] CHAR(12))
INSERT INTO #Sample (Record, [Concatenate])
SELECT 1, '201201010510' UNION ALL
SELECT 2, '201201010511' UNION ALL
SELECT 3, '201201010514' UNION ALL
SELECT 4, '201201010515' UNION ALL
SELECT 5, '201201010517';

CREATE UNIQUE CLUSTERED INDEX ucx_Everything ON #Sample ([Concatenate], Record);


--------------------------
-- construct an inline tally table (Jeff Moden et al). This source of rows will be used
-- to fill in the missing rows in the data
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
iTally(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
--------------------------
-- Find the rows at the beginning and end of each gap
GapBounds AS (
SELECT
StartDate = CAST(s.[Concatenate] AS BIGINT),
EndDate = x.iConcatenate
FROM #Sample s -- CI scan
CROSS APPLY ( -- CI seek
SELECT TOP 1
iConcatenate = CAST(i.[Concatenate] AS BIGINT)
FROM #Sample i
WHERE i.[Concatenate] > s.[Concatenate]
ORDER BY i.[Concatenate]) x
WHERE x.iConcatenate - CAST(s.[Concatenate] AS BIGINT) > 1
)
---------------------------
-- Construct the missing rows
-- Start and End date from GapBounds tells us how many rows to take from iTally
-- Add 0-based sequence to Startdate to obtain the date for the row
SELECT CAST(StartDate + gaprows.rn AS CHAR(12))
FROM GapBounds g
CROSS APPLY (
SELECT TOP (EndDate+1-StartDate)
rn = ROW_NUMBER() OVER(ORDER BY StartDate)-1
FROM iTally
) gaprows



“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 #1433082
Posted Saturday, March 30, 2013 11:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 12:22 PM
Points: 24, Visits: 105
ChrisM

Thank you for the solution. I will give it a try.
Post #1437271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse