pzozulka
asked on
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:
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.
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:
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?
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)
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)
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)
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Bye, Olaf.