chandan m
asked on
SQL Query Task
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.[Canc
from debtor as d
join client as c
on C.PK_Client=D.Client
where c.LogonID in('acs', 'acs2') and (d.Client like 'chw%')
group by c.LogonID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How to calculate for the same task, the new month field and average the new calculated days field.
for the above query can you get me new month field and average calculated days field.
for the above query can you get me new month field and average calculated days field.
ASKER
Hi Please find the attachment of the query where you can have look at example, Below is the description for the task.
The ACS and ACS2 records are completely different – how we know it is the same account is the CD is the same and the Report Name on the client table is the same. We want to see the difference in days between the Cancel date on the ACS record and the SetupDate on the ACS2 record. In the below case they are the same so it’s zero.
So you’ll want to isolate the ACS records in one query, and the ACS2 records in a different one and then join those on Cd and Report Name, and then do the calculation to get number of days.
Please write me a query to get the output .
Capture.PNG
The ACS and ACS2 records are completely different – how we know it is the same account is the CD is the same and the Report Name on the client table is the same. We want to see the difference in days between the Cancel date on the ACS record and the SetupDate on the ACS2 record. In the below case they are the same so it’s zero.
So you’ll want to isolate the ACS records in one query, and the ACS2 records in a different one and then join those on Cd and Report Name, and then do the calculation to get number of days.
Please write me a query to get the output .
Capture.PNG
ASKER
Please write me query, how to join the two querys and rendering different debtor no using CD and calculating the difference between canceldate and setupdate.
I'm not sure what the key fields are. Can you show an example of what output would look like?
Had a bit of confusion as to which fields were in which tables. The fields were specified in different tables in prior comments than they were in your last comment.
I added more "test" data to see if this is what you are looking for.
I added more "test" data to see if this is what you are looking for.
Create Table #debtor
(
DebtorNo varchar(20),
client varchar(20),
SetupDate datetime,
CancelDate datetime,
CD varchar(30),
LoginID varchar(10)
)
Create Table #client
(
PK_Client varchar(20),
ReportName varchar(30)
)
INSERT INTO #debtor (DebtorNo,client,CD,SetupDate,CancelDate,LoginID) values ('9920786','cha','MT4400420502','2016-01-08','2016-01-08','acs2')
INSERT INTO #debtor (DebtorNo,client,CD,SetupDate,CancelDate,LoginID) values ('31411298','chb','MT4400420502','2016-01-08','2016-01-08','acs')
INSERT INTO #debtor (DebtorNo,client,CD,SetupDate,CancelDate,LoginID) values ('40835451','chc','MT2200420503','2016-01-08','2016-01-08','acs2')
INSERT INTO #debtor (DebtorNo,client,CD,SetupDate,CancelDate,LoginID) values ('67835053','chd','MT2200420503','2016-01-08','2016-01-09','acs')
INSERT INTO #client (PK_Client,ReportName) values ('cha','Mark Twain Medical Center')
INSERT INTO #client (PK_Client,ReportName) values ('chb','Mark Twain Medical Center')
INSERT INTO #client (PK_Client,ReportName) values ('chc','Robert Frost Emergency Care')
INSERT INTO #client (PK_Client,ReportName) values ('chd','Robert Frost Emergency Care')
select G1.DebtorNo,G1.ReportName,G1.CD,G1.SetupDate,G2.CancelDate,DATEDIFF(DAY,G1.SetupDate,G2.CancelDate) as DaysDiff FROM
(
select a.debtorno,b.Reportname,a.cd,a.Setupdate,a.loginid
from #Debtor a join #client b
on a.Client = b.PK_Client
Where a.Client like 'CH%'
and a.SetupDate >= '2016-01-01'
and a.LoginID like 'ACS2'
) G1 Join
(
select a.debtorno,b.Reportname,a.cd,a.CancelDate,a.loginid
from #Debtor a join #client b
on a.Client = b.PK_Client
Where a.Client like 'CH%'
and a.LoginID like 'ACS'
) G2 on G1.ReportName = G2.ReportName and G1.CD = G2.CD
ASKER
Hi
For the same query i need to Exclude anything over 10 days from the average.
How to add this in query. Please write me query
For the same query i need to Exclude anything over 10 days from the average.
How to add this in query. Please write me query
ASKER
How to fetch the data whose average daysdiff is less than or equal to 10 in the above query.
ASKER
Hey Thank you Jeff i got it ....
ASKER