Home All Groups Group Topic Archive Search About

TSQL TIme Series

microsoft.public.sqlserver.programming
Author
31 Aug 2006 10:20 PM
Kayode Yusuf
have a problem with time series.



This is my problem :



Scenario 1 – I have the output below which is made up intervals with  hourly
repeating values



StartDate                                  EndDate                          
         DMask  MW1    MW2    MW3     MW4   MW5    MW6     MW7   MW8

2006-08-24 00:00:00.000            2006-08-25 00:00:00.000            56    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000

2006-08-26 00:00:00.000            2006-08-27 00:00:00.000            71    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300 45.2300

2006-08-28 00:00:00.000            2006-09-01 00:00:00.000            23456 
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000

2006-09-02 00:00:00.000            2006-09-04 00:00:00.000            712   
   45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300 45.2300

2006-09-05 00:00:00.000            2006-09-08 00:00:00.000            3456  
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000

2006-09-09 00:00:00.000            2006-09-10 00:00:00.000            71    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300 45.2300

2006-09-11 00:00:00.000            2006-09-15 00:00:00.000            23456 
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000

2006-09-16 00:00:00.000            2006-09-17 00:00:00.000            71    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300 45.2300

2006-09-18 00:00:00.000            2006-09-22 00:00:00.000            23456 
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000

2006-09-23 00:00:00.000            2006-09-24 00:00:00.000            71    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300 45.2300

2006-09-25 00:00:00.000            2006-09-29 00:00:00.000            23456 
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000

2006-09-30 00:00:00.000            2006-10-01 00:00:00.000            71    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300 45.2300

2006-10-02 00:00:00.000            2006-10-06 00:00:00.000            23456 
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000

2006-10-07 00:00:00.000            2006-10-08 00:00:00.000            71    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300 45.2300

2006-10-09 00:00:00.000            2006-10-13 00:00:00.000            23456 
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000

2006-10-14 00:00:00.000            2006-10-15 00:00:00.000            71    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300 45.2300

2006-10-16 00:00:00.000            2006-10-20 00:00:00.000            23456 
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000



I need to have a select statement  that will return repeating profile based
on the days of the week. So . I want something like



2006-08-24 00:00:00.000            2006-10-20 00:00:00.000            23456 
45.2300 45.2300 45.2300 45.2300 45.2300 45.2300  0.0000   0.0000

2006-08-26 00:00:00.000            2006-10-15 00:00:00.000            71    
    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300  45.2300

Author
1 Sep 2006 4:47 AM
Martin Poon [MVP]
SELECT min(StartDate) as StartDate
        , max(EndDate) as EndDate
        , DMask, MW1, MW2, ...
    from MyTable
    group by DMask, MW1, MW2, ...

--
Martin C K Poon
Microsoft MVP - SQL Server
----------------------------------------------------------
Show quote
"Kayode Yusuf" <KayodeYu***@discussions.microsoft.com> wrote in message
news:D2A6A220-53BA-490D-8605-B37E780F6729@microsoft.com...
> have a problem with time series.
>
>
>
> This is my problem :
>
>
>
> Scenario 1 ¡V I have the output below which is made up intervals with
> hourly
> repeating values
>
>
>
> StartDate                                  EndDate
>         DMask  MW1    MW2    MW3     MW4   MW5    MW6     MW7   MW8
>
> 2006-08-24 00:00:00.000            2006-08-25 00:00:00.000            56
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
> 0.0000
>
> 2006-08-26 00:00:00.000            2006-08-27 00:00:00.000            71
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> 45.2300
>
> 2006-08-28 00:00:00.000            2006-09-01 00:00:00.000
> 23456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
>
> 2006-09-02 00:00:00.000            2006-09-04 00:00:00.000            712
>   45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> 45.2300
>
> 2006-09-05 00:00:00.000            2006-09-08 00:00:00.000            3456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
>
> 2006-09-09 00:00:00.000            2006-09-10 00:00:00.000            71
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> 45.2300
>
> 2006-09-11 00:00:00.000            2006-09-15 00:00:00.000
> 23456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
>
> 2006-09-16 00:00:00.000            2006-09-17 00:00:00.000            71
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> 45.2300
>
> 2006-09-18 00:00:00.000            2006-09-22 00:00:00.000
> 23456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
>
> 2006-09-23 00:00:00.000            2006-09-24 00:00:00.000            71
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> 45.2300
>
> 2006-09-25 00:00:00.000            2006-09-29 00:00:00.000
> 23456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
>
> 2006-09-30 00:00:00.000            2006-10-01 00:00:00.000            71
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> 45.2300
>
> 2006-10-02 00:00:00.000            2006-10-06 00:00:00.000
> 23456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
>
> 2006-10-07 00:00:00.000            2006-10-08 00:00:00.000            71
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> 45.2300
>
> 2006-10-09 00:00:00.000            2006-10-13 00:00:00.000
> 23456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
>
> 2006-10-14 00:00:00.000            2006-10-15 00:00:00.000            71
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> 45.2300
>
> 2006-10-16 00:00:00.000            2006-10-20 00:00:00.000
> 23456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
>
>
>
> I need to have a select statement  that will return repeating profile
> based
> on the days of the week. So . I want something like
>
>
>
> 2006-08-24 00:00:00.000            2006-10-20 00:00:00.000
> 23456
> 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300  0.0000   0.0000
>
> 2006-08-26 00:00:00.000            2006-10-15 00:00:00.000            71
>    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300  45.2300
>
>
Author
1 Sep 2006 2:02 PM
Kayode Yusuf
Thanks for the feedback but that would not work. The result is supposed to be
two
rows based on the DayMask ( which is the main thing) - We want to group by
repeating Days of the Week where there is no other Day withing the Start Date
and EndDate with a different profile.

