SQL Query Help

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?
LVL 8
pzozulkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.