none
SQL -Update One Table based on Other Table ID & Date

    Question

  • Hi Experts ,

    I have Two Table "Dim" and "Sales".I need to Update Sales Table using Dim Table .
    Below is the Sample Structure of Dim Table ..

    CREATE TABLE DIM(EMPid INT,FILEDid INT,VALUE INT ,CHANGE_DATE DATE) INSERT INTO DIM SELECT 1001, 2, 13, '2012-01-13'UNION ALL SELECT 1002, 3, 99, '2012-01-14'UNION ALL SELECT 1002, 1, 223,'2012-01-11'UNION ALL SELECT 1001, 2, 55, '2012-01-01'UNION ALL SELECT 1001, 1, 23, '2012-01-13'UNION ALL SELECT 1001, 1, 12, '2012-01-17'UNION ALL SELECT 1001, 2, 78, '2012-01-13'UNION ALL SELECT 1002, 1, 41, '2012-02-10'UNION ALL SELECT 1002, 2, 445,'2012-01-13'UNION ALL SELECT 1002, 2, 20, '2012-01-14'UNION ALL SELECT 1001, 1, 21, '2012-01-15'UNION ALL SELECT 1001, 2, 45, '2012-01-11'UNION ALL SELECT 1002, 2, 14, '2012-03-13'UNION ALL SELECT 1001, 1, 24, '2012-01-28'

     

    FILEDID 1 OF DIM TABLE Refer to SALESid OF SALES TABLE
    FILEDID 2 OF DIM TABLE Refer to PRODid OF SALES TABLE

    EXAMPLE

    FOR EMPLOYEE 1001 IN DIM TABLE,VALUE AGAINST FILEDid 2 IS 13 FROM '2012-01-13' (CHANGE_DATE )

    THEN UPDATE PRODid AS 13 OF SALES TABLE FOR EMPLOYEE 1001 FROM '2012-01-13' ONWARDS.
    NEXT RECORDS OF 1001 IN DIM TABLE FILEDid 1 HAS VALUE =55 FROM '2012-01-01',THEN
    UPDATE SALESid as 55 OF SALES TABLE FOR EMPLOYEE 1001 FROM '2012-01-01' ONWARDS
    AGAIN FOR EMPLOYEE 1001 IN DIM TABLE,VALUE AGAINST FILEDid 1 IS 23 FROM '2012-01-13' (CHANGE_DATE ),THEN
    UPDATE SALESid as 23 OF SALES TABLE FOR EMPLOYEE 1001 FROM '2012-01-13' ONWARDS.

    Below is the Sample Output as How SALES Table Should Look like after Updating SALESid and PRODid .

    CREATE TABLE SALES (EMPid INT,SALESid INT ,PRODid INT,DATES DATE)
    INSERT INTO SALES
    SELECT 	1001,''	,	55,	'2012-01-01'	UNION ALL
    SELECT 	1002,''	,''	,	'2012-01-01'	UNION ALL
    SELECT 	1002,''	,	258,	'2012-01-10'	UNION ALL
    SELECT 	1001,	21,	45,	'2012-01-20'	UNION ALL
    SELECT 	1001,''	,	45,	'2012-01-12'	UNION ALL
    SELECT 	1001,	23,	45,	'2012-01-13'	UNION ALL
    SELECT 	1001,''	,	45,	'2012-01-11'	UNION ALL
    SELECT 	1002,	223,	20,	'2012-01-25'	UNION ALL
    SELECT 	1002,	223,	20,	'2012-01-15'	UNION ALL
    SELECT 	1002,	41,	14,	'2012-03-20'	UNION ALL
    SELECT 	1002,	223,	20,	'2012-02-02'	UNION ALL
    SELECT 	1001,''	,	55,	'2012-01-04'	UNION ALL
    SELECT 	1001,	23,	45,	'2012-01-14'UNION ALL
    SELECT 	1001,	24,	45,	'2012-02-29'UNION ALL
    SELECT 	1001,	21,	45,	'2012-01-16'UNION ALL
    SELECT 	1001,	21,	45,	'2012-01-26'UNION ALL
    SELECT 	1001,	24,	45,	'2012-05-26'
    SELECT * FROM DIM
    SELECT * FROM SALES

    NOTE: I do not want to use Cursor because my Real Table contain more than 20 MILLION records .

    Any Help on This update Query ?


    Friday, January 25, 2013 11:59 AM

