--------------PMS ----------------------Treatment wise length by all budget scenarios select L.RoadCode, L.LinkCode,L.Direction as 'Direction' , w.WRK_Code as treatment --,a.WORKS_NAME ,bs.[Name] 'Budget',round(convert(float,substring(a.[SECT_ID],charindex(':',a.[sect_id])+1,len(a.[sect_id])))/1000,3) as 'StartChainage' , round(convert(float,substring(a.[SECT_NAME],charindex(':',a.[SECT_NAME])+1,len(a.[SECT_NAME])))/1000,3) as 'EndChainage' , (round(convert(decimal(18,2),((isnull(Convert(float,a.[LENGTH]),0)))),2)) as [Length] ,a.ExstStartCh,a.ExstEndCh , a.SurveyID,l.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision from WorkProgramme a inner join sections s on a.SECT_ID = s.SECT_ID and a.HDMWorkSpaceName = s.HDMWorkSpaceName inner join LKPTPmsWorks w on w.Wrk_Desc=a.WORKS_NAME inner join linkmaster L on s.LINK_ID=l.linkcode inner join BudgetScenarios bs on a.BUDGET_ID = bs.BUDGET_ID and a.HDMWorkSpaceName = bs.HDMWorkSpaceName inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID where a.BUDGET_ID <> 255 and a.OPTIMISED = 1 --and a.YEAR_INDEX = 0 --and a.BUDGET_ID = 0 and a.HDMWorkSpaceName = 'SikarJhunjhunu_02Mar24' and a.Budget = 0 ----------------------Treatment wise Cost by all scenarios select L.RoadCode, L.LinkCode,L.Direction as 'Direction' , w.WRK_Code as treatment --,a.WORKS_NAME ,bs.[Name] 'Budget' , (round(convert(decimal(18,2),((isnull(Convert(float,a.FIN_COST),0))))/10,2)) as [cost] ,round(convert(float,substring(a.[SECT_ID],charindex(':',a.[sect_id])+1,len(a.[sect_id])))/1000,3) as 'StartChainage' , round(convert(float,substring(a.[SECT_NAME],charindex(':',a.[SECT_NAME])+1,len(a.[SECT_NAME])))/1000,3) as 'EndChainage' , (round(convert(decimal(18,2),((isnull(Convert(float,a.[LENGTH]),0)))),2)) as [Length] ,a.ExstStartCh,a.ExstEndCh, a.SurveyID , a.SurveyID,l.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision from WorkProgramme a inner join sections s on a.SECT_ID = s.SECT_ID and a.HDMWorkSpaceName = s.HDMWorkSpaceName inner join LKPTPmsWorks w on w.Wrk_Desc=a.WORKS_NAME inner join linkmaster L on s.LINK_ID=l.linkcode inner join BudgetScenarios bs on a.BUDGET_ID = bs.BUDGET_ID and a.HDMWorkSpaceName = bs.HDMWorkSpaceName inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID where a.BUDGET_ID <> 255 and a.OPTIMISED = 1 --and a.YEAR_INDEX = 0 --and a.BUDGET_ID = 0 and a.HDMWorkSpaceName = 'SikarJhunjhunu_02Mar24' and a.Budget = 0 ----------ARSIS --------------- Accident type select al.RoadCode,al.LinkCode,al.Dir as 'Direction', t.[Name] as AccidentType, count(AccTypeId) AS NoofAccidents,l.surveyid ,convert(varchar,l.SurveyDt,105)as 'SurveyDt',lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision FROM dbo.AISAccDetl AS l INNER JOIN AISAccLoc as al on l.AccLocCode=al.accloccode inner join dbo.LKPTAISAccCls AS t ON t.Code = l.AccTypeId inner join linkmaster LM on al.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by al.LinkCode, al.RoadCode, t.[Name],l.surveyid,al.dir,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision ,convert(varchar,l.SurveyDt,105) -------------Vehicle type SELECT al.RoadCode, al.LinkCode,al.dir as 'Direction', t.[Name] as VehicleType, count(VehTypeId) AS NoofVehicles,l.surveyid ,convert(varchar,l.SurveyDt,105)as 'SurveyDt' ,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision FROM dbo.AISAccDetl AS l INNER JOIN AISAccLoc as al on l.AccLocCode=al.accloccode inner join dbo.LKPTAISVehType AS t ON t.Code = l.VehTypeId inner join linkmaster LM on al.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by al.LinkCode, al.RoadCode, t.[Name],l.surveyid,al.dir,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision ,convert(varchar,l.SurveyDt,105) --------------Other objects SELECT al.RoadCode,al.LinkCode,al.dir as 'Direction',fc.[name] as OtherObjects, count(Factsinvl) AS NoofOtherObjects,l.surveyid,convert(varchar,l.SurveyDt,105)as 'SurveyDt' ,lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision, fc.Code FROM dbo.AISAccRptGen AS l INNER JOIN AISAccLoc as al on l.AccLocCode=al.accloccode inner join LKPTAISAccFctrs fc on l.FactsInvl=fc.Code inner join linkmaster LM on al.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by l.surveyid, al.RoadCode, al.LinkCode,al.dir, FC.CODE, fc.[Name],convert(varchar,l.SurveyDt,105),lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision, fc.Code --------------Accident effect on victims select aal.RoadCode, aal.LinkCode,aal.dir as 'Direction','Death on Spot'as Victims,Count(arg.DeathonSpt) as Counts ,aal.SurveyID,convert(varchar,arg.SurveyDt,105) as 'SurveyDt',lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision from AISAccRptGen arg inner join AISAccLoc aal on arg.AccLocCode = aal.AccLocCode inner join linkmaster LM on aal.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by aal.RoadCode,aal.LinkCode,aal.SurveyID,convert(varchar,arg.SurveyDt,105),lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision,aal.dir union all select aal.RoadCode,aal.LinkCode,aal.dir as 'Direction','Death In Hospitals' ,Count(arg.DeathInHsptls) ,aal.SurveyID,convert(varchar,arg.SurveyDt,105)'SurveyDt',lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision from AISAccRptGen arg inner join AISAccLoc aal on arg.AccLocCode = aal.AccLocCode inner join linkmaster LM on aal.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by aal.RoadCode,aal.LinkCode,aal.SurveyID,convert(varchar,arg.SurveyDt,105),lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision,aal.dir union all select aal.RoadCode,aal.LinkCode,aal.dir as 'Direction','Major Injury', Count(arg.MajInjury) ,aal.SurveyID,convert(varchar,arg.SurveyDt,105)'SurveyDt',lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision from AISAccRptGen arg inner join AISAccLoc aal on arg.AccLocCode = aal.AccLocCode inner join linkmaster LM on aal.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by aal.RoadCode,aal.LinkCode,aal.SurveyID,convert(varchar,arg.SurveyDt,105),lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision,aal.dir union all select aal.RoadCode,aal.LinkCode,aal.dir as 'Direction', 'Minor Injury',Count(arg.MinorInjury) ,aal.SurveyID,convert(varchar,arg.SurveyDt,105)'SurveyDt',lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision from AISAccRptGen arg inner join AISAccLoc aal on arg.AccLocCode = aal.AccLocCode inner join linkmaster LM on aal.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by aal.RoadCode,aal.LinkCode,aal.SurveyID,convert(varchar,arg.SurveyDt,105),lm.Agency,ab.Zone,ab.District ,ab.Circle,ab.Division,ab.Subdivision,aal.dir --------Defects count select al.RoadCode, al.LinkCode,l.dir as 'Direction',l.Chainage,l.ExstCh as 'ExistingChainage', t.[Desc] as DefectType, count(DefType) AS NoofDefects,l.surveyid ,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision FROM dbo.AISDefects AS l INNER JOIN AISAccLoc as al on l.RoadCode=al.RoadCode inner join dbo.LKPTAISDefects AS t ON t.Code = l.DefType inner join linkmaster LM on l.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by al.LinkCode, al.RoadCode,l.Chainage,l.ExstCh, t.[Desc],l.surveyid,l.dir ,lm.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision --------Time of Accidents SELECT al.RoadCode,al.dir as 'Direction',al.linkcode,[time], CASE WHEN [time]>='00:00' and [time]<='18:00' THEN 'Day' WHEN [time]>'18:00' and [time]<='24:00' THEN 'Night' Else [time] END AS Other,Count([time]) as totalcount ,l.SurveyId,convert(varchar,l.SurveyDt,105)as 'SurveyDt' ,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision FROM dbo.AISAccRptGen AS l INNER JOIN AISAccLoc as al on l.AccLocCode=al.accloccode inner join linkmaster LM on al.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID group by al.linkcode , al.RoadCode,al.dir,[Time],l.SurveyId,convert(varchar,l.SurveyDt,105) ,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision ------AMT --------------------Reconstruction costwise select a.RoadCode,a.rdnm as 'RoadName', l.linkcode,a.Dir as 'Direction', a.Startch,a.Endch,a.len as 'Length' ,a.ExstStartCh as 'ExistingStartChainage',a.ExstEndCh as 'ExistingEndChainage', w.WrkCode 'treatment' , (round(convert(decimal(18,2),((isnull(a.cost,0)+isnull(a.widenCost,0))/10000000)),2)) as [cost] , a.surveyid ,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision from oSmplPmsRawData a inner join linkmaster L on a.roadcode=l.roadcode and a.linkcode=l.linkcode inner join LKPTAMTWorks w on a.treatment = w.WrkName inner join linkmaster LM on a.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID where a.trtcode like 'TRC%' --------------Reconstruction lengthwise select a.RoadCode,a.rdnm as 'RoadName', l.linkcode,a.Dir as 'Direction', a.Startch,a.Endch,a.len as 'Length' ,a.ExstStartCh as 'ExistingStartChainage',a.ExstEndCh as 'ExistingEndChainage', w.WrkCode 'treatment' --, (round(convert(decimal(18,3),(a.EndCh-a.StartCh))/1000,3)) as [length] , a.surveyid,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision from oSmplPmsRawData a inner join linkmaster L on a.roadcode=l.roadcode and a.linkcode=l.linkcode inner join LKPTAMTWorks w on a.treatment = w.WrkName inner join linkmaster LM on a.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID where a.trtcode like 'TRC%' --------TIS --------------AADT traffic category Select tm.RoadCode,tm.linkcode, t.StnCode As StationCode,tm.Chainage,tm.ExstCh,Sum(t.Tot)/6 as AADT,t.SurveyID,MIN(FORMAT(t.SurveyDt, 'MMM')) AS SurveyMonth ,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision from TISTVC t inner join tistrfmaster tm on t.stncode=tm.stncode inner join linkmaster LM on tm.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID Group by t.StnCode,t.SurveyID,tm.RoadCode,tm.linkcode,tm.Chainage,tm.ExstCh,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision -----------AADT Traffic Category by Axle Select b.RoadCode, b.linkcode,b.StationCode,b.Chainage,b.ExstCh as 'ExisingChainage',AxelAvg ,c.[Desc] as [Traffic Category],b.SurveyID,SurveyMonth ,b.Agency,b.Zone,b.District,b.Circle,b.Division,b.Subdivision From (Select tm.RoadCode,tm.linkcode, t.StnCode As StationCode,tm.Chainage,tm.ExstCh,t.SurveyID,MIN(FORMAT(t.SurveyDt, 'MMM')) AS SurveyMonth ,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,Sum(t.Tot)/2 as AxelAvg from [dbo].[TISAxleLoadTVC] t inner join tistrfmaster tm on t.stncode=tm.stncode inner join TISAxleLoadDtls ax on t.StnCode = ax.StnCode inner join linkmaster LM on tm.LinkCode=LM.linkcode inner join VWAdminBoundaries ab on ab.SubDCode=lM.GeoID Group by t.StnCode,t.SurveyID,tm.RoadCode,tm.linkcode,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,tm.Chainage,tm.ExstCh) b left Join LKPTTrfCatg c on b.AxelAvg >=c.[min] and b.AxelAvg