asked on

import data from one SQL table into another with a field that must increment by date for each groupID

I have a table in a clients production database that I need to copy records from (daily) into a table on their report server.

The appropriate columns in the table in the production system look something like:

ID - counter
GroupID - nvarchar
docDate - date
Field4 - numeric
Field5 - numeric

The destination table has all of these columns, but also has a column (Time_Zero) which is an integer, and needs to increase by one for each day within each GroupID (this allows the client to graph data by either date, or compare Groups over time based on different start dates - which each have a Time_Zero value of 1). Because the values in Field4 and Field5 can be changed for up to two weeks after the record is initially entered, I intend to perform a MERGE operation by selecting all of the above fields from the source table, for the last two weeks, and then updating or inserting records into the reporting table based on joins which use the GroupID and docDate fields.

The piece I cannot figure out is how to include values for the Time_Zero field (from the destination table) into the portion of the query that generates the Source values for the Merge operation.

For simplicity, lets just assume that these tables are tblProduction and tblReporting on the same server and in the same database.

Pawan Kumar

Could you please post some sample data and expected output?

Dale Fye

ASKER

tblReporting
Time_Zero GroupID docDate Field4 Field5
1 ABC 2017-08-01 25 10
2 ABC 2017-08-02 31 9
3 ABC 2017-08-03 23 12

tblProduction
GroupID docDate Field4 Field5
ABC 2017-08-01 25 10
ABC 2017-08-02 31 9
ABC 2017-08-03 27 14 <= note change in values
ABC 2017-08-04 17 11
ABC 2017-08-06 29 10 <= note gap in dates
ABC 2017-08-07 21 13

tblReporting (after merge)
Time_Zero GroupID docDate Field4 Field5
1 ABC 2017-08-01 25 10
2 ABC 2017-08-02 31 9
3 ABC 2017-08-03 27 14 <= note change in values
4 ABC 2017-08-04 17 11
5 ABC 2017-08-05 NULL NULL
6 ABC 2017-08-06 29 10
7 ABC 2017-08-07 21 13

Obviously, there will be multiple GroupIDs values but each should have (in tblReporting) a series of docDate values starting at the date associated with Time_Zero = 1 and running to the current date for each group; generating this list of GroupID/docDate values with the appropriate Time_Zero value is the piece I cannot figure out. If there is no record in tblProduction for a particular GroupID, docDate, then we need to insert that GroupID/docDate combination with the appropriate Time_Zero value and NULLs in the associated Field4 and Field5.

Thanks for your help
I know there is a way to generate a list of consecutive dates starting at the date in tblReporting associated with the Time_Zero, and I'm guessing that

Pawan Kumar

PLs try

DECLARE @StartDate AS DATETIME = '2017-08-01'

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) --+ (10000*d5.Number) + (100000*d6.Number)
	Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE2 AS
(
	SELECT DATEADD(Day,Number-1, @StartDate) st , Number FROM Series
	WHERE DATEADD(Day,Number-1, @StartDate) < GETDATE()
)
SELECT TimePeriod = ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY k.docdate)  , * 
FROM 
(
	SELECT  
	CASE WHEN k.GroupId IS NULL AND 
	LEAD(GroupId) OVER (ORDER BY c.st) = 
	LAG(GroupId) OVER (ORDER BY c.st) THEN LAG(GroupId) OVER (ORDER BY c.st) ELSE GroupId END GroupId
	, c.st docdate, k.Field4 , k.Field5 FROM CTE2 c
	LEFT JOIN
	(
		select b.Time_Zero, ISNULL(b.GroupID,a.GroupID) GroupId ,ISNULL(b.docDate,a.docDate) docDate ,
		ISNULL(a.Field4,b.Field4 ) Field4 ,ISNULL(a.Field5,b.Field5) Field5 from tblReporting b
		FULL JOIN tblProduction a ON a.GroupID =b.GroupID and a.docDate = b.docDate
	)k
	ON k.docDate = c.st
	WHERE c.st <= (SELECT MAX(docDate) FROM tblProduction)
)k

Open in new window

Result

TimePeriod           GroupId                                                                                              docdate                 Field4      Field5
-------------------- ---------------------------------------------------------------------------------------------------- ----------------------- ----------- -----------
1                    ABC                                                                                                  2017-08-01 00:00:00.000 25          10
2                    ABC                                                                                                  2017-08-02 00:00:00.000 31          9
3                    ABC                                                                                                  2017-08-03 00:00:00.000 27          14
4                    ABC                                                                                                  2017-08-04 00:00:00.000 17          11
5                    ABC                                                                                                  2017-08-05 00:00:00.000 NULL        NULL
6                    ABC                                                                                                  2017-08-06 00:00:00.000 29          10
7                    ABC                                                                                                  2017-08-07 00:00:00.000 21          13

(7 row(s) affected)

Open in new window

Dale Fye

ASKER

OK, so I took your code and made a couple of modifications to a MERGE statement I have which looks like:

MERGE tbl_New_Well_Performance as Target
USING (
SELECT TD.Bolo_ID, TD.Time_Zero, TD.docDate
, PCW.WellFacName as Common_Name, PCW.Gas, PCW.Oil, PCW.Water, PCW.Choke
FROM (
SELECT TZ.Bolo_ID, N.lngNumber, TZ.Time_Zero + N.lngNumber as Time_Zero
, DATEADD(day, N.lngNumber, TZ.docDate) as docDate
FROM (
SELECT Bolo_ID, Time_ZERo, docDate
FROM tbl_New_Well_Performance
WHERE tbl_New_Well_Performance.Time_Zero = 1
) as TZ, 
(SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM Accounting_Ledger.dbo.tbl_Numbers as Hundreds,
Accounting_Ledger.dbo.tbl_Numbers as Tens,
Accounting_Ledger.dbo.tbl_Numbers as Ones
) as N
) as TD
INNER JOIN Accounting_Ledger.dbo.vw_Production_Current_Wells as PCW
ON TD.Bolo_ID = PCW.Bolo_ID AND TD.docDate = PCW.docDate
WHERE TD.docDate >= @FromDate
) as S
ON (Target.Bolo_ID = S.Bolo_ID) 
AND (Cast(Target.docDate as date) = Case(s.docDate as date))

WHEN NOT MATCHED BY Target THEN 
    INSERT (Bolo_ID, Time_Zero, Common_Name, docDate, Gas, Oil, Water, Choke)
    VALUES (S.Bolo_ID, s.Time_Zero, s.Common_Name, S.docDate, S.Gas, S.Oil, S.Water, S.Choke)
WHEN MATCHED
    THEN UPDATE SET
        Target.Gas = S.Gas
      , Target.Oil = S.Oil
      , Target.Water = S.Water
      , Target.Choke = S.Choke
;

Open in new window

Everything inside the:

Use(...) as S

works properly to get me a list of Bolo_ID, doc_Date, Time_Zero, and the production values for those dates. But when I dump this into a SP, and attempt to save it I get an error message:

msg 156, level 15, state 1, Procedure df_my_proc_name, line 43
Incorrect Syntax near the keyword 'as'

where line 43 of the SP is:

WHERE tbl_New_Well_Performance.Time_Zero = 1

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

ASKER

Thanks, Pawan,

Sometimes you just need another set of eyes to look at something you have stared at for too long.

Pawan Kumar

Yes you are correct. :)

Exactly.

Pawan Kumar

Hi Dale, Could you pls select the answer and close the question. Thnx.