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
Solved

selecting with same time date stamp

Posted on 2014-12-09
5
91 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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server / Update DB? 22 36
SSMS Opening Mode 9 18
MS SQL Server select from Sub Table 14 23
SQL - Curser to do an insert based on a select 2 10
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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