Solved

SQL Subquery Syntax Assistance

Posted on 2016-10-27
13
53 Views
Last Modified: 2016-10-28
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,DocDate,OrigDocAmt,DocBal
from ARDoc
where DocType = 'CM'
and DocBal<>0


select CustId,RefNbr,DocType,DocDate,OrigDocAmt,DocBal
from ARDoc
where DocType = 'IN'
and DocBal<>0
0
Comment
Question by:armgon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41862955
Do they share the same custid and refnbr?

If not what is shared between them.

One or 2 samples would help.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41862959
SELECT cm.CustId,cm.RefNbr,cm.DocType,cm.DocDate,cm.OrigDocAmt,cm.DocBal
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
0
 

Author Comment

by:armgon
ID: 41863007
CustId is what is shared.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:armgon
ID: 41863049
ScottPletcher, I think we are close but not returning the correct data.
0
 

Author Comment

by:armgon
ID: 41863052
0
 

Author Comment

by:armgon
ID: 41863055
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41863100
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

Open in new window


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')
;

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41863109
Hmm, I think it got it backwards, maybe this then:

SELECT cm.CustId,cm.RefNbr,cm.DocType,cm.DocDate,cm.OrigDocAmt,cm.DocBal
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41863208
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

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41863216
Hi,

Which column in the excel refers to the DocBal?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41863219
Try.. Check for docBal..column

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

Open in new window


O/p
-------------
custid      doctype      refnbr      docdate      DocBal
PAR6554      CM      IR016861      2016-02-24 00:00:00.000      -367
0
 

Author Comment

by:armgon
ID: 41864031
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.
0
 

Author Closing Comment

by:armgon
ID: 41864035
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.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Throw Error 7 33
Syntax error creating JSON recordset 4 26
sql server cross db update 2 20
add stored proc on publlication 4 19
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question