#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0

    MYSQL Select/Design with Date Calculation


    I have a set of data that I receive via an automated export process as a csv. I am importing this data into a MYSQL table called data.

    data.metric = metric we are measuring
    data.amount = floating value of the metric value
    data.dob = date o business value stored as MYSQL type date()

    We have another table that breaks down our finacial period into the period ans the start and end date for the period

    fp.per = the period P01, P02, etc.
    fp.start = date that period starts stored as MYSQL type date()
    fp.end = date that the period ends (inclusive) stored as MYSQL type date()

    I would like to perform a select that results in a data set that looks like this:
    data.metric, data.amount, fp.per where fp=P01

    I am having trouble trying to create the select statement. I am starting with something like this but I am not sure how to embed select statements and how the ordering of the selects work.
    Code:
    select data.metric, data.amount, data.dob, fp.per where @CALCFY = '2009'
    	select fp.per where data.dob between fp.start and fp.end as @CALFY;
    Any suggestions? The data table has over 1,000,00 records. My intent is to hopefully create these selects as stored views/queries for use via ODBC connection in Excel to create pivot table reports.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,240
    Rep Power
    4279
    Code:
    SELECT data.metric
         , data.amount
         , data.dob
         , fp.start 
         , fp.end  
      FROM fp
    INNER     
      JOIN data
        ON data.dob BETWEEN fp.start AND fp.end 
     WHERE fp.per = '2009'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Thanks! Worked great.
    New question:
    I have 3 other similiar tables of period definitions:
    fp = fiscal period
    fq = fiscal quarter
    fw = fiscal week
    fy = fiscal year

    To arrive at a SELECT result like this:
    Code:
    data.metric, data.amount, fy.year, fq.Qtr, fp.Per, fw.week
    (Where each different fiscal period is determined by evaluating the data.dob date to between the start/end dates of each fiscal definition table)

    Hoow would I create additional separate joins to evaluate the data.dob and return the desired results? I am trying this but it is not working :
    Code:
    select data.metric
    		, data.amount
    		, data.dob
    		, fy.Year
    		, fq.Qtr
    		, fp.Per
    		, fw.Week
    From fy
    	Inner 
    		join data
    		on data.dob between fy.start and fy.End
    From fq
    	Inner
    		join data
    		on data.dob between fq.start and fq.End
    From fp
    	Inner
    		join data
    		on data.dob between fp.start and fp.End
    From fw
    	Inner
    		join data
    		on data.dob between fw.start and fw.End
    where fp.per = 'P01';
    The goal is to save this query as a view in the database without the "Where" portion of the select statement. I want to learn how to build each piece of the query first.

    Saving this as a MYSQL View will be next so an end end user using Excel can use the Excel Query Builder tool to filter the metrics, dates, etc. on the spreadsheet to create a pivot table data set.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,240
    Rep Power
    4279
    Originally Posted by skydiver
    (Where each different fiscal period is determined by evaluating the data.dob date to between the start/end dates of each fiscal definition table)
    any chance you can change this? because it seems inefficient

    those tables should relate to each other, and not all of them be driven by start and end dates

    if you can't redesign them, please at least do a SHOW CREATE TABLE for each one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    I cannot redesign the raw data I am importing, it is a fixed data set from a third party tool. I thought of trying to alter the data table and then create triggers to add what would be static values for each record in the data table but this is beyond what I know so far in SQL.

    I am trying to replace the process we use via excel where we have a table of this raw data and then do pivot table analysis.

    I currently have about 1,200,000 records in the data table and Excel chokes on this when you try to open the data.

    We also add columns every once in a while like Day Of the Week (again calculated for the data.dob column) among others i cannot think of right now. Here is my current set of tables:

    data:
    Code:
    CREATE TABLE `data` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `section` varchar(45) DEFAULT NULL,
      `amount` float DEFAULT NULL,
      `metric` varchar(45) DEFAULT NULL,
      `dob` date DEFAULT NULL,
      `loc_id` varchar(10) DEFAULT NULL,
      `loc_abbrv` varchar(10) DEFAULT NULL,
      `section_sort` int(11) DEFAULT NULL,
      `metric_sort` int(11) DEFAULT NULL,
      PRIMARY KEY (`ID`),
      KEY `dob` (`dob`),
      KEY `section` (`section`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6291361 DEFAULT CHARSET=utf8
    fy:
    Code:
    CREATE TABLE `fy` (
       `ID` int(11) NOT NULL AUTO_INCREMENT,
       `Start` date DEFAULT NULL,
       `Year` varchar(4) DEFAULT NULL,
       `End` date DEFAULT NULL,
       PRIMARY KEY (`ID`)
     ) ENGINE=InnoDB AUTO_INCREMENT=262 DEFAULT CHARSET=utf8
    fq:
    Code:
    CREATE TABLE `fq` (
       `ID` int(11) NOT NULL AUTO_INCREMENT,
       `Qtr` varchar(2) DEFAULT NULL,
       `Start` date DEFAULT NULL,
       `End` date DEFAULT NULL,
       PRIMARY KEY (`ID`)
     ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8
    fp:
    Code:
    CREATE TABLE `fp` (
       `ID` int(11) NOT NULL AUTO_INCREMENT,
       `Per` varchar(3) DEFAULT NULL,
       `Start` date DEFAULT NULL,
       `End` date DEFAULT NULL,
       PRIMARY KEY (`ID`)
     ) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8
    fw:
    Code:
    CREATE TABLE `fw` (
       `ID` int(11) NOT NULL AUTO_INCREMENT,
       `Week` varchar(3) DEFAULT NULL,
       `Start` date DEFAULT NULL,
       `End` date DEFAULT NULL,
       PRIMARY KEY (`ID`)
     ) ENGINE=InnoDB AUTO_INCREMENT=512 DEFAULT CHARSET=utf8
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,240
    Rep Power
    4279
    Originally Posted by skydiver
    I cannot redesign the raw data I am importing, it is a fixed data set from a third party tool.
    no, i meant the fiscal tables

    fiscal week should belong to fiscal period, which should belong to fiscal quarter, which should belong to fiscal year

    you would eliminate several needless joins that way

    but nevermind

    anyhow, the reason your query isn't working is because you cannot repeat the FROM keyword
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Interesting. If I did this it would mean only one join on the select right?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,240
    Rep Power
    4279
    Originally Posted by skydiver
    Interesting. If I did this it would mean only one join on the select right?
    yes

    you would start with the fp table (since that's the limiting condition in your WHERE clause) and it would have FKs for the fiscal quarter and year

    not sure why you'd need to return the fiscal weeks, since it would be all of them in the period
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo