For Mad Cyril (and any other SQL nerds)

[MK Shrimper]

...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]

 

[Mad Cyril]

Using this information I have now cleared out every Network Rail employees bank acount and am en route to the Caymans although I'll be back for the Wakering friendly.

 

[londonblue]

Personally I wouldn't have any spaces in my headings. So for example I would call "Weighted_Max Temp", "WeightedMaxTemp". It might make life easier in the long run.

 

[MK Shrimper]

Personally I wouldn't have any spaces in my headings. So for example I would call "Weighted_Max Temp", "WeightedMaxTemp". It might make life easier in the long run.

Oh I get told off by my boss who's got OCD....my code isn't lined up correctly either :facepalm:

 

[Mad Cyril]

Oh I get told off by my boss who's got OCD....my code isn't lined up correctly either :facepalm:

It's not OCD.

Code is written once but read many times by many people. That is why formatting is important.

 

[MK Shrimper]

It's not OCD.

Code is written once but read many times by many people. That is why formatting is important.

Oh yeah, I appreciate that for big projects, code needs to be carefully arranged and commented on, but he aligns even the simplest on queries! In the past we've had to try a deciper Fortran code from the 1980s with no documentation and no commentrary. :stunned: