Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2004
    Posts
    15

    Unanswered: Need assistance with complex query

    I hate doing this, but I'm stumped on writing a particular query. It's probably a case of me just over thinking it but I haven't had a breakthrough on it yet in a few days. Time to call in some more minds.

    I have two tables (TableA and TableB). They share similar columns COL1, COL2 and COL3.

    Sample Data:
    TableA
    COL1-----COL2-----COL3----------COL4-----COL5----COL6-----COL7.....
    100------OPEN-----01-JAN-2014
    101------CLOSE----12-JAN-2014
    102------LOCK-----10-JAN-2014
    103------CLOSE----12-JAN-2014

    TableB
    COL1-----COL2-----COL3----------COL4----COL5
    100------OPEN-----01-JAN-2014
    101------OPEN-----04-JAN-2014
    101------LOCK-----05-JAN-2014
    101------CLOSE----12-JAN-2014
    102------OPEN-----08-JAN-2014
    102------LOCK-----10-JAN-2014
    103------OPEN-----11-JAN-2014
    103------CLOSE----12-JAN-2014

    Here's where it gets interesting. I want to capture all records from TableA and select records from TableB meetings this criteria:

    - If there is a LOCK record (regardless of other status')
    - If there is a CLOSE record BUT no associated LOCK record

    Output should look like this:

    COL1-----COL2-----COL3
    100------OPEN-----01-JAN-2014
    101------LOCK-----05-JAN-2014
    102------LOCK-----10-JAN-2014
    103------CLOSE----12-JAN-2014

    I don't have any issues in pulling data matching the first criteria for the LOCKED records. What I am having difficulites with is also pulling the CLOSED records that do not have an associated LOCK with them.

    Any coding advice would be greatly appreciated. If you have any further questions or need more info, please let me know.

    Thanks kindly,
    J.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    The following row (Col1 = 100) should really be returned?

    COL1-----COL2-----COL3
    100------OPEN-----01-JAN-2014

  3. #3
    Join Date
    Aug 2004
    Posts
    15
    Quote Originally Posted by imex View Post
    The following row (Col1 = 100) should really be returned?

    COL1-----COL2-----COL3
    100------OPEN-----01-JAN-2014
    yes....record 100 needs to be displayed as well.

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    with CTE_RN as
    (
        select
            COL1,
            COL2, 
            COL3,
            ROW_NUMBER() OVER(PARTITION BY COL1 
                              ORDER BY 
                                  CASE COL2 
                                      WHEN 'OPEN' THEN '3'
                                      WHEN 'LOCK' THEN '1'
                                      ELSE '2'
                                  END) AS RN
        from TableB
    )
    
    select * from CTE_RN
    WHERE RN = 1
    Hope this helps.

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I have two tables (Alphas and Betas). They share similar columns alpha_id INTEGER NOT NULL PRIMARY KEY, COL2 and COL3. <<

    The goal of databases, not just SQL, is to reduce redundancy. You are increasing it!

    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 failed). Temporal data should use ISO-8601 formats (you failed again). Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Here is my guesses, made without any help from you. First of all, your design is wrong. Time is a continuum so it has to be shown with the ISO half-open, interval model.

    Rows are not anything like records; this is a fundamental concept.

    CREATE TABLE Alphas
    (alpha_id INTEGER NOT NULL,
    start_date DATE NOT NULL,
    PRIMARY KEY (alpha_id, start_date),
    end_date DATE,
    CHECK (start_date <= end_date),
    latch_status CHAR(5) NOT NULL
    CHECK (latch_status IN ('open', 'close', 'lock')),
    ..);

    INSERT INTO Alphas
    VALUES
    (100, 'open', '2014-01-01', NULL),
    (101, 'open', '2014-01-04', '2014-01-04'),
    (101, 'lock', '2014-01-05','2014-01-11' ),
    (101, 'close', '2014-01-12', NULL),
    (102, 'open', '2014-01-08', '2014-01-09'),
    (102, 'lock', '2014-01-10', NULL),
    (103, 'open', '2014-01-11', NULL);

    Another guess is that you want a state transition constraint:

    https://www.simple-talk.com/sql/t-sq...n-constraints/

  6. #6
    Join Date
    Aug 2004
    Posts
    15
    Quote Originally Posted by Celko View Post
    >> I have two tables (Alphas and Betas). They share similar columns alpha_id INTEGER NOT NULL PRIMARY KEY, COL2 and COL3. <<

    The goal of databases, not just SQL, is to reduce redundancy. You are increasing it!

    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 failed). Temporal data should use ISO-8601 formats (you failed again). Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Here is my guesses, made without any help from you. First of all, your design is wrong. Time is a continuum so it has to be shown with the ISO half-open, interval model.

    Rows are not anything like records; this is a fundamental concept.
    My apologies but I haven't a clue as to what you are talking about.

    I thought I provided clear enough information as to what I have and what I am looking to achieve here.

    The query in itself is more complex than what I am accustomed to programming and reading your response makes it 1000 times much more.

    Didn't help one bit.

    Sry.

  7. #7
    Join Date
    Aug 2004
    Posts
    15
    Quote Originally Posted by imex View Post
    Try something like this:

    Code:
    with CTE_RN as
    (
        select
            COL1,
            COL2, 
            COL3,
            ROW_NUMBER() OVER(PARTITION BY COL1 
                              ORDER BY 
                                  CASE COL2 
                                      WHEN 'OPEN' THEN '3'
                                      WHEN 'LOCK' THEN '1'
                                      ELSE '2'
                                  END) AS RN
        from TableB
    )
    
    select * from CTE_RN
    WHERE RN = 1
    Hope this helps.
    Sry Imex, didn't achieve the goal unfortunately.

  8. #8
    Join Date
    Apr 2012
    Posts
    213
    Quote Originally Posted by James0816 View Post
    Sry Imex, didn't achieve the goal unfortunately.
    Why? What is wrong?

    Try a test:

    Code:
    declare @TableB table
    (COL1 int, COL2 varchar(10), COL3 date);
    
    insert into @TableB values
    (100, 'OPEN', '01-JAN-2014'),
    (101, 'OPEN', '04-JAN-2014'),
    (101, 'LOCK', '05-JAN-2014'),
    (101, 'CLOSE', '12-JAN-2014'),
    (102, 'OPEN', '08-JAN-2014'),
    (102, 'LOCK', '10-JAN-2014'),
    (103, 'OPEN', '11-JAN-2014'),
    (103, 'CLOSE', '12-JAN-2014');
    
    with CTE_RN as
    (
        select
            COL1,
            COL2, 
            COL3,
            ROW_NUMBER() OVER(PARTITION BY COL1 
                              ORDER BY 
                                  CASE COL2 
                                      WHEN 'OPEN' THEN '3'
                                      WHEN 'LOCK' THEN '1'
                                      ELSE '2'
                                  END) AS RN
        from @TableB
    )
    
    select * from CTE_RN
    WHERE RN = 1

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This code produces the same results you expected from the data that you provided. If you disagree with this answer, please post WHY you disagree so that we can fix the underlying problem.
    Code:
    DECLARE @a TABLE (
       COL1 VARCHAR(9)  NULL,  COL2 VARCHAR(9)  NULL
    ,  COL3 VARCHAR(19) NULL,  COL4 VARCHAR(9)  NULL
    ,  COL5 VARCHAR(9)  NULL,  COL6 VARCHAR(9)  NULL
    ,  COL7 VARCHAR(9)  NULL
       )
    
    INSERT INTO @a (COL1, COL2, COL3)
       VALUES
       ('100', 'OPEN',  '01-JAN-2014')
    ,  ('101', 'CLOSE', '12-JAN-2014')
    ,  ('102', 'LOCK',  '10-JAN-2014')
    ,  ('103', 'CLOSE', '12-JAN-2014')
    
    DECLARE @b TABLE (
       COL1 VARCHAR(9)  NULL,  COL2 VARCHAR(9)  NULL
    ,  COL3 VARCHAR(19) NULL,  COL4 VARCHAR(9)  NULL
    ,  COL5 VARCHAR(9)  NULL
       )
    
    INSERT INTO @b (COL1, COL2, COL3)
       VALUES
       ('100', 'OPEN',  '01-JAN-2014')
    ,  ('101', 'OPEN',  '04-JAN-2014')
    ,  ('101', 'LOCK',  '05-JAN-2014')
    ,  ('101', 'CLOSE', '12-JAN-2014')
    ,  ('102', 'OPEN',  '08-JAN-2014')
    ,  ('102', 'LOCK',  '10-JAN-2014')
    ,  ('103', 'OPEN',  '11-JAN-2014')
    ,  ('103', 'CLOSE', '12-JAN-2014')
    
    SELECT COL1, COL2, COL3
       FROM @a
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Aug 2004
    Posts
    15
    Please disregard my comment IMEX. After sorting the data, it may just be what I'm looking for. Operator error the result of dealing with too much data at one time.

    I'll investigate further.

    Oh...by the way...greatly appreciate the input. Helps a ton.

  11. #11
    Join Date
    Aug 2004
    Posts
    15
    imex, in examining this closer, your query never even uses TableA.

    I think this gets me to a starting point, but in the end game, I will need to add additional columns from TableA and conditions against TableA.

    My apologies, didn't think that would have came into play.

  12. #12
    Join Date
    Aug 2004
    Posts
    15
    Progress indeed. Just needed that little push. It's starting to come together nicely now.

    Thanks kindly Imex!

  13. #13
    Join Date
    Aug 2004
    Posts
    15
    Well poo. I just found out that it is not pulling all the data as originally thought. Dang!

    Example, I plugged in the values for the real data fields and it only returned (17) OPEN records. There are (317) for 2014 alone.

    Back to the drawing the board.

  14. #14
    Join Date
    Aug 2004
    Posts
    15
    Let's just back up a bit here if we can. Like I said, I may be over thinking things and might be making it more complicated than it needs to be. I'm going to steer this in a different direction which is probably where I should have started to begin with.


    TableA (Master Table) - Contains one record for an event. Status of this record will change depending on what is occuring to it. (I.e. OPEN, LOCK, CLOSE).

    TableB (Audit Table) - Contains multiple records for a single event. Every time the status of the record is changed, a record is written to this table. For instance and normal cycle would have (1) record in the Master table to (3) records in the Audit table. (Normal cycle is OPEN, LOCK, CLOSE). There is an exception. A record can go from OPEN to CLOSE.


    For this example, I'm only going to focus on CLOSED records. Nothing more, nothing less. So here is my new sample data:


    TableA (Has 24 Columns)

    COL1 ---------- COL2 ---------- COL3 --------------- COL4 ------------ COL5 ................ rowstamp

    100 ------------- CLOSE -------- 01-JAN-2014 ----- FAIL ------------ {some data} ........ 0000001

    101 ------------- CLOSE -------- 04-JAN-2014 ----- PASS ---------- {some data} ........ 0000002

    102 ------------- CLOSE -------- 12-JAN-2014 ----- PASS ---------- {some data} ........ 0000003

    103 ------------- CLOSE -------- 14-JAN-2014 ----- PASS ---------- {some data} ........ 0000004

    104 ------------- CLOSE -------- 06-JAN-2014 ----- FAIL ------------ {some data} ........ 0000005

    105 ------------- CLOSE -------- 09-JAN-2014 ----- PASS ---------- {some data} ........ 0000006

    106 ------------- CLOSE -------- 10-JAN-2014 ----- FAIL ------------ {some data} ........ 0000007

    107 ------------- CLOSE -------- 12-JAN-2014 ----- FAIL ------------ {some data} ........ 0000008


    TableB (Has 6 Columns)

    COL1 ---------- COL2 ---------- COL3 --------------- COL4 ------------ COL5 ------------- rowstamp

    100 ------------- OPEN ---------- 30-DEC-2013 ----- {some data} -- {some data} ........ 0000001

    100 ------------- LOCK ---------- 30-DEC-2013 ----- {some data} -- {some data} ........ 0000002

    100 ------------- CLOSE -------- 01-JAN-2014 ----- {some data} -- {some data} ........ 0000003

    101 ------------- OPEN --------- 30-DEC-2014 ----- {some data} -- {some data} ........ 0000004

    101 ------------- LOCK ---------- 02-JAN-2014 ----- {some data} -- {some data} ........ 0000005

    101 ------------- CLOSE -------- 04-JAN-2014 ----- {some data} -- {some data} ........ 0000006

    102 ------------- OPEN ---------- 10-JAN-2014 ----- {some data} -- {some data} ........ 0000007

    102 ------------- CLOSE -------- 12-JAN-2014 ----- {some data} -- {some data} ........ 0000008

    103 ------------- OPEN ---------- 14-JAN-2014 ----- {some data} -- {some data} ........ 0000009

    103 ------------- CLOSE -------- 14-JAN-2014 ----- {some data} -- {some data} ........ 0000010

    104 ------------- OPEN ---------- 04-JAN-2014 ----- {some data} -- {some data} ........ 0000011

    104 ------------- LOCK ---------- 05-JAN-2014 ----- {some data} -- {some data} ........ 0000012

    104 ------------- CLOSE -------- 06-JAN-2014 ----- {some data} -- {some data} ........ 0000013

    105 ------------- OPEN ---------- 07-JAN-2014 ----- {some data} -- {some data} ........ 0000014

    105 ------------- LOCK ---------- 09-JAN-2014 ----- {some data} -- {some data} ........ 0000015

    105 ------------- CLOSE -------- 09-JAN-2014 ----- {some data} -- {some data} ........ 0000016

    106 ------------- OPEN ---------- 09-JAN-2014 ----- {some data} -- {some data} ........ 0000017

    106 ------------- CLOSE -------- 10-JAN-2014 ----- {some data} -- {some data} ........ 0000018

    107 ------------- OPEN ---------- 10-JAN-2014 ----- {some data} -- {some data} ........ 0000019

    107 ------------- LOCK ---------- 11-JAN-2014 ----- {some data} -- {some data} ........ 0000020

    107 ------------- CLOSE -------- 12-JAN-2014 ----- {some data} -- {some data} ........ 0000021


    For my output, I want to track all records from TableA that are in CLOSED status. I want to know when those records were LOCKED. The catch being those records that were CLOSED without first being LOCKED. Therefore that CLOSED status would then be the date I'm looking for.


    Output from above data should look like this:

    TblA(Col1) .....TblA(Col2) ..... TblB(Col3) ........... TblA(Col4)

    100 ------------- CLOSE -------- 30-DEC-2013 ----- FAIL ---> Date record was locked

    101 ------------- CLOSE -------- 02-JAN-2014 ----- PASS --> Date record was locked

    102 ------------- CLOSE -------- 12-JAN-2014 ----- PASS --> No LOCK date must use CLOSE date

    103 ------------- CLOSE -------- 14-JAN-2014 ----- PASS --> No LOCK date must use CLOSE date

    104 ------------- CLOSE -------- 05-JAN-2014 ----- FAIL ----> Date record was locked

    105 ------------- CLOSE -------- 09-JAN-2014 ----- PASS --> Date record was locked

    106 ------------- CLOSE -------- 10-JAN-2014 ----- FAIL ----> No LOCK date must use CLOSE date

    107 ------------- CLOSE -------- 11-JAN-2014 ----- FAIL ----> Date record was locked


    I hope this scenario clears the air and makes it more understandable of what I am trying to accomplish.


    Once again, I greatly appreciate the assist.


    Thanks kindly

  15. #15
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    select
        a.Col1,
        a.Col2,
        coalesce
            ((select top 1 b.Col3 from TblB as b 
              where b.Col1 = a.Col1 and b.Col2 = 'LOCK'),
             a.Col3) as Col3,
        a.Col4
    from TblA as a
    where
        a.Col2 = 'CLOSE'
    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •