Obtaining Before & After Record & Missing Record.

Question

Obtaining Before & After Record & Missing Record.

mcertini

Mr or Mrs. 500

Points: 550
More actions
March 15, 2013 at 6:43 pm

#275342

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

raym85 SSC Eights! Points: 938 More actions · Answer 1

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.

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 2

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.

mcertini Mr or Mrs. 500 Points: 550 More actions · Answer 3

Erin,

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

Mike

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

-- 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

), --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

mcertini Mr or Mrs. 500 Points: 550 More actions · Answer 5

ChrisM

Thank you for the solution. I will give it a try.