Get the correct order of the results.

  • Hi Team,

    my data looks like below.

    please help to get the correct data.

    create table #Year

    (

    Year VARCHAR(100),

    FIscalPeriod VARCHAR(100),

    FiscalQuarter VARCHAR(100)

    )

    insert into #Year

    select 2015,'FY','Y'

    UNION ALL

    select 2016,'FY','Y'

    UNION ALL

    select 2017,'FY','Y'

    UNION ALL

    select 2017,'FQ1','1'

    UNION ALL

    select 2015,'FQ3','3'

    UNION ALL

    select 2015,'FQ4','4'

    UNION ALL

    select 2016,'FG4','4'

    UNION ALL

    select 2017,'LTM','L'

    select * from #Year

    --Existing data:

    Year        FIscalPeriod      FiscalQuarter

    2015      FY                         Y

    2016      FY                        Y

    2017      FY                       Y

    2017      FQ1                 1

    2015      FQ3                  3

    2015      FQ4                 4

    2016     FG4                   4

    2017     LTM                  L

     

    Expected Data:

    Year        FIscalPeriod      FiscalQuarter

    2015      FY                         Y

    2016      FY                        Y

    2017      FY                       Y

    2015      FQ3                 3

    2015      FQ4                 4

    2016     FG4                   4

    2017      FQ1                 1

    2017     LTM                  L

     

     

     

  • You can write a case statement in your order by clause like below - however this will not be an ideal solution because of hardcoded values . I guess better way to do in create a lookup table be desired order and use that order by

    SELECT  *
    FROM #Year
    ORDER BY CASE WHEN FiscalQuarter = 'Y' THEN -2
    WHEN FiscalQuarter = 'L' THEN 99
    WHEN FiscalQuarter = 3 THEN -1
    WHEN FiscalQuarter = 4 THEN 0
    ELSE FiscalQuarter
    END;
  • thank you.

  • A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:

    CREATE TABLE Report_Periods

    (report_name CHAR(10) NOT NULL PRIMARY KEY,

    report_start_date DATE NOT NULL,

    report_end_date DATE NOT NULL,

    CONSTRAINT date_ordering

    CHECK (report_start_date <= report_end_date),

    ordinal_period INTEGER NOT NULL UNIQUE

    CHECK(ordinal_period > 0)

    etc);

    These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • The disadvantage is that it violates 1NF big time.  A "date" such as yyyy-mm-00 is not a date it is multiple dates.  Thus, it violates 1NF.

    Besides which, SQL Server does not allow such a "date".  Again, that's very logical since that value is not a date.

    What results would one expect from joining to a value such as yyyy-mm-00 or yyyy-00-00?  Does it join only other multi-value dates or does it join to every date within its range ... or both?

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Actually the problem is that SQL Server does not have an interval temporal data type. This is part of the full ANSI/ISO standard SQL. Intervals are considered scalars. I would also recommend downloading the book on temporal queries in SQL by Rick Snodgrass. It's a PDF at the University of Arizona website

    and it's free.

    The "yyyy-mm-00" and "yyyy-00-00" came from MySQL, but are not yet part of the ISO 8601 standard. The advantage is that they will sort properly with the other ISO 8601 formatted dates allowed in ANSI/ISO standard SQL.  We can follow Allen's Interval Algebra (just posted an article with a short description of this).

    If you want an analogy, consider (Longitude, Latitude) pairs that are reduced to a single point on the globe, but make no sense by themselves. If we wanted a single value for such a location then we could use other grid systems, such as HTM or that funny one that uses words that they use in Mongolia.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • yyyy-mm-00 is not an interval either.  The interval would be yyyy-mm-01 to yyyy-mm-{EOM}.

    yyyy-mm-00 sorta looks like a date, but it is not one, no matter how much fluff you put around it.  It's a horrible kludge, which normally you're against.  I don't see why you like that monstrosity.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply