Day Of The Week Aggregate

I'm looking for a way to turn a list of dates into a string of
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be greatCheck into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Darn spellcheck.
Check into the use of datename().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
>|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:

>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
Hugo Kornelis, SQL Server MVP