locked
update date range table based on date RRS feed

  • Question

  • Hi,

    I need some help writing some sql... bit difficult to explain but here goes

    I have two tables, see below for example. I need to update the DateRange table and set the flag to 1, if the date from the Date table falls with in the range. Date are all intergers

    DateRange Table

    columns(MonthStart,MonthEnd,Flag)

    201301,20130131,(1 - set to 1 because the date from the date table falls within this date range )

    Date Table

    columns(Date)

    20130101

    20131201

    etc etc

    Thursday, August 8, 2013 8:57 PM

Answers

  • Try this:

    UPDATE "Date"
    SET Flag = 1
    WHERE EXISTS (
      SELECT *
      FROM DateRange
      WHERE "Date" BETWEEN MonthStart AND MonthEnd
    )
    

    Please note that the column name "Date" and table name "Date" are horrible, that you are using inappropriate data types, and that you typically wouldn't store this "flag" in the table, but instead use a query or view to determine it when you need it.

    Please also note that this query will be slow if there are many rows in DateRange. If you have to optimize that, then you can have a look at the Date Range Scans article.


    Gert-Jan

    • Marked as answer by kiwiNspain Friday, August 9, 2013 3:50 PM
    Friday, August 9, 2013 9:17 AM

All replies

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    >>  I need to update the DateRange table and set the flag to 1, if the date from the Date table falls with in the range. Date are all integers <<

    No, no, no! Tables are sets, so their names are plural or collective, not singular. DATE is a reserved word in SQL and a unit of temporal measurement. It cannot be a table name! 

    Using integer for dates is absurd! On a scale from 1 to 10, what color is your favorite letter of the alphabet? 

    Finally, we do not use flags in RDBMS; that was assembly language, not SQL! 

    This is called a Report Period model. Here is how you program this idiom:

    Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. 

    CREATE TABLE Something_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK (something_report_name LIKE <pattern>),
     something_report_start_date DATE NOT NULL,
     something_report_end_date DATE NOT NULL,
      CONSTRAINT date_ordering
        CHECK (something_report_start_date <= something_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

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

    Thursday, August 8, 2013 10:33 PM
  • yes yes yes, you sound like your from that shampoo add...herbal essence. 

    if your going to write rubbish like that, don't bother. i don't care about your naming conventions. the table name and columns are like that, so if it's too difficult for you too actual help with a constructive answer... well then don't answer!

    Friday, August 9, 2013 7:09 AM
  • Try this:

    UPDATE "Date"
    SET Flag = 1
    WHERE EXISTS (
      SELECT *
      FROM DateRange
      WHERE "Date" BETWEEN MonthStart AND MonthEnd
    )
    

    Please note that the column name "Date" and table name "Date" are horrible, that you are using inappropriate data types, and that you typically wouldn't store this "flag" in the table, but instead use a query or view to determine it when you need it.

    Please also note that this query will be slow if there are many rows in DateRange. If you have to optimize that, then you can have a look at the Date Range Scans article.


    Gert-Jan

    • Marked as answer by kiwiNspain Friday, August 9, 2013 3:50 PM
    Friday, August 9, 2013 9:17 AM
  • thanks Gert-Jan. that is what I was after... rather easy in the end.
    Friday, August 9, 2013 3:50 PM
  • if your going to write rubbish like that, don't bother. i don't care about your naming conventions. the table name and columns are like that, so if it's too difficult for you too actual help with a constructive answer... well then don't answer!

    When I look up your history, I see that other people have the same problems with your postings, too

    Google and Wikipedia me so you can see that I have some credentials in this area. Perhaps you should listen :)  Now, show your research to your boss and tell him that if he will fire you with a bad recommendation, I will do a 3 to 5-day SQL training class for the company for expenses and $500/day.  

    My other choice is to use your public postings as bad examples in the next edition of one of my books. That will follow you for the rest of your career. 


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

    Saturday, August 10, 2013 3:13 AM
  • we are actually looking for some sql training, well not so much a typical training, specifically we are interested in the new features of sql 2014 ctp1 and hopefully rtm (mainly clustered column store indexes and in memory tables). are you able to provide this? if yes, send me the details pls.
    Saturday, August 10, 2013 6:53 AM