Get the correct order of the results.

Question

Get the correct order of the results.

kbhanu15

SSCarpal Tunnel

Points: 4498
More actions
July 31, 2019 at 10:44 am

#3667488

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

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

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

@Taps SSC Eights! Points: 879 More actions · Answer 1

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;

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 2

kbhanu15

SSCarpal Tunnel

Points: 4498

July 31, 2019 at 1:09 pm

#3667548

thank you.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

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.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 5

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.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".