Hi Please can anybody help me with query i am stuck in middle please can any body write me a query,
In the tables they are separated by the LogonID field. In that field ACS is the first line of business, ACS2 is the second.
We want to find out the average number of days from when an account leaves the first line, and is set up in the second. The CancelDate is the date it leaves ACS and the SetupDate is the date it is set up in ACS2.
Debtor is joined to Client on Debtor.Client = Client.K_Client. I only need for Client field that starts with ‘abc%’
Isolate accounts in the first line of business. So Client starts with CHW, logonID is ACS and CancelDate is not null. Bring in the fields necessary along with ReportName from the Client table.
Isolate accounts in the second line of business. So Client starts with CHW, logonID is ACS2 and SetupDate is not null. Same as above bring in ReportName.
Join those two queries on Cd and ReportName, add a calculation to find the number of days from the ACS CancelDate and the ACS2 SetupDate. Plus add a new field to show the first day of the month based on CancelDate – that way we can average by month to see if the average has been getting bigger or smaller.
The last outer query will be to group it by the new month field and average the new calculated days field.
Below is the query i am workin on,
Select avg(DATEDIFF(MONTH,d.[CancelDate],c.SetupDate)) as MonthPosted
from debtor as d
join client as c
where c.LogonID in('acs', 'acs2') and (d.Client like 'chw%')
group by c.LogonID