Solved

SQL Subquery Syntax Assistance

Posted on 2016-10-27
13
50 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

832 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