Query

Question

Below I have the following query which will show all id_number that have a creation date greater than the last day.  I'm looking to tweak the query as it sits looking back at the last day.  It is possible to have multiple id_number under one customer_code.  How can I rewrite this to show the oldest id_number by creation date if the person has multiple id_number?  Can the prior card be listed first?  If they don't have any then just list the new single id_number.

select id_number, Creation_Date, customer_code from Customer_IDs (NOLOCK)

where
ID_Code = 'PS'
and
creation_date >= DateAdd(DD, -1, GETDATE())

Answer 1

You can use ROW_NUMBER() OVER PARTITION BY construct:

http://www.sqlusa.com/bestpractices2005/overpartitionby/

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 2

 
select c1.id_number, c1.customer_code, c1.creation_date from Customer_IDs c1 
inner join
(
select min(id_number) idnumber, customer_code from Customer_IDs (NOLOCK)
where
ID_Code = 'PS'
and
creation_date >= DateAdd(DD, -1, GETDATE())
group by customer_code
)C2 on c1.id_number=c2.idnumber and c1.customer_code=c2.customer_code

--Prashanth

Answer 3

Deleted

Answer 4

select id_number, Creation_Date, customer_code from Customer_IDs (NOLOCK) c
where ID_Code = 'PS'
and creation_date >= DateAdd(DD, -1, GETDATE())
and not exists (select 1
from Customer_IDs 
where customer_code = c.customer_code 
and creation_date >= DateAdd(DD, -1, GETDATE())
and creation_date < c.creation_date 
)

I assume you'e aware of the issues that can cause due to usage of NOLOCK hint. Do you really want it here?

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 5

Thank you for the responses.  I should have worded the question below better.

How can I rewrite this to show the oldest id_number by creation date if the person has multiple id_number?

I still want to show the new id_number that was added within the last day and I want to show the older id_number first if they have one.  If not just list the new id_number.  In the query results, some customer_code could be listed twice with two id_number. 

Answer 6

Is this what you where looking to do?

declare @customerIDs table (ID bigint, creationDate datetime, customerCode bigint)
 insert into @customerIDs (ID, creationDate, customerCode)
 values 
  (3, CURRENT_TIMESTAMP-5, 1),
  (2, CURRENT_TIMESTAMP-2, 1),
  (1, CURRENT_TIMESTAMP-1, 1),
  (4, CURRENT_TIMESTAMP-3, 4),
  (5, CURRENT_TIMESTAMP-2, 5),
  (6, CURRENT_TIMESTAMP-2, 2),
  (10, CURRENT_TIMESTAMP, 1),
  (11, CURRENT_TIMESTAMP, 3)

  select c1.ID, c1.creationDate, c1.customerCode, c2.ID as originalID, c2.creationDate as originalDate
    From @customerIDs c1
	  inner join @customerIDs c2
	    on c1.customerCode = c2.customerCode
		and c2.creationDate = (select min(creationDate) from @customerIDs where customerCode = c2.customerCode)

Answer 7

Deleted

Answer 8

Creation_date for an example is formatted 2014-06-25 11:47:34.963.

Answer 9

Deleted

Answer 10

Creation_date for an example is formatted 2014-06-25 11:47:34.963.

That is how it appears as a string date.  Because Microsoft programmers decided on that display format for SSMS.

While there can be hundreds of string date formats, there are only a few internal (binary) formats such as DATE, DATETIME, DATETIME2. The binary formats are same across the world. The string display format varies, annoying it may be, a frequent source of database developer productivity loss.

What is the column definition in CREATE TABLE?   DATETIME?

Datetime to string conversion:

http://www.sqlusa.com/bestpractices/datetimeconversion/

Best to store temporal data in binary format (DATETIME), not string format (varchar(20)).

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 11

select customer_code,
MAX(CASE WHEN fseq = 1 THEN id_number END) AS First_Id_Number,
MAX(CASE WHEN bseq = 1 THEN id_number END) AS Last_Id_Number
FROM
(
select id_number, Creation_Date, customer_code,
row_number() over (partition by  customer_code order by creation_date desc) as bseq,
row_number() over (partition by  customer_code order by creation_date) as fseq
from Customer_IDs (NOLOCK) c
where ID_Code = 'PS'
)t
where MAX(CASE WHEN bseq = 1 THEN id_number END) >= DateAdd(DD, -1, GETDATE())

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 12

select top (1) id_number, Creation_Date, customer_code

from Customer_IDs

WHERE ID_Code = 'PS'

AND creation_date >= DATEADD(day, -1 + datediff(day, '19000101', CURRENT_TIMESTAMP), '19000101') -- to get the start of the last date

ORDER BY id_number -- to get the oldest id_number 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles