Assistance with SQL query

[former_member459477]

Hi Experts

I am trying to get query to return list of customer visits due to be made (due date from UDF in CRD1) alongside last date this customer was visited (last visit date should be most recent date taken from OCLG.Recontact where activities is of 'Visit to Customer' type.

This query is causing error.

I would be grateful for help to make it work

Regards,

Karen

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue, MAX(T2.Recontact) AS 'Last Visit Date'

FROM  OCRD T0 INNER JOIN CRD1 T1 on T1.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <0

INNER JOIN OCLG T2 on T2.CardCode=T0.CardCode where T2.CntctType='Visit to Customer'

FOR BROWSE

[Former Member · Accepted solution]

Hi

you cant write "where" before the inner join and you have the "where" twice

try this:

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue, MAX(T2.Recontact) AS 'Last Visit Date'

FROM  OCRD T0 INNER JOIN CRD1 T1 on T1.CardCode=T0.CardCode
INNER JOIN OCLG T2 on T2.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <0

and  T2.CntctType='Visit to Customer'

FOR BROWSE

shachar

[former_member459477 · Accepted solution]

Hi shachar

Thanks for help!

This is now working but problem is customers who have never been visited (where no Activity of 'Visit to Customer' Type exists) are being excluded by Query.

I would like all Customers who have a Visit Due date in CRD1 to be in query result even if no previous visit Activity exists .... in such cases 'Last Visit Date' should be empty field in results.

is this possible?

Regards,

Karen

--Customer visits due - due date for next standard visit due from UDF in Address also bringing in Last visit date from Visit Activity IF any such Visit Activities exist

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue, MAX(T2.Recontact) AS 'Last Visit Date'

FROM  OCRD T0 INNER JOIN CRD1 T1 on T1.CardCode=T0.CardCode

INNER JOIN OCLG T2 on T2.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <0

and  T2.CntctType=21

GROUP BY T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue

[Johan_H · Accepted solution]

Hi Andrew,

Please change "INNER JOIN OCLG" to "LEFT OUTER JOIN OCLG"

Regards,

Johan

[former_member459477 · Accepted solution]

Hi Johan,

thanks for suggestion.

I tried making the change but unfortunately query is still not returning customers where no previous visit Activity exists.

Regards,

Karen

--Customer visits due - due date for next standard visit due from UDF in Address also bringing in Last visit date from Visit Activity IF any such Visit Activities exist

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue, MAX(T2.Recontact) AS 'Last Visit Date'

FROM  OCRD T0 INNER JOIN CRD1 T1 on T1.CardCode=T0.CardCode

LEFT OUTER JOIN OCLG T2 on T2.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <0

and  T2.CntctType=21

GROUP BY T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue

[Johan_H · Accepted solution]

Hi Andrew,

Please also change "INNER JOIN CRD1" to "LEFT OUTER JOIN CRD1"

Regards,

Johan

[former_member459477 · Accepted solution]

Hi Johan,

unfortunately this still returning same result

--Customer visits due - due date for next standard visit due from UDF in Address also bringing in Last visit date from Visit Activity IF any such Visit Activities exist

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue, MAX(T2.Recontact) AS 'Last Visit Date'

FROM  OCRD T0 LEFT OUTER JOIN CRD1 T1 on T1.CardCode=T0.CardCode

LEFT OUTER JOIN OCLG T2 on T2.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <0

and  T2.CntctType=21

GROUP BY T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue

Regards,

Karen

[Johan_H · Accepted solution]

Hi Andrew,

Can T1.U_NxtVisitDue be null ?

Regards,

Johan

[former_member459477 · Accepted solution]

Hi Johan,

Yes it can be null

Regards,

karen

[Johan_H · Accepted solution]

Ok, could you please change "DateDiff(dd,GetDate(), T1.U_NxtVisitDue)" to "DateDiff(dd,GetDate(), ISNULL(T1.U_NxtVisitDue, GETDATE()))"

[Former Member · Accepted solution]

Hi ,

Try:

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue, IsNUll(SELECT MAX(T2.Recontact) FROM OCLG T2 WHERE T2.CardCode=T0.CardCode and T2.CntctType=21),'') AS 'Last Visit Date'

FROM  OCRD T0

LEFT JOIN CRD1 T1 on T1.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <0

GROUP BY T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue

Thanks,

Gordon

[former_member459477 · Accepted solution]

Hi Johan,

Thanks for assistance

I think this is still unsuccessful. Customers who have visit due date in T1.U_NxtVisitDue are not in result if there is no previous Visit Activity

If I get your edit right - I have:

--Customer visits due - due date for next standard visit due from UDF in Address also bringing in Last visit date from Visit Activity IF any such Visit Activities exist

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue, MAX(T2.Recontact) AS 'Last Visit Date'

FROM  OCRD T0 LEFT OUTER JOIN CRD1 T1 on T1.CardCode=T0.CardCode

LEFT OUTER JOIN OCLG T2 on T2.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), ISNULL(T1.U_NxtVisitDue, GETDATE()))<0

and  T2.CntctType=21

GROUP BY T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue

[Johan_H · Accepted solution]

Hi Andrew,

In your query I see nothing pertaining to previous visit activity. There appears to be only the one field T1.U_NxtVisitDue.

In its current form, your query should be getting all customer names from OCRD, regardless of whether there are any pertinent records in CRD1 or OCLG. Is this correct ?

If not, could you please turn all the joins around? What I mean is, where it reads "on T1.CardCode=T0.CardCode", change it to "on T0.CardCode=T1.CardCode", and "on T2.CardCode=T0.CardCode" to "on T0.CardCode=T2.CardCode"

Regards,

Johan

[former_member459477 · Accepted solution]

Hi Gordon,

I think this is working with small edit.

Before taking out "ISNULL" query is causing errors - my knowledge insufficient to understand why.

When I take out ISNULL and ,'') which appear at end of 2nd select statement then the result appear correct (with limited testing) -

1. All customers with a date entry <0 in CRD1.U_NxtVisitDue seem to be included in result

2. Those included customers which have an activity of CntctType=21 have an entry in that column, otherwise blank

--Customer visits due - due date for next standard visit due from UDF in Address also bringing in Last visit date from Visit Activity IF any such Visit Activities exist otherwise blank field

SELECT T0.[CardName], T1.Address, T1.U_Territory, T1.U_NxtVisitDue, T1.U_LastVisit, (SELECT MAX(T2.Recontact) FROM OCLG T2 WHERE T2.CardCode=T0.CardCode and T2.CntctType=21) AS 'Last Visit date' FROM  OCRD T0

LEFT JOIN CRD1 T1 on T1.CardCode=T0.CardCode

WHERE DateDiff(dd,GetDate(), T1.U_NxtVisitDue) <0

If you see any problem not apparent to me from limited testing please advise.

Thank you for your help

Regards

Karen

[former_member459477 · Accepted solution]

Hi Johan,

My problem query was only returning customers where there is an entry in CRD1.U_NxtVisitDue AND where there is an Activity Type 21 in existance.

I have not yet sufficient knowledge of types of JOIN to fully understand below solution.

Thanks for your assistance

Regards,

Karen