Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Peter01
Starting Member
16 Posts |
Posted - 2014-06-16 : 10:29:09
|
Hello SQLTeam,This is my first post and I´m fairly new to SQL. At present I´m puzzle with the query below. Please help!Thanks in advanced!!!PeterI have the following 2 tables Table#A: Invoice_linesA_date, A_product_code, A_balance, A_value01/01/14 | BIC 01 | 122 | 001/01/14 | BIC02 | 122 | 001/01/14 | CASH | 180 | 002/01/14 | BIC01 | 213 | 002/01/14 | CASH | 321 | 0 04/01/14 | BIC02 | 412 | 004/01/14 | CASH | 244 | 0 Table#B: Product_catalogueB_date, B_product_code, B_price01/01/14 | BIC01 | 12.202/01/14 | BIC01 | 13.401/01/14 | BIC02 | 29.102/01/14 | BIC02 | 28.204/01/14 | BIC02 | 27.2And I would like to update the Table#A. For doing this I have two different task:Task#1:I would like to fill in the date gaps in Table#A . In other words, there are times when there is no record for a specific date and I would like to insert a new record for that date with the following info:A_date = the actual date missingA_product_code = 'CASH'A_balance = The A_balance from the most recent date where A_product was =CASHA_value = 0So the table#A after running the statement would look like:Table#A: Invoice_linesA_date, A_product_code, A_balance, A_value01/01/14 | BIC 01 | 122 | 001/01/14 | BIC02 | 122 | 001/01/14 | CASH | 180 | 002/01/14 | BIC01 | 213 | 002/01/14 | CASH | 321 | 003/01/14 | CASH | 321 | 0 <=========04/01/14 | BIC02 | 412 | 004/01/14 | CASH | 244 | 0Task#2:Again, I would like to update Table#A with the price from Table#B and based on matching the B_product_code=A_product_code and the B_date=A_dateSo the table after running the query would look like:A_date, A_product_code, A_balance, A_value01/01/14 | BIC 01 |122 | 12.201/01/14 | BIC02 |122 | 29.101/01/14 | CASH |180 | 002/01/14 | BIC01 |213 | 13.402/01/14 | CASH |321 | 003/01/14 | CASH |321 | 0 04/01/14 | BIC02 |412 | 27.204/01/14 | CASH |244 | 0Thanks in advanced!!!!Peter |
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2014-06-16 : 12:08:40
|
First produce your test data in a consumable format with ISO dates:CREATE TABLE #Invoice_lines( A_date datetime NOT NULL ,A_product_code varchar(10) NOT NULL ,A_balance money NOT NULL ,A_value money NOT NULL)INSERT INTO #Invoice_linesSELECT '20140101', 'BIC01', 122, 0UNION ALL SELECT '20140101', 'BIC02', 122, 0UNION ALL SELECT '20140101', 'CASH', 180, 0UNION ALL SELECT '20140102', 'BIC01', 213, 0UNION ALL SELECT '20140102', 'CASH', 321, 0UNION ALL SELECT '20140104', 'BIC02', 412, 0UNION ALL SELECT '20140104', 'CASH', 244, 0CREATE TABLE #Product_catalogue( B_date datetime NOT NULL ,B_product_code varchar(10) NOT NULL ,B_price money NOT NULL)INSERT INTO #Product_catalogueSELECT '20140101', 'BIC01', 12.2UNION ALL SELECT '20140102', 'BIC01', 13.4UNION ALL SELECT '20140101', 'BIC02', 29.1UNION ALL SELECT '20140102', 'BIC02', 28.2UNION ALL SELECT '20140104', 'BIC02', 27.2 Task1If you have a calendar table (google) - use it.If not generate the dates in the range using a number/tally table (google)egWITH N1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),N2(N) AS (SELECT 1 FROM N1 a, N1 b),N4(N) AS (SELECT 1 FROM N2 a, N2 b) ,N(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N4),DateRangeAS( SELECT MIN(A_Date) AS StartDate ,MAX(A_Date) AS EndDate FROM #Invoice_lines)INSERT INTO #Invoice_linesSELECT R.StartDate + N.N, 'CASH', D.A_balance, 0FROM DateRange R JOIN N ON N.N < DATEDIFF(day, R.StartDate, R.EndDate) CROSS APPLY ( SELECT TOP 1 A_balance FROM #Invoice_lines L WHERE L.A_date < R.StartDate + N.N AND L.A_product_code = 'CASH' ORDER BY L.A_date DESC ) DWHERE NOT EXISTS( SELECT 1 FROM #Invoice_lines L WHERE L.A_date = R.StartDate + N.N) Task2A simple update:UPDATE LSET A_value = C.B_priceFROM #Invoice_lines L JOIN #Product_catalogue C ON L.A_product_code = C.B_product_code AND L.A_date = C.B_date |
|
|
Peter01
Starting Member
16 Posts |
Posted - 2014-06-16 : 14:56:23
|
Many, many thanks Ifor.Task#2 is great and works very well.With Task#1 I get an error with the 'with' statement:Msg 319, Level 15, State 1, Line 30Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.Msg 102, Level 15, State 1, Line 32Incorrect syntax near ','.Msg 102, Level 15, State 1, Line 33Incorrect syntax near ','.Msg 102, Level 15, State 1, Line 34Incorrect syntax near ','.Msg 102, Level 15, State 1, Line 35Incorrect syntax near ','.Any idea as what this could be?thanks again,Peter |
|
|
Peter01
Starting Member
16 Posts |
Posted - 2014-06-16 : 18:00:54
|
I got it... It was just the ';' before the statementcheersPeter |
|
|
|
|
|
|
|