This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.
Course Of The Month
8 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Subquery Syntax Assistance
Posted on 2016-10-27
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
[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
Active today
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.
Active today
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
Active today
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
Active today
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')
;
Active today
Hmm, I think it got it backwards, maybe this then:
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
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
Active today
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
Featured Post
For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month8 days, 11 hours left to enroll
739 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.