Writing Query to change the way that the data is presented

[Mark Braithwaite]

Hi

We are attempting to create a dynamic spreadsheet which will extract information from CRM / SQL.

We have very limited expierence in creating SQL queries and we would really appreciate any assistance. We had some assistance in creating an initial query which looked as follows:

SELECT equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity,
activitypartycu stomer.partyidn ame,
serviceappointm ent.scheduledst art,
serviceappointm ent.scheduleden d
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty
ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS
serviceappointm ent
ON serviceappointm ent.activityid =
activityparty.a ctivityid
INNER JOIN FilteredActivit yParty AS activitypartycu stomer
ON serviceappointm ent.activityid =
activitypartycu stomer.activity id AND
activitypartycu stomer.particip ationtypemask = 11
WHERE (serviceappoint ment.statecode = 0)
ORDER BY equipment.name

This query gave us exactly the information we wanted however we noticed that a resource which has been scheduled more than once appears twice on the report on a second row.

We would prefer for the resource to be listed once and the additonal service activity details to appear in additional columns on the report.

For example, we would have equipment name, equipment model, equipment make, equipment serial number, customer name for service activity 1, dates for service activity 1, customer name for service activity 2 and the dates for service activity 2. All these fields would appear as columns and the full detail would not be repeated in a second row.

For example
Equipment Name Equipment SerialNo Customer Start Date End Date
E15 3232 ABC Shoes 15-01-2007 30-06-2007
E15 3232 ABC Shoes 01-08-2007 30-04-2007
AS

EquipName EquipSerialNo Cust Start Date End Date Cust Start Date End Date
E15,3232,ABC Shoes,5-01-2007,30-06-2007,ABC Shoes,1-08-2007,30-04-2007

Is this possible to achieve? Can we edit the query in some way to give us
the required information?

Really appreciate any assistance. .

Thanks alot
Mark

[azimmer]

Hi

We are attempting to create a dynamic spreadsheet which will extract information from CRM / SQL.

We have very limited expierence in creating SQL queries and we would really appreciate any assistance. We had some assistance in creating an initial query which looked as follows:

SELECT equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity,
activitypartycu stomer.partyidn ame,
serviceappointm ent.scheduledst art,
serviceappointm ent.scheduleden d
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty
ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS
serviceappointm ent
ON serviceappointm ent.activityid =
activityparty.a ctivityid
INNER JOIN FilteredActivit yParty AS activitypartycu stomer
ON serviceappointm ent.activityid =
activitypartycu stomer.activity id AND
activitypartycu stomer.particip ationtypemask = 11
WHERE (serviceappoint ment.statecode = 0)
ORDER BY equipment.name

This query gave us exactly the information we wanted however we noticed that a resource which has been scheduled more than once appears twice on the report on a second row.

We would prefer for the resource to be listed once and the additonal service activity details to appear in additional columns on the report.

For example, we would have equipment name, equipment model, equipment make, equipment serial number, customer name for service activity 1, dates for service activity 1, customer name for service activity 2 and the dates for service activity 2. All these fields would appear as columns and the full detail would not be repeated in a second row.

For example
Equipment Name Equipment SerialNo Customer Start Date End Date
E15 3232 ABC Shoes 15-01-2007 30-06-2007
E15 3232 ABC Shoes 01-08-2007 30-04-2007
AS

EquipName EquipSerialNo Cust Start Date End Date Cust Start Date End Date
E15,3232,ABC Shoes,5-01-2007,30-06-2007,ABC Shoes,1-08-2007,30-04-2007

Is this possible to achieve? Can we edit the query in some way to give us
the required information?

Really appreciate any assistance. .

Thanks alot
Mark

