November 2, 2021 at 12:45 am
I have a table structure such that each record has a lookback to a previous record. In my below example, I want to pull just the previous record where the Type is 'F' as well, but I'm stuggling with how to JOIN the 2 datasets:
create table #temp_Lookback (
Id bigint null,
[Type] nvarchar(255) null,
ClientName nvarchar(255) null,
CompletedOn datetime null,
CustName nvarchar(255) null,
CustId nvarchar(255) null,
PreviouslyCompletedId bigint null )
INSERT INTO #temp_Lookback VALUES (25, 'F', 'FirstData', '2021-10-31 17:35:34', 'Main Shop', '12345A', NULL)
INSERT INTO #temp_Lookback VALUES (26, 'S', 'FirstData', '2021-09-22 12:53:11', 'Main Shop', '12345A', 25)
INSERT INTO #temp_Lookback VALUES (27, 'F', 'FirstData', '2021-08-11 08:19:44', 'Main Shop', '12345A', 26)
select
t2.*
FROM (
select
a.Id,
a.CustName,
MAX(a.CompletedOn) as LastAudit
from
#temp_Lookback a
where
a.ClientName = 'FirstData'
and
a.Type = 'F'
GROUP BY a.Id, a.CustName) t1
INNER JOIN #temp_Lookback t2 ON t1.Id = t2.PreviouslyCompletedIddrop table #temp_Lookback
November 2, 2021 at 4:26 am
Something like this?
--return the previous record where type is F as well.
SELECT tl1.ID, tl1.[type] as ClientType, tl1.ClientName, tl1.completedOn, tl1.CustName, tl1.CustID, tl1.PreviouslyCompletedID,
prev.id, prev.clienttype, prev.clientname, prev.CompletedOn
FROM #temp_Lookback tl1
CROSS APPLY (
SELECT TOP 1 ID, [type] as clientType, ClientName, completedOn, CustName, CustID, PreviouslyCompletedID
FROM #temp_Lookback tl2
WHERE [type] = 'F'
AND tl2.ID = tl1.ID
) prev;
November 2, 2021 at 3:29 pm
If I understand correctly, you just need to add the condition on the JOIN:
...previous_part_of_query_same_as_before...
INNER JOIN #temp_Lookback t2 ON t1.Id = t2.PreviouslyCompletedId AND t2.Type = 'F'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 2, 2021 at 4:25 pm
Edited to rephrase my question:
What if there were two 'F' rows preceding the 'S' row?
Do you only want data from the most recent 'F' row, prior to the 'S' row?
Will you always be looking for a 'S' row preceded by an 'F' ? Or could there be other types not shown in your example?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 2, 2021 at 4:29 pm
I ask because you can get a fairly efficient solution to the example above using the LAG() function instead of a join. But this assumes that there are no other rows between the last 'F' and the row that refers back to it.
;with cte as (select * from #temp_Lookback where type in ('F','S'))
,cte2 as (select *, PrevID = lag(ID) over(order by ID)
, PrevType = lag([Type]) over(order by ID)
, PrevClient = lag([ClientName]) over(order by ID)
, PrevCompleted = lag([CompletedOn]) over(order by ID)
, PrevCustName = lag([CustName]) over(order by ID)
, PrevCustID = lag([CustID]) over(order by ID)
from cte
)
select *
from cte2
where Prevtype = 'F' and PrevID = PreviouslyCompletedID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 5, 2021 at 9:02 pm
>> I have a table structure such that each record [sic: row are not records] has a lookback to a previous record [sic]. <<
Since the table is an unordered set, the concept of a previous record doesn’t apply in SQL. Following Dr. Codd’s information principal, you have to have a way of creating this relationship. This means there should be a column that gives you the ordering. Your DDL is a real mess because everything is NULL-able. Remember that first week of RDBMS classes? By definition, a table must have a key; by definition, a key cannot be null. What you’ve given us is something that can never be a table; this is a deck of punch cards from the 1960s.
Why do you think that the same data changes its very nature and requires a separate table because of some temporal component? Your real problem is the DDL that you don’t know how to write. You’ll find that 80 to 90% of the work and SQL is done in the DDL, and when it’s right, DML is usually simpler. So let’s try rewriting what you’ve got
First of all, there are no such things as generic column names. A column is a particular attribute so stuff like “id” makes no sense in RDBMS. Following ISO 11179 and the laws of logic (the law of identity in particular). It has to be “<something>_id“ ad not a generic Kabbalah number. Likewise, there is no such thing as a generic “type” in RDBMS.
By definition identifiers are on a nominal scale; this means they can’t be numerics because you don’t any computations with them. I see you have the love of improper datatypes that newbies have, and love to make them much too big for any practical purpose. Do you really need 255 Chinese characters to encode a type? Please post an example; I’ve never seen one. Here’s my guess at what your rough table should have looked like
CREATE TABLE Something_Tasks
(foo_type CHAR (255) NOT NULL
CHECK (foo_type IN (.’S’, ‘F’, ...)), – Types also nominal scale; list them
client_name NVARCHAR(25) NOT NULL, – Following international postal standards for names
completion_date DATE NOT NULL, – Do you really need minutes and seconds? We have a date data type
customer_name NVARCHAR(25) NOT NULL,
customer_id CHAR(16) NOT NULL, – Credit card number length
PRIMARY KEY (???),
fake_pointer ???? NOT NULL);
It also looks like this non-table is Is not even close to being normalized. You combine customers and clients, which should be entities, as if they are attributes. Have you ever read a book that had a chapter on normalization?
The reason you are using big integers is because they look like pointers. But we don’t use pointers in RDBMS. You’re using SQL to write assembly language. It’s a very common design error made by new SQL programmers. It’s so bad it’s got a name; attributes splitting. If you have a completion date, where is the corresponding initiation date?
Did you know that the values clause of an insertion statement can take a table constructor? This lets the optimizer do things. But if you’re going to do insertions, one punch card at a timeiInstead of as a set, then at least terminate each row by row statement with a semicolon.
CREATE TABLE Something_Tasks
(task_type CHAR (255) NOT NULL
CHECK (task_type IN (.’S’, ‘F’, ...)),
client_name NVARCHAR(25) NOT NULL,
task_initiation_date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
task_completion_date DATE, – null means still in process
customer_id CHAR(16) NOT NULL REFERENCES Customers,
PRIMARY KEY (client_name, task_initiation_date));
Please notice the use of reference clauses, check constraints and primary keys. This is one of the differences between a table and a deck of punch cards. You will also want to spend some time completing this table:
CREATE TABLE Customers
(customer_name NVARCHAR(25) NOT NULL,
customer_id CHAR(16) NOT NULL PRIMARY KEY);
When I accuse you of thinking and punchcards, you can see it in the way you lay out your code. Data elements that should be related as a set (in SQL learn to think in sets!) are split onto separate lines of text. An SQL programmer would tend to put related data elements on the same lines of the be seen as a set, for example, the city state and ZIP Code would not be put on separate lines, but in one line of program text because there always used as a unit. I am old enough, that I remember punchcards. The reason we did our code that way. Is it punchcards were only 80 columns wide and we like to rearrange and reuse the deck. Ever since about 1970. If you want to format your code. All you have to do is push a button on the pretty printer package and the machine will do it for you.
If you want to make sure that you have no gaps between task intervals for a customer, then you use some coding tricks from Kuznetsov. You can Google this as “contiguous dates” in some of my older postings.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 6, 2021 at 4:59 am
fake_pointer ???? NOT NULL);
The reason you are using big integers is because they look like pointers. But we don’t use pointers in RDBMS. You’re using SQL to write assembly language. It’s a very common design error made by new SQL programmers. It’s so bad it’s got a name; attributes splitting. If you have a completion date, where is the corresponding initiation date?
...
When I accuse you of thinking and punchcards, you can see it in the way you lay out your code.
No and no. Most people have never used pointers nor punch cards. This isn't 1980 or even 1990. Rather, yhey are using bigint because they believe over time the total number of key values may exceed the size of an int. It's that simple. Yes, here they are chaining rows, but it's not really related to pointers in the sense they were used decades ago.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 6, 2021 at 7:26 pm
Since the table is an unordered set, the concept of a previous record doesn’t apply in SQL.
That's not actually true but, even if it was, the logical selection establishes the order.
You really should take a course in modern SQL, Joe. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply