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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query - please

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


I have the following 2 tables

Table#A: Invoice_lines

A_date, A_product_code, A_balance, A_value
01/01/14 | BIC 01 | 122 | 0
01/01/14 | BIC02 | 122 | 0
01/01/14 | CASH | 180 | 0
02/01/14 | BIC01 | 213 | 0
02/01/14 | CASH | 321 | 0
04/01/14 | BIC02 | 412 | 0
04/01/14 | CASH | 244 | 0

Table#B: Product_catalogue

B_date, B_product_code, B_price
01/01/14 | BIC01 | 12.2
02/01/14 | BIC01 | 13.4
01/01/14 | BIC02 | 29.1
02/01/14 | BIC02 | 28.2
04/01/14 | BIC02 | 27.2

And 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 missing
A_product_code = 'CASH'
A_balance = The A_balance from the most recent date where A_product was =CASH
A_value = 0

So the table#A after running the statement would look like:

Table#A: Invoice_lines
A_date, A_product_code, A_balance, A_value
01/01/14 | BIC 01 | 122 | 0
01/01/14 | BIC02 | 122 | 0
01/01/14 | CASH | 180 | 0
02/01/14 | BIC01 | 213 | 0
02/01/14 | CASH | 321 | 0
03/01/14 | CASH | 321 | 0 <=========
04/01/14 | BIC02 | 412 | 0
04/01/14 | CASH | 244 | 0

Task#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_date
So the table after running the query would look like:

A_date, A_product_code, A_balance, A_value
01/01/14 | BIC 01 |122 | 12.2
01/01/14 | BIC02 |122 | 29.1
01/01/14 | CASH |180 | 0
02/01/14 | BIC01 |213 | 13.4
02/01/14 | CASH |321 | 0
03/01/14 | CASH |321 | 0
04/01/14 | BIC02 |412 | 27.2
04/01/14 | CASH |244 | 0


Thanks 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_lines
SELECT '20140101', 'BIC01', 122, 0
UNION ALL SELECT '20140101', 'BIC02', 122, 0
UNION ALL SELECT '20140101', 'CASH', 180, 0
UNION ALL SELECT '20140102', 'BIC01', 213, 0
UNION ALL SELECT '20140102', 'CASH', 321, 0
UNION ALL SELECT '20140104', 'BIC02', 412, 0
UNION ALL SELECT '20140104', 'CASH', 244, 0

CREATE TABLE #Product_catalogue
(
B_date datetime NOT NULL
,B_product_code varchar(10) NOT NULL
,B_price money NOT NULL
)
INSERT INTO #Product_catalogue
SELECT '20140101', 'BIC01', 12.2
UNION ALL SELECT '20140102', 'BIC01', 13.4
UNION ALL SELECT '20140101', 'BIC02', 29.1
UNION ALL SELECT '20140102', 'BIC02', 28.2
UNION ALL SELECT '20140104', 'BIC02', 27.2


Task1
If you have a calendar table (google) - use it.
If not generate the dates in the range using a number/tally table (google)
eg

WITH 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)
,DateRange
AS
(
SELECT MIN(A_Date) AS StartDate
,MAX(A_Date) AS EndDate
FROM #Invoice_lines
)
INSERT INTO #Invoice_lines
SELECT R.StartDate + N.N, 'CASH', D.A_balance, 0
FROM 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
) D
WHERE NOT EXISTS
(
SELECT 1
FROM #Invoice_lines L
WHERE L.A_date = R.StartDate + N.N
)


Task2
A simple update:

UPDATE L
SET A_value = C.B_price
FROM #Invoice_lines L
JOIN #Product_catalogue C
ON L.A_product_code = C.B_product_code
AND L.A_date = C.B_date

Go to Top of Page

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 30

Incorrect 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 32
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ','.

Any idea as what this could be?
thanks again,
Peter
Go to Top of Page

Peter01
Starting Member

16 Posts

Posted - 2014-06-16 : 18:00:54
I got it...
It was just the ';' before the statement
cheers
Peter
Go to Top of Page
   

- Advertisement -