Query SQL Column Date Range

Question

I'm having an issue at the moment whereby I've been presented with a database that has its data layed out as follows:

'ID | CALCULATIONS | 2015-06 | 2015-07 | 2015-08 | 2015-09
  1    Spend          100        300       
  1    Forecast       200        200       300       800
  2    Spend          300        400       600       300
  2    Forecast       200         0        900       200
  1    Spend                               400       700

I know how to write a 'conventional' query based on calculating a spend between say two columns i.e

ID | CALCULATIONS | Date | Amount 1 Spend 01/06/15 100

But is there a way to perform a query based on the layout of my current (problimatic) database? For example select x From x where date is between y & z ?

Thanks in advance

Please tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2015-10-21 14:15:26 +00:00
Commented Oct 21, 2015 at 14:15 — Gordon Linoff
– Gordon Linoff, Commented Oct 21, 2015 at 14:15
have you tried anything at all?

Vamsi Prabhala
– Vamsi Prabhala

2015-10-21 14:15:50 +00:00
Commented Oct 21, 2015 at 14:15 — Vamsi Prabhala
– Vamsi Prabhala, Commented Oct 21, 2015 at 14:15
Ask for a better formatted table, one with a date column.

jarlh
– jarlh

2015-10-21 14:16:15 +00:00
Commented Oct 21, 2015 at 14:16 — jarlh
– jarlh, Commented Oct 21, 2015 at 14:16
so they add a column for every month?

BryanOfEarth
– BryanOfEarth

2015-10-21 14:18:10 +00:00
Commented Oct 21, 2015 at 14:18 — BryanOfEarth
– BryanOfEarth, Commented Oct 21, 2015 at 14:18
Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2015-10-21 14:19:16 +00:00
Commented Oct 21, 2015 at 14:19 — user330315
– user330315, Commented Oct 21, 2015 at 14:19

Gordon Linoff · Accepted Answer · 2015-10-21 14:18:28Z

My best advice would be to create a view to put the table in a saner format:

create view v_table as
    select id, calculations, '2015-06' as yyyymm, ?2015-06? as value
    from t
    union all
    select id, calculations, '2015-07' as yyyymm, ?2015-07? as value
    from t
    union all
    . . .;

You can then use v_table to write the queries that you want. Note that the ? in the above is meant to be whatever escape delimiter is used for your database, typically double quotes, backticks, or square braces.

The "date" is a string in the format YYYY-MM, which can be used for equality and between comparisons.

D Stanley · Accepted Answer · 2015-10-21 14:45:21Z

0

You're looking for a unpivot. There are different ways to do it in various SQL systems, the "standard" SQL way would be something like:

Select ID, Calculations, '2015-06-01' As Date, [2015-06] As Amount
UNION
Select ID, Calculations, '2015-07-01' As Date, [2015-07] As Amount
UNION
Select ID, Calculations, '2015-08-01' As Date, [2015-08] As Amount
UNION
Select ID, Calculations, '2015-09-01' As Date, [2015-09] As Amount

Using the appropriate db-specific methods to convert '2015-06-01' to a DateTime value, and appropriate delimiters to reference the column 2015-06 by name.

edited Oct 21, 2015 at 14:45

answered Oct 21, 2015 at 14:18

D Stanley

153k12 gold badges189 silver badges257 bronze badges

2 Comments

user330315 Over a year ago

[2015-08] is not "standard" SQL

D Stanley Over a year ago

@a_horse_with_no_name True - added that caveat.

Collectives™ on Stack Overflow

Query SQL Column Date Range

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related