Writing Query to change the way that the data is presented

[Mark Braithwaite]

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

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 the scheduled date that would appear in the spreadsheet.

Really appreciate anyone's assistance.

Many Thanks
Mark

[iburyak]

Try this:

SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivityParty where activityid = Activity2 AND participationtypemask = 11) partyidname1,
(select scheduledstart from FilteredServiceAppointment where activityid = Activity2) scheduledstart1,
(select scheduledend from FilteredServiceAppointment where activityid = Activity2) scheduledend1,

(select partyidname from FilteredActivityParty where activityid = Activity1 AND Activity2 <> Activity1 AND participationtypemask = 11) partyidname2,
(select scheduledstart from FilteredServiceAppointment where activityid = Activity1 AND Activity2 <> Activity1) scheduledstart2,
(select scheduledend from FilteredServiceAppointment where activityid = Activity1 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 name

Good Luck.

[Mark Braithwaite]

Try this:

SELECT name, new_modelno, new_serialno, new_type, new_make, new_mast, new_capacity,
(select partyidname from FilteredActivityParty where activityid = Activity2 AND participationtypemask = 11) partyidname1,
(select scheduledstart from FilteredServiceAppointment where activityid = Activity2) scheduledstart1,
(select scheduledend from FilteredServiceAppointment where activityid = Activity2) scheduledend1,

(select partyidname from FilteredActivityParty where activityid = Activity1 AND Activity2 <> Activity1 AND participationtypemask = 11) partyidname2,
(select scheduledstart from FilteredServiceAppointment where activityid = Activity1 AND Activity2 <> Activity1) scheduledstart2,
(select scheduledend from FilteredServiceAppointment where activityid = Activity1 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 name

Good Luck.

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

[iburyak]

I am not sure what did you do with varchar(255)
To get over this problem you should give me sample of your data in both tables and desired result.

I will be away starting next week so you have 2 days to finish this.
Give me as much data as possible and as many different cases as possible and results you want to get in each case. It is a complicated query and without testing in on life data I don't think I can help you further.
If you want, you can export everything into Excel or Access file and attach to your Message.

Thank you.

[Mark Braithwaite]

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 activityid = Activity2) scheduledstart1 ,
(select scheduledend from FilteredService Appointment where activityid = Activity2) scheduledend1,
(select partyidname from FilteredActivit yParty where activityid = Activity1 AND Activity2 <> Activity1 AND participationty pemask = 11) partyidname2,
(select scheduledstart from FilteredService Appointment where activityid = Activity1 AND Activity2 <> Activity1) scheduledstart2 ,
(select scheduledend from FilteredService Appointment where activityid = Activity1 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 char255))servic eappointment.ac tivityid) Activity1,
max(convert(var char255))servic eappointment.ac tivityid) 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 = 3)
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

I have added the convert(varchar (255)) to avoid the minimum / maximum error aggregate error message which appears if this is not added.

Please find herewith a sample of the data that we receive after inserting the query.

Customer 1 sch start1 sch end1 Customer 2 sch start2 sch end2
ABC 09/10/2009 09/10/2011 Integrated 04/05/2007 18/10/2008
Walkers 06/05/2005 09/04/2006 Pack 15/04/2006 11/05/2007
Smith 11/12/2005 15/03/2006 Brown 11/12/2006 08/09/2007
James 05/06/2006 08/04/2007 Marine 04/06/2007 05/05/2008
RC 10/10/2006 08/06/2007 Wright 07/08/2007 07/06/2008
Associate17/07/2008 18/09/200 ABC 04/11/2005 06/07/2006

The incorrect fields relate to the customer ABC on the first and last line. As you can see the columns Scheduled Start 1 and Scheduled Start 2 need to be sorted correctly.

The Scheduled Start 1 column should include all the activities which were scheduled in the past and Scheduled Start 2 should include all the activities that have been scheduled for the future.

To be more correct, Customer 1, Scheduled Start 1, Scheduled End 1 should all include the past activities and Customer 2, Scheduled Start 2, Scheduled End 2 should include the future activities.

At the moment everything is displayed randomly with the correct information though. It seems like that if a future activity is created before the activity which occurred in the past then this activity will be displayed first.

Thanks so much for all your help

[iburyak]

I need not a result but the data that you have in tables and column definitions (means datatypes) for following tables:

FilteredEquipme nt
FilteredActivit yParty

Result could be incorrect for example if you have 90 and 10000 in numeric column then 1000 is grater then 90 but if the same values are in character columns then 90 grater then 10000 because character '9' is grater then character '1'.

Thank you.

[Mark Braithwaite]

I need not a result but the data that you have in tables and column definitions (means datatypes) for following tables:

FilteredEquipme nt
FilteredActivit yParty