Show quote
"Martin Poon [MVP]" wrote:

> SELECT min(StartDate) as StartDate
>         , max(EndDate) as EndDate
>         , DMask, MW1, MW2, ...
>     from MyTable
>     group by DMask, MW1, MW2, ...
>
> --
> Martin C K Poon
> Microsoft MVP - SQL Server
> ----------------------------------------------------------
> "Kayode Yusuf" <KayodeYu***@discussions.microsoft.com> wrote in message
> news:D2A6A220-53BA-490D-8605-B37E780F6729@microsoft.com...
> > have a problem with time series.
> >
> >
> >
> > This is my problem :
> >
> >
> >
> > Scenario 1 ¡V I have the output below which is made up intervals with
> > hourly
> > repeating values
> >
> >
> >
> > StartDate                                  EndDate
> >         DMask  MW1    MW2    MW3     MW4   MW5    MW6     MW7   MW8
> >
> > 2006-08-24 00:00:00.000            2006-08-25 00:00:00.000            56
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
> > 0.0000
> >
> > 2006-08-26 00:00:00.000            2006-08-27 00:00:00.000            71
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > 45.2300
> >
> > 2006-08-28 00:00:00.000            2006-09-01 00:00:00.000
> > 23456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
> >
> > 2006-09-02 00:00:00.000            2006-09-04 00:00:00.000            712
> >   45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > 45.2300
> >
> > 2006-09-05 00:00:00.000            2006-09-08 00:00:00.000            3456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
> >
> > 2006-09-09 00:00:00.000            2006-09-10 00:00:00.000            71
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > 45.2300
> >
> > 2006-09-11 00:00:00.000            2006-09-15 00:00:00.000
> > 23456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
> >
> > 2006-09-16 00:00:00.000            2006-09-17 00:00:00.000            71
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > 45.2300
> >
> > 2006-09-18 00:00:00.000            2006-09-22 00:00:00.000
> > 23456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
> >
> > 2006-09-23 00:00:00.000            2006-09-24 00:00:00.000            71
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > 45.2300
> >
> > 2006-09-25 00:00:00.000            2006-09-29 00:00:00.000
> > 23456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
> >
> > 2006-09-30 00:00:00.000            2006-10-01 00:00:00.000            71
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > 45.2300
> >
> > 2006-10-02 00:00:00.000            2006-10-06 00:00:00.000
> > 23456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
> >
> > 2006-10-07 00:00:00.000            2006-10-08 00:00:00.000            71
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > 45.2300
> >
> > 2006-10-09 00:00:00.000            2006-10-13 00:00:00.000
> > 23456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
> >
> > 2006-10-14 00:00:00.000            2006-10-15 00:00:00.000            71
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > 45.2300
> >
> > 2006-10-16 00:00:00.000            2006-10-20 00:00:00.000
> > 23456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000  0.0000
> >
> >
> >
> > I need to have a select statement  that will return repeating profile
> > based
> > on the days of the week. So . I want something like
> >
> >
> >
> > 2006-08-24 00:00:00.000            2006-10-20 00:00:00.000
> > 23456
> > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300  0.0000   0.0000
> >
> > 2006-08-26 00:00:00.000            2006-10-15 00:00:00.000            71
> >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300  45.2300
> >
> >
>
>
>
Author
1 Sep 2006 2:46 PM
Martin Poon [MVP]
>> We want to group by repeating Days of the Week
>> where there is no other Day withing the Start Date and EndDate
>> with a different profile.

