June 27, 2018 at 7:16 am
Hello experts
I need some help regarding SQL query, I have a table to save invoices and receipts against invoices. need a query to get invoices against which receipts generated and invoices with no receipts
the table structure is as follow :
docdate (invoice date/receipt date)
docID ('R" for receipts and 'I' for invoices)
docno (document number)
invno
(for invoices invno and docno is same, and for receipts docno is different and invno is the one against which invoice created)
value
thanks & regards,
Imran
June 27, 2018 at 7:24 am
ihsyed - Wednesday, June 27, 2018 7:16 AMHello experts
I need some help regarding SQL query, I have a table to save invoices and receipts against invoices. need a query to get invoices against which receipts generated and invoices with no receipts
the table structure is as follow :
docdate (invoice date/receipt date)
docID ('R" for receipts and 'I' for invoices)
docno (document number)
invno
(for invoices invno and docno is same, and for receipts docno is different and invno is the one against which invoice created)
valuethanks & regards,
Imran
Well, with no DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT statements) for the table(s) involved, or expected results based on the sample data to test against the best I can come up is this:
WITH Invoices AS (
SELECT
*
FROM
MyTable mt1
WHERE
docID = 'I'
), Receipts AS (
SELECT
*
FROM
MyTable mt1
WHERE
docID = 'R'
)
SELECT
*
FROM
[Invoices] AS [Inv]
LEFT OUTER JOIN [Receipts] AS [Rec]
ON [Inv].[invno] = [Rec].[invno];
June 27, 2018 at 8:01 am
thanks for your time and sorry for my incomplete question,
here is sample data and desired result
DECLARE @test-2 TABLE
(
docdate datetime
docID VARCHAR(1)
docno int
invno int
value decimal (12,2)
INSERT @test-2 Values('2018-06-01', 'I', 2001,2001,100.00)
INSERT @test-2 Values('2018-06-01', 'I', 2002,2002,870.00)
INSERT @test-2 Values('2018-06-13', 'I', 2003,2003,700.00)
INSERT @test-2 Values('2018-06-16', 'I', 2004,2004,150.00)
INSERT @test-2 Values('2018-06-17', 'R', 8001,2001,100.00)
INSERT @test-2 Values('2018-06-01', 'R', 8002,2004,100.00)
desired result
=================================
inv no amount receipt balance
==================================
2001 100.00 8001 0.00
2002 870.00 - 870.00
2003 700.00 - 700.00
2004 150.00 8002 50.00
thanks & regards,
Imran
June 27, 2018 at 8:35 am
ihsyed - Wednesday, June 27, 2018 8:01 AMthanks for your time and sorry for my incomplete question,
here is sample data and desired resultDECLARE @test-2 TABLE
(
docdate datetime
docID VARCHAR(1)
docno int
invno int
value decimal (12,2)INSERT @test-2 Values('2018-06-01', 'I', 2001,2001,100.00)
INSERT @test-2 Values('2018-06-01', 'I', 2002,2002,870.00)
INSERT @test-2 Values('2018-06-13', 'I', 2003,2003,700.00)
INSERT @test-2 Values('2018-06-16', 'I', 2004,2004,150.00)INSERT @test-2 Values('2018-06-17', 'R', 8001,2001,100.00)
INSERT @test-2 Values('2018-06-01', 'R', 8002,2004,100.00)desired result
=================================
inv no amount receipt balance
==================================
2001 100.00 8001 0.00
2002 870.00 - 870.00
2003 700.00 - 700.00
2004 150.00 8002 50.00thanks & regards,
Imran
Well, I gave you some code so you can play with that and see what other questions you may have.
June 27, 2018 at 8:59 am
I've updated the code that Lynn kindly provided to produce this. This will group multiple reciepts against an invoice to provide outstanding balances.
DECLARE @test-2 TABLE
(
docdate datetime,
docID VARCHAR(1),
docno int,
invno int,
value decimal (12,2)
)
INSERT @test-2 Values('2018-06-01', 'I', 2001,2001,100.00)
INSERT @test-2 Values('2018-06-01', 'I', 2002,2002,870.00)
INSERT @test-2 Values('2018-06-13', 'I', 2003,2003,700.00)
INSERT @test-2 Values('2018-06-16', 'I', 2004,2004,150.00)
INSERT @test-2 Values('2018-06-17', 'R', 8001,2001,100.00)
INSERT @test-2 Values('2018-06-01', 'R', 8002,2004,100.00)
;WITH Invoices as (
SELECT docdate, docID,docno,invno,value
FROM @test-2
WHERE docID = 'I'),Reciepts AS (--SELECT docdate, docID,docno,invno,value
SELECT invno,SUM(value) as value
FROM @test-2
WHERE docID = 'R'
GROUP BY invno)
SELECT
Inv.invno, inv.value as amount,ISNULL(rec.value,0) as reciept, Inv.value-ISNULL(rec.value,0) as outstanding
FROM
[Invoices] AS [Inv]
LEFT OUTER JOIN [Reciepts] AS [Rec]
ON [Inv].[invno] = [Rec].[invno];
June 27, 2018 at 9:39 am
Dwayne Dibley - Wednesday, June 27, 2018 8:59 AMI've updated the code that Lynn kindly provided to produce this. This will group multiple reciepts against an invoice to provide outstanding balances.
DECLARE @test-2 TABLE(
docdate datetime,
docID VARCHAR(1),
docno int,
invno int,
value decimal (12,2)
)
INSERT @test-2 Values('2018-06-01', 'I', 2001,2001,100.00)
INSERT @test-2 Values('2018-06-01', 'I', 2002,2002,870.00)
INSERT @test-2 Values('2018-06-13', 'I', 2003,2003,700.00)
INSERT @test-2 Values('2018-06-16', 'I', 2004,2004,150.00)INSERT @test-2 Values('2018-06-17', 'R', 8001,2001,100.00)
INSERT @test-2 Values('2018-06-01', 'R', 8002,2004,100.00);WITH Invoices as (
SELECT docdate, docID,docno,invno,value
FROM @test-2
WHERE docID = 'I'),Reciepts AS (--SELECT docdate, docID,docno,invno,value
SELECT invno,SUM(value) as value
FROM @test-2
WHERE docID = 'R'
GROUP BY invno)
SELECT
Inv.invno, inv.value as amount,ISNULL(rec.value,0) as reciept, Inv.value-ISNULL(rec.value,0) as outstanding
FROM
[Invoices] AS [Inv]
LEFT OUTER JOIN [Reciepts] AS [Rec]
ON [Inv].[invno] = [Rec].[invno];
Fixed your code to eliminate my pet peeve, semicolon at the beginning of a CTE definition. Semicolons belong at the end of statements, not the beginning.
DECLARE @test-2 TABLE(
docdate datetime,
docID VARCHAR(1),
docno int,
invno int,
value decimal (12,2)
);
INSERT @test-2
Values ('2018-06-01', 'I', 2001,2001,100.00)
,('2018-06-01', 'I', 2002,2002,870.00)
,('2018-06-13', 'I', 2003,2003,700.00)
,('2018-06-16', 'I', 2004,2004,150.00)
,('2018-06-17', 'R', 8001,2001,100.00)
,('2018-06-01', 'R', 8002,2004,100.00);-- Semicolons are a terminator, not a begininator
WITH Invoices as (
SELECT docdate, docID,docno,invno,value
FROM @test-2
WHERE docID = 'I'),Reciepts AS (--SELECT docdate, docID,docno,invno,value
SELECT invno,SUM(value) as value
FROM @test-2
WHERE docID = 'R'
GROUP BY invno)
SELECT
Inv.invno, inv.value as amount,ISNULL(rec.value,0) as reciept, Inv.value-ISNULL(rec.value,0) as outstanding
FROM
[Invoices] AS [Inv]
LEFT OUTER JOIN [Reciepts] AS [Rec]
ON [Inv].[invno] = [Rec].[invno];
June 27, 2018 at 10:06 am
Lynn Pettis - Wednesday, June 27, 2018 9:39 AMFixed your code to eliminate my pet peeve, semicolon at the beginning of a CTE definition. Semicolons belong at the end of statements, not the beginning.
The misspelling of Receipts actually bothered me more than the semicolon placement. SQL is not English and does not need to follow the same punctuation conventions as English. I also think of the semicolon as a delimiter rather than a terminator. Also, I hate having semicolons between lines and comments associated with those lines, because it's a pain to edit if I have to add more lines.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 27, 2018 at 10:18 am
drew.allen - Wednesday, June 27, 2018 10:06 AMLynn Pettis - Wednesday, June 27, 2018 9:39 AMFixed your code to eliminate my pet peeve, semicolon at the beginning of a CTE definition. Semicolons belong at the end of statements, not the beginning.The misspelling of Receipts actually bothered me more than the semicolon placement. SQL is not English and does not need to follow the same punctuation conventions as English. I also think of the semicolon as a delimiter rather than a terminator. Also, I hate having semicolons between lines and comments associated with those lines, because it's a pain to edit if I have to add more lines.
Drew
Didn't catch that the new code had flipped the i and e. As for calling the ; a delimiter rather than a terminator, it is a terminator. Even the MS documentation regarding CTE's says that the previous statement must be terminated with a semicolon. Also, remember that MS has deprecated NOT terminating statements with a semicolon. I sincerely doubt they will enforce it in the near future (5 to 10 years good enough?) as doing so will probably break a lot of code.
Also, you may not mind if a CTE is started with a semicolon, I do and that is why I call it one of my pet peeves. I have started putting my commas before column names in SELECT lists as I have found I am more likely to comment columns at the end of a list than the beginning.
June 27, 2018 at 11:37 pm
thanks for your help Lynn
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply