Link to home
Start Free TrialLog in
Avatar of chandan m
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.[CancelDate],c.SetupDate)) as MonthPosted  
 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
Avatar of Jeff Darling
Jeff Darling
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of chandan m
chandan m

ASKER

It was really helpfull got some ideas, I am also waiting for the exact data if i get to know about it i would share with u for more queries, Thanks a lot.
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.
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
ok so given this sample data I have the tables setup incorrectly.

User generated image
It seems you have SetupDate and CancelDate in the same table, but for some reason, you don't update CancelDate on the ACS record, but only on the ACS2 record.
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.

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

Open in new window


User generated image
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
How to fetch the data whose average daysdiff is less than or equal to 10 in the above query.
Hey Thank you Jeff i got it ....