,Electricit圜onsumption = RAND(CAST(CAST(NEWID() AS VARBINARY(16)) AS INT)) ,MeasurementDay = CONVERT(DATE,SYSDATETIME()) With the following script, we generate random sample data for five customers:
,Measurement91 NUMERIC(10,5) NULL,Measurement92 NUMERIC(10,5) NULL,Measurement93 NUMERIC(10,5) NULL,Measurement94 NUMERIC(10,5) NULL,Measurement95 NUMERIC(10,5) NULL ,Measurement86 NUMERIC(10,5) NULL,Measurement87 NUMERIC(10,5) NULL,Measurement88 NUMERIC(10,5) NULL,Measurement89 NUMERIC(10,5) NULL,Measurement90 NUMERIC(10,5) NULL ,Measurement81 NUMERIC(10,5) NULL,Measurement82 NUMERIC(10,5) NULL,Measurement83 NUMERIC(10,5) NULL,Measurement84 NUMERIC(10,5) NULL,Measurement85 NUMERIC(10,5) NULL ,Measurement76 NUMERIC(10,5) NULL,Measurement77 NUMERIC(10,5) NULL,Measurement78 NUMERIC(10,5) NULL,Measurement79 NUMERIC(10,5) NULL,Measurement80 NUMERIC(10,5) NULL
,Measurement71 NUMERIC(10,5) NULL,Measurement72 NUMERIC(10,5) NULL,Measurement73 NUMERIC(10,5) NULL,Measurement74 NUMERIC(10,5) NULL,Measurement75 NUMERIC(10,5) NULL ,Measurement66 NUMERIC(10,5) NULL,Measurement67 NUMERIC(10,5) NULL,Measurement68 NUMERIC(10,5) NULL,Measurement69 NUMERIC(10,5) NULL,Measurement70 NUMERIC(10,5) NULL ,Measurement61 NUMERIC(10,5) NULL,Measurement62 NUMERIC(10,5) NULL,Measurement63 NUMERIC(10,5) NULL,Measurement64 NUMERIC(10,5) NULL,Measurement65 NUMERIC(10,5) NULL ,Measurement56 NUMERIC(10,5) NULL,Measurement57 NUMERIC(10,5) NULL,Measurement58 NUMERIC(10,5) NULL,Measurement59 NUMERIC(10,5) NULL,Measurement60 NUMERIC(10,5) NULL ,Measurement51 NUMERIC(10,5) NULL,Measurement52 NUMERIC(10,5) NULL,Measurement53 NUMERIC(10,5) NULL,Measurement54 NUMERIC(10,5) NULL,Measurement55 NUMERIC(10,5) NULL ,Measurement46 NUMERIC(10,5) NULL,Measurement47 NUMERIC(10,5) NULL,Measurement48 NUMERIC(10,5) NULL,Measurement49 NUMERIC(10,5) NULL,Measurement50 NUMERIC(10,5) NULL ,Measurement41 NUMERIC(10,5) NULL,Measurement42 NUMERIC(10,5) NULL,Measurement43 NUMERIC(10,5) NULL,Measurement44 NUMERIC(10,5) NULL,Measurement45 NUMERIC(10,5) NULL ,Measurement36 NUMERIC(10,5) NULL,Measurement37 NUMERIC(10,5) NULL,Measurement38 NUMERIC(10,5) NULL,Measurement39 NUMERIC(10,5) NULL,Measurement40 NUMERIC(10,5) NULL ,Measurement31 NUMERIC(10,5) NULL,Measurement32 NUMERIC(10,5) NULL,Measurement33 NUMERIC(10,5) NULL,Measurement34 NUMERIC(10,5) NULL,Measurement35 NUMERIC(10,5) NULL ,Measurement26 NUMERIC(10,5) NULL,Measurement27 NUMERIC(10,5) NULL,Measurement28 NUMERIC(10,5) NULL,Measurement29 NUMERIC(10,5) NULL,Measurement30 NUMERIC(10,5) NULL ,Measurement21 NUMERIC(10,5) NULL,Measurement22 NUMERIC(10,5) NULL,Measurement23 NUMERIC(10,5) NULL,Measurement24 NUMERIC(10,5) NULL,Measurement25 NUMERIC(10,5) NULL ,Measurement16 NUMERIC(10,5) NULL,Measurement17 NUMERIC(10,5) NULL,Measurement18 NUMERIC(10,5) NULL,Measurement19 NUMERIC(10,5) NULL,Measurement20 NUMERIC(10,5) NULL ,Measurement11 NUMERIC(10,5) NULL,Measurement12 NUMERIC(10,5) NULL,Measurement13 NUMERIC(10,5) NULL,Measurement14 NUMERIC(10,5) NULL,Measurement15 NUMERIC(10,5) NULL ,Measurement6 NUMERIC(10,5) NULL,Measurement7 NUMERIC(10,5) NULL,Measurement8 NUMERIC(10,5) NULL,Measurement9 NUMERIC(10,5) NULL,Measurement10 NUMERIC(10,5) NULL ,Measurement1 NUMERIC(10,5) NULL,Measurement2 NUMERIC(10,5) NULL,Measurement3 NUMERIC(10,5) NULL,Measurement4 NUMERIC(10,5) NULL,Measurement5 NUMERIC(10,5) NULL The following script creates a table with all the necessary columns:ĭROP TABLE IF EXISTS dbo.Electricit圜onsumption Let's also assume we're working with UTCĭates and no daylight savings time. Whether or not this is a good design decision Since there will always be 96 quarters inĪ day (24 * 4 = 96), the database table tracking the consumption has 96 columns-oneĬolumn for each quarter of the day. Every 15 minutes, the consumed kWh of the past 15 minutes The use case for the calculation is as follows: a digital meter tracks a household'sĮlectricity consumption. This means functionality or features of SQL Server might change,ĭisappear, or be added in the final release. They have been added to Azure SQL DB and are now available in SQL Server 2022.Īt the time of writing, SQL Server 2022 is still in preview (RC0 was used to Manyĭatabase vendors have had the GREATEST and LEAST functions in their version of SQL,īut in SQL Server these functions have been absent for a long time. Values across columns, which is not supported by the MIN and MAX functions. In different columns? This would mean you would have the find the highest and lowest However, what if the values are not stored as rows inside a column but as values SELECT TheRange = MAX(mycolumn) - MIN(mycolumn)
0 kommentar(er)
