sql query formation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

sql query formation problem

Author

Topic

navbingo20
Starting Member

12 Posts

Posted - 2011-04-25 : 06:56:50

hi,

i have attached my sql query with this mail

SELECT Convert(varchar(10), hours) + ':' + Convert(varchar(10), mins) As total_time, total_minutes,machinename,description_name
FROM (
SELECT total_minutes / 60 As hours
, total_minutes % 60 As mins,total_minutes, machinename ,description_name
FROM (
SELECT Sum(DateDiff(mi, 0, act_hour_as_datetime)) As total_minutes, machinename ,description_name
FROM (
SELECT Convert(datetime, total_time) As act_hour_as_datetime, machinename ,description_name
FROM [ven_fullreportmaster] with(nolock)
INNER JOIN ven_descriptionmaster VDM ON VDM.description_id = ven_fullreportmaster.description_id
inner join ven_machinemaster vm on vm.machine_id = ven_fullreportmaster.machine_id
where entry_date = convert(varchar, getdate(), 105) and shift_type ='DAY_SHIFT' and is_task_completed ='Y'
) As derived_table group by machinename ,description_name
) As another_derived_table group by total_minutes, machinename ,description_name
) As yet_another_derived_table group by total_minutes, machinename,description_name,hours,mins

the out put looks like this :

total_time total_minutes machine_name description_name

6:0 300 ABC IDLE

4:0 240 DEF RUN

1:15 75 GHI DOWNTIME

2:00 120 ABC DOWNTIME

but i want to frame the table like this :

Machinename IDLE_TIME RUNTIME DOWN_TIME

ABC 6:0 0:0 2:00

DEF 0:0 4:0 0:0

GHI 0:0 0:0 1:15

could you plz help me out to solve this issue

thnx
navin

Serrano88
Starting Member

4 Posts

Posted - 2011-05-10 : 12:06:59

A format like the one I've written below might solve your problem, and make your code a bit more manageable. Of course I probably don't have your data sources correct, but I think you can update the queries to match your environment.

Essentially, I find it easiest to create a table and insert my key fields first. In this case that would be the machine names and ids. Then I update each field using the keys I've inserted. Hope this makes sense:

/* Create Production Table */
CREATE TABLE MachineProductionTime (
MachineId varchar(10),
Machine_Name varchar(3),
Idle_Time datetime,
Run_Time datetime,
Down_Time datetime
)

/* Insert Machine Names */
INSERT INTO MachineProductionTime
SELECT DISTINCT MachineId, Machine_Name
FROM ven_machinemaster

/* Update table with Idle Time data */
UPDATE MachineProductionTime
SET Idle_Time = Convert(varchar,total_time, 108)
FROM ven_fullreportmaster
JOIN ven_descriptionmaster ON ven_fullreportmaster.description_id = ven_descriptionmaster.description_id
WHERE MachineProductionTime.MachineId = ven_fullreportmaster.MachineId
AND ven_descriptionmaster.description_id = 'IDLE'

/* Update table with Run Time data */
UPDATE MachineProductionTime
SET Idle_Time = Convert(varchar,total_time, 108)
FROM ven_fullreportmaster
JOIN ven_descriptionmaster ON ven_fullreportmaster.description_id = ven_descriptionmaster.description_id
WHERE MachineProductionTime.MachineId = ven_fullreportmaster.MachineId
AND ven_descriptionmaster.description_id = 'RUN'

/* Update table with Down Time data */
UPDATE MachineProductionTime
SET Idle_Time = Convert(varchar,total_time, 108)
FROM ven_fullreportmaster
JOIN ven_descriptionmaster ON ven_fullreportmaster.description_id = ven_descriptionmaster.description_id
WHERE MachineProductionTime.MachineId = ven_fullreportmaster.MachineId
AND ven_descriptionmaster.description_id = 'DOWN'

---------------------------------------------------------------------
For SQL Contractor and Consulting Support:
http://www.thedatasolutioncenter.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources