Solved

selecting with same time date stamp

Posted on 2014-12-09
5
89 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
  • 3
5 Comments
 
LVL 48

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 46

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now