Answers

  • Below is a query. The resulting table from my UPDATE does not match your expected output, but from your description, I expect the product id to for empid 1001 be 78 from 2012-01-13 and on. And in your SALES table there is an occurence of a prodid 258 which is not in DIM at all.

    CREATE TABLE DIM(EMPid INT,FILEDid INT,VALUE INT ,CHANGE_DATE DATE)
    INSERT INTO DIM
    SELECT    1001,   2,   13,   '2012-01-13'UNION ALL
    SELECT    1002,   3,   99,   '2012-01-14'UNION ALL
    SELECT    1002,   1,   223,'2012-01-11'UNION ALL
    SELECT    1001,   2,   55,   '2012-01-01'UNION ALL
    SELECT    1001,   1,   23,   '2012-01-13'UNION ALL
    SELECT    1001,   1,   12,   '2012-01-17'UNION ALL
    SELECT    1001,   2,   78,   '2012-01-13'UNION ALL
    SELECT    1002,   1,   41,   '2012-02-10'UNION ALL
    SELECT    1002,   2,   445,'2012-01-13'UNION ALL
    SELECT    1002,   2,   20,   '2012-01-14'UNION ALL
    SELECT    1001,   1,   21,   '2012-01-15'UNION ALL
    SELECT    1001,   2,   45,   '2012-01-11'UNION ALL
    SELECT    1002,   2,   14,   '2012-03-13'UNION ALL
    SELECT    1001,   1,   24,   '2012-01-28' 

    CREATE TABLE SALES (EMPid INT,SALESid INT ,PRODid INT,DATES DATE)
    INSERT INTO SALES
    SELECT    1001,''   ,   55,   '2012-01-01'   UNION ALL
    SELECT    1002,''   ,''   ,   '2012-01-01'   UNION ALL
    SELECT    1002,''   ,   258,   '2012-01-10'   UNION ALL
    SELECT    1001,   21,   45,   '2012-01-20'   UNION ALL
    SELECT    1001,''   ,   45,   '2012-01-12'   UNION ALL
    SELECT    1001,   23,   45,   '2012-01-13'   UNION ALL
    SELECT    1001,''   ,   45,   '2012-01-11'   UNION ALL
    SELECT    1002,   223,   20,   '2012-01-25'   UNION ALL
    SELECT    1002,   223,   20,   '2012-01-15'   UNION ALL
    SELECT    1002,   41,   14,   '2012-03-20'   UNION ALL
    SELECT    1002,   223,   20,   '2012-02-02'   UNION ALL
    SELECT    1001,''   ,   55,   '2012-01-04'   UNION ALL
    SELECT    1001,   23,   45,   '2012-01-14'UNION ALL
    SELECT    1001,   24,   45,   '2012-02-29'UNION ALL
    SELECT    1001,   21,   45,   '2012-01-16'UNION ALL
    SELECT    1001,   21,   45,   '2012-01-26'UNION ALL
    SELECT    1001,   24,   45,   '2012-05-26'

    CREATE TABLE target(empid int, salesid int, prodid int, dates date)
    INSERT target (empid, salesid, prodid, dates)
    SELECT EMPid, 0, 0, DATES FROM SALES
    go
    UPDATE target
    SET    salesid = isnull(sub.salesid, 0),
           prodid  = isnull(sub.prodid, 0)
    FROM   target t
    CROSS  APPLY (SELECT salesid = MIN(CASE FILEDid WHEN 1 THEN VALUE END),
                         prodid  = MIN(CASE FILEDid WHEN 2 THEN VALUE END)
                  FROM   (SELECT VALUE, FILEDid,
                                 rowno = row_number() OVER(PARTITION BY FILEDid ORDER BY CHANGE_DATE DESC)
                          FROM   DIM d
                          WHERE  d.EMPid = t.empid
                           AND   d.CHANGE_DATE <= t.dates) AS sub1
                  WHERE   rowno = 1) AS  sub
    go
    SELECT * FROM DIM ORDER BY EMPid, FILEDid, CHANGE_DATE
    SELECT * FROM SALES ORDER BY EMPid, DATES
    SELECT * FROM target ORDER BY empid, dates
    go
    DROP TABLE DIM
    DROP TABLE SALES, target


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 25, 2013 11:04 PM

