SQL Subquery status for specific week added to aggregate

Question

I have spent several hours trying to solve this and come up with a lot of frustration and no results. I am a newbie to this but willing to learn. I am using SQLiteStudio

Data:

I have a table with a years worth of rental data.
There is an entry per week for customers who have a tenancy (some tenancies started and or ended within the year so may appear less than 52 times
Each weeks data is imported from a different report into this table and is differentiated by the report name (field: ReportName)
Customers can change statuses from CUR (current) to TER (terminate = 4 weeks notice) to FOR (former)
Each customer has a unique tenancy reference number (field: TenancyRef)

My Query:

So this query works fine/great:

SELECT TenancyRef,
PropertyType,
TenancyType,
ManagementArea,
count() AS NumberEntries,
Round(Sum(Payments), 2) AS TotalPaid,
Round(Sum(HBUCAmount), 2) AS TotalHB,
Round(Sum(DebitAmount), 2) AS DebitTotal,
Round(Round(Sum(DebitAmount), 2) - Round(Sum(HBUCAmount), 2), 2) AS mySubtraction
FROM PSEData15
WHERE PropertyType != "LOCK" AND
PropertyType != "GP"
GROUP BY TenancyRef
HAVING TotalPaid = 0 AND
DebitTotal > TotalHB
ORDER BY TenancyRef DESC

It gives me a list of just under 900 references where those customers have paid £0.

If I run this query separately:

SELECT TenancyRef, Status, ClosingBal
FROM PSEData15
WHERE (ReportName = "PSE-W201552030416" OR ReportName = "PSE-F201552030416") AND
Status != "FOR" AND
PropertyType != "LOCK" AND
PropertyType != "GP" AND
ClosingBal > 0

It give me all the current customers in the last week and their closing balance (3667 of them)

Aim:

Essentially what I am looking for is the closing balance and wk52 status to be added to the first query. Any help will be greatly appreciated. thank you in advance

Edit1:

I was asked for sample data and a desired result. Simplifying the table my sample would be:

TenancyRef | Status | Payments | Closing Bal | ReportName
1          | CUR    | 0.00     | 10.00       | 2015-Wk49
1          | CUR    | 0.00     | 20.00       | 2015-Wk50
1          | CUR    | 0.00     | 30.00       | 2015-Wk51
1          | CUR    | 0.00     | 40.00       | 2015-Wk52
2          | CUR    | 10.00    | 20.00       | 2015-Wk49
2          | CUR    | 10.00    | 20.00       | 2015-Wk50
2          | TER    | 10.00    | 20.00       | 2015-Wk51
2          | FOR    | 10.00    | 20.00       | 2015-Wk52
3          | CUR    | 10.00    | 20.00       | 2015-Wk49
3          | TER    | 10.00    | 20.00       | 2015-Wk50
3          | FOR    | 10.00    | 20.00       | 2015-Wk51

Desired Result:

TenancyRef | Count  | TotalPaid | Wk52_ClosingBal | Wk52_status
1          | 4      | 0.00      | 40.00           | CUR
2          | 4      | 40.00     | 20.00           | FOR
3          | 3      | 30.00     | 20.00           | FOR

Thanks CL. I have done as you asked hopefully that makes my question more clear now! — Mark, May 16 '16 at 20:10
Does everyone have a Wk52 report status? In your sample, tenant #3 does not. — Kyle Hale, May 16 '16 at 20:16

score 1 · Accepted Answer · answered May 16 '16 at 20:20

If everyone has a Week52 report row, it's pretty simple, just add

max(case when reportname = '2015-Wk52' then ClosingBal else null end) as Week52ClosingBal,
max(case when reportname = '2015-Wk52' then Status else null end) as Week52Status

to your first query.

If, on the other hand, you need to determine the last status for a customer (ie not all of them have a Week52 report):

Write a query which returns the last reportname that you want (given your filters) for each tenancyref.
Join that query to your first query on tenancyref.
Change the above conditional aggregates to max(case when reportname = joinedTable.reportname ... )

SQL Subquery status for specific week added to aggregate

1 Answers1