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 ?

Answer 1

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, [e-mail]

Answer 2

I am working on SQL 2008

Answer 3

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

Answer 4

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, [e-mail]