CODEWITHSUNDEEP
mysqlsqlsql-server
Jay H.
Jay H.

Reputation: 33

SQL - Query same column twice with different dates in where clause

I have tried searching all over for answers but none have answered my exact issue. I have what should be a relatively simple query. However, I am very new and still learning SQL.
I need to query two columns with different dates. I want to return rows with the current number of accounts and current outstanding balance and in the same query, return rows for the same columns with data 90 days prior. This way, we can see how much the number of accounts and balance increased over the past 90 days. Optimally, I am looking for results like this:

PropCode|PropCat|Accts|AcctBal|PriorAccts|PriorBal|
----------------------------------------------------
 77     |Comm   | 350 | 1,000|  275      | 750

Below is my starting query. I realize it's completely wrong but I have tried numerous different solution attempts but none seem to work for my specific problem. I included it to give an idea of my needs. The accts & AcctBal columns would contain the 1/31/14 data. The PriorAcct & PriorBal columns would contain the 10/31/13 data.

select 
prop_code AS PropCode,
prop_cat, 
COUNT(act_num) Accts,
SUM(act_bal) AcctBal,

(SELECT 
COUNT(act_num) 
FROM table1 
where date = '10/31/13'
and Pro_Group in ('BB','FF') 
and prop_cat not in ('retail', 'personal') 
and Not (Acct_Code = 53 and ACTType in (1,2,3,4,5,6,7)) 
) 
AS PriorAccts,

(SELECT 
SUM(act_bal) 
FROM table1 
where date = '10/31/13'
and Pro_Group in ('BB','FF') 
and prop_cat not in ('retail', 'personal') 
and Not (Acct_Code = 53 and ACTType in (1,2,3,4,5,6,7)) 
) 
AS PriorBal


from table1
where date = '01/31/14'
and Pro_Group in ('BB','FF') 
and prop_cat not in ('retail', 'personal') 
and Not (Acct_Code = 53 and ACTType in (1,2,3,4,5,6,7)) 
group by prop_code, prop_cat
order by prop_cat

Upvotes: 3

Views: 2858

Answers (1)

JNK
JNK

Reputation: 65177

You can use a CASE with aggregates for this (at least in SQL Server, not sure about MySQL):

...
COUNT(CASE WHEN date='1/31/14' THEN act_num ELSE NULL END) as 'Accts'
,SUM(CASE WHEN date='1/31/14' THEN act_bal ELSE NULL END) as 'AcctBal'
,COUNT(CASE WHEN date='10/31/13' THEN act_num ELSE NULL END) as 'PriorAccts'
,SUM(CASE WHEN date='10/31/13' THEN act_bal ELSE NULL END) as 'PriorAcctBal'
....
WHERE Date IN ('1/31/14', '10/31/13')

Upvotes: 3

Related Questions