asked on Need help flagging duplicate records.
Can someone help me with a query. I am lost as how to write this code.
I need to set the "Active" column to a value of Zero if it's DUP_PARAM value is equal to "2" and if there are duplicate values for the TransoporationValue within the same group of records with the same SSN.
Below is an example of what I am talking about. attached is the actual database table
Before Update :
SSN Active DUP_PARAM TransoporationValue
111119077 1 2 AirPlane
111119077 1 0 AirPlane
111119077 1 3 AirPlane
888990000 1 3 Train
222334000 1 2 Car
222334000 1 13 Car
222334000 1 15 Car
222334000 1 2 Truck
After Update :
SSN Active DUP_PARAM TransoporationValue
111119077 0 2 AirPlane
111119077 1 0 AirPlane
111119077 1 3 AirPlane
888990000 1 3 Train
222334000 0 2 Car
222334000 1 13 Car
222334000 1 15 Car
222334000 1 2 Truck
Sample.txt
I need to set the "Active" column to a value of Zero if it's DUP_PARAM value is equal to "2" and if there are duplicate values for the TransoporationValue within the same group of records with the same SSN.
Below is an example of what I am talking about. attached is the actual database table
Before Update :
SSN Active DUP_PARAM TransoporationValue
111119077 1 2 AirPlane
111119077 1 0 AirPlane
111119077 1 3 AirPlane
888990000 1 3 Train
222334000 1 2 Car
222334000 1 13 Car
222334000 1 15 Car
222334000 1 2 Truck
After Update :
SSN Active DUP_PARAM TransoporationValue
111119077 0 2 AirPlane
111119077 1 0 AirPlane
111119077 1 3 AirPlane
888990000 1 3 Train
222334000 0 2 Car
222334000 1 13 Car
222334000 1 15 Car
222334000 1 2 Truck
Sample.txt
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
brgdotnet
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
Hi Anthony. I think I have it working based upon your query. I will test it out more today. Thank you.
SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
I tried your query, but I get an error message:
Oops you are right it should be group by SSN, TransportationValue
But Scott may have a point ...
Oops you are right it should be group by SSN, TransportationValue
But Scott may have a point ...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Thanks guys.
Msg 8120, Level 16, State 1, Line 6
Column 'STG.SSN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Also, I have to live with the current table structure, I don't have a choice. Reasons I can't get into, so I will spare you the details.