locked
Query RRS feed

  • 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())

    Wednesday, June 25, 2014 7:14 PM

Answers

All replies

  • 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








    • Proposed as answer by Naomi N Monday, June 30, 2014 4:02 PM
    • Marked as answer by Fanny Liu Monday, July 7, 2014 6:57 AM
    Wednesday, June 25, 2014 7:29 PM
  •  
    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
    Wednesday, June 25, 2014 7:42 PM
  • Deleted
    Thursday, June 26, 2014 12:31 AM
  • 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


    • Edited by Visakh16MVP Thursday, June 26, 2014 1:46 AM
    Thursday, June 26, 2014 1:45 AM
  • 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. 

    Thursday, June 26, 2014 3:49 PM
  • 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)

    Thursday, June 26, 2014 4:49 PM
  • Deleted
    Thursday, June 26, 2014 8:42 PM
  • Creation_date for an example is formatted 2014-06-25 11:47:34.963.
    Friday, June 27, 2014 1:48 PM
  • Deleted
    Sunday, June 29, 2014 11:27 PM
  • 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








    Monday, June 30, 2014 1:52 AM
  • 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

    Monday, June 30, 2014 2:14 AM
  • 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

    • Marked as answer by Fanny Liu Monday, July 7, 2014 6:57 AM
    Monday, June 30, 2014 4:06 PM