Workstation Inventory Report (Need assistance)
I have two custom reports we put together using MySQL. We have migrated to MSSQL and I need assistance with converting the two reports.
The first one is a Workstation inventory report. I am able to convert the first part of the report but I am unable to get the Inventory totals (Union portion) converted properly.
Workstation Inventory Report in MySQL:
select si.manufacturer 'Make', si.model 'Model', count(if(rss.statedesc='In Use',1,null)) 'In Use', count(if(rss.statedesc='In Store',1,null)) 'In Store', count(if(rss.statedesc='In Repair',1,null)) 'In Repair', count(if(rss.statedesc='Expired',1,null)) 'Expired', count(if(rss.statedesc='Disposed',1,null)) 'Disposed', count(si.model) 'Total per Model' from systeminfo si left join resourceowner ro on si.workstationid=ro.resourceid left join resources rs on si.workstationid=rs.resourceid left join resourcestate rss on rs.resourcestateid=rss.resourcestateid group by si.model union select 'TOTAL INVENTORY' as ti,'.' as t2,count(if(rss.statedesc='In Use',1,null)) 'In Use', count(if(rss.statedesc='In Store',1,null)) 'In Store', count(if(rss.statedesc='In Repair',1,null)) 'In Repair',count(if(rss.statedesc='Expired',1,null)) 'Expired', count(if(rss.statedesc='Disposed',1,null)) 'Disposed', count(si.model) from systeminfo si left join resourceowner ro on si.workstationid=ro.resourceid left join resources rs on si.workstationid=rs.resourceid left join resourcestate rss on rs.resourcestateid=rss.resourcestateid group by ti
Workstation Inventory Summary
Model In Use In Store In Repair Expired Disposed Total per Model
Dell Latitude 600 10 2 0 0 0 12
Lenovo T400 25 3 1 0 0 28
TOTAL INVENTORY 35 5 1 0 0 40
The second report is Requests by Technician and Department: Total Hours Spent and Total Request Count for Closed Requests for previous month. The primary thing throwing me off is the time conversions. I have several reports similar to this. If someone can show me the proper way to code this for MSSQL it would help a lot.
select aa.first_name 'Technician', dp.deptname 'Department', concat(floor(((sum(rc.mm2completerequest)/1000)/3600)),':',lpad(floor((((sum(rc.mm2completerequest)/1000)/3600)-floor(((sum(rc.mm2completerequest)/1000)/3600)))*60),2,'00'),':',lpad(floor((((((sum(rc.mm2completerequest)/1000)/3600)-floor(((sum(rc.mm2completerequest)/1000)/3600)))*60)-Floor((((sum(rc.mm2completerequest)/1000)/3600)-floor(((sum(rc.mm2completerequest)/1000)/3600)))*60))*60),2,'00')) 'Total Hours Spent', concat(floor(((avg(rc.mm2completerequest)/1000)/3600)),':',lpad(floor((((avg(rc.mm2completerequest)/1000)/3600)-floor(((avg(rc.mm2completerequest)/1000)/3600)))*60),2,'00'),':',lpad(floor((((((avg(rc.mm2completerequest)/1000)/3600)-floor(((avg(rc.mm2completerequest)/1000)/3600)))*60)-Floor((((avg(rc.mm2completerequest)/1000)/3600)-floor(((avg(rc.mm2completerequest)/1000)/3600)))*60))*60),2,'00')) 'Avg Time per Request', count(wos.workorderid) 'Number of Requests' from workorder wo left join workorderstates wos on wo.workorderid=wos.workorderid left join requestcharges rc on wo.workorderid=rc.workorderid left join departmentdefinition dp on wo.deptid=dp.deptid Left join workorderhistory woh on wo.workorderid=woh.workorderid left join aaauser aa on woh.operationownerid=aa.user_id where dp.deptname is not null and wos.statusid=3 and woh.operation='CLOSE' and wo.completedtime>=<from_lastmonth> AND wo.completedtime<=<to_lastmonth> GROUP BY aa.first_name, dp.deptname
Currently this is how the report looks.
Technician Department Total Hours Spent Average Spent Number of Requests
John Doe Operations 2:15:00 0:45:00 3
Accounting 9:00:00 1:30:00 6
Facilities 1:00:00 0:20:00 5
Jane Lowe Operations 0:30:00 0:30:00 1
Human Resources 1:15:00 0:37:30 2
Ideally I would also want to add a summary total for each technician and a report total for total hours spent, average time spent per request and total number of requests. Any help would be appreciated.