The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
SQL Subquery Syntax Assistance
Help greatly appreciated with formatting a SQL Query
I need to select from an ARDOC table Credit Memo documents that are older than the oldest Invoice in the same table.
Both of which have a DOCBAL<>0 and for the same CUSTID.
I am basically identifying Credit Memos with DOCBAL <>0 that are older than the oldest Invoice with a DOCBAL <>0 within a CUSTID.
One document per row in the table. Table Contains both CM and IN type entries.
Credit Memos are DOCTYPE='CM'
Invoices are DOCTYPE='IN'
Here are a couple of queries I have:
Your help in formatting these to yield the results is truly appreciated:
select CustId,RefNbr,DocType,DocD
from ARDoc
where DocType = 'CM'
and DocBal<>0
select CustId,RefNbr,DocType,DocD
from ARDoc
where DocType = 'IN'
and DocBal<>0
I need to select from an ARDOC table Credit Memo documents that are older than the oldest Invoice in the same table.
Both of which have a DOCBAL<>0 and for the same CUSTID.
I am basically identifying Credit Memos with DOCBAL <>0 that are older than the oldest Invoice with a DOCBAL <>0 within a CUSTID.
One document per row in the table. Table Contains both CM and IN type entries.
Credit Memos are DOCTYPE='CM'
Invoices are DOCTYPE='IN'
Here are a couple of queries I have:
Your help in formatting these to yield the results is truly appreciated:
select CustId,RefNbr,DocType,DocD
from ARDoc
where DocType = 'CM'
and DocBal<>0
select CustId,RefNbr,DocType,DocD
from ARDoc
where DocType = 'IN'
and DocBal<>0
Who is Participating?
Do they share the same custid and refnbr?
If not what is shared between them.
One or 2 samples would help.
If not what is shared between them.
One or 2 samples would help.
SELECT cm.CustId,cm.RefNbr,cm.Doc
FROM dbo.ARDoc cm
INNER JOIN (
SELECT CustId,Max(DocDate) AS DocDate
FROM dbo.ARDoc
WHERE DocType = 'IN'
AND DocBal<>0
GROUP BY CustId
) AS [in] ON [in].CustId = cm.CustId AND cm.DocDate > [in].DocDate
WHERE cm.DocType = 'CM'
AND cm.DocBal<>0
FROM dbo.ARDoc cm
INNER JOIN (
SELECT CustId,Max(DocDate) AS DocDate
FROM dbo.ARDoc
WHERE DocType = 'IN'
AND DocBal<>0
GROUP BY CustId
) AS [in] ON [in].CustId = cm.CustId AND cm.DocDate > [in].DocDate
WHERE cm.DocType = 'CM'
AND cm.DocBal<>0
I am looking for the query to return just the highlighted in this scenario.
Credit Memo Doc date is older than oldest invoice date.
Again this is just a sample for 1 specific CustID. Many custid are found in the table for both CM and IN Doctypes
Credit Memo Doc date is older than oldest invoice date.
Again this is just a sample for 1 specific CustID. Many custid are found in the table for both CM and IN Doctypes
I prefer the first of these 2 options but both should work I think (assuming I understand the problem)
select
*
from (
select
*
, min(case when doctype='CM' then docdate end) over(partition by custid) min_cm_date
from @ARDoc D1
) d
where docdate < min_cm_date
;
select
*
from @ARDoc D1
cross apply (
select min(docdate) as min_cm_date
from @ARDoc D2
where D1.custid = D2.custid
and D2.doctype = 'CM'
) ca
where D1.docdate < ca.min_cm_date
declare @ARDoc table
([custid] varchar(7), [doctype] varchar(2), [refnbr] varchar(9), [docdate] datetime)
;
INSERT INTO @ARDoc
([custid], [doctype], [refnbr], [docdate])
VALUES
('PAR6554', 'IN', 'I00580234', '2016-01-20 00:00:00'),
('PAR6554', 'IN', 'I00582390', '2016-10-12 00:00:00'),
('PAR6554', 'IN', 'I00582391', '2016-10-12 00:00:00'),
('PAR6554', 'IN', 'I00582797', '2016-10-17 00:00:00'),
('PAR6554', 'IN', 'I00582807', '2016-10-17 00:00:00'),
('PAR6554', 'IN', 'I00582858', '2016-10-18 00:00:00'),
('PAR6554', 'IN', 'I00583192', '2016-10-20 00:00:00'),
('PAR6554', 'IN', 'I00583512', '2016-10-25 00:00:00'),
('PAR6554', 'IN', 'I00583584', '2016-10-25 00:00:00'),
('PAR6554', 'IN', 'I00583601', '2016-10-25 00:00:00'),
('PAR6554', 'IN', 'I00583636', '2016-10-25 00:00:00'),
('PAR6554', 'CM', 'IR016861', '2016-02-24 00:00:00'),
('PAR6554', 'CM', 'IR018201', '2016-10-26 00:00:00')
;
is it the other way around?
SELECT * --cm.CustId,cm.RefNbr,cm.DocType,cm.DocDate,cm.OrigDocAmt,cm.DocBal
FROM @ARDoc cm
INNER JOIN (
SELECT CustId, MIN(DocDate) AS DocDate
FROM @ARDoc
WHERE DocType = 'CM'
--AND DocBal<>0
GROUP BY CustId
) AS [in] ON [in].CustId = cm.CustId AND cm.DocDate < [in].DocDate
WHERE cm.DocType = 'IN'
--AND cm.DocBal<>0
Try.. Check for docBal..column
O/p
-------------
custid doctype refnbr docdate DocBal
PAR6554 CM IR016861 2016-02-24 00:00:00.000 -367
CREATE TABLE ARDoc
(
[custid] varchar(7)
,[doctype] varchar(2)
,[refnbr] varchar(9)
,[docdate] datetime
,[DocBal] INT
)
INSERT INTO ARDoc
([custid], [doctype], [refnbr], [docdate],[DocBal])
VALUES
('PAR6554', 'IN', 'I00580234', '2016-01-20 00:00:00',1.00),
('PAR6554', 'IN', 'I00582390', '2016-10-12 00:00:00',1.00),
('PAR6554', 'IN', 'I00582391', '2016-10-12 00:00:00',2.00),
('PAR6554', 'IN', 'I00582797', '2016-10-17 00:00:00',1.00),
('PAR6554', 'IN', 'I00582807', '2016-10-17 00:00:00',1.00),
('PAR6554', 'IN', 'I00582858', '2016-10-18 00:00:00',1.00),
('PAR6554', 'IN', 'I00583192', '2016-10-20 00:00:00',1.00),
('PAR6554', 'IN', 'I00583512', '2016-10-25 00:00:00',1.00),
('PAR6554', 'IN', 'I00583584', '2016-10-25 00:00:00',1.00),
('PAR6554', 'IN', 'I00583601', '2016-10-25 00:00:00',1.00),
('PAR6554', 'IN', 'I00583636', '2016-10-25 00:00:00',1.00),
('PAR6554', 'CM', 'IR016861', '2016-02-24 00:00:00',-367.75),
('PAR6554', 'CM', 'IR018201', '2016-10-26 00:00:00',0.00)
GO
SELECT a1.*
FROM ARDoc a1
CROSS APPLY
(
SELECT TOP 1 DocDate
FROM ARDoc a2
WHERE a1.custid = a2.custid AND a2.DocType = 'IN'
AND a2.DocBal <> 0
ORDER BY a2.DocDate
)x
WHERE a1.DocDate > x.DocDate
AND a1.DocType = 'CM'
AND a1.DocBal <> 0
O/p
-------------
custid doctype refnbr docdate DocBal
PAR6554 CM IR016861 2016-02-24 00:00:00.000 -367
Pawan:
The last column contains the docbal.
It would be create if we could query from ARDOC table where all docs are so we dont have to create a temp table.
The last column contains the docbal.
It would be create if we could query from ARDOC table where all docs are so we dont have to create a temp table.
Scott - Going to be testing it against multiple data scenarios but so far bringing back the right data. Awesome.
Everyone: I cannot thank you enought for all of your efforts.
Everyone: I cannot thank you enought for all of your efforts.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
SELECT cm.CustId,cm.RefNbr,cm.Doc
FROM dbo.ARDoc cm
INNER JOIN (
SELECT CustId, MIN(DocDate) AS DocDate
FROM dbo.ARDoc
WHERE DocType = 'IN'
AND DocBal<>0
GROUP BY CustId
) AS [in] ON [in].CustId = cm.CustId AND cm.DocDate < [in].DocDate
WHERE cm.DocType = 'CM'
AND cm.DocBal<>0