• Welcome to the ShrimperZone forums.
    You are currently viewing our boards as a guest which only gives you limited access.

    Existing Users:.
    Please log-in using your existing username and password. If you have any problems, please see below.

    New Users:
    Join our free community now and gain access to post topics, communicate privately with other members, respond to polls, upload content and access many other special features. Registration is fast, simple and free. Click here to join.

    Fans from other clubs
    We welcome and appreciate supporters from other clubs who wish to engage in sensible discussion. Please feel free to join as above but understand that this is a moderated site and those who cannot play nicely will be quickly removed.

    Assistance Required
    For help with the registration process or accessing your account, please send a note using the Contact us link in the footer, please include your account name. We can then provide you with a new password and verification to get you on the site.

For Mad Cyril (and any other SQL nerds)

MK Shrimper

Well-known member
Joined
Aug 6, 2005
Messages
52,293
...as requested. Apologies if this is the dullest thread on SZ ever......:zzzzz:

use NetworkRail
go


select wcs.imdm as [IMDM],wcs.Day,wcs.Month,wcs.Year,

sum(wcs.maxT*rat.RATIO) as [Weighted_Max Temp],sum(wcs.minT*rat.RATIO) as [Weighted_Min Temp],sum(wcs.maxw*rat.ratio) as [Weighted_WindGust],
sum(wcs.MaxPr*rat.RATIO) as [Weighted_Precipitation],sum(wcs.maxsnow*rat.ratio) as [Weighted_Snowfall]
from

(
SELECT im.[WeatherCell],im.IMDM

,datepart(day,[DateTime]) as [Day]
,datepart(month,[DateTime]) as [Month]
,datepart(YEAR,[DateTime]) as [Year]
,max(Temperature) as MaxT
,min(Temperature) as MinT
,max(WindGust) as MaxW
,sum(TotalPrecipitation) as MaxPr
,sum(Snowfall) as MaxSnow

FROM [NetworkRail].[dbo].[Weather] as wt

inner join IMDMWeatherCellMap as im
on im.WeatherCell=wt.WeatherCell
where wt.DateTime>='2017-04-02 00:00:00'

group by im.weathercell,imdm,datepart(DAY,wt.DateTime),datepart(month,wt.datetime),datepart(year,wt.datetime)

) as wcs


inner join

(
SELECT t1.IMDM,t1.WeatherCell,T1.TrackLength/TTL.TTL AS RATIO FROM

(
SELECT WC.[WeatherCell]
,IM.IMDM
,[TrackLength]
FROM [NetworkRail].[dbo].[WeatherCellProperties] AS WC


INNER JOIN IMDMWeatherCellMap AS IM
ON IM.WeatherCell=WC.WeatherCell

) T1

INNER JOIN TTL
ON TTL.IMDM = T1.IMDM
) as rat

on wcs.WeatherCell=rat.WeatherCell and wcs.IMDM=rat.IMDM

group by wcs.IMDM,
wcs.Day,wcs.Month,wcs.Year

order by wcs.IMDM,wcs.[year],wcs.[month],wcs.[day]
 
Top