Hi guys,
RIght now i am using 3 CSV files ( appointment, Person and Referral ) to calculate what i have called as KPI4, KPI5 and KPI6.
I import them into an Access Database that run some SQL queries. I want to automate this proces a bit and create a measure for these KPI's and just upload/update the 3 csv files and the POWER BI report just get updated.
For example for KPI4 I have the following SQL's queries
First and the main one but which reffers to z_KPI8_part1 :
SELECT Person.LPTID, Person.ccgName, Person.Caseload, t1.FTDate, t1.SERVICEID, Person.LTCondition, Referral.Caseload, *
FROM Person INNER JOIN ((SELECT SERVICEID,FTDate
FROM z_KPI8_part1) AS t1 INNER JOIN Referral ON t1.SERVICEID = Referral.SERVICEID) ON Person.LPTID = Referral.LPTID;
z_KPI8_part1 which referrs to z_FirstTreatmentDate :
SELECT z_FirstTreatmentDate.SERVICEID, Min(z_FirstTreatmentDate.FTDate) AS FTDate, Max(Appointment.ATTENDANCE) AS ATTENDANCE
FROM Appointment INNER JOIN z_FirstTreatmentDate ON (Appointment.SERVICEID = z_FirstTreatmentDate.SERVICEID) AND (Appointment.Appointment = z_FirstTreatmentDate.FTDate)
WHERE (((z_FirstTreatmentDate.FTDate) Between [forms]![frmMain]![txtFTDateFrom] And [forms]![frmMain]![txtFTDateTo]))
GROUP BY z_FirstTreatmentDate.SERVICEID;
z_FirstTreatmentDate :
SELECT Appointment.SERVICEID, Min(Appointment.APPOINTMENT) AS FTDate, Min(Appointment.APPTYPE) AS APPTYPE
FROM (SELECT *
FROM Appointment
WHERE (Appointment.APPTYPE=02 OR Appointment.APPTYPE=03 OR Appointment.APPTYPE=05)
AND (Appointment.ATTENDANCE='5' OR Appointment.ATTENDANCE='6')
) AS [%$##@_Alias]
GROUP BY Appointment.SERVICEID;
In theory KPI4 is First Appointment Date when client's Session Type ( APPTYPE )is one of the following: 2,3,5
And
- Attendance Status is one of the following: 5 and 6.
From my method i am not getting anywhere near the numbers i get from Access database.
Please help me or guide me
