Advanced Find Queries SQL

(0) Share

Report

Posted on by Brookie

Morning All,

We are currently 2 weeks away from a migration from an on prem legacy CRM to Dynamics365, to that end a number of reports that are currently SQL scripts need to be ported and for the most part i can rebuild these using the Advanced Find query builder and save as views. However a view are a little more complex and as i dont know SQL i was looking for a pointer to the best solution.

The query has a statement like the below where we are trying to target a Serial Number that has communication in the last 720 days where the source code is like "AXTX1..."

select distinct serialnumber from communication where

( SOURCECODE LIKE 'AWTX1_-54/003' AND DATEOFCOMMUNICATION >= GETDATE() -720)

OR (SOURCECODE LIKE 'AWTX1_-54/004' AND DATEOFCOMMUNICATION >= GETDATE() -720)

While i can specify the Source Code in advanced find how can i pick up if this contact received the comms in last 720 days?

Thanks in Advance

All responses (2)

Answers (0)

Brookie 15 on at

Like (1)

Report

RE: Advanced Find Queries SQL

brilliant, thats the ticket, helped me a load as i need to construct a loads of these style queries over the next 2 weeks. As my query was -720 days i used Last X years and set to 2
Suggested answer

Pedro Cadavez de Fr... on at

Like (2)

Report

RE: Advanced Find Queries SQL

Hello Brookie,

Hope you are well.

On advanced find (checked v9.0.17 onprem and CRM online), you have options on date fields around "older than X" days/weeks etc.

It seems we have a limitation on using 500 days / 100 weeks / 100 months

Maybe you could use older than 24 months = 730 days or 100 weeks == 700days

But using fetchXML it seems you don't have that limitation

Let me know if this is valuable or not, or if it tottally misses the context of the questions!

Thank you!