Yes it is but far from trivial. If you use SQL Server 2005 you can use the PIVOT functionality ( http://technet.microso ft.com/en-us/library/ms177410.aspx ); either way the following page is worth reading: http://www.mssqltips.c om/tip.asp?tip=937

[Mark Braithwaite]

Yes it is but far from trivial. If you use SQL Server 2005 you can use the PIVOT functionality ( http://technet.microso ft.com/en-us/library/ms177410.aspx ); either way the following page is worth reading: http://www.mssqltips.c om/tip.asp?tip=937

Hi

Thanks alot for your reply. As least I have an idea as to how this can be done. We have very limited experience in writing SQL Queries and it seems as though we have to write quite a complex query to get the results we need. I have written the following query:

SELECT <equipment.name >
partyidname AS Customer
scheduledstart AS Scheduled Start
scheduledend AS Scheduled End
FROM
(equipment.name ) AS Sourcetable
FROM
SELECT equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity,
activitypartycu stomer.partyidn ame,
serviceappointm ent.scheduledst art,
serviceappointm ent.scheduleden d
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty
ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS
serviceappointm ent
ON serviceappointm ent.activityid =
activityparty.a ctivityid
INNER JOIN FilteredActivit yParty AS activitypartycu stomer ON
serviceappointm ent.activityid =
activitypartycu stomer.activity id AND
activitypartycu stomer.particip ationtypemask = 11
AS Sourcetable
PIVOT
(equipment.name )
IN (partyidname,
scheduledstart,
scheduledend)
AS
Sourcetable

I will be testing this query shortly and I very much doubt that it is going to work one hundered percent.

Is there anything that you notice immediately that is wrong with this query.

Thanks

[iburyak]

Hello I don’t give you a guaranty that it would work because I have no way of testing this query. Also this query would work only if you have not more than 2 activities because with assumption of two I go for first selecting MIN and last selecting MAX which is not the same as MIN nothing in between will be available in this type of query and if you think that you have cases where you have more then 2 customers stick to your original query otherwise it will need serious programming like looping through the data and pivoting it.

[PHP]

SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivit yParty where activityid = Activity1 AND activitypartycu stomer.particip ationtypemask = 11) partyidname1,
(select scheduledstart from FilteredService Appointment where activityid = Activity1) scheduledstart1 ,
(select scheduledend from FilteredService Appointment where activityid = Activity1) scheduledend1,

(select partyidname from FilteredActivit yParty where activityid = Activity2 AND Activity2 <> Activity1 AND activitypartycu stomer.particip ationtypemask = 11) partyidname2,
(select scheduledstart from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledstart2 ,
(select scheduledend from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledend2,
FROM (
SELECT equipment.name name ,
equipment.new_m odelno new_modelno,
equipment.new_s erialno new_serialno,
equipment.new_t ype new_type,
equipment.new_m ake new_make,
equipment.new_m ast new_mast,
equipment.new_c apacity new_capacity,
min(serviceappo intment.activit yid) Activity1,
max(serviceappo intment.activit yid) Activity2
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS serviceappointm ent ON serviceappointm ent.activityid = activityparty.a ctivityid
WHERE (serviceappoint ment.statecode = 0)
group by equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity) a

ORDER BY equipment.name [/PHP]

Good Luck.

[Mark Braithwaite]

Hello I don’t give you a guaranty that it would work because I have no way of testing this query. Also this query would work only if you have not more than 2 activities because with assumption of two I go for first selecting MIN and last selecting MAX which is not the same as MIN nothing in between will be available in this type of query and if you think that you have cases where you have more then 2 customers stick to your original query otherwise it will need serious programming like looping through the data and pivoting it.

[PHP]

SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivit yParty where activityid = Activity1 AND activitypartycu stomer.particip ationtypemask = 11) partyidname1,
(select scheduledstart from FilteredService Appointment where activityid = Activity1) scheduledstart1 ,
(select scheduledend from FilteredService Appointment where activityid = Activity1) scheduledend1,

(select partyidname from FilteredActivit yParty where activityid = Activity2 AND Activity2 <> Activity1 AND activitypartycu stomer.particip ationtypemask = 11) partyidname2,
(select scheduledstart from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledstart2 ,
(select scheduledend from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledend2,
FROM (
SELECT equipment.name name ,
equipment.new_m odelno new_modelno,
equipment.new_s erialno new_serialno,
equipment.new_t ype new_type,
equipment.new_m ake new_make,
equipment.new_m ast new_mast,
equipment.new_c apacity new_capacity,
min(serviceappo intment.activit yid) Activity1,
max(serviceappo intment.activit yid) Activity2
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS serviceappointm ent ON serviceappointm ent.activityid = activityparty.a ctivityid
WHERE (serviceappoint ment.statecode = 0)
group by equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity) a

ORDER BY equipment.name [/PHP]

Good Luck.

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 and we received the following error message. Incorrect Syntax near the keyword 'FROM'
Incorrect Syntax near 'a'
Statement(s) could not be prepared.

I removed the 'a' from the end of the query as I thought this may have been inserted incorrrectly and we received the following error: Incorrect Syntax near the keyword 'FROM'.

If you have any ideas, please let us know.

Thanks again

[Mark Braithwaite]

Hello I don’t give you a guaranty that it would work because I have no way of testing this query. Also this query would work only if you have not more than 2 activities because with assumption of two I go for first selecting MIN and last selecting MAX which is not the same as MIN nothing in between will be available in this type of query and if you think that you have cases where you have more then 2 customers stick to your original query otherwise it will need serious programming like looping through the data and pivoting it.

[PHP]

SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivit yParty where activityid = Activity1 AND activitypartycu stomer.particip ationtypemask = 11) partyidname1,
(select scheduledstart from FilteredService Appointment where activityid = Activity1) scheduledstart1 ,
(select scheduledend from FilteredService Appointment where activityid = Activity1) scheduledend1,

(select partyidname from FilteredActivit yParty where activityid = Activity2 AND Activity2 <> Activity1 AND activitypartycu stomer.particip ationtypemask = 11) partyidname2,
(select scheduledstart from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledstart2 ,
(select scheduledend from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledend2,
FROM (
SELECT equipment.name name ,
equipment.new_m odelno new_modelno,
equipment.new_s erialno new_serialno,
equipment.new_t ype new_type,
equipment.new_m ake new_make,
equipment.new_m ast new_mast,
equipment.new_c apacity new_capacity,
min(serviceappo intment.activit yid) Activity1,
max(serviceappo intment.activit yid) Activity2
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS serviceappointm ent ON serviceappointm ent.activityid = activityparty.a ctivityid
WHERE (serviceappoint ment.statecode = 0)
group by equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity) a

ORDER BY equipment.name [/PHP]

Good Luck.

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 and we received the following error message. Incorrect Syntax near the keyword 'FROM'
Incorrect Syntax near 'a'
Statement(s) could not be prepared.

I removed the 'a' from the end of the query as I thought this may have been inserted incorrrectly and we received the following error: Incorrect Syntax near the keyword 'FROM'.

If you have any ideas, please let us know.

Thanks again

[iburyak]

Try this:

SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivityParty where activityid = Activity1 AND activitypartycustomer.participationtypemask = 11) partyidname1,
(select scheduledstart from FilteredServiceAppointment where activityid = Activity1) scheduledstart1,
(select scheduledend from FilteredServiceAppointment where activityid = Activity1) scheduledend1,
 
(select partyidname from FilteredActivityParty where activityid = Activity2 AND Activity2 <> Activity1 AND activitypartycustomer.participationtypemask = 11) partyidname2,
(select scheduledstart from FilteredServiceAppointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledstart2,
(select scheduledend from FilteredServiceAppointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledend2
FROM (
    SELECT equipment.name name , 
    equipment.new_modelno new_modelno, 
    equipment.new_serialno new_serialno, 
    equipment.new_type new_type, 
    equipment.new_make new_make, 
    equipment.new_mast new_mast, 
    equipment.new_capacity new_capacity, 
    min(serviceappointment.activityid) Activity1,
    max(serviceappointment.activityid) Activity2
    FROM FilteredEquipment AS equipment 
    INNER JOIN FilteredActivityParty AS activityparty ON equipment.equipmentid = partyid 
    INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
    WHERE (serviceappointment.statecode = 0) 
        group by equipment.name , 
             equipment.new_modelno, 
             equipment.new_serialno , 
             equipment.new_type , 
             equipment.new_make, 
             equipment.new_mast, 
             equipment.new_capacity) a
 
ORDER BY equipment.name

[Mark Braithwaite]

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.

Really appreciate your help.

Thanks

[iburyak]

Try this:


SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivit yParty where activityid = Activity1 AND participationty pemask = 11) partyidname1,
(select scheduledstart from FilteredService Appointment where activityid = Activity1) scheduledstart1 ,
(select scheduledend from FilteredService Appointment where activityid = Activity1) scheduledend1,

(select partyidname from FilteredActivit yParty where activityid = Activity2 AND Activity2 <> Activity1 AND participationty pemask = 11) partyidname2,
(select scheduledstart from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledstart2 ,
(select scheduledend from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledend2
FROM (
SELECT equipment.name name ,
equipment.new_m odelno new_modelno,
equipment.new_s erialno new_serialno,
equipment.new_t ype new_type,
equipment.new_m ake new_make,
equipment.new_m ast new_mast,
equipment.new_c apacity new_capacity,
min(serviceappo intment.activit yid) Activity1,
max(serviceappo intment.activit yid) Activity2
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS serviceappointm ent ON serviceappointm ent.activityid = activityparty.a ctivityid
WHERE (serviceappoint ment.statecode = 0)
group by equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity) a

ORDER BY name

[Mark Braithwaite]

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 again

[iburyak]

It is a data-type of a column FilteredActivit yParty.activity id I guess. It is when you put some universal alphanumeric value which is hard to read or query.
I am not sure how to overcome this.
Let me think during the day on what to do with this problem.
People with little knowledge or just no intention of querying data put such fancy things in a database but then it makes it extremely hard to work with.

In a meanwhile try this:

SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivit yParty where activityid = Activity1 AND participationty pemask = 11) partyidname1,
(select scheduledstart from FilteredService Appointment where activityid = Activity1) scheduledstart1 ,
(select scheduledend from FilteredService Appointment where activityid = Activity1) scheduledend1,

(select partyidname from FilteredActivit yParty where activityid = Activity2 AND Activity2 <> Activity1 AND participationty pemask = 11) partyidname2,
(select scheduledstart from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledstart2 ,
(select scheduledend from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledend2
FROM (
SELECT equipment.name name ,
equipment.new_m odelno new_modelno,
equipment.new_s erialno new_serialno,
equipment.new_t ype new_type,
equipment.new_m ake new_make,
equipment.new_m ast new_mast,
equipment.new_c apacity new_capacity,
min(convert(var char(255), serviceappointm ent.activityid) ) Activity1,
max(convert(var char(255), serviceappointm ent.activityid) ) Activity2
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS serviceappointm ent ON serviceappointm ent.activityid = activityparty.a ctivityid
WHERE (serviceappoint ment.statecode = 0)
group by equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity) a

ORDER BY name

[Mark Braithwaite]

It is a data-type of a column FilteredActivit yParty.activity id I guess. It is when you put some universal alphanumeric value which is hard to read or query.
I am not sure how to overcome this.
Let me think during the day on what to do with this problem.
People with little knowledge or just no intention of querying data put such fancy things in a database but then it makes it extremely hard to work with.

In a meanwhile try this:

SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivit yParty where activityid = Activity1 AND participationty pemask = 11) partyidname1,
(select scheduledstart from FilteredService Appointment where activityid = Activity1) scheduledstart1 ,
(select scheduledend from FilteredService Appointment where activityid = Activity1) scheduledend1,

(select partyidname from FilteredActivit yParty where activityid = Activity2 AND Activity2 <> Activity1 AND participationty pemask = 11) partyidname2,
(select scheduledstart from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledstart2 ,
(select scheduledend from FilteredService Appointment where activityid = Activity2 AND Activity2 <> Activity1) scheduledend2
FROM (
SELECT equipment.name name ,
equipment.new_m odelno new_modelno,
equipment.new_s erialno new_serialno,
equipment.new_t ype new_type,
equipment.new_m ake new_make,
equipment.new_m ast new_mast,
equipment.new_c apacity new_capacity,
min(convert(var char(255), serviceappointm ent.activityid) ) Activity1,
max(convert(var char(255), serviceappointm ent.activityid) ) Activity2
FROM FilteredEquipme nt AS equipment
INNER JOIN FilteredActivit yParty AS activityparty ON equipment.equip mentid = partyid
INNER JOIN FilteredService Appointment AS serviceappointm ent ON serviceappointm ent.activityid = activityparty.a ctivityid
WHERE (serviceappoint ment.statecode = 0)
group by equipment.name ,
equipment.new_m odelno,
equipment.new_s erialno ,
equipment.new_t ype ,
equipment.new_m ake,
equipment.new_m ast,
equipment.new_c apacity) a

ORDER BY name

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 been scheduled for a third time ie. A third service activity has been created.

Is there way to display only the service activities / service appointments that have a status of 'Open'. I assume this will create a WHERE statement(servi ceappointment.s tatecode = 1).

I will test this. Thanks so much for all your help. If there any limitations, please let me know. Thanks alot.

[iburyak]

Unfortunately I have no knowledge of your business and values in columns.
I just used whatever it was in original query so I wouldn't break the logic.

You should test carefully and compare with your original report to see if you get results you desire.

Sorry, no help from me here.

Irina.

[Mark Braithwaite]

Unfortunately I have no knowledge of your business and values in columns.
I just used whatever it was in original query so I wouldn't break the logic.

You should test carefully and compare with your original report to see if you get results you desire.

Sorry, no help from me here.

Irina.

Hi

No problem, Thanks anyways for all your help

Thanks again

[Mark Braithwaite]

Hi

No problem, Thanks anyways for all your help

Thanks again

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 for your help. Really appreciate your assistance.

Thanks
Mark