set nocount on declare @seedDate datetime set @seedDate=dateadd(year,-3,getdate()) --dateadd(month,-6,getdate()) create table #reports (reportid int,callid int,meterreadinggroupid int) insert into #reports select reportid,callid,MeterReadingGroupID from screports r with (nolock) where r.createdate>=@seedDate and r.voidperiod is null and r.voidid is null select identity(int) as scid, rl.DetailID as LaborRecord, replace(replace(replace(isnull(cul.address,cu.address),',',''),char(13),' '),char(10),' ') as [Address], replace(replace(replace(bc.billcode+':'+bc.description,',',''),char(13),' '),char(10),' ') as BillCode, replace(replace(replace(ct.calltype,',',''),char(13),' '),char(10),' ') as CallType, replace(replace(replace(ct.category,',',''),char(13),' '),char(10),' ') as CallTypeCategory, replace(replace(replace(ct.description,',',''),char(13),' '),char(10),' ') as CallTypeDescription, replace(replace(replace(isnull(co.PrefFullName,c.Caller),',',''),char(13),' '),char(10),' ') as Contact, replace(replace(replace(cc.CancelCode,',',''),char(13),' '),char(10),' ') as CancelCode, replace(replace(replace(c.CancelDescription,',',''),char(13),' '),char(10),' ') as CancelDescription, replace(replace(replace(isnull(cul.city, cu.city),',',''),char(13),' '),char(10),' ') as City, c.CloseDate as CloseDate, replace(replace(replace(isnull(sc.contractnumber,''),',',''),char(13),' '),char(10),' ') as ContractNumber, c.date as CreateDate, replace(replace(replace(cu.customername,',',''),char(13),' '),char(10),' ') as Customer, replace(replace(replace(cu.address,',',''),char(13),' '),char(10),' ') as CustomerAddress, replace(replace(replace(cu.city,',',''),char(13),' '),char(10),' ') as CustomerCity, replace(replace(replace(cu.CustomerNumber,',',''),char(13),' '),char(10),' ') as CustomerNumber, replace(replace(replace(cu.State,',',''),char(13),' '),char(10),' ') as CustomerState, replace(replace(replace(cu.zip,',',''),char(13),' '),char(10),' ') as CustomerZip, (SELECT MIN(DispatchDate) from SCReports scr INNER JOIN SCReportLabors scl on scr.ReportID = scl.ReportID where scr.CallID = c.CallID and scr.voidperiod is null and scr.voidid is null) AS DispatchDate, c.ReqDate as DueDate, replace(replace(replace(cu.Email,',',''),char(13),' '),char(10),' ') as Email, (SELECT MAX(DepartureDate) from SCReports scr INNER JOIN SCReportLabors scl on scr.ReportID = scl.ReportID where scr.CallID = c.CallID and scr.voidperiod is null and scr.voidid is null) AS EndDate, replace(replace(replace(isnull(co.PrefFullName,eco.PrefFullName),',',''),char(13),' '),char(10),' ') as EquipmentContact, replace(replace(replace(isnull(co.phone1,eco.phone1),',',''),char(13),' '),char(10),' ') as EquipmentContactPhone, e.EquipmentNumber as EquipmentNumber, replace(replace(replace(hc.onholdcode,',',''),char(13),' '),char(10),' ') as HoldCode, replace(replace(replace(hc.Description,',',''),char(13),' '),char(10),' ') as HoldDescription, cast(' ' as varchar(3000)) as IncompleteCode, cast(' ' as varchar(3000)) as IncompleteCodeDescription, e.InstallDate as InstallDate, replace(replace(replace(isnull(cul.customername,cu.customername),',',''),char(13),' '),char(10),' ') as InstalledLocationName, replace(replace(replace(isnull(cul.customernumber,cu.customernumber),',',''),char(13),' '),char(10),' ') as InstalledLocationNumber, replace(replace(replace(c.description,',',''),char(13),' '),char(10),' ') as Issue, rl.DispatchDate as LaborRecordDispatchDate, rl.ArrivalDate as LaborRecordArrivalDate, rl.DepartureDate as LaborRecordDepartureDate, replace(replace(replace(ma.Make,',',''),char(13),' '),char(10),' ') as Make, cast(' ' as varchar(5000)) as MeterReadings, replace(replace(replace(m.Model,',',''),char(13),' '),char(10),' ') as Model, replace(replace(replace(cu.Phone1,',',''),char(13),' '),char(10),' ') as Phone, replace(replace(replace(cp.priority+':'+cp.description+':'+cast(cp.rank as varchar(10)),',',''),char(13),' '),char(10),' ') as [Priority], cast(' ' as varchar(3000)) as ProblemCodes, cast(' ' as varchar(3000)) as ResolutionCodes, e.serialnumber as SerialNumber, cast(' ' as varchar(3000)) as ServiceCoverage, c.EstStartDate as StartDate, replace(replace(replace(isnull(cul.state,cu.State),',',''),char(13),' '),char(10),' ') as [State], cs.description as [Status], replace(replace(replace(a.preffullname,',',''),char(13),' '),char(10),' ') as Technician, replace(replace(replace(cast(c.notes as varchar(8000)),',',''),char(13),' '),char(10),' ') as TechnicianClosingNotes, replace(replace(replace(a.AgentNumber,',',''),char(13),' '),char(10),' ') as TechnicianNumber, replace(replace(replace(et.territory,',',''),char(13),' '),char(10),' ') as TerritoryCode, replace(replace(replace(c.callnumber,',',''),char(13),' '),char(10),' ') as TicketNumber, replace(replace(replace(wo.workordernumber,',',''),char(13),' '),char(10),' ') as WorkOrderNumber, replace(replace(replace(isnull(cul.Zip,cu.Zip),',',''),char(13),' '),char(10),' ') as [Zip], r.reportid, r.MeterReadingGroupID into #schistory from #reports r inner join screportlabors rl with (nolock) on r.reportid=rl.reportid inner join sccalls c with (nolock) on r.callid=c.callid inner join scworkorders wo with (nolock) on c.workorderid=wo.workorderid inner join sccallstatustypes cs with (nolock) on c.status=cs.type left join sccalltypes ct with (nolock) on c.calltypeid=ct.calltypeid left join SCPriorities cp with (nolock) on cp.Priorityid=c.PriorityID inner join shagents a with (nolock) on c.TechnicianID=a.AgentID inner join scequipments e with (nolock) on e.equipmentid=c.equipmentid left join icmodels m with (nolock) on e.modelid=m.modelid left join scterritories et with (nolock) on et.territoryid=e.territoryid left join sccontracts sc with (nolock) on c.contractid=sc.contractid left join icmakes ma with (nolock) on ma.makeid=m.makeid left join scbillcodes bc with (nolock) on c.billcodeid=bc.billcodeid inner join arcustomers cu with (nolock) on c.customerid=cu.customerid left join arcustomers cul with (nolock) on e.locationid=cul.customerid left join cmcontacts co with (nolock) on c.CallerContactID=co.contactid left join cmcontacts eco with (nolock) on e.equipmentcontactid=eco.contactid left join sccancelcodes cc with (nolock) on c.cancelcodeid = cc.cancelcodeid left join ShOnHoldCodes hc with (nolock) on c.onholdcodeid = hc.onholdcodeid order by c.callnumber,rl.detailid update #schistory set problemCodes=rplist from #schistory inner join (select rp.reportid,STUFF((select '|'+replace(pc.problemcode,',','')+':'+replace(pc.description,',','') from screportproblemcodes p with (nolock) inner join scproblemcodes pc with (nolock) on p.problemcodeid=pc.problemcodeid where p.reportid=rp.reportid for XML PATH('')),1,1,'') rplist from screportproblemcodes rp with (nolock) inner join #schistory h on rp.ReportID=h.reportid group by rp.reportid) h2 on h2.reportid=#schistory.reportid update #schistory set resolutionCodes=rplist from #schistory inner join (select rp.reportid,STUFF((select '|'+replace(pc.RepairCode,',','')+':'+replace(pc.description,',','') from screportrepaircodes p with (nolock) inner join screpaircodes pc with (nolock) on p.repaircodeid=pc.repaircodeid where p.reportid=rp.reportid for XML PATH('')),1,1,'') rplist from screportrepaircodes rp with (nolock) inner join #schistory h on rp.ReportID=h.reportid group by rp.reportid) h2 on h2.reportid=#schistory.reportid DECLARE @SERVICE_hOURS VARCHAR(3000) SET @SERVICE_HOURS= Stuff((select '|'+Description+':'+cast(datepart(hour,openingtime) as varchar(2))+':'+right('0'+cast(datepart(MI,openingtime) as varchar(2)),2)+'-'+ cast(datepart(hour,closingtime) as varchar(2))+':'+right('0'+cast(datepart(MI,closingtime) as varchar(2)),2) from shservicehours with (nolock) for XML PATH('')),1,1,'') UPDATE #SCHISTory set servicecoverage=@service_hours update #schistory set meterReadings=mclist from #schistory inner join (select mr2.MeterReadingGroupID,STUFF((select '|'+replace(mt.metertype,',','')+':'+replace(mt.description,',','')+':'+case mt.issystemtype when 1 then 'Yes' else 'No' end+':'+replace(mtc.metertypecategory,',','')+':'+cast(cast(display as numeric(15,0)) as varchar(15)) from mtmeterreadings mr with (nolock) inner join mtmeters m with (nolock) on mr.meterid=m.meterid inner join mtmetertypes mt with (nolock) on m.metertypeid=mt.metertypeid inner join mtmetertypecategories mtc with (nolock) on mtc.MeterTypeCategoryID=mt.metertypecategoryid where mr.MeterReadingGroupID=mr2.meterreadinggroupid for XML PATH('')),1,1,'') mclist from mtmeterreadings mr2 with (nolock) inner join #schistory h on mr2.meterreadinggroupid=h.meterreadinggroupid group by mr2.meterreadinggroupid) h2 on h2.meterreadinggroupid=#schistory.MeterReadingGroupID update #schistory set IncompleteCode = ic.IncompleteCode, IncompleteCodeDescription = ic.Description from #schistory inner join screports sr on #schistory.reportid=sr.reportid inner join SCIncompleteCodes ic on ic.IncompleteCodeID = sr.IncompleteCodeID create table #schistoryoutput (scid int,LaborRecord varchar(8000), Address varchar(8000),BillCode varchar(8000),CallType varchar(8000),CallTypeCategory varchar(8000),CallTypeDescription varchar(8000), Contact varchar(8000),CancelCode varchar(8000),CancelDescription varchar(8000),City varchar(8000),CloseDate varchar(8000),ContractNumber varchar(8000),CreateDate varchar(8000), Customer varchar(8000),CustomerAddress varchar(8000),CustomerCity varchar(8000),CustomerNumber varchar(8000),CustomerState varchar(8000),CustomerZip varchar(8000),DispatchDate varchar(8000), DueDate varchar(8000),Email varchar(8000),EndDate varchar(8000),EquipmentContact varchar(8000),EquipmentContactPhone varchar(8000),EquipmentNumber varchar(8000),HoldCode varchar(8000), HoldDescription varchar(8000),IncompleteCode varchar(8000),IncompleteCodeDescription varchar(8000),InstallDate varchar(8000),InstalledLocationName varchar(8000),InstalledLocationNumber varchar(8000), Issue varchar(8000),LaborRecordDispatchDate varchar(8000),LaborRecordArrivalDate varchar(8000),LaborRecordDepartureDate varchar(8000),Make varchar(8000),MeterReadings varchar(8000),Model varchar(8000), Phone varchar(8000),Priority varchar(8000),ProblemCodes varchar(8000),ResolutionCodes varchar(8000),SerialNumber varchar(8000),ServiceCoverage varchar(8000),StartDate varchar(8000),State varchar(8000), Status varchar(8000),Technician varchar(8000),TechnicianClosingNotes varchar(8000),TechnicianNumber varchar(8000),TerritoryCode varchar(8000),TicketNumber varchar(8000),WorkOrderNumber varchar(8000), Zip varchar(8000),ReportID int,MeterReadingGroupID int) insert into #schistoryoutput (scid,LaborRecord,Address,BillCode,CallType,CallTypeCategory,CallTypeDescription, Contact,CancelCode,CancelDescription,City,CloseDate,ContractNumber,CreateDate, Customer,CustomerAddress,CustomerCity,CustomerNumber,CustomerState,CustomerZip,DispatchDate, DueDate,Email,EndDate,EquipmentContact,EquipmentContactPhone,EquipmentNumber,HoldCode, HoldDescription,IncompleteCode,IncompleteCodeDescription,InstallDate,InstalledLocationName,InstalledLocationNumber, Issue,LaborRecordDispatchDate,LaborRecordArrivalDate,LaborRecordDepartureDate,Make,MeterReadings,Model, Phone,Priority,ProblemCodes,ResolutionCodes,SerialNumber,ServiceCoverage,StartDate,State, Status,Technician,TechnicianClosingNotes,TechnicianNumber,TerritoryCode,TicketNumber,WorkOrderNumber,Zip) select 0,'LaborRecord','Address','BillCode','CallType','CallTypeCategory','CallTypeDescription', 'Contact','CancelCode','CancelDescription','City','CloseDate','ContractNumber','CreateDate', 'Customer','CustomerAddress','CustomerCity','CustomerNumber','CustomerState','CustomerZip','DispatchDate', 'DueDate','Email','EndDate','EquipmentContact','EquipmentContactPhone','EquipmentNumber','HoldCode', 'HoldDescription','IncompleteCode','IncompleteCodeDescription','InstallDate','InstalledLocationName','InstalledLocationNumber', 'Issue','LaborRecordDispatchDate','LaborRecordArrivalDate','LaborRecordDepartureDate','Make','MeterReadings','Model', 'Phone','Priority','ProblemCodes','ResolutionCodes','SerialNumber','ServiceCoverage','StartDate','State', 'Status','Technician','TechnicianClosingNotes','TechnicianNumber','TerritoryCode','TicketNumber','WorkOrderNumber','Zip' insert into #schistoryoutput select scid,LaborRecord,Address,BillCode,CallType,CallTypeCategory,CallTypeDescription, Contact,CancelCode,CancelDescription,City,CloseDate,ContractNumber,CreateDate, Customer,CustomerAddress,CustomerCity,CustomerNumber,CustomerState,CustomerZip,DispatchDate, DueDate,Email,EndDate,EquipmentContact,EquipmentContactPhone,EquipmentNumber,HoldCode, HoldDescription,IncompleteCode,IncompleteCodeDescription,InstallDate,InstalledLocationName,InstalledLocationNumber, Issue,LaborRecordDispatchDate,LaborRecordArrivalDate,LaborRecordDepartureDate,Make,MeterReadings,Model, Phone,Priority,ProblemCodes,ResolutionCodes,SerialNumber,ServiceCoverage,StartDate,State, Status,Technician,TechnicianClosingNotes,TechnicianNumber,TerritoryCode,TicketNumber,WorkOrderNumber,Zip,reportid,meterreadinggroupid from #schistory select Address,BillCode,CallType,CallTypeCategory,CallTypeDescription, Contact,CancelCode,CancelDescription,City,CloseDate,ContractNumber,CreateDate, Customer,CustomerAddress,CustomerCity,CustomerNumber,CustomerState,CustomerZip,DispatchDate, DueDate,Email,EndDate,EquipmentContact,EquipmentContactPhone,EquipmentNumber,HoldCode, HoldDescription,IncompleteCode,IncompleteCodeDescription,InstallDate,InstalledLocationName,InstalledLocationNumber, Issue,LaborRecord,LaborRecordDispatchDate,LaborRecordArrivalDate,LaborRecordDepartureDate,Make,MeterReadings,Model, Phone,Priority,ProblemCodes,ResolutionCodes,SerialNumber,ServiceCoverage,StartDate,State, Status,Technician,TechnicianClosingNotes,TechnicianNumber,TerritoryCode,TicketNumber,WorkOrderNumber,Zip From #schistoryoutput order by scid create table #scparts (LineID int identity(1,1),SerialNumber varchar(30),TicketNumber varchar(30),DealerItemNumber varchar(100),ItemDescription varchar(255),Quantity numeric(11,6),LineCost money) insert into #scparts (serialnumber,TicketNumber,dealeritemnumber,ItemDescription,quantity,linecost) select replace(e.SerialNumber,',','') as SerialNumber, replace(CallNumber,',','') as TicketNumber, replace(Item,',','') as DealerItemNumber, replace(cm.description,',','') as ItemDescription, Quantity, isnull(i.avgcost,0) as LineCost from #reports r inner join sccalls c with (nolock) on r.callid=c.callid inner join scequipments e with (nolock) on e.equipmentid=c.equipmentid inner join shagents a with (nolock) on c.technicianid=a.agentid left join icmodels m with (nolock) on e.modelid=m.modelid left join icmakes ma with (nolock) on ma.makeid=m.makeid inner join screportmaterials cm with (nolock) on cm.reportid=r.reportid inner join icitems i with (nolock) on cm.itemid=i.itemid order by callnumber create table #scpartsout (scid int,LineID varchar(8000),SerialNumber varchar(8000),TicketNumber varchar(8000),DealerItemNumber varchar(8000),ItemDescription varchar(8000), Quantity varchar(8000),LineCost varchar(8000)) insert into #scpartsout (scid,LineID,serialnumber,TicketNumber,dealeritemnumber,itemdescription,quantity,linecost) select 0,'LineID','SerialNumber','TicketNumber','DealerItemNumber','ItemDescription','Quantity','LineCost' insert into #scpartsout select LineID,* from #scparts select LineID,SerialNumber,TicketNumber,DealerItemNumber,ItemDescription,Quantity,LineCost From #scpartsout order by scid drop table #schistory drop table #scparts drop table #schistoryoutput drop table #scpartsout drop table #reports