none
Predicted figures into the future.

    Question

  • CREATE TABLE dbo.average
    (
    first_date datetime,
    first_date_day nvarchar(50),
    Area nvarchar(50),
    Lorrys nvarchar(50),
    Cars nvarchar(50),
    Buses nvarchar(50),
    );

    INSERT INTO dbo.average VALUES('2013-05-06','Saturday', 'Scotland', '215','412','241');
    INSERT INTO dbo.average VALUES('2013-05-06','Saturday', 'England', '265','132','224');
    INSERT INTO dbo.average VALUES('2013-05-05','Sunday','Scotland','364','125','356');
    INSERT INTO dbo.average VALUES('2013-05-05','Sunday','England','334','412','346');
    INSERT INTO dbo.average VALUES('2013-05-06','Monday','Scotland','541','467','512');
    INSERT INTO dbo.average VALUES('2013-05-06','Monday','England','534','467','412');
    INSERT INTO dbo.average VALUES('2013-05-07','Tuesday','Scotland','751','627','336');
    INSERT INTO dbo.average VALUES('2013-05-07','Tuesday','England','755','657','336');
    INSERT INTO dbo.average VALUES('2013-05-08','Wednesday','Scotland','345','324','116');
    INSERT INTO dbo.average VALUES('2013-05-08','Wednesday','England','324','312','116');
    INSERT INTO dbo.average VALUES('2013-05-09','Thursday','Scotland','625', '324', '214');
    INSERT INTO dbo.average VALUES('2013-05-09','Thursday','England','365', '334', '214');
    INSERT INTO dbo.average VALUES('2013-05-10','Friday','Scotland','733', '545', '234' );
    INSERT INTO dbo.average VALUES('2013-05-10','Friday','England','332', '452', '232' );
    INSERT INTO dbo.average VALUES('2013-05-11','Saturday','Scotland','452', '124', '544');
    INSERT INTO dbo.average VALUES('2013-05-12','Sunday','Scotland','231', '231', '834');
    INSERT INTO dbo.average VALUES('2013-05-12','Sunday','England','232', '223', '823');
    INSERT INTO dbo.average VALUES('2013-05-13','Monday','Scotland','454','553', '958');
    INSERT INTO dbo.average VALUES('2013-05-13','Monday','England','454','443', '938');
    INSERT INTO dbo.average VALUES('2013-05-14','Tuesday','Scotland','687', '312', '922');
    INSERT INTO dbo.average VALUES('2013-05-14','Tuesday','England','687', '162', '942');
    INSERT INTO dbo.average VALUES('2013-05-15','Wednesday','Scotland','645', '243', '442');
    INSERT INTO dbo.average VALUES('2013-05-15','Wednesday','England','645', '213', '412');
    INSERT INTO dbo.average VALUES('2013-05-16','Thursday','Scotland','121', '531', '312');
    INSERT INTO dbo.average VALUES('2013-05-16','Thursday','England','112', '253', '323');
    INSERT INTO dbo.average VALUES('2013-05-17','Friday','Scotland','643','623', '732');
    INSERT INTO dbo.average VALUES('2013-05-17','Friday','England','463','235', '721');
    INSERT INTO dbo.average VALUES('2013-05-18','Saturday','Scotland','615','113', '342');
    INSERT INTO dbo.average VALUES('2013-05-18','Saturday','England','615','132', '362');
    INSERT INTO dbo.average VALUES('2013-05-19','Sunday','Scotland','254','536', '462');
    INSERT INTO dbo.average VALUES('2013-05-19','Sunday','England','254','453', '342');
    INSERT INTO dbo.average VALUES('2013-05-20','Monday','Scotland','214','223', '122');
    INSERT INTO dbo.average VALUES('2013-05-20','Monday','England','154','523', '225');
    INSERT INTO dbo.average VALUES('2013-05-21','Tuesday','Scotland','644','823', '322');
    INSERT INTO dbo.average VALUES('2013-05-21','Tuesday','England','645','843', '323');
    INSERT INTO dbo.average VALUES('2013-05-22','Wednesday','Scotland','344','533', '323');
    INSERT INTO dbo.average VALUES('2013-05-22','Wednesday','England','434','543', '342');
    INSERT INTO dbo.average VALUES('2013-05-23','Thursday','Scotland','423','934', '223');
    INSERT INTO dbo.average VALUES('2013-05-23','Thursday','England','723','793', '242');
    INSERT INTO dbo.average VALUES('2013-05-24','Friday','Scotland','158','233', '825');
    INSERT INTO dbo.average VALUES('2013-05-24','Friday','England','128','234', '812');
    INSERT INTO dbo.average VALUES('2013-05-25','Saturday','Scotland','556','423', '523');
    INSERT INTO dbo.average VALUES('2013-05-25','Saturday','England','562','213', '512');
    INSERT INTO dbo.average VALUES('2013-05-26','Sunday','Scotland','245','333', '823');
    INSERT INTO dbo.average VALUES('2013-05-26','Sunday','England','465','377', '827');
    INSERT INTO dbo.average VALUES('2013-05-27','Monday','Scotland','354','423', '132');
    INSERT INTO dbo.average VALUES('2013-05-27','Monday','England','344','223', '122');
    INSERT INTO dbo.average VALUES('2013-05-28','Tuesday','Scotland','654','253', '452');
    INSERT INTO dbo.average VALUES('2013-05-28','Tuesday','England','635','323', '426');
    INSERT INTO dbo.average VALUES('2013-05-29','Wednesday','Scotland','465','289', '382');
    INSERT INTO dbo.average VALUES('2013-05-29','Wednesday','England','425','259', '326');
    INSERT INTO dbo.average VALUES('2013-05-30','Thursday','Scotland','667','739', '626');
    INSERT INTO dbo.average VALUES('2013-05-30','Thursday','England','617','253', '621');
    INSERT INTO dbo.average VALUES('2013-05-31','Friday','Scotland','165','253', '122');
    INSERT INTO dbo.average VALUES('2013-05-31','Friday','England','425','233', '142');

    SELECT DISTINCT A.first_date, A.first_date_day, A.Area, A.Lorrys, AVG(Prev.Lorrys) OVER (PARTITION BY A.Area, A.first_date) AS [Average_Lorrys], A.Cars, AVG(Prev.Cars) OVER (PARTITION BY A.Area, A.first_date) AS [Average_Cars], A.Buses, AVG(Prev.Buses) OVER (PARTITION BY A.Area, A.first_date) AS [Average_Lorrys] FROM dbo.average A OUTER APPLY ( SELECT TOP (4) cast(Lorrys as int) as Lorrys, cast(Cars as int) as Cars, cast(Buses as int) as Buses FROM dbo.average A1 WHERE A1.first_date <= A.first_date AND A1.first_date_day = A.first_date_day AND A.Area = A1.Area ORDER BY A1.first_date DESC ) Prev ORDER BY A.first_date, A.Area

    I have the following SQL which provides actual and average figures.  The average is based on the day of the week, and the average for the past 4 days of the week e.g. last 4 mondays, last 4 tuesdays etc)

    See post: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/99dc85e4-99dc-42d1-8fd4-b0a8f02a214b/average-over-the-past-4-days-of-the-week#9cc45190-ca4d-4de2-9340-05441dc6b87d

    I would like to now extend this to provide me future predicted figures e.g. tomorrow, thurs, fri predicted figures based on thesame methodology.   Does anyone know how can this be achieved? Thanks

    Tuesday, July 09, 2013 9:01 AM

