Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
How to find a record change and raise a flag in T-SQL
Hi Guys,
I have got a master table which contains the unique records, just like below
I have got a child table as below
JobNo BookingStatusDescription WebSupplierStatusID WebSupplierStatusDescripti
4363267 Amended 2 Assigned 2 4969
4363267 Confirmed 3 Rejected 1 5202
4363267 Amended 4 Accepted 4 4969
4363267 Amended 5 Error 3 4969
WIth in my T-SQL I want to figure out that if the supplier ID has been changed then raise some sort of Flag of 0 or 1. I don't want to use inline UDF as the data contains hundred of thousands of rows.
How do i achieve this. Please note i am using SQL Server 2014
regards
I have got a master table which contains the unique records, just like below
JobNo BookingStatusID
4363267 2
I have got a child table as below
JobNo BookingStatusDescription WebSupplierStatusID WebSupplierStatusDescripti
4363267 Amended 2 Assigned 2 4969
4363267 Confirmed 3 Rejected 1 5202
4363267 Amended 4 Accepted 4 4969
4363267 Amended 5 Error 3 4969
WIth in my T-SQL I want to figure out that if the supplier ID has been changed then raise some sort of Flag of 0 or 1. I don't want to use inline UDF as the data contains hundred of thousands of rows.
How do i achieve this. Please note i am using SQL Server 2014
regards
Asked:
-
5
4
- +2
1 Solution
Use a SQL trigger that fires on update to that table and you can code it to do something you want when the supplier field value changes.
I mean in my query if it detects that supplier has changed then i add an extra column if say AmmendedSupplier to True or False.
Well i am not allowed to write any triggers as this is live database. I want to achieve by querying and using TSQL?
Well i am not allowed to write any triggers as this is live database. I want to achieve by querying and using TSQL?
you can try this:
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.jobNo),
p.*
FROM yourTable p
)
SELECT
CTE.JobNo
FROM CTE
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
where nex.SupplierID <> CTE.SupplierID
Group By CTE.JobNo
Order By CTE.JobNo
Hi Ryan,
Thanks a lot for your help. However it is showing those job numbers where the supplierId has not changed. I need those job numbers where SupplierId has been changed
regards
Thanks a lot for your help. However it is showing those job numbers where the supplierId has not changed. I need those job numbers where SupplierId has been changed
regards
>>However it is showing those job numbers where the supplierId has not changed. I need those job numbers where SupplierId has been changed
it was tested before post so it should work just fine.
anyway, you can try this as well:
it was tested before post so it should work just fine.
anyway, you can try this as well:
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.jobNo),
p.*
FROM yourTable p
)
select a.*, case when b.JobNo is null then 0 else 1 end AmmendedSupplier
from
yourTable a
left join (
SELECT
CTE.JobNo
FROM CTE
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
where nex.SupplierID <> CTE.SupplierID
Group By CTE.JobNo
) b
on a.JobNo = b.JobNo
Hi Ryan,
I really appreciate your help. I have got almost 5 millions records and have tested with few records and am afraid it is not showing the correct data. Please find the images of the two test jobnos below Where it is showing as Ammended supplier but in data supplier id is same
I really appreciate your help. I have got almost 5 millions records and have tested with few records and am afraid it is not showing the correct data. Please find the images of the two test jobnos below Where it is showing as Ammended supplier but in data supplier id is same
check this.
declare @table table (JobNo int,
BookingStatusDescription varchar(100),
WebSupplierStatusID int,
WebSupplierStatusDescription varchar(100),
RevisionNo int,
SupplierID int)
insert @table values (4363267, 'Amended', 2, 'Assigned', 2, 4969),
(4363267, 'Confirmed', 3, 'Rejected', 1, 5202),
(4363267, 'Amended', 4, 'Accepted', 4, 4969),
(4363267, 'Amended', 5, 'Error', 3, 4969)
;with cte as (
select *,lag(SupplierID) over (order by WebSupplierStatusID) prev_SupplierID
from @table)
select *,case when SupplierID <> coalesce(prev_SupplierID, SupplierID) then 0 else 1 end Flag
from cte
/*
JobNo BookingStatusDescription WebSupplierStatusID WebSupplierStatusDescription RevisionNo SupplierID prev_SupplierID Flag
4363267 Amended 2 Assigned 2 4969 NULL 1
4363267 Confirmed 3 Rejected 1 5202 4969 0
4363267 Amended 4 Accepted 4 4969 5202 0
4363267 Amended 5 Error 3 4969 4969 1
*/
@shah36
try this instead:
try this instead:
WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (partition by p.jobNo ORDER BY p.jobNo),
p.*
FROM BookingRevision p
)
select a.*, case when b.JobNo is null then 0 else 1 end AmmendedSupplier
from
BookingRevision a
left join (
select
cte.jobNo from CTE
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1 and cte.jobNo = nex.jobNo
where nex.SupplierID is not null
and nex.SupplierID <> cte.SupplierID
group by cte.jobNo
) b
on a.JobNo = b.JobNo
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
You can use a TRIGGER do detect that the record is updated, and specifically check if the supplier column has changed.