Solved

selecting with same time date stamp

Posted on 2014-12-09
5
92 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 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 49

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

733 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