Would you please elaborate and give a *simple* and/or concrete example?

--
Martin Poon
Microsoft MVP - SQL Server
====================================
Show quote
"Kayode Yusuf" <KayodeYu***@discussions.microsoft.com> ¦b¶l¥ó
news:50C91E96-F593-44B1-8BB3-417A8A0888F6@microsoft.com ¤¤¼¶¼g...
> Thanks for the feedback but that would not work. The result is supposed to
be
> two
> rows based on the DayMask ( which is the main thing) - We want to group by
> repeating Days of the Week where there is no other Day withing the Start
Date
> and EndDate with a different profile.
>
> "Martin Poon [MVP]" wrote:
>
> > SELECT min(StartDate) as StartDate
> >         , max(EndDate) as EndDate
> >         , DMask, MW1, MW2, ...
> >     from MyTable
> >     group by DMask, MW1, MW2, ...
> >
> > --
> > Martin C K Poon
> > Microsoft MVP - SQL Server
> > ----------------------------------------------------------
> > "Kayode Yusuf" <KayodeYu***@discussions.microsoft.com> wrote in message
> > news:D2A6A220-53BA-490D-8605-B37E780F6729@microsoft.com...
> > > have a problem with time series.
> > >
> > >
> > >
> > > This is my problem :
> > >
> > >
> > >
> > > Scenario 1 !V I have the output below which is made up intervals with
> > > hourly
> > > repeating values
> > >
> > >
> > >
> > > StartDate                                  EndDate
> > >         DMask  MW1    MW2    MW3     MW4   MW5    MW6     MW7   MW8
> > >
> > > 2006-08-24 00:00:00.000            2006-08-25 00:00:00.000
56
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
> > > 0.0000
> > >
> > > 2006-08-26 00:00:00.000            2006-08-27 00:00:00.000
71
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > > 45.2300
> > >
> > > 2006-08-28 00:00:00.000            2006-09-01 00:00:00.000
> > > 23456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
0.0000
> > >
> > > 2006-09-02 00:00:00.000            2006-09-04 00:00:00.000
712
> > >   45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > > 45.2300
> > >
> > > 2006-09-05 00:00:00.000            2006-09-08 00:00:00.000
3456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
0.0000
> > >
> > > 2006-09-09 00:00:00.000            2006-09-10 00:00:00.000
71
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > > 45.2300
> > >
> > > 2006-09-11 00:00:00.000            2006-09-15 00:00:00.000
> > > 23456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
0.0000
> > >
> > > 2006-09-16 00:00:00.000            2006-09-17 00:00:00.000
71
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > > 45.2300
> > >
> > > 2006-09-18 00:00:00.000            2006-09-22 00:00:00.000
> > > 23456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
0.0000
> > >
> > > 2006-09-23 00:00:00.000            2006-09-24 00:00:00.000
71
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > > 45.2300
> > >
> > > 2006-09-25 00:00:00.000            2006-09-29 00:00:00.000
> > > 23456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
0.0000
> > >
> > > 2006-09-30 00:00:00.000            2006-10-01 00:00:00.000
71
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > > 45.2300
> > >
> > > 2006-10-02 00:00:00.000            2006-10-06 00:00:00.000
> > > 23456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
0.0000
> > >
> > > 2006-10-07 00:00:00.000            2006-10-08 00:00:00.000
71
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > > 45.2300
> > >
> > > 2006-10-09 00:00:00.000            2006-10-13 00:00:00.000
> > > 23456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
0.0000
> > >
> > > 2006-10-14 00:00:00.000            2006-10-15 00:00:00.000
71
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            45.2300
> > > 45.2300
> > >
> > > 2006-10-16 00:00:00.000            2006-10-20 00:00:00.000
> > > 23456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300            0.0000
0.0000
> > >
> > >
> > >
> > > I need to have a select statement  that will return repeating profile
> > > based
> > > on the days of the week. So . I want something like
> > >
> > >
> > >
> > > 2006-08-24 00:00:00.000            2006-10-20 00:00:00.000
> > > 23456
> > > 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300  0.0000   0.0000
> > >
> > > 2006-08-26 00:00:00.000            2006-10-15 00:00:00.000
71
> > >    45.2300 45.2300 45.2300 45.2300 45.2300 45.2300 45.2300  45.2300
> > >
> > >
> >
> >
> >
Author
2 Sep 2006 8:28 PM
David Portas
Here's a guess together with your sample data and my assumptions about your
table and constraints. I've also assumed SQL Server 2005. If you are on an
earlier version then you can create the CTE (the derived table structure
called "t") as a view instead. Possibly there are more concise and efficient
solutions but this may give you some idea of what's possible.

