none
Query Tweak

    Question

  • Below is the current query I have that I am looking to tweak.  Below that are some results.  A customer code could have multiple cards.  In this query if the customer is new they will only have one card listed.  If they already have an existing card it will list the oldest existing active card first. 

    How can I add another column to have one line per customer code?  If they already have an existing card I would like to put it in the new column.  If they don't and they are new, then I just want the second column to be NULL.  We can eliminate the creation date field.  Hope this makes sense.

    declare @LastDay datetime;
    set @LastDay= DateAdd(DD, -1, GETDATE());

    with
    TabCustLD as (
    select customer_code
      from Customer_IDs
      where ID_Code = 'PS'
            and Creation_Date >= @LastDay
    ),
    TabCustSeq as (
    SELECT id_number, Creation_Date, customer_code,
           SeqF= row_number() over (partition by customer_code order by Creation_Date),
           SeqL= row_number() over (partition by customer_code order by Creation_Date desc)
      from Customer_IDs
      where customer_code in (SELECT customer_code from TabCustLD)
           and ID_Code = 'PS' and Positive_ID = 1
    )
    SELECT customer_code, id_number, Creation_Date
      from TabCustSeq
      where SeqF = 1 or SeqL = 1
      order by customer_code, Creation_Date;

    customer_code                        id_number                                                          Creation_Date
    ------------------------------------ ------------------------------------------------------------------ -----------------------
    0014bd3f-33cd-4951-988a-18bd6ec93535 627755xxxxxx1801                                                   2014-08-13 04:16:00.000
    00489510-f81d-440d-be41-2c5c67d435a3 627755xxxxxx9521                                                   2014-08-12 18:34:41.253
    00593f5f-2e24-4387-993e-2e4a495ae1a1 627755xxxxxx8951                                                   2014-08-12 16:01:44.983
    00a98cee-76c4-469f-aecc-1199439389fb 627755xxxxxx2047                                                   2014-08-12 21:32:00.760
    00b1fccf-89bf-447e-90f3-9710861c25d6 627755xxxxxx9124                                                   2012-12-11 05:58:46.553
    00b1fccf-89bf-447e-90f3-9710861c25d6 627755xxxxxx4606                                                   2014-08-13 05:08:56.157

    Wednesday, August 13, 2014 7:26 PM

