jeudi 7 juillet 2016

SQL: calculate value based on two different conditions

Below is my table schema:- Appointments

--------------------------------------
| schID | appointment_date | amount | location |
--------------------------------------

I want to fire a single query where I can get the sum of amount, total appointment_date this year i.e 2016 and remaining appointment_date this year i.e 2016.

So I wrote the below query to calculate the above fields:-

SELECT sum(a.amount) as total, count(distinct a.appointment_date) as total_appointment, 
count(distinct a2.appointment_date) as remaining appointments from Appointments a LEFT 
JOIN Appointments a2 ON a.schID = a2.schID WHERE a2.appointment_date > GETDATE() AND year(a.appointment_date) = 2016
group by a.location

The above query doesnt return value as per requirement :(

The database belongs to SQL Server.

Aucun commentaire:

Enregistrer un commentaire