We help IT Professionals succeed at work.

SQL Query Task

chandan m
chandan m asked
on
141 Views
Last Modified: 2018-01-17

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
Comment
Watch Question

Developer Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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
Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
ok so given this sample data I have the tables setup incorrectly.

sample
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.

Author

Commented:
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.
Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
I'm not sure what the key fields are.  Can you show an example of what output would look like?
Jeff DarlingDeveloper Analyst
CERTIFIED EXPERT

Commented:
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


sample output

Author

Commented:
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

Author

Commented:
How to fetch the data whose average daysdiff is less than or equal to 10 in the above query.

Author

Commented:
Hey Thank you Jeff i got it ....

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.