Solved

selecting with same time date stamp

Posted on 2014-12-09
5
93 Views
Last Modified: 2014-12-10
I have a sql script shown below. The script will set all records (Except the very first one in the query) to a value of 'I###'
if the value contains the characters 'IAEE'. That is  exactly what I want the script to do.
However the script will fail if the very first record (Earliest time date stamp) and another record with 'IAEE' has the same time date
stamp. How can I fix the query so that it will work if two records have the same time date stamp (Namely if two records have the
same date and the earliest time date stamp).
I have scripted that database table and it is included as an attachement.

The query works fine unless their are two records and they both have the very same date. Is their a way to correct the query so that
t
select * from Goop
 
 update t
   set code = 'I###'
   from Goop t
   where t.DateEntered >= ( select min(x.DateEntered) from Goop x  WHERE SUBSTRING(Code,1,1) = 'I') AND SUBSTRING(Code,1,1) = 'I'
tz.txt
0
Comment
Question by:brgdotnet
[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
  • 3
5 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 450 total points
ID: 40490578
Derived from an approach already provided by angeliii in a former question
;WITH DATA
AS (
      SELECT
            ssn
          , code
          , ROW_NUMBER() OVER (PARTITION BY code ORDER BY ssn ASC) RN
      FROM yourtable
      WHERE code = '####'
   )
UPDATE DATA
SET code = 'I###'
WHERE rn = 1

Open in new window

The advantage of row_number() is that it can ONLY supply the value 1 once within each PARTITION, so even if there are multiple records sharing a SSN only one of these will be chosen.
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 50 total points
ID: 40491244
Why are you working with SUBSTRING? Like this won't work?
update t
    set code = 'I###'
    from Goop t
    where t.DateEntered >= ( select min(x.DateEntered) from Goop WHERE Code = 'I###')
       AND Code = 'IAEE' 

Open in new window

0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40491246
The solution you proposed does not work?
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40491248
Thanks Victor I believe yours works
0
 
LVL 2

Author Closing Comment

by:brgdotnet
ID: 40493053
The first solution works. And the second one by Vitor does not. Thanks anyway guys. The solution wasn't quite was I was looking for, I think I might have to rephrase the question.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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