August 30, 2018 at 1:56 pm
update tab1.ut_prc with tab2.prcup of seqnum in which amtperact fall under.
for example if you take act1003, amtperact 76 fall under seqnum=2 (i.e in between min_cst and max_cst for prd1) then tab1.ut_prc should be updated with prcup of seqnum=2 for prd1 i.e.1.7
Attached the sampe data. Please advise the options to write query for this scenario.
August 30, 2018 at 2:22 pm
It always helps to have data. I think this is what you are looking for, but you're question is not very clear and you don't specify any expected results in the excel file
drop table if exists #tab1
drop table if exists #tab2
Create table #Tab1 (
SubNum varchar(20),
ActNum varchar(20),
UnitPrice decimal(5,2))
Create table #Tab2 (
SubNum varchar(20),
ActNum varchar(20),
amtperact tinyint,
min_cst decimal(5,2),
max_cst decimal(5,2),
prcup decimal(5,2),
seqnum tinyint,
prd varchar(5))
insert into #Tab1
values
('Sub001','act1001',0.7),
('Sub002','act1001',0.7),
('Sub003','act1001',0.7),
('Sub004','act1002',0.7),
('Sub005','act1002',0.7),
('Sub006','act1003',0.7),
('Sub007','act1003',0.7),
('Sub008','act1003',0.7),
('Sub010','act1003',0.7),
('Sub011','act1003',0.7),
('Sub012','act1003',0.7)
insert into #Tab2
values
('Sub001','act1001',25,21,22,0.9,1,'prd1'),
('Sub002','act1001',25,24,27,1.1,2,'prd1'),
('Sub003','act1001',25,26,28,1.4,3,'prd1'),
('Sub004','act1002',27,21,22,0.9,1,'prd2'),
('Sub005','act1002',27,24,27,1.1,2,'prd2'),
('Sub006','act1003',76,74,75,1.5,1,'prd1'),
('Sub007','act1003',76,74,77,1.7,2,'prd1'),
('Sub008','act1003',76,77,78,1.8,3,'prd1'),
('Sub010','act1003',76,74,75,1.5,1,'prd2'),
('Sub011','act1003',76,74,77,1.7,2,'prd2'),
('Sub012','act1003',76,77,78,1.8,3,'prd2')
select * from #Tab1
begin tran
update t1
set UnitPrice = t2.prcup
from #tab1 t1
join #Tab2 t2
on t1.ActNum = t2.ActNum
where amtperact between min_cst and max_cst
select * from #Tab1
--rollback
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 30, 2018 at 2:27 pm
Please see the first link under "Helpful Links" in my signature line below for posting methods that will help us help you better and more quickly.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2018 at 2:58 pm
Thank you. But thing is not sure how to handle other two scenarios
1. if amtperact <min_cst of lowest seqnum, then UnitPrice should be updated with min_cst of lowest of seqnum(1) always.
2. if amtperact >max_cst of highest seqnum, then UnitPrice should be updated with max_cst of highest seqnum(it varies)
sample data
('Sub013','act1004',72,74,77,1.7,1,'prd2'),
('Sub014','act1004',72,77,78,1.8,2,'prd2')
('Sub013','act1004',72,78,79,1.9,3,'prd2'),
('Sub014','act1004',72,80,85,2.1,4,'prd2')
Then it should pick 1.7 from seqnum as 72 less than 74 lowest min_cst
('Sub015','act1004',87,74,77,1.7,1,'prd2'),
('Sub016','act1004',87,77,78,1.8,2,'prd2')
('Sub017','act1004',87,78,79,1.9,3,'prd2'),
('Sub018','act1004',87,80,82,2.1,4,'prd2')
Then unitprice should be updated as 2.1 as 87 is greater than 82(max_cst of highest seqnum(4)).
Please help me
September 1, 2018 at 4:37 pm
saptek9 - Saturday, September 1, 2018 2:58 PMThank you. But thing is not sure how to handle other two scenarios
1. if amtperact <min_cst of lowest seqnum, then UnitPrice should be updated with min_cst of lowest of seqnum(1) always.
2. if amtperact >max_cst of highest seqnum, then UnitPrice should be updated with max_cst of highest seqnum(it varies)sample data
('Sub013','act1004',72,74,77,1.7,1,'prd2'),
('Sub014','act1004',72,77,78,1.8,2,'prd2')
('Sub013','act1004',72,78,79,1.9,3,'prd2'),
('Sub014','act1004',72,80,85,2.1,4,'prd2')Then it should pick 1.7 from seqnum as 72 less than 74 lowest min_cst
('Sub015','act1004',87,74,77,1.7,1,'prd2'),
('Sub016','act1004',87,77,78,1.8,2,'prd2')
('Sub017','act1004',87,78,79,1.9,3,'prd2'),
('Sub018','act1004',87,80,82,2.1,4,'prd2')Then unitprice should be updated as 2.1 as 87 is greater than 82(max_cst of highest seqnum(4)).
Please help me
You seem to have ignored Jeff's request.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 3, 2018 at 5:12 am
Sorry. I forgot to attach sample data file. Please advise the steps to do following scenarios:
1. if amtperact <min_cst of lowest seqnum, then UnitPrice should be updated with min_cst of lowest of seqnum(1) always.
2. if amtperact >max_cst of highest seqnum, then UnitPrice should be updated with max_cst of highest seqnum(it varies)
sample data
('Sub013','act1004',72,74,77,1.7,1,'prd2'),
('Sub014','act1004',72,77,78,1.8,2,'prd2')
('Sub013','act1004',72,78,79,1.9,3,'prd2'),
('Sub014','act1004',72,80,85,2.1,4,'prd2')
Then it should pick 1.7 from seqnum as 72 less than 74 lowest min_cst
('Sub015','act1004',87,74,77,1.7,1,'prd2'),
('Sub016','act1004',87,77,78,1.8,2,'prd2')
('Sub017','act1004',87,78,79,1.9,3,'prd2'),
('Sub018','act1004',87,80,82,2.1,4,'prd2')
Then unitprice should be updated as 2.1 as 87 is greater than 82(max_cst of highest seqnum(4)).
Please help me
September 4, 2018 at 6:48 am
I can only presume you chose to NOT read Jeff's signature links and put the data in "readily consumable" form. That is NOT a spreadsheet, as many are unwilling to trust spreadsheets coming from the internet. When you supply the CREATE TABLE and INSERT statements for the sample data within a SQL Code block, it will be MUCH easier for folks to copy and paste into SSMS (or similar tools). Having to trust a spreadsheet entails unnecessary risk and additional effort, which is also in the unnecessary category....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply