SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query - please
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Peter01
Starting Member

United Kingdom
12 Posts

Posted - 06/16/2014 :  10:29:09  Show Profile  Reply with Quote
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

Edited by - Peter01 on 06/16/2014 11:15:25

Ifor
Aged Yak Warrior

585 Posts

Posted - 06/16/2014 :  12:08:40  Show Profile  Reply with Quote
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

United Kingdom
12 Posts

Posted - 06/16/2014 :  14:56:23  Show Profile  Reply with Quote
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

United Kingdom
12 Posts

Posted - 06/16/2014 :  18:00:54  Show Profile  Reply with Quote
I got it...
It was just the ';' before the statement
cheers
Peter
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000