Hi
I tried Left OUTER JOIN and now all cases are returned however for some reason a service activity is created for each case even if the service type equals Maintenance.
I just need assistance with this last bit.
Thanks
Mark...
Mark Braithwaite
Last Activity: Sep 29 '08, 02:24 PM
Joined: Oct 5 '07
Location:
-
Hi
Thanks very much for your help. I tried INNER JOIN ON serviceappointm entregardingobj ectid = (CRM_FilteredIn cident.incident id and serviceappointm ent.serviceidna me = Maintenance) and the results returned only the cases associated with service activities that are associated with the service type Maintenance.
This is excellent. Thanks so much for the bit of guidance that I needed. I just have one problem with my report....Leave a comment:
-
Custom Report
Hi
I am trying to create a custom report of Microsoft CRM data in Visual Studio.
I am extracting data from two different tables, an incident table and a service appointment / activity table. We trying to create a service report which we can send to our customers at the end of each month.
My query is:
SELECT CRMAF_FilteredI ncident .createdon
CRMAF_FilteredI ncident .customeridname... -
SQL Query Assistance
Hi
We are using Microsoft CRM and we have exported a list of service activities to a dynamic Excel worksheet. I have attempted to insert the name of customers that are related to service activities in the worksheet. When editing the SQL query I used the following script;
SELECT activitypartycu stomer.partyidn ame,
serviceappointm ent.subject ,
serviceappointm ent.scheduledst art ,... -
Joining Tables
Hi
We are trying to edit the SQL query in a dynamic excel spreadsheet which we extracted from CRM. We pull information for different entities and we need to join the tables. We have written the following script and we received an error stating 'Incorrect Column Expression:'Quo te.'.
SELECT quote.createdon ,
quote.customeri dname,
quote. customerid,... -
Hi
Is anyone able to assit with this? We desperate for a solution. Seems like we are almost there.
We will really appreciate any assistance that anyone is able to offer.
Thanks...Leave a comment:
-
Hi
We attempted to use the query you suggested and we received the following error message. Could not add the table '('
I did not realize that such a complex query would be required and I am unsure where to make the adjustment.
Thanks for all your help.
MarkLeave a comment:
-
Hi
I did not realize that this would require a query which is so complex. We attempted to use your query but we received an error stating, Could not add the table '('. We are unsure where an additional close or open bracket option is required.
I am sure this requires a simple solution.
Thanks for all you help.
Mark...Leave a comment:
-
Hi
Thanks for all your help. You must be quite frustrated. Thanks. I inserted the above statement after I received an error message stating that the minimum aggregate operation cannot take a unique indentifier data type as an argument.
This statement was your suggestion after I tested an earlier script that you wrote. You may find an explanation earlier in this post.
I am at a loss with this. I guess it...Leave a comment:
-
Hi
I am not 100% certain what you are asking for but the datatypes for FilteredEquipme nt are nvarchar fields. The FilteredActivit yParty which is the customers name is a uniqueidentifie r and FilteredService Appointment, the scheduled date fields are datetime types.
Do you need samples from SQL or samples of the Excel spreadsheets? Please let me know if there is anything else you need.
Thanks so much...Leave a comment:
-
Hi
Thanks alot for your assistance. Please find herewith a sample of the revised query that I have drafted from the information you sent me.
SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivit yParty where activityid = Activity2 AND participationty pemask = 11) partyidname1,
(select scheduledstart from FilteredService Appointment where...Leave a comment:
-
Hi
I adjusted your query to include convert varchar(255). The script did not seem to work 100%. At first all the future activities appeared in the second colum however I created a few additional activities in the system and refreshed the data. It appears that only a few of the past activities are appearing in the second column.
I am not sure why this is happening and I will test this again.
Thanks...Leave a comment:
-
Hi
We have since found that the activities appear in the order that they were created. We have attempted to edit the query where scheduledstart1 is greater then scheduledstart2 however this did not seem to work.
I suppose this is because it still appears in the order that it was created. I guess we need to add some sort of a condition. Would a CASE statement be the correct solution?
We need to reference...Leave a comment:
-
Hi
I just have one more question which I am sure you can assist me with. The report works perfectly however we would like to sort by date.
The last service activity should be appear in the first column and the most recent service activity should appear in the second column.
Is there a condition we can add to the WHERE Statement? I am sure this should be quite easy to achieve.
Thanks again...Leave a comment:
-
Hi
This worked perfectly! I would like to just clarify a few things with you before I test this in a live environment.
The report worked first time however there was no data in the report. I removed the WHERE statement and everything seemed to work.
Just want confirm with you. This report will only display Equipment that have been scheduled. Right? What problems or what may occur if the equipment has...Leave a comment:
-
Hi
I now get the following error message:
The minimum aggregate operation cannot take a unique identifier data type as an argument.
The maximum aggregate operation cannot take a unique identifier data type as an argument.
Any ideas? Thanks for all your help. Really appreciate it.
Thanks againLeave a comment:
-
Hi
I now receive the following error message,
The column prefix 'activitypartyc ustomer' does not match with a table name or alias name used in the query.
The column prefix 'equipment' does not match with a table name or alias name used in the query.
Statements could not be prepared.
I tested the query by using the query analyzer in SQL and I received an invalid object name message.
...Leave a comment:
-
Hi
Thanks so much for your help. As you can probably tell this type of query is what we consider to be quite complex and is way behind our scope. So we really appreciate your assistance.
There may be situations where we will have more than one service activity however there should never be more than two so this is fine. We will definitely have more than two customers.
I tested your Query in a virtual image...Leave a comment:
-
Hi
Thanks so much for your help. As you can probably tell this type of query is what we consider to be quite complex and is way behind our scope. So we really appreciate your assistance.
There may be situations where we will have more than one service activity however there should never be more than two so this is fine. We will definitely have more than two customers.
I tested your Query in a virtual image...Leave a comment:
No activity results to display
Leave a comment: