Tuesday, 6 August 2013

displaying SQL results on structured webpage

displaying SQL results on structured webpage

OK, got a bit of a conundrum here.
I've got an SQL query that i wan't to display on a webpage. The problem I
have is getting it to display the results in the format that is required.
It's essentially to be a weekly schedule of jobs, split into days, and
grouped by each installation team. The number of jobs per day per team can
vary from none to 3 or 4. The SQL query pull of the job data from the base
in a big list (as i would expect) - no problems there.
The conundrum is getting it to display on the webpage in the specific
format - which is this
https://dl.dropboxusercontent.com/u/1499080/installs.JPG
The webserver is a IIS windows server, but has PHP installed, so can use
ASP/VB or PHP.
This is the query.
select Pers_Name, PER_Data7, Call_Ref, dbo.dateonly(Scheduled_Date_Time)
as sched_date, Link_to_Contract_Header, Add1, Add2, Post_Code,
Call_Type_Description, LUCFC_Description, Call_Status_Description
from Calls with (nolock)
inner join Clients with (nolock) on Link_to_Client=Client_Ref
left join Personnel with (nolock) on Last_Allocated_To=Pers_Ref
left join LU_Call_Types with (nolock) on Call_Type=Call_Type_Code
left join Personnel_More with (nolock) on
Last_Allocated_To=PER_Link_to_Pers_Ref
left join LU_Call_Fault_codes with (nolock) on
call_fault_code_1=LUCFC_Code
left join LU_Call_Status with (nolock) on
Last_Event_Status=Call_Status_Code
where dbo.dateonly(Scheduled_Date_Time) between '5 August 2013' and '11
August 2013'
and Call_Type in ('BC','IN')
and PER_Data7 is not NULL
order by sched_date, PER_Data7
"PER_Data7" is the field that holds the team name (ie Team 1, Team 2 etc).
"sched_date" is the date of the job.
So.... how to tackle this?
I have a working version that displays just a 1 day schedule, but the
structure for that is completely different and very basic, and will be
junked.

No comments:

Post a Comment