All replies

  • Have you considered putting the data in a table (this is not! NO KEY!), normalizing it, following ISO Standards and Modern SQL? There is an ISO country code. The day of the week is computed and the data in the real world is not made up of fifty Chinese character, especially numeric data.  


    CREATE TABLE Transportation_Report
    (posting_date DATE NOT NULL, 
     country_code CHAR(3) NOT NULL, 
     PRIMARY KEY (posting_date, country_code),
     lorry_cnt INTEGER DEFAULT 0 NOT NULL 
     CHECK (lorry_cnt >= 0), 
     auto_cnt INTEGER DEFAULT 0 NOT NULL 
     CHECK (auto_cnt >= 0), 
     bus_cnt INTEGER DEFAULT 0 NOT NULL 
     CHECK (bus_cnt >= 0));

    INSERT INTO Transportation_Report
    VALUES
    ('2013-05-05', 'ENG', 334, 412, 346), 
    ('2013-05-05', 'SCT', 364, 125, 356), 
    ('2013-05-06', 'ENG', 265, 132, 224), 
    ('2013-05-06', 'ENG', 534, 467, 412),
    ('2013-05-06', 'SCT', 541, 467, 512),
    ('2013-05-07', 'ENG', 755, 657, 336),
    ('2013-05-07', 'SCT', 751, 627, 336),
    ('2013-05-08', 'ENG', 324, 312, 116),
    ('2013-05-08', 'SCT', 345, 324, 116),
    ('2013-05-09', 'ENG', 365, 334, 214),
    ('2013-05-09', 'SCT', 625, 324, 214), 
    ('2013-05-10', 'ENG', 332, 452, 232 ),
    ('2013-05-10', 'SCT', 733, 545, 234 ), 
    ('2013-05-11', 'SCT', 452, 124, 544),
    ('2013-05-12', 'ENG', 232, 223, 823), 
    ('2013-05-12', 'SCT', 231, 231, 834), 
    ('2013-05-13', 'ENG', 454, 443, 938), 
    ('2013-05-13', 'SCT', 454, 553, 958), 
    ('2013-05-14', 'ENG', 687, 162, 942), 
    ('2013-05-14', 'SCT', 687, 312, 922), 
    ('2013-05-15', 'ENG', 645, 213, 412),
    ('2013-05-15', 'SCT', 645, 243, 442),
    ('2013-05-16', 'ENG', 112, 253, 323),
    ('2013-05-16', 'SCT', 121, 531, 312),
    ('2013-05-17', 'ENG', 463, 235, 721),
    ('2013-05-17', 'SCT', 643, 623, 732),
    ('2013-05-18', 'ENG', 615, 132, 362),
    ('2013-05-18', 'SCT', 615, 113, 342),
    ('2013-05-19', 'ENG', 254, 453, 342),
    ('2013-05-19', 'SCT', 254, 536, 462),
    ('2013-05-20', 'ENG', 154, 523, 225),
    ('2013-05-20', 'SCT', 214, 223, 122),
    ('2013-05-21', 'ENG', 645, 843, 323),
    ('2013-05-21', 'SCT', 644, 823, 322),
    ('2013-05-22', 'ENG', 434, 543, 342),
    ('2013-05-22', 'SCT', 344, 533, 323),
    ('2013-05-23', 'ENG', 723, 793, 242),
    ('2013-05-23', 'SCT', 423, 934, 223),
    ('2013-05-24', 'ENG', 128, 234, 812),
    ('2013-05-24', 'SCT', 158, 233, 825),
    ('2013-05-25', 'ENG', 562, 213, 512),
    ('2013-05-25', 'SCT', 556, 423, 523),
    ('2013-05-26', 'ENG', 465, 377, 827),
    ('2013-05-26', 'SCT', 245, 333, 823),
    ('2013-05-27', 'ENG', 344, 223, 122),
    ('2013-05-27', 'SCT', 354, 423, 132),
    ('2013-05-28', 'ENG', 635, 323, 426),
    ('2013-05-28', 'SCT', 654, 253, 452),
    ('2013-05-29', 'ENG', 425, 259, 326),
    ('2013-05-29', 'SCT', 465, 289, 382),
    ('2013-05-30', 'ENG', 617, 253, 621),
    ('2013-05-30', 'SCT', 667, 739, 626),
    ('2013-05-31', 'ENG', 425, 233, 142),
    ('2013-05-31', 'SCT', 165, 253, 122);

    I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is a separator token, ww is (01-53) week number and d is (1-7) day of the week.

    You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
    WHERE sale_day LIKE '2012W26-[67]'

    There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html 

    The julian date is a sequential numbering in the Calendar table; if you rally wanted to you could use t6he Astronomer's Julian date, buit most people do not need that. 

    CREATE VIEW Weekly_Transportation_Report
    (posting_date, week_posting_date, country_code, lorry_cnt, 
     auto_cnt, bus_cnt)
    AS
    SELECT T.posting_date, C.week_date, C.julian_date, T.country_code, T.lorry_cnt, 
     T.auto_cnt, T.bus_cnt
      FROM Transportation_Report AS T,
           Calendar AS C
     WHERE T.posting_date = C.cal_date; 

    >> The Transportation_Report is based on the day of the week, and the Transportation_Report for the past 4 days of the week e.g. last 4 Mondays, last 4 Tuesdays etc) <<

    The view has all of the temporal data in numeric form. There is no need to use computation with non-ANSI CROSS APPLY or other proprietary extensions. You can now do many things with this; what if we use the Julian date as a parameter? It just makes the code easier to read; we could use V CURRENT_TIMESTAMP and do another look-up. 

    Here is a skeleton:

    WITH X
    AS
    (SELECT posting_date, SUBSTRING (week_date,9,1) AS week_day, 
           country_code, lorry_cnt, auto_cnt, bus_cnt
      FROM Weekly_Transportation_Report AS W
     WHERE julian_date BETWEEN @in_julian_date - 28 AND @in_julian_date)

    SELECT week_day, country_code
           AVG(lorry_cnt) AS lorry_cnt_avg, 
           AVG(auto_cnt) AS auto_cnt_avg,
           AVG(bus_cnt) AS bus_cnt_avg       
      FROM X
     GROUP BY week_day, country_code

    >> I would like to now extend this to provide me future predicted figures e.g. tomorrow, Thursday, Friday predicted figures based on the same methodology. Does anyone know how can this be achieved? <<

    This is a tricky statistics issue. Do you know what a Geometric Mean is and how it is used in time series? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 3:09 PM