Result could be incorrect for example if you have 90 and 10000 in numeric column then 1000 is grater then 90 but if the same values are in character columns then 90 grater then 10000 because character '9' is grater then character '1'.

Thank you.

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

[iburyak]

In your query you join 3 tables from database.

FROM [B]FilteredEquipment[/B] AS equipment 
INNER JOIN [B]FilteredActivityParty[/B] AS activityparty ON equipment.equipmentid = partyid 
INNER JOIN [B]FilteredServiceAppointment [/B] AS serviceappointment ON serviceappointment.activityid = activityparty.activityid

I thought to recriate the same tables with data in them on my server and try correcting SQL statment with your data. Unfortunately i don't see how i can help you any further without seeing data itself and having the same environment that you do.


This statement is totally incorrect. Don't you get an error message when you execute query with it? I am not sure what you were trying to do here.

min(convert(var char255))servic eappointment.ac tivit yid) Activity1,
max(convert(var char255))servic eappointment.ac tivit yid) Activity2

[Mark Braithwaite]

In your query you join 3 tables from database.

FROM [B]FilteredEquipment[/B] AS equipment 
INNER JOIN [B]FilteredActivityParty[/B] AS activityparty ON equipment.equipmentid = partyid 
INNER JOIN [B]FilteredServiceAppointment [/B] AS serviceappointment ON serviceappointment.activityid = activityparty.activityid

I thought to recriate the same tables with data in them on my server and try correcting SQL statment with your data. Unfortunately i don't see how i can help you any further without seeing data itself and having the same environment that you do.


This statement is totally incorrect. Don't you get an error message when you execute query with it? I am not sure what you were trying to do here.

min(convert(var char255))servic eappointment.ac tivit yid) Activity1,
max(convert(var char255))servic eappointment.ac tivit yid) Activity2

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 is quite hard to achieve.

Thanks for all your help.
Mark

[iburyak]

This is why you are getting random dates because GUID is not consecutive ID it is no guaranty that latest GUID will be always grater then earliest the only guaranty you have with GUID is that it is unique and MAX and MIN here will not work.

[iburyak]