CREATE TABLE MyTable (StartDate DATETIME NOT NULL, EndDate DATETIME NOT
NULL, CHECK (StartDate < EndDate), DMask INT NOT NULL, MW1 NUMERIC(6,4) NOT
NULL, MW2 NUMERIC(6,4) NOT NULL, MW3 NUMERIC(6,4) NOT NULL, MW4 NUMERIC(6,4)
NOT NULL, MW5 NUMERIC(6,4) NOT NULL, MW6 NUMERIC(6,4) NOT NULL, MW7
NUMERIC(6,4) NOT NULL, MW8 NUMERIC(6,4) NOT NULL /* PRIMARY KEY ??? not
specified */)

INSERT INTO MyTable (StartDate, EndDate, DMask, MW1, MW2, MW3, MW4, MW5,
MW6, MW7, MW8)
SELECT '20060824', '20060825',    56, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 UNION ALL
SELECT '20060826', '20060827',    71, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 45.2300, 45.2300 UNION ALL
SELECT '20060828', '20060901', 23456, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 UNION ALL
SELECT '20060902', '20060904',   712, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 45.2300, 45.2300 UNION ALL
SELECT '20060905', '20060908',  3456, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 UNION ALL
SELECT '20060909', '20060910',    71, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 45.2300, 45.2300 UNION ALL
SELECT '20060911', '20060915', 23456, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 UNION ALL
SELECT '20060916', '20060917',    71, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 45.2300, 45.2300 UNION ALL
SELECT '20060918', '20060922', 23456, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 UNION ALL
SELECT '20060923', '20060924',    71, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 45.2300, 45.2300 UNION ALL
SELECT '20060925', '20060929', 23456, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 UNION ALL
SELECT '20060930', '20061001',    71, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 45.2300, 45.2300 UNION ALL
SELECT '20061002', '20061006', 23456, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 UNION ALL
SELECT '20061007', '20061008',    71, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 45.2300, 45.2300 UNION ALL
SELECT '20061009', '20061013', 23456, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 UNION ALL
SELECT '20061014', '20061015',    71, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 45.2300, 45.2300 UNION ALL
SELECT '20061016', '20061020', 23456, 45.2300, 45.2300, 45.2300,
  45.2300, 45.2300, 45.2300, 0.0000, 0.0000 ;

WITH t AS
(SELECT *
FROM MyTable AS m
JOIN
  (
   SELECT StartDate
    FROM MyTable
   UNION
   SELECT EndDate
    FROM MyTable
  ) AS d (dt)
ON d.dt BETWEEN m.StartDate AND m.EndDate)
SELECT MIN(dt) AS StartDate, MAX(dt) AS EndDate,
DMask, MW1, MW2, MW3, MW4, MW5, MW6, MW7, MW8
FROM
  (
   SELECT t1.dt, t1.DMask,
     t1.MW1, t1.MW2, t1.MW3, t1.MW4, t1.MW5, t1.MW6, t1.MW7, t1.MW8,
    MIN(t2.dt) AS xdt
    FROM t AS t1
    LEFT JOIN t AS t2
     ON t1.DMask = t2.DMask
      AND t1.StartDate < t2.StartDate
      AND (t1.MW1 <> t2.MW1
       OR t1.MW2 <> t2.MW2
       OR t1.MW3 <> t2.MW3
       OR t1.MW4 <> t2.MW4
       OR t1.MW5 <> t2.MW5
       OR t1.MW6 <> t2.MW6
       OR t1.MW7 <> t2.MW7
       OR t1.MW8 <> t2.MW8)
    WHERE t1.DMask IN (23456, 71) /* ... for example */
    GROUP BY t1.dt, t1.DMask,
      t1.MW1, t1.MW2, t1.MW3, t1.MW4, t1.MW5, t1.MW6, t1.MW7, t1.MW8
  ) AS q
GROUP BY xdt, DMask, MW1, MW2, MW3, MW4, MW5, MW6, MW7, MW8 ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

AddThis Social Bookmark Button

Post Other interesting topics
parse out set of numbers from a column
stored procedure stopped working
How is the design for this complex requirement ?
trying to use a cursor to update table..
Iterating through SQL Server data