Solved

How to find a record change and raise a flag in T-SQL

Posted on 2016-09-08
12
55 Views
Last Modified: 2016-09-09
Hi Guys,

I have got a master table which contains the unique records, just like below

JobNo	   BookingStatusID
4363267	   2

Open in new window


I have got a child table as below
JobNo      BookingStatusDescription      WebSupplierStatusID      WebSupplierStatusDescription      RevisionNo      SupplierID
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
0
Comment
Question by:shah36
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 6

Expert Comment

by:pritaeas
ID: 41789261
What do you mean by raising a flag?

You can use a TRIGGER do detect that the record is updated, and specifically check if the supplier column has changed.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41789262
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.
0
 

Author Comment

by:shah36
ID: 41789264
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?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41789268
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

Open in new window

script above detect the change of SupplierID in your records in a particular JobNo and show the JobNo accordingly.
0
 

Author Comment

by:shah36
ID: 41789270
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
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41789273
>>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:
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

Open in new window

0
 

Author Comment

by:shah36
ID: 41789283
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
JobNos showing that supplier has been changed but its not the case
0
 

Author Comment

by:shah36
ID: 41789286
Sorry just forgot to attach the query confirming directly from the table
Suppliers confirming from the table
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41789661
let me try to diagnose the issue... will get back to you tomorrow when I go back to office.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41789947
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
*/

Open in new window

0
 
LVL 52

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41790677
@shah36

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

Open in new window

0
 

Author Comment

by:shah36
ID: 41791287
@Ryan Chong. Thanks a lot it worked as expected. Thanks again you are a champ.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
View SQL database for NPS accounting 4 25
Access PS SQLSERVER from powershell 1 30
SQL - Subquery in WHERE section 4 34
learning MS SSIS 13 27
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question