|
--------------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 <c.[max]
|
|
|
|
----------AADT by Station-TVC
|
|
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 TVCAvg 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
|
|
,LM.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision,tm.Chainage,tm.ExstCh
|
|
|
|
--------------AADT by Stations-Axle
|
|
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 td on td.StnCode=t.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
|
|
|
|
-------------Districtwise traffic locations
|
|
SELECT a.RoadCode, a.linkcode,a.Dir as 'Direction',a.StnCode AS StationCode,a.Chainage,a.ExstCh as ExistingChainage
|
|
,a.SurveyID,MIN(FORMAT(a.SurveyDt, 'MMM')) AS SurveyMonth,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
|
|
FROM
|
|
TISTrfMaster as a
|
|
INNER JOIN dbo.LinkMaster AS L ON a.LinkCode = L.LinkCode
|
|
inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID
|
|
--inner join Sysgeolevel5 Sd on l.GeoID=sd.Code
|
|
--inner join Sysgeolevel4 dv on sd.parentcode=dv.code
|
|
--inner join Sysgeolevel3 Cr on dv.parentcode=cr.code
|
|
--inner join Sysgeolevel2 dr on Cr.parentcode=dr.code
|
|
--inner join Sysgeolevel1 z on dr.parentcode=z.code
|
|
Group by a.RoadCode, a.linkcode,a.Dir,a.StnCode,a.Chainage,a.ExstCh
|
|
,a.SurveyID,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
|
|
|
|
---------District Wise-TVC
|
|
SELECT a.RoadCode, a.linkcode,a.Dir as 'Direction',a.StnCode AS StationCode,a.Chainage,a.ExstCh as ExistingChainage,count(t.StnCode) as TVC
|
|
,a.SurveyID,MIN(FORMAT(a.SurveyDt, 'MMM')) AS SurveyMonth,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
|
|
FROM
|
|
TISTrfMaster as a
|
|
Left join (Select Distinct StnCode From TISAxleLoadTVC)b on b.Stncode=a.StnCode
|
|
Left join (Select Distinct StnCode From [dbo].[TISTVC]) t on t.Stncode=a.StnCode
|
|
INNER JOIN dbo.LinkMaster AS L ON a.LinkCode = L.LinkCode
|
|
inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID
|
|
--inner join Sysgeolevel5 Sd on l.GeoID=sd.Code
|
|
--inner join Sysgeolevel4 dv on sd.parentcode=dv.code
|
|
--inner join Sysgeolevel3 Cr on dv.parentcode=cr.code
|
|
--inner join Sysgeolevel2 dr on Cr.parentcode=dr.code
|
|
--inner join Sysgeolevel1 z on dr.parentcode=z.code
|
|
Group by a.RoadCode, a.linkcode,a.Dir,a.StnCode,a.Chainage,a.ExstCh
|
|
,a.SurveyID,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
|
|
|
|
----------District Wise-Axle
|
|
SELECT a.RoadCode, a.LinkCode,a.Dir as 'Direction',a.StnCode as StationCode,a.Chainage,a.ExstCh as ExistingChainage,count(ax.StnCode) as Axle
|
|
,a.SurveyID,MIN(FORMAT(a.SurveyDt, 'MMM')) AS SurveyMonth,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
|
|
FROM
|
|
TISTrfMaster as a
|
|
--Left join (Select Distinct StnCode From TISAxleLoadTVC)b on b.Stncode=a.StnCode
|
|
--Left join (Select Distinct StnCode From [dbo].[TISTVC]) t on t.Stncode=a.StnCode
|
|
INNER JOIN dbo.LinkMaster AS L ON a.LinkCode = L.LinkCode
|
|
inner join TISAxleLoadDtls ax on a.StnCode = ax.StnCode
|
|
inner join VWAdminBoundaries ab on ab.SubDCode=l.GeoID
|
|
--inner join Sysgeolevel5 Sd on l.GeoID=sd.Code
|
|
--inner join Sysgeolevel4 dv on sd.parentcode=dv.code
|
|
--inner join Sysgeolevel3 Cr on dv.parentcode=cr.code
|
|
--inner join Sysgeolevel2 dr on Cr.parentcode=dr.code
|
|
--inner join Sysgeolevel1 z on dr.parentcode=z.code
|
|
Group by a.RoadCode, a.linkcode,a.Dir,a.StnCode,a.Chainage,a.ExstCh
|
|
,a.SurveyID,L.Agency,ab.Zone,ab.District,ab.Circle,ab.Division,ab.Subdivision
|