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


Obtaining Before & After Record & Missing Record.


Obtaining Before & After Record & Missing Record.

Author
Message
mcertini
mcertini
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 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
raym85
raym85
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 198
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
Erin Ramsay
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2139 Visits: 1119
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
mcertini
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41345 Visits: 20003
-- 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
mcertini
mcertini
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 105
ChrisM

Thank you for the solution. I will give it a try.
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