All replies

  • Sounds like this

    declare @LastDay datetime;
    set @LastDay= DateAdd(DD, -1, GETDATE());
    
    with 
    TabCustLD as (
    select customer_code
      from Customer_IDs
      where ID_Code = 'PS'
            and Creation_Date >= @LastDay
    ),
    TabCustSeq as (
    SELECT id_number, Creation_Date, customer_code,
           SeqF= row_number() over (partition by customer_code order by Creation_Date),
           SeqL= row_number() over (partition by customer_code order by Creation_Date desc)
      from Customer_IDs
      where customer_code in (SELECT customer_code from TabCustLD)
           and ID_Code = 'PS' and Positive_ID = 1
    )
    SELECT customer_code,
    MAX(CASE WHEN SeqF = 1 THEN id_number END) AS IDNumber1,
    MAX(CASE WHEN SeqL = 1 THEN id_number END) AS IDNumber2
      from TabCustSeq
      where SeqF = 1 or SeqL = 1
    GROUP BY custoner_code
      order by customer_code;


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

    Wednesday, August 13, 2014 7:40 PM
  • This is now putting it on one line.

    It still isn't putting a NULL on IDNumber2 if there is only one card.  Also the last entry the cards should be flip flopped.  The card ending in 4606 is the new card so it should be listed first the 9124 in IDNumber2.  You can see that in the original post.

    customer_code                        IDNumber1                                                          IDNumber2
    0014bd3f-33cd-4951-988a-18bd6ec93535 627755xxxxxx1801                     627755xxxxxx1801
    00489510-f81d-440d-be41-2c5c67d435a3 627755xxxxxx9521                     627755xxxxxx9521
    00593f5f-2e24-4387-993e-2e4a495ae1a1 627755xxxxxx8951                     627755xxxxxx8951
    00a98cee-76c4-469f-aecc-1199439389fb 627755xxxxxx2047                       627755xxxxxx2047
    00b1fccf-89bf-447e-90f3-9710861c25d6 627755xxxxxx9124                         627755xxxxxx4606

    Wednesday, August 13, 2014 7:57 PM
  • Bumping this thread to see if I get an answer
    Thursday, August 14, 2014 1:06 PM
  • Can you provide your table DDL and some sample data (data inserts...) and your expect result? Thanks.
    Thursday, August 14, 2014 1:44 PM
    Moderator
  • WITH cte AS(
        SELECT [customer_code]
              ,[CardID]
              ,ROW_NUMBER() OVER (PARTITION BY customer_code ORDER BY [CreationDate]) as RowNumber
        FROM [dbo].[test]
    )
    SELECT
        cte.[customer_code]
        ,cte.[CardID]
        ,c2.CardID as CardID2
    FROM cte
    LEFT JOIN cte c2 ON c2.customer_code=cte.customer_code AND c2.RowNumber=2
    WHERE cte.RowNumber=1


    RowNumber=1 is your first card, RowNumber=2 is the card after, sorting by CreationDate

    Result:

    customer_code CardID CardID2
    0014bd3f-33cd-4951-988a-18bd6ec93535 627755xxxxxx1801 NULL
    00489510-f81d-440d-be41-2c5c67d435a3 627755xxxxxx9521 NULL
    00593f5f-2e24-4387-993e-2e4a495ae1a1 627755xxxxxx8951 NULL
    00a98cee-76c4-469f-aecc-1199439389fb 627755xxxxxx2047 NULL
    00b1fccf-89bf-447e-90f3-9710861c25d6 627755xxxxxx9124 627755xxxxxx4606



    Thursday, August 14, 2014 2:42 PM
  • I'm not sure how I take from above and add that to the existing query.  The first solution worked except it didn't have NULLS in the second column and the older card was showing first.
    Thursday, August 14, 2014 8:12 PM
  • If you want the latest first :

    ROW_NUMBER() OVER (PARTITION BY customer_code ORDER BY [CreationDate] DESC) as RowNumber

    Thursday, August 14, 2014 8:19 PM
  • Can someone take the query below that someone suggested on the second post. 

    It still isn't putting a NULL on IDNumber2 if there is only one card.  Also the last entry the cards should be flip flopped.  The card ending in 4606 is the new card so it should be listed first the 9124 in IDNumber2.  You can see that in the original post.  I'd like to use this query since it is so close to giving me what I need.  Thanks for your help.

    customer_code                        IDNumber1                                                          IDNumber2
    0014bd3f-33cd-4951-988a-18bd6ec93535 627755xxxxxx1801                     627755xxxxxx1801
    00489510-f81d-440d-be41-2c5c67d435a3 627755xxxxxx9521                     627755xxxxxx9521
    00593f5f-2e24-4387-993e-2e4a495ae1a1 627755xxxxxx8951                     627755xxxxxx8951
    00a98cee-76c4-469f-aecc-1199439389fb 627755xxxxxx2047                       627755xxxxxx2047
    00b1fccf-89bf-447e-90f3-9710861c25d6 627755xxxxxx9124                         627755xxxxxx4606

    declare @LastDay datetime;
    set @LastDay= DateAdd(DD, -1, GETDATE());
    
    with 
    TabCustLD as (
    select customer_code
      from Customer_IDs
      where ID_Code = 'PS'
            and Creation_Date >= @LastDay
    ),
    TabCustSeq as (
    SELECT id_number, Creation_Date, customer_code,
           SeqF= row_number() over (partition by customer_code order by Creation_Date),
           SeqL= row_number() over (partition by customer_code order by Creation_Date desc)
      from Customer_IDs
      where customer_code in (SELECT customer_code from TabCustLD)
           and ID_Code = 'PS' and Positive_ID = 1
    )
    SELECT customer_code,
    MAX(CASE WHEN SeqF = 1 THEN id_number END) AS IDNumber1,
    MAX(CASE WHEN SeqL = 1 THEN id_number END) AS IDNumber2
      from TabCustSeq
      where SeqF = 1 or SeqL = 1
    GROUP BY custoner_code
      order by customer_code;
    Friday, August 15, 2014 6:12 PM
  • Sorry to keep asking this but can someone help tweak the query in my previous post to the desired results.  Thanks.
    Monday, August 18, 2014 3:29 PM
  • Sorry to keep asking this but can someone help tweak the query in my previous post to the desired results.  Thanks.

    Sorry came across this thread only now

    here you go with the tweak

    see if this works for you

    declare @LastDay datetime;
    set @LastDay= DateAdd(DD, -1, GETDATE());
    
    with 
    TabCustLD as (
    select customer_code
      from Customer_IDs
      where ID_Code = 'PS'
            and Creation_Date >= @LastDay
    ),
    TabCustSeq as (
    SELECT id_number, Creation_Date, customer_code,
           SeqF= row_number() over (partition by customer_code order by CASE WHEN id_number LIKE '%4606' THEN 1 ELSE 2 END,Creation_Date),
           SeqL= row_number() over (partition by customer_code order by CASE WHEN id_number LIKE '%4606' THEN 1 ELSE 2 END DESC,Creation_Date desc)
      from Customer_IDs
      where customer_code in (SELECT customer_code from TabCustLD)
           and ID_Code = 'PS' and Positive_ID = 1
    )
    SELECT customer_code,
    MAX(CASE WHEN SeqF = 1 THEN id_number END) AS IDNumber1,
    NULLIF(MAX(CASE WHEN SeqL = 1 THEN id_number END),MAX(CASE WHEN SeqF = 1 THEN id_number END)) AS IDNumber2
      from TabCustSeq
      where SeqF = 1 or SeqL = 1
    GROUP BY custoner_code
      order by customer_code;


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

    Monday, August 18, 2014 3:56 PM
  • I am getting the NULLS now which is good.  The only thing left is id_number1 should have the newest card and id_number2 should have the second card if they have two.  Those numbers just need flipped.  Thanks.
    Monday, August 18, 2014 5:21 PM
  • I guess I should explain it a little better.  The id_number1 should contain the most recent card according to creation_date.  Id_number2 should contain a card if the customer_code has another card and it would be the oldest by creation_date.  If not then NULL.
    Monday, August 18, 2014 6:32 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You have no idea, do you? Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Can you explain why you are rude? Why did you fail to read the forum rules before posting? 

    What little you did post is garbage. Can fire the moron who used GUIds?? 

    >> A customer code could have multiple cards. In this query if the customer is new they will only have one card listed. If they already have an existing card it will list the oldest existing active card first. << 

    Card? Where did you define this card? 

    The term “customer_code” is not a customer identifier. Again, rude person, where is the DDL? Your vague narrative implies a 1NF violation! 

    >> How can I add another column to have one line per customer code? If they already have an existing card I would like to put it in the new column. If they don't and they are new, then I just want the second column to be NULL. We can eliminate the creation date field [sic].<<

    >> Hope this makes sense. <<

    No. A column is not a field. Putting that silly “tab-” prefix is called a “tibble” and we laugh at this designs error. There is no such crap as an “id_number” or “id_code”; you can have a “<something>_id”, or “<something>_code”. You do not know about the DATE data type, either. We do not use getdate(); we have CURRENT_TIMESTAMP and have for many years. 

    Look at this code! Why did you create a local variable?? We do not use local variables in declarative languages. This is not just not knowing SQL; you do not know how to program. 

    DECLARE @lastday DATE;
    SET @lastday= DATEADD(DD, -1, CURRENT_TIMESTAMP);

    We would never have a table of Customer_Ids. This is an attribute, not an entity or a relationship. It is a column by its nature. 

    We do not put creation dates in tables. That is audit data and you would destroy when that row is deleted. 

    You do not know what a SEQUENCE is; read a book on SQL and learn hw that schema object works. 

    I had to laugh at “positive_id”; this says a “positive” is an entity which can be distinguished from all other “positives” in the data model

    SQL programmers use shorthand, but you do not know the language so you speak it like a foreigner. For example: 

    foobar_seq = 1 OR loony_seq = 1
    would be
    1 IN (foobar_seq, loony_seq)

    Nothing here is right. Here is a guess at a skeleton for a valid schema: 

    CREATE TABLE Customers
    (customer_id CHAR(16) NOT NULL PRIMARY KEY,
     ..);

    CREATE TABLE Credit_cards
    (customer_id CHAR(16) NOT NULL
      REFERENCES Customers (customer_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE, 
     credit_card_nbr CHAR(16) NOT NULL,
     issue_date DATE NOT NULL,
     termination_date DATE, 
     CHECK (issue_date < termination_date),
     PRIMARY KEY (customer_id, credit_card_nbr),
     ..);

     Want to try again and follow the forum rules next time?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, August 18, 2014 6:40 PM
  • This is now putting it on one line.

     Why do you want to destroy First Normal Form? The poster has no idea what he is doing. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, August 18, 2014 6:42 PM