Solved

SQL Embedded Sub-Query

Posted on 2016-07-29
11
68 Views
Last Modified: 2016-08-01
I have my main query and I need an additional sub-query I need to embed.

I currently have this query in Access that needs to be a sub-query and return the oldest TRANS_DATE for the conditions listed

SELECT FINAN.FILENO, FINAN.TRANS_DATE
FROM FINAN INNER JOIN MASTER ON FINAN.FILENO = MASTER.FILENO
WHERE (((FINAN.CODE) Between "19" And "23" Or (FINAN.CODE)="2") AND ((FINAN.BPJ)="J") AND ((FINAN.TRUST)="TR53") AND ((FINAN.AMOUNT)>0) AND ((MASTER.JMT_DATE)<[TRANS_DATE]));

I also have a second query in Access that finds the MIN date for each FILENO and returns it to the query.

So, in plain English...

FINAN and MASTER tables link with FILENO

I need the oldest TRANS_DATE when the WHERE statement above is true

Thanks in advance...
0
Comment
Question by:sparker1970
  • 6
  • 4
11 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 41735258
Please post a concise and complete example. This includes table DDL and sample data INSERT statements as runnable T-SQL script.

Caveat:  (((FINAN.CODE) Between "19" And "23" does a lexical match. Thus "190" is between your boundaries:

SELECT IIF(  '190' BETWEEN '19' AND '23', 1, 0 );

Open in new window


returns 1.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41735264
Don't quite follow your question. Think you  want either the min date for each row or all of the rows;

SELECT FINAN.FILENO, MIN(FINAN.TRANS_DATE) min_date
FROM FINAN INNER JOIN MASTER ON FINAN.FILENO = MASTER.FILENO
WHERE (((FINAN.CODE) Between "19" And "23" Or (FINAN.CODE)="2") AND ((FINAN.BPJ)="J") AND ((FINAN.TRUST)="TR53") AND ((FINAN.AMOUNT)>0) AND ((MASTER.JMT_DATE)<[TRANS_DATE]));
GROUP BY FINAN.FILENO


SELECT MIN(FINAN.TRANS_DATE) minmindate
FROM FINAN INNER JOIN MASTER ON FINAN.FILENO = MASTER.FILENO
WHERE (((FINAN.CODE) Between "19" And "23" Or (FINAN.CODE)="2") AND ((FINAN.BPJ)="J") AND ((FINAN.TRUST)="TR53") AND ((FINAN.AMOUNT)>0) AND ((MASTER.JMT_DATE)<[TRANS_DATE]));
0
 

Author Comment

by:sparker1970
ID: 41736498
Sorry, now that I read it again I can see the confusion...

My primary query uses a table named MASTER and links to FINAN using the common field named FILENO from each table

In my primary query I need to return the earliest TRANS_DATE for each occurrence of FILENO where the following conditions are true:

WHERE (((FINAN.CODE) Between "19" And "23" Or (FINAN.CODE)="2") AND ((FINAN.BPJ)="J") AND ((FINAN.TRUST)="TR53") AND ((FINAN.AMOUNT)>0) AND ((MASTER.JMT_DATE)<[TRANS_DATE]))

thx
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 41736799
Cant you just use a group by query and joint that to the main table?

Im using a phone right now,  good coding is next to impossible but like this:

Select mt.*, sq.mindate
From maintable mt
Left join (
   Select fileno, min (adate) mindate
   From othertable
   Group by fileno
  ) sq on mt.fileno = sq.fileno

Now you get the minimum date for every fileno.

Please note because i find copy paste too hard via a phone any table or column names are just representative of your real names
0
 

Author Comment

by:sparker1970
ID: 41736932
Am I able to slip the where statement between the FROM and GROUP BY lines?

I need the MIN date based on the conditions I mentioned in my where statement
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41737028
>>"Am I able to slip the where statement between the FROM and GROUP BY lines? "

Yes.

Select mt.*, sq.mindate
From maintable mt
Left join (
   Select fileno, min (adate) mindate
   From othertable
 
   where ...

   Group by fileno
  ) sq on mt.fileno = sq.fileno
0
 

Author Comment

by:sparker1970
ID: 41737339
Not sure where I am going wrong with this query...

I have attached the full query in case it helps.

Here is the section I created for this post:

      SELECT mt.*, sq.MINDATE
      FROM dbo.CLS_MASTER_POOL mt
      LEFT JOIN(
            SELECT FILENO, MIN(TRANS_DATE) AS MINDATE
            FROM dbo.CLS_FINAN_POOL
            WHERE ((CODE Between "19" And "23") Or (CODE="2")) AND BPJ="J" AND TRUST="TR53" AND AMOUNT>0 AND JMT_DATE<[TRANS_DATE]
            GROUP BY FILENO
            ) sq ON mt.FILENO = sq.FILENO
0
 

Author Comment

by:sparker1970
ID: 41737350
Full query attached
LJD-Query.txt
0
 

Author Comment

by:sparker1970
ID: 41737803
Here is the final query I got to work today:

      isnull(convert(varchar,sq.MINDATE,12),'') as 'FIRST PMT AFTER JUDGMENT'
      FROM dbo.CLS_MASTER_POOL as mt
      LEFT JOIN(
            SELECT CLS_FINAN_POOL.FILENO as FILENO , MIN(TRANS_DATE) AS MINDATE
            FROM dbo.CLS_FINAN_POOL
                     left outer join dbo.CLS_MASTER_POOL on CLS_FINAN_POOL .FILENO = CLS_MASTER_POOL.FILENO
            WHERE ((CODE like '20%' or CODE like '22%' Or CODE like '2%') AND BPJ LIKE 'J' AND TRUST LIKE 'TR53' AND AMOUNT>0 AND JMT_DATE<TRANS_DATE)
            GROUP BY CLS_FINAN_POOL .FILENO
            ) sq ON mt.FILENO = sq.FILENO
0
 

Author Closing Comment

by:sparker1970
ID: 41737807
Your response got me the closest to my final result
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41738115
thanks.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

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. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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