All replies

  • I don't have the time to look at this right now, but will try later. In the meanwhile, can you post the output of "SELECT @@version"?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 25, 2013 2:52 PM
  • I am working on SQL 2008
    Friday, January 25, 2013 8:13 PM
  • To track the history of, say, Foobars we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when a foobar had a particular value. Here is the skeleton.

    CREATE TABLE Foobar_History
    (foo_id CHAR(9) NOT NULL,
     start_date DATE NOT NULL,
     end_date DATETIME, --null means current
     CHECK (start_date <= end_date),
     foo_status INTEGER NOT NULL,
     PRIMARY KEY (foo_id, start_date));

    When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;

    SELECT *
      FROM Foobar
     WHERE @in_cal_date
         BETWEEN start_date
          AND COALESCE (end_date, CURRENT_TIMESTAMP);

    There are more tricks in the DDL to prevent gaps, etc

    CREATE TABLE Events
    (event_id CHAR(10) NOT NULL,
     previous_event_end_date DATE NOT NULL 
     CONSTRAINT Chained_Dates 
      REFERENCES Events (event_end_date),
     event_start_date DATE NOT NULL,
     event_end_date DATE UNIQUE, -- null means event in progress
      PRIMARY KEY (event_id, event_start_date),
     CONSTRAINT Event_Order_Valid
      CHECK (event_start_date <= event_end_date),
     CONSTRAINT Chained_Dates
      CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)
    -- CHECK (previous_event_end_date + INTERVAL '01' DAYS) = event_start_date)
    );

    -- disable the Chained_Dates constraint
    ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;
    GO

    -- insert a starter row
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');
    GO

    -- enable the constraint in the table
    ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;
    GO

    -- this works
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

    -- this fails
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15');


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

    Friday, January 25, 2013 9:11 PM
  • Below is a query. The resulting table from my UPDATE does not match your expected output, but from your description, I expect the product id to for empid 1001 be 78 from 2012-01-13 and on. And in your SALES table there is an occurence of a prodid 258 which is not in DIM at all.

    CREATE TABLE DIM(EMPid INT,FILEDid INT,VALUE INT ,CHANGE_DATE DATE)
    INSERT INTO DIM
    SELECT    1001,   2,   13,   '2012-01-13'UNION ALL
    SELECT    1002,   3,   99,   '2012-01-14'UNION ALL
    SELECT    1002,   1,   223,'2012-01-11'UNION ALL
    SELECT    1001,   2,   55,   '2012-01-01'UNION ALL
    SELECT    1001,   1,   23,   '2012-01-13'UNION ALL
    SELECT    1001,   1,   12,   '2012-01-17'UNION ALL
    SELECT    1001,   2,   78,   '2012-01-13'UNION ALL
    SELECT    1002,   1,   41,   '2012-02-10'UNION ALL
    SELECT    1002,   2,   445,'2012-01-13'UNION ALL
    SELECT    1002,   2,   20,   '2012-01-14'UNION ALL
    SELECT    1001,   1,   21,   '2012-01-15'UNION ALL
    SELECT    1001,   2,   45,   '2012-01-11'UNION ALL
    SELECT    1002,   2,   14,   '2012-03-13'UNION ALL
    SELECT    1001,   1,   24,   '2012-01-28' 

    CREATE TABLE SALES (EMPid INT,SALESid INT ,PRODid INT,DATES DATE)
    INSERT INTO SALES
    SELECT    1001,''   ,   55,   '2012-01-01'   UNION ALL
    SELECT    1002,''   ,''   ,   '2012-01-01'   UNION ALL
    SELECT    1002,''   ,   258,   '2012-01-10'   UNION ALL
    SELECT    1001,   21,   45,   '2012-01-20'   UNION ALL
    SELECT    1001,''   ,   45,   '2012-01-12'   UNION ALL
    SELECT    1001,   23,   45,   '2012-01-13'   UNION ALL
    SELECT    1001,''   ,   45,   '2012-01-11'   UNION ALL
    SELECT    1002,   223,   20,   '2012-01-25'   UNION ALL
    SELECT    1002,   223,   20,   '2012-01-15'   UNION ALL
    SELECT    1002,   41,   14,   '2012-03-20'   UNION ALL
    SELECT    1002,   223,   20,   '2012-02-02'   UNION ALL
    SELECT    1001,''   ,   55,   '2012-01-04'   UNION ALL
    SELECT    1001,   23,   45,   '2012-01-14'UNION ALL
    SELECT    1001,   24,   45,   '2012-02-29'UNION ALL
    SELECT    1001,   21,   45,   '2012-01-16'UNION ALL
    SELECT    1001,   21,   45,   '2012-01-26'UNION ALL
    SELECT    1001,   24,   45,   '2012-05-26'

    CREATE TABLE target(empid int, salesid int, prodid int, dates date)
    INSERT target (empid, salesid, prodid, dates)
    SELECT EMPid, 0, 0, DATES FROM SALES
    go
    UPDATE target
    SET    salesid = isnull(sub.salesid, 0),
           prodid  = isnull(sub.prodid, 0)
    FROM   target t
    CROSS  APPLY (SELECT salesid = MIN(CASE FILEDid WHEN 1 THEN VALUE END),
                         prodid  = MIN(CASE FILEDid WHEN 2 THEN VALUE END)
                  FROM   (SELECT VALUE, FILEDid,
                                 rowno = row_number() OVER(PARTITION BY FILEDid ORDER BY CHANGE_DATE DESC)
                          FROM   DIM d
                          WHERE  d.EMPid = t.empid
                           AND   d.CHANGE_DATE <= t.dates) AS sub1
                  WHERE   rowno = 1) AS  sub
    go
    SELECT * FROM DIM ORDER BY EMPid, FILEDid, CHANGE_DATE
    SELECT * FROM SALES ORDER BY EMPid, DATES
    SELECT * FROM target ORDER BY empid, dates
    go
    DROP TABLE DIM
    DROP TABLE SALES, target


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 25, 2013 11:04 PM