Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Query Help

Posted on 2014-08-14
2
Medium Priority
?
521 Views
Last Modified: 2014-08-25
The SQL report I'm building requires the following two columns:

1. Period: List the month and year (MM/YYYY) for all the months included within date range specified.
2. Loans: Number of loans with a create date during the associated period
3. ...

Example:
User selects Date Range 1/1/2014 to 3/31/2014, and runs the report. Data returned should be:

Period             Loans           ........ More columns based on date range
01/2014           2
02/2014           3
03/2014           7

The solution to this was:
DECLARE @start_date date --or datetime
DECLARE @end_date date --or datetime

SET @start_date =  '20140101'
SET @end_date = '20140331'

SELECT 
    DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn),0) AS CreateMonth,
    COUNT(*) AS Loans,
    ...
FROM tablename PFL
WHERE
    PFL.CreatedOn >= @start_date AND
    PFL.CreatedOn < DATEADD(DAY, 1, @end_date)
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0)

Open in new window


I now need to create a subquery to return values where DateField in subquery is greater than the "CreateMonth" date calculated (above) in the outer query. The problem is that the subquery does not see this date because it's in the PFL table, and at the moment the only way to make it see this value is to re-create it again in the subquery -- but that will require JOINing to the PFL table and others in the process. The subquery is trying to return data that has absolutely nothing to do with the PFL table in the outer query.

Any recommendations?

My approach:

Requirement: Calculate total fees paid during the associated period
* Paid refers to the DatePaid column in the Fees table.
* Associated period refers to the CreateMonth calculated date field in the outer query.

DECLARE @start_date date --or datetime
DECLARE @end_date date --or datetime

SET @start_date =  '20140101'
SET @end_date = '20140331'

SELECT 
    DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn),0) AS CreateMonth,
    COUNT(*) AS Loans,
    ...  ,
    SUM(RF.Amount)
    
FROM tablename PFL
LEFT JOIN 
		(
			SELECT Fees.CustomerAccountId, SUM(Fees.Amount) as Amount,
				   DATEADD(month, DATEDIFF(month,0,myPFL.CreatedOn),0) as monthStart
			FROM Fees 
				   LEFT JOIN ...
				   LEFT JOIN ...
				   LEFT JOIN tablename myPFL ON ...
			WHERE  Fees.DatePaid >= DATEADD(month, DATEDIFF(month,0,myPFL.CreatedOn),0)	--monthStart
			
			GROUP BY Fees.CustomerAccountId,
				   DATEADD(month, DATEDIFF(month,0,myPFL.CreatedOn),0)
					 
		) AS RF ON RF.CustomerAccountId = CA.PartyId and
			 RF.monthStart = DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn),0)
			 
WHERE
    PFL.CreatedOn >= @start_date AND
    PFL.CreatedOn < DATEADD(DAY, 1, @end_date)
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0)

Open in new window


Did I have to join to the myPFL table in the subquery just to get the monthStart date?
Would it be the same effect if I didn't join to the myPFL table in the subquery, and instead did:
FROM tablename PFL
LEFT JOIN 
		(
			SELECT Fees.CustomerAccountId, SUM(Fees.Amount) as Amount,
				   DATEADD(month, DATEDIFF(month,0,Fees.DatePaid),0) as monthStart
			FROM Fees 
			GROUP BY Fees.CustomerAccountId,
				   DATEADD(month, DATEDIFF(month,0,Fees.DatePaid),0)
					 
		) AS RF ON RF.CustomerAccountId = CA.PartyId and
			 RF.monthStart >= DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn),0)

Open in new window


Basically, I think what this does is removes the WHERE clause from the subquery, and replaces it with the JOIN connection to the outer query. Am I correct to assume that?
0
Comment
Question by:pzozulka
2 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40262249
I would simply repeat the date range logic inside the subquery against the relevant field.

You can imply the date range through join conditions but it's more definite (and more easily understood IMHO) if you are explicit.
FROM tablename PFL
LEFT JOIN 
		(
			SELECT Fees.CustomerAccountId, SUM(Fees.Amount) as Amount,
				   DATEADD(month, DATEDIFF(month,0,myPFL.CreatedOn),0) as monthStart
			FROM Fees 
				   LEFT JOIN ...
				   LEFT JOIN ...
				   LEFT JOIN tablename myPFL ON ...
            WHERE
                Fees.DatePaid >= @start_date AND
                Fees.DatePaid < DATEADD(DAY, 1, @end_date)
			
			GROUP BY Fees.CustomerAccountId,
				   DATEADD(month, DATEDIFF(month,0,myPFL.CreatedOn),0)
					 
		) AS RF ON RF.CustomerAccountId = CA.PartyId and
			 RF.monthStart = DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn),0)

Open in new window

It isn't necessary to left join everything inside the subquery, basically treat it like an outer query while you are inside the parentheses.

My question is, could you have payments that do not match to creation of loans
e.g. no new loans in a month, but payments still happen for pre-existing loans

If the answer is yes you can't use a left join.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 40266477
It's like Paul says. Repeat the date range logic inside the subquery against the relevant field, which now is Fees.DatePaid and you can get statistics belonging to the same Period/Month into the same record by an FULL OUTER join based on the months.

Bye, Olaf.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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