It is very hard to write query of this complexity blindfolded... :(

Try this:

Select a.*, 
case when a.scheduledstart = b.scheduledstart then '' else b.partyidname end,
case when a.scheduledstart = b.scheduledstart then '' else b.scheduledstart end,
case when a.scheduledstart = b.scheduledstart then '' else b.scheduledend end
FROM (
	SELECT equipment.name , 
	equipment.new_modelno, 
	equipment.new_serialno , 
	equipment.new_type , 
	equipment.new_make, 
	equipment.new_mast, 
	equipment.new_capacity, 
	
	activitypartycustomer.partyidname, 
	serviceappointment.scheduledstart, 
	serviceappointment.scheduledend 
	
	FROM FilteredEquipment AS equipment 
	INNER JOIN FilteredActivityParty AS activityparty ON equipment.equipmentid = partyid 
	INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
	INNER JOIN FilteredActivityParty AS activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid AND 
	                                                             activitypartycustomer.participationtypemask = 11 
	WHERE (serviceappointment.statecode = 0) 
	and serviceappointment.scheduledstart = (select max(serviceappointment.scheduledstart)
						 FROM FilteredEquipment AS e 
						 INNER JOIN FilteredActivityParty AS activityparty ON e.equipmentid = partyid 
						 INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
						 INNER JOIN FilteredActivityParty AS activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid AND 
						                                                             activitypartycustomer.participationtypemask = 11
                                                 where e.name = equipment.name 
	                                         and e.new_modelno = equipment.new_modelno 
	                                         and e.new_serialno = equipment.new_serialno  
	                                         and e.new_type = equipment.new_type  
	                                         and e.new_make = equipment.new_make
	                                         and e.new_mast = equipment.new_mast 
	                                         and e.new_capacity = equipment.new_capacity
	                                         )
      ) a
JOIN (

	SELECT equipment.name , 
	equipment.new_modelno, 
	equipment.new_serialno , 
	equipment.new_type , 
	equipment.new_make, 
	equipment.new_mast, 
	equipment.new_capacity, 
	
	activitypartycustomer.partyidname, 
	serviceappointment.scheduledstart, 
	serviceappointment.scheduledend 
	
	FROM FilteredEquipment AS equipment 
	INNER JOIN FilteredActivityParty AS activityparty ON equipment.equipmentid = partyid 
	INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
	INNER JOIN FilteredActivityParty AS activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid AND 
	                                                             activitypartycustomer.participationtypemask = 11 
	WHERE (serviceappointment.statecode = 0) 
	and serviceappointment.scheduledstart = (select min(serviceappointment.scheduledstart)
						 FROM FilteredEquipment AS e 
						 INNER JOIN FilteredActivityParty AS activityparty ON e.equipmentid = partyid 
						 INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
						 INNER JOIN FilteredActivityParty AS activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid AND 
						                                                             activitypartycustomer.participationtypemask = 11
                                                 where e.name = equipment.name 
	                                         and e.new_modelno = equipment.new_modelno 
	                                         and e.new_serialno = equipment.new_serialno  
	                                         and e.new_type = equipment.new_type  
	                                         and e.new_make = equipment.new_make
	                                         and e.new_mast = equipment.new_mast 
	                                         and e.new_capacity = equipment.new_capacity
	                                         )
     ) b on a.name = b.name and a.new_modelno = b.new_modelno and a.new_serialno = b.new_serialno and a.new_type = b.new_type and
            a.new_make = b.new_make and a.new_mast = b.new_mast and a.new_capacity = b.new_capacity
ORDER BY equipment.name

[Mark Braithwaite]

It is very hard to write query of this complexity blindfolded... :(

Try this:

Select a.*, 
case when a.scheduledstart = b.scheduledstart then '' else b.partyidname end,
case when a.scheduledstart = b.scheduledstart then '' else b.scheduledstart end,
case when a.scheduledstart = b.scheduledstart then '' else b.scheduledend end
FROM (
	SELECT equipment.name , 
	equipment.new_modelno, 
	equipment.new_serialno , 
	equipment.new_type , 
	equipment.new_make, 
	equipment.new_mast, 
	equipment.new_capacity, 
	
	activitypartycustomer.partyidname, 
	serviceappointment.scheduledstart, 
	serviceappointment.scheduledend 
	
	FROM FilteredEquipment AS equipment 
	INNER JOIN FilteredActivityParty AS activityparty ON equipment.equipmentid = partyid 
	INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
	INNER JOIN FilteredActivityParty AS activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid AND 
	                                                             activitypartycustomer.participationtypemask = 11 
	WHERE (serviceappointment.statecode = 0) 
	and serviceappointment.scheduledstart = (select max(serviceappointment.scheduledstart)
						 FROM FilteredEquipment AS e 
						 INNER JOIN FilteredActivityParty AS activityparty ON e.equipmentid = partyid 
						 INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
						 INNER JOIN FilteredActivityParty AS activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid AND 
						                                                             activitypartycustomer.participationtypemask = 11
                                                 where e.name = equipment.name 
	                                         and e.new_modelno = equipment.new_modelno 
	                                         and e.new_serialno = equipment.new_serialno  
	                                         and e.new_type = equipment.new_type  
	                                         and e.new_make = equipment.new_make
	                                         and e.new_mast = equipment.new_mast 
	                                         and e.new_capacity = equipment.new_capacity
	                                         )
      ) a
JOIN (

	SELECT equipment.name , 
	equipment.new_modelno, 
	equipment.new_serialno , 
	equipment.new_type , 
	equipment.new_make, 
	equipment.new_mast, 
	equipment.new_capacity, 
	
	activitypartycustomer.partyidname, 
	serviceappointment.scheduledstart, 
	serviceappointment.scheduledend 
	
	FROM FilteredEquipment AS equipment 
	INNER JOIN FilteredActivityParty AS activityparty ON equipment.equipmentid = partyid 
	INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
	INNER JOIN FilteredActivityParty AS activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid AND 
	                                                             activitypartycustomer.participationtypemask = 11 
	WHERE (serviceappointment.statecode = 0) 
	and serviceappointment.scheduledstart = (select min(serviceappointment.scheduledstart)
						 FROM FilteredEquipment AS e 
						 INNER JOIN FilteredActivityParty AS activityparty ON e.equipmentid = partyid 
						 INNER JOIN FilteredServiceAppointment AS serviceappointment ON serviceappointment.activityid = activityparty.activityid 
						 INNER JOIN FilteredActivityParty AS activitypartycustomer ON serviceappointment.activityid = activitypartycustomer.activityid AND 
						                                                             activitypartycustomer.participationtypemask = 11
                                                 where e.name = equipment.name 
	                                         and e.new_modelno = equipment.new_modelno 
	                                         and e.new_serialno = equipment.new_serialno  
	                                         and e.new_type = equipment.new_type  
	                                         and e.new_make = equipment.new_make
	                                         and e.new_mast = equipment.new_mast 
	                                         and e.new_capacity = equipment.new_capacity
	                                         )
     ) b on a.name = b.name and a.new_modelno = b.new_modelno and a.new_serialno = b.new_serialno and a.new_type = b.new_type and
            a.new_make = b.new_make and a.new_mast = b.new_mast and a.new_capacity = b.new_capacity
ORDER BY equipment.name

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

[Mark Braithwaite]

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

[Mark Braithwaite]

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

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