none
TSQL Help

    Question

  • I need to write TSQL which gives the Result as below from Table A and Table B. Any help would be appreciated.

    Table A :

    LogId LogDate         NumberOfRecords
    1       1/1/2013       1000
    1       1/5/2013       0
    1      1/7/2013       10000

    Table B :

    LogId LogDate
    1 1/1/2013
    1 1/2/2013
    1 1/3/2013
    1 1/4/2013
    1 1/5/2013
    1 1/6/2013
    1 1/7/2013
    1 1/8/2013

    Result:

    LogId LogId NumberOfRecords
    1 1/1/2013 1000
    1 1/2/2013 1000
    1 1/3/2013 1000
    1 1/4/2013 1000
    1 1/5/2013 0
    1 1/6/2013 0
    1 1/7/2013 10000
    1 1/8/2013 10000
    Saturday, January 25, 2014 3:57 PM

Answers

  • Declare @TableA Table(LogId int, LogDate date, NumberOfRecords int);
    Insert @TableA(LogId, LogDate, NumberOfRecords) Values
    (1,       '1/1/2013',       1000),
    (1,       '1/5/2013',       0),
    (1,      '1/7/2013',       10000);
    
    Declare @TableB Table(LogId int, LogDate date);
    Insert @TableB(LogId, LogDate) Values
    (1, '1/1/2013'),
    (1, '1/2/2013'),
    (1, '1/3/2013'),
    (1, '1/4/2013'),
    (1, '1/5/2013'),
    (1, '1/6/2013'),
    (1, '1/7/2013'),
    (1, '1/8/2013');
    
    ;With cte As
    (Select b.LogId, b.LogDate, a.NumberOfRecords,
        Row_Number() Over(Partition By b.Logid, b.LogDate Order By a.LogDate Desc) As rn
    From @TableB b
    Inner Join @TableA a On b.LogDate >= a.LogDate)
    Select LogId, LogDate, NumberOfRecords
    From cte
    Where rn = 1;

    Tom
    • Proposed as answer by Jayakumaur (JK) Saturday, January 25, 2014 6:17 PM
    • Marked as answer by zenithsql Saturday, January 25, 2014 7:28 PM
    Saturday, January 25, 2014 5:09 PM
  • Use below query

    SELECT b.LogId,b.LogDate,a.NumberOfRecords
    FROM TableB b
    CROSS APPLY (SELECT TOP 1 NumberOfRecords
                 FROM TableA
                 WHERE LogID = b.LogID
                 AND LogDate <= b.LogDate
                 ORDER BY LogDate DESC)a


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

    • Marked as answer by zenithsql Saturday, January 25, 2014 7:28 PM
    Saturday, January 25, 2014 5:12 PM

All replies

  • what is the sql version that you are using?

    Satheesh
    My Blog


    Saturday, January 25, 2014 4:11 PM
  • 2008R2
    Saturday, January 25, 2014 4:38 PM
  • Declare @TableA Table(LogId int, LogDate date, NumberOfRecords int);
    Insert @TableA(LogId, LogDate, NumberOfRecords) Values
    (1,       '1/1/2013',       1000),
    (1,       '1/5/2013',       0),
    (1,      '1/7/2013',       10000);
    
    Declare @TableB Table(LogId int, LogDate date);
    Insert @TableB(LogId, LogDate) Values
    (1, '1/1/2013'),
    (1, '1/2/2013'),
    (1, '1/3/2013'),
    (1, '1/4/2013'),
    (1, '1/5/2013'),
    (1, '1/6/2013'),
    (1, '1/7/2013'),
    (1, '1/8/2013');
    
    ;With cte As
    (Select b.LogId, b.LogDate, a.NumberOfRecords,
        Row_Number() Over(Partition By b.Logid, b.LogDate Order By a.LogDate Desc) As rn
    From @TableB b
    Inner Join @TableA a On b.LogDate >= a.LogDate)
    Select LogId, LogDate, NumberOfRecords
    From cte
    Where rn = 1;

    Tom
    • Proposed as answer by Jayakumaur (JK) Saturday, January 25, 2014 6:17 PM
    • Marked as answer by zenithsql Saturday, January 25, 2014 7:28 PM
    Saturday, January 25, 2014 5:09 PM
  • Use below query

    SELECT b.LogId,b.LogDate,a.NumberOfRecords
    FROM TableB b
    CROSS APPLY (SELECT TOP 1 NumberOfRecords
                 FROM TableA
                 WHERE LogID = b.LogID
                 AND LogDate <= b.LogDate
                 ORDER BY LogDate DESC)a


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

    • Marked as answer by zenithsql Saturday, January 25, 2014 7:28 PM
    Saturday, January 25, 2014 5:12 PM
  • Both of the answers produced the accurate results. Thanks much! I am trying to understand the logic but I am not getting it. Can you please explain it to  me ? I am not getting the logic where you are doing a.logdate <= b.logdate Order by a.logdate desc.

    Saturday, January 25, 2014 6:13 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 failed). Temporal data should use ISO-8601 formats (you failed again and in a much worse way). Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Now we have to read your mind and do all your typing!! 

    Please read a book on basic data modeling and RDBMS. Time is a continuum. This means that we have to use (start_timestamp, end_timestamp) pairs to model them. A NULL end timestamp means the event is still in process. 

    CREATE TABLE Foobar_Log
    (log_id  INTEGER NOT NULL, 
     log_start_date DATE NOT NULL, 
     PRIMARY KEY (log_id, log_start_date), 
     CHECK (log_start_date <= log_end_date)
     log_end_date DATE, 
     record_cnt INTEGER);

    INSERT INTO Foobar_Log
    (1, '2013-01-01', '2013-01-04', 1000), 
    (1, '2013-01-05', '2013-01-06', 0), 
    (1, '2013-01-07', NULL, 10000);

    Instead of materializing a base table, create a VIEW or query using your Calendar table (if you do not know what that is, Google it). This is SQL and not a file system; we use a lot of virtual constructs.

    SELECT L.log_id, C.cal_date, L.record_cnt
      FROM Foobar_Log AS L, Calendar AS C
     WHERE C.cal_date BETWEEN L.log_start_date 
       AND COALESCE (L.log_end_date, CURRENT_TIMESTAMP)
     -- AND C.cal_date BETWEEN '2013-01-01' AND '2013-01-08'
    ;

    Most of the work in SQL is in DDL; when you have to kludge self-joins and other complex DML, then look for bad DDL . 


    --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

    Saturday, January 25, 2014 8:17 PM
  • Both Visakh's and my solution essentially do the following:  for every row in TableB, find the rows in TableA where the LogID is the same and the the date in TableA is less than or equal to the date in TableB.  Of those rows in TableA, choose the row in TableA with the most recent date.  That row contains the correct NumberOfRecords for that date.  Vasakh and I chose different methods to find that row.

    I took TableB and joined it to TableA where the LogID's are equal and the date in TableA is less than or equal to the date in TableB.  I then used the Row_Number() function to assign a number to each of those rows.  For any given row in TableB the function assigns the number 1 to the most recent date in TableA, 2 to the next most recent date in TableA, etc.  Then I select only the rows where that row number = 1.

    Visakh's query gets each row in TableB, and for that row finds the rows in TableA where the LogID's are equal and the date in Table is less than or equal to the date in TableB.  It sorts these rows by the date in TableA in descending order, and then takes the first one.  Which is, of course, the most recent date in TableA for that row in TableB.

    Tom

    Sunday, January 26, 2014 2:04 AM