Creating Day Of Week columns

Laurie McIntosh

Delphi Developer

Creating Day Of Week columns

2006-01-09 10:44:21 AM
delphi171
Hi all
I've got a table that looks like this
VNAME BDATE QTY VALUE
Suess 1/1/2006 2 3.50
Suess 1/2/2006 3 4.70
Suess 1/8/2006 5 8.30
King 1/1/2006 1 5.30
King 1/2/2006 3 11.6
King 1/8/2006 2 3.20
Now. I want to create from this, via the magic of SQL,
Name SuQ SuP MoQ MoP TuQ TuP WeQ WeP ThQ ThP FrQ FrP SaQ SaP
Suess 7 11.80 3 4.70 0 0 0 0 0 0 0 0 0 0
King 3 8.50 3 11.60 0 0 0 0 0 0 0 0 0 0
So, I want to create a result set that has the days-of-the-week
quantities and Values (I'm not multiplying Qty x Price here) as columns.
I suspect there's a way to do this with case statements... anyone done
anything like this?
Regards,
Laurie

Craig Stuntz [TeamB]

Delphi Developer

2006-01-09 07:52:44 PM

Re:Creating Day Of Week columns

Laurie McIntosh writes:

Quote

So, I want to create a result set that has the days-of-the-week
quantities and Values (I'm not multiplying Qty x Price here) as
columns. I suspect there's a way to do this with case statements...
You can do that, I think. Try something like this:

SELECT
VNAME,
CASE EXTRACT(WEEKDAY FROM BDATE)
WHEN 0 THEN SUM(QTY)
ELSE NULL
END AS SuQ,
CASE EXTRACT(WEEKDAY FROM BDATE)
WHEN 0 THEN SUM(VALUE)
ELSE NULL
END AS SuP,
[...]
FROM
MY_TABLE
GROUP BY
VNAME
If CASE {*word*88}s on using a function as the selector (I can not remember
if this is allowed or not) then make the EXTRACT WEEKDAY a computed
field.
-Craig
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
qc.borland.com -- Vote for important issues

Laurie McIntosh

Delphi Developer

2006-01-10 04:32:30 AM

Re:Creating Day Of Week columns

Perfect Craig - I had to put the SUM() around the CASE, but that was
exactly the sort of thing I was after.
Thanks a lot, that is an elegant solution.
Regards,
---=L
Craig Stuntz [TeamB] writes:

Quote

Laurie McIntosh writes:

>So, I want to create a result set that has the days-of-the-week
>quantities and Values (I'm not multiplying Qty x Price here) as
>columns. I suspect there's a way to do this with case statements...

You can do that, I think. Try something like this:

SELECT
VNAME,
CASE EXTRACT(WEEKDAY FROM BDATE)
WHEN 0 THEN SUM(QTY)
ELSE NULL
END AS SuQ,
CASE EXTRACT(WEEKDAY FROM BDATE)
WHEN 0 THEN SUM(VALUE)
ELSE NULL
END AS SuP,
[...]
FROM
MY_TABLE
GROUP BY
VNAME

If CASE {*word*88}s on using a function as the selector (I can not remember
if this is allowed or not) then make the EXTRACT WEEKDAY a computed
field.

-Craig