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...
>> 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
>|||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:
> 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
> >|||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