Need-a-Guru: Gaps in Sequences

I'm trying to find gaps in sequences of year + month values, but there's a
twist - I want to return the 'reverse' of the gaps; or the year + month
values that create a sequence Note below that a 'sequence' can be a single
month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL and
sample data:
-- DDL to create Coverage table
CREATE TABLE [dbo].[Coverage] (
[state] [char] (2),
[year] [smallint] NOT NULL ,
[month] [smallint] NOT NULL
)
GO
ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
(
[state],
[year],
[month]
)
GO
--Sample Data
INSERT INTO Coverage VALUES ('NJ', 2001, 12)
INSERT INTO Coverage VALUES ('NJ', 2002, 1)
INSERT INTO Coverage VALUES ('NJ', 2002, 2)
INSERT INTO Coverage VALUES ('NJ', 2002, 3)
INSERT INTO Coverage VALUES ('NJ', 2002, 5)
INSERT INTO Coverage VALUES ('NJ', 2002, 9)
INSERT INTO Coverage VALUES ('NJ', 2002, 10)
INSERT INTO Coverage VALUES ('NJ', 2002, 11)
INSERT INTO Coverage VALUES ('NJ', 2002, 12)
INSERT INTO Coverage VALUES ('NJ', 2003, 1)
INSERT INTO Coverage VALUES ('NJ', 2003, 5)
INSERT INTO Coverage VALUES ('NY', 2001, 1)
INSERT INTO Coverage VALUES ('NY', 2001, 2)
INSERT INTO Coverage VALUES ('NY', 2002, 7)
INSERT INTO Coverage VALUES ('NY', 2002, 8)
GO
Here's the output I'm trying to get from the above data:
+--+--+--+--+--+
|State | StartYear | StartMonth | EndYear | EndMonth |
+--+--+--+--+--+
| NJ | 2001 | 12 | 2002 | 3 |
| NJ | 2002 | 5 | 2002 | 5 |
| NJ | 2002 | 9 | 2003 | 1 |
| NJ | 2003 | 5 | 2003 | 5 |
| NY | 2001 | 1 | 2001 | 2 |
| NY | 2002 | 7 | 2002 | 8 |
+--+--+--+--+--+
(Note: If the table doesn't line up right, copy and paste it into NotePad
in a fixed-width font).
As you can see from the above, I want to query and summarize the year +
month ranges for which coverage is available per state. TIA for any help on
this one!Ok, here's goes...
First you need to know the the entire sequence which you are interested in.
To Get this create a temporary table with the Year and Month Column. Then
insert the years and months which cover that entire range. The temp table
looks like this
Create Table #MyTemp( Year smallint not null, Month smallint not null )
Insert Into #MyTemp(Year, Month) Values (2001,1)
Insert Into #MyTemp(Year, Month) Values (2001,2)
:
Insert Into #MyTemp(Year, Month) Values (2003,11)
Insert Into #MyTemp(Year, Month) Values (2003,12)
Next you can perform a left outer join on your table to match your known
values to the range. Finally, adding a where clause to remove rows where
the joined table has no available row...
Select
a.Year,
a.Month
From #MyTemp a
Left Outer Join (Select Distinct
year,
month
From Coverage) c On c.year = a.Year
And c.month =
a.month
where c.year is null
Hope this helps
Regards
Colin Dawson
MCP.
www.cjdawson.com
"Michael C#" <howsa@.boutdat.com> wrote in message
news:eq$kbYFbFHA.1312@.TK2MSFTNGP09.phx.gbl...
> I'm trying to find gaps in sequences of year + month values, but there's a
> twist - I want to return the 'reverse' of the gaps; or the year + month
> values that create a sequence Note below that a 'sequence' can be a single
> month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL
> and sample data:
> -- DDL to create Coverage table
> CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2),
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
> GO
> ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
> GO
> --Sample Data
> INSERT INTO Coverage VALUES ('NJ', 2001, 12)
> INSERT INTO Coverage VALUES ('NJ', 2002, 1)
> INSERT INTO Coverage VALUES ('NJ', 2002, 2)
> INSERT INTO Coverage VALUES ('NJ', 2002, 3)
> INSERT INTO Coverage VALUES ('NJ', 2002, 5)
> INSERT INTO Coverage VALUES ('NJ', 2002, 9)
> INSERT INTO Coverage VALUES ('NJ', 2002, 10)
> INSERT INTO Coverage VALUES ('NJ', 2002, 11)
> INSERT INTO Coverage VALUES ('NJ', 2002, 12)
> INSERT INTO Coverage VALUES ('NJ', 2003, 1)
> INSERT INTO Coverage VALUES ('NJ', 2003, 5)
> INSERT INTO Coverage VALUES ('NY', 2001, 1)
> INSERT INTO Coverage VALUES ('NY', 2001, 2)
> INSERT INTO Coverage VALUES ('NY', 2002, 7)
> INSERT INTO Coverage VALUES ('NY', 2002, 8)
> GO
> Here's the output I'm trying to get from the above data:
> +--+--+--+--+--+
> |State | StartYear | StartMonth | EndYear | EndMonth |
> +--+--+--+--+--+
> | NJ | 2001 | 12 | 2002 | 3 |
> | NJ | 2002 | 5 | 2002 | 5 |
> | NJ | 2002 | 9 | 2003 | 1 |
> | NJ | 2003 | 5 | 2003 | 5 |
> | NY | 2001 | 1 | 2001 | 2 |
> | NY | 2002 | 7 | 2002 | 8 |
> +--+--+--+--+--+
> (Note: If the table doesn't line up right, copy and paste it into NotePad
> in a fixed-width font).
> As you can see from the above, I want to query and summarize the year +
> month ranges for which coverage is available per state. TIA for any help
> on this one!
>|||How about this?
alter table Coverage add RangeDate smalldatetime null
go
update Coverage
set RangeDate = convert(smalldatetime, convert(varchar(4), year) + '-' +
convert(varchar(2), month) + '-01')
go
declare @.start table ([id] int identity(1, 1), state char(2), [year]
smallint, [month] tinyint)
declare @.end table ([id] int identity(1, 1), state char(2), [year] smallint,
[month] tinyint)
insert into @.start(state, [year], [month])
select c1.state, c1.[year], c1.[month]
from Coverage c1
left join Coverage c2 on c1.state = c2.state and c1.rangedate = dateadd(mm,
1, c2.rangedate)
where c2.state is null
insert into @.end(state, [year], [month])
select c1.state, c1.[year], c1.[month]
from Coverage c1
left join Coverage c2 on c1.state = c2.state and c1.rangedate = dateadd(mm,
-1, c2.rangedate)
where c2.state is null
select s.state, s.[year] as StartYear, s.[month] as StartMonth, e.[year] as
EndYear, e.[month] as EndMonth
from @.start s
join @.end e on s.[id] = e.[id] and s.state = e.state
"Michael C#" wrote:

> I'm trying to find gaps in sequences of year + month values, but there's a
> twist - I want to return the 'reverse' of the gaps; or the year + month
> values that create a sequence Note below that a 'sequence' can be a single
> month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL a
nd
> sample data:
> -- DDL to create Coverage table
> CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2),
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
> GO
> ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
> GO
> --Sample Data
> INSERT INTO Coverage VALUES ('NJ', 2001, 12)
> INSERT INTO Coverage VALUES ('NJ', 2002, 1)
> INSERT INTO Coverage VALUES ('NJ', 2002, 2)
> INSERT INTO Coverage VALUES ('NJ', 2002, 3)
> INSERT INTO Coverage VALUES ('NJ', 2002, 5)
> INSERT INTO Coverage VALUES ('NJ', 2002, 9)
> INSERT INTO Coverage VALUES ('NJ', 2002, 10)
> INSERT INTO Coverage VALUES ('NJ', 2002, 11)
> INSERT INTO Coverage VALUES ('NJ', 2002, 12)
> INSERT INTO Coverage VALUES ('NJ', 2003, 1)
> INSERT INTO Coverage VALUES ('NJ', 2003, 5)
> INSERT INTO Coverage VALUES ('NY', 2001, 1)
> INSERT INTO Coverage VALUES ('NY', 2001, 2)
> INSERT INTO Coverage VALUES ('NY', 2002, 7)
> INSERT INTO Coverage VALUES ('NY', 2002, 8)
> GO
> Here's the output I'm trying to get from the above data:
> +--+--+--+--+--+
> |State | StartYear | StartMonth | EndYear | EndMonth |
> +--+--+--+--+--+
> | NJ | 2001 | 12 | 2002 | 3 |
> | NJ | 2002 | 5 | 2002 | 5 |
> | NJ | 2002 | 9 | 2003 | 1 |
> | NJ | 2003 | 5 | 2003 | 5 |
> | NY | 2001 | 1 | 2001 | 2 |
> | NY | 2002 | 7 | 2002 | 8 |
> +--+--+--+--+--+
> (Note: If the table doesn't line up right, copy and paste it into NotePad
> in a fixed-width font).
> As you can see from the above, I want to query and summarize the year +
> month ranges for which coverage is available per state. TIA for any help
on
> this one!
>
>|||Michael,
Here's one solution. I've added a computed column to hold the date
as one unit, so you'll have to stick in the expression if you can't do that.
CREATE TABLE [dbo].[Coverage] (
[state] [char] (2) NOT NULL,
[year] [smallint] NOT NULL ,
[month] [smallint] NOT NULL
)
GO
ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
(
[state],
[year],
[month]
)
GO
--Sample Data
INSERT INTO Coverage VALUES ('NJ', 2001, 12)
INSERT INTO Coverage VALUES ('NJ', 2002, 1)
INSERT INTO Coverage VALUES ('NJ', 2002, 2)
INSERT INTO Coverage VALUES ('NJ', 2002, 3)
INSERT INTO Coverage VALUES ('NJ', 2002, 5)
INSERT INTO Coverage VALUES ('NJ', 2002, 9)
INSERT INTO Coverage VALUES ('NJ', 2002, 10)
INSERT INTO Coverage VALUES ('NJ', 2002, 11)
INSERT INTO Coverage VALUES ('NJ', 2002, 12)
INSERT INTO Coverage VALUES ('NJ', 2003, 1)
INSERT INTO Coverage VALUES ('NJ', 2003, 5)
INSERT INTO Coverage VALUES ('NY', 2001, 1)
INSERT INTO Coverage VALUES ('NY', 2001, 2)
INSERT INTO Coverage VALUES ('NY', 2002, 7)
INSERT INTO Coverage VALUES ('NY', 2002, 8)
GO
alter table Coverage add ymd as
dateadd(year,[year]-1990,dateadd(month,[month]-1,'19900101'))
go
select
state,
min(ymd) as Startym,
max(ymd) as Endym
from (
select
C1.state,
C1.ymd,
count(C2.ymd)-datediff(month,'19900101',C1.ymd) as rk
from Coverage as C1
join Coverage as C2
on C1.state = C2.state
and C2.ymd <= C1.ymd
group by
C1.state,
C1.ymd
) T
group by state, rk
order by state, Startym
go
drop table Coverage
-- Steve Kass
-- Drew University
Michael C# wrote:

>I'm trying to find gaps in sequences of year + month values, but there's a
>twist - I want to return the 'reverse' of the gaps; or the year + month
>values that create a sequence Note below that a 'sequence' can be a single
>month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL an
d
>sample data:
>-- DDL to create Coverage table
>CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2),
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
>GO
>ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
>GO
>--Sample Data
>INSERT INTO Coverage VALUES ('NJ', 2001, 12)
>INSERT INTO Coverage VALUES ('NJ', 2002, 1)
>INSERT INTO Coverage VALUES ('NJ', 2002, 2)
>INSERT INTO Coverage VALUES ('NJ', 2002, 3)
>INSERT INTO Coverage VALUES ('NJ', 2002, 5)
>INSERT INTO Coverage VALUES ('NJ', 2002, 9)
>INSERT INTO Coverage VALUES ('NJ', 2002, 10)
>INSERT INTO Coverage VALUES ('NJ', 2002, 11)
>INSERT INTO Coverage VALUES ('NJ', 2002, 12)
>INSERT INTO Coverage VALUES ('NJ', 2003, 1)
>INSERT INTO Coverage VALUES ('NJ', 2003, 5)
>INSERT INTO Coverage VALUES ('NY', 2001, 1)
>INSERT INTO Coverage VALUES ('NY', 2001, 2)
>INSERT INTO Coverage VALUES ('NY', 2002, 7)
>INSERT INTO Coverage VALUES ('NY', 2002, 8)
>GO
>Here's the output I'm trying to get from the above data:
>+--+--+--+--+--+
>|State | StartYear | StartMonth | EndYear | EndMonth |
>+--+--+--+--+--+
>| NJ | 2001 | 12 | 2002 | 3 |
>| NJ | 2002 | 5 | 2002 | 5 |
>| NJ | 2002 | 9 | 2003 | 1 |
>| NJ | 2003 | 5 | 2003 | 5 |
>| NY | 2001 | 1 | 2001 | 2 |
>| NY | 2002 | 7 | 2002 | 8 |
>+--+--+--+--+--+
>(Note: If the table doesn't line up right, copy and paste it into NotePad
>in a fixed-width font).
>As you can see from the above, I want to query and summarize the year +
>month ranges for which coverage is available per state. TIA for any help o
n
>this one!
>
>|||Thanks Colin, but that actually just returns a simple list of the year +
month combos that are not in the Coverage table, like this:
+--+--+
| Year | Month |
+--+--+
| 2001 | 3 |
| 2001 | 4 |
| 2001 | 5 |
| 2001 | 6 |
| 2001 | 7 |
| ... | ... |
+--+--+
This might be a first step toward getting the output I'm looking for (see
below), but it's not quite the same. Thanks.
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:ENGpe.16958$bl3.10718@.fe1.news.blueyonder.co.uk...
> Ok, here's goes...
> First you need to know the the entire sequence which you are interested
> in. To Get this create a temporary table with the Year and Month Column.
> Then insert the years and months which cover that entire range. The
> temp table looks like this
> Create Table #MyTemp( Year smallint not null, Month smallint not null )
> Insert Into #MyTemp(Year, Month) Values (2001,1)
> Insert Into #MyTemp(Year, Month) Values (2001,2)
> :
> Insert Into #MyTemp(Year, Month) Values (2003,11)
> Insert Into #MyTemp(Year, Month) Values (2003,12)
> Next you can perform a left outer join on your table to match your known
> values to the range. Finally, adding a where clause to remove rows where
> the joined table has no available row...
> Select
> a.Year,
> a.Month
> From #MyTemp a
> Left Outer Join (Select Distinct
> year,
> month
> From Coverage) c On c.year = a.Year
> And c.month =
> a.month
> where c.year is null
> Hope this helps
> Regards
> Colin Dawson
> MCP.
> www.cjdawson.com
>
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:eq$kbYFbFHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||Perfect! Thank you!
"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:1EDB2A8A-E817-43EA-95C1-491ABB3D2995@.microsoft.com...
> How about this?
> alter table Coverage add RangeDate smalldatetime null
> go
> update Coverage
> set RangeDate = convert(smalldatetime, convert(varchar(4), year) + '-' +
> convert(varchar(2), month) + '-01')
> go
> declare @.start table ([id] int identity(1, 1), state char(2), [year]
> smallint, [month] tinyint)
> declare @.end table ([id] int identity(1, 1), state char(2), [year]
> smallint,
> [month] tinyint)
> insert into @.start(state, [year], [month])
> select c1.state, c1.[year], c1.[month]
> from Coverage c1
> left join Coverage c2 on c1.state = c2.state and c1.rangedate =
> dateadd(mm,
> 1, c2.rangedate)
> where c2.state is null
> insert into @.end(state, [year], [month])
> select c1.state, c1.[year], c1.[month]
> from Coverage c1
> left join Coverage c2 on c1.state = c2.state and c1.rangedate =
> dateadd(mm,
> -1, c2.rangedate)
> where c2.state is null
> select s.state, s.[year] as StartYear, s.[month] as StartMonth, e.[year]
> as
> EndYear, e.[month] as EndMonth
> from @.start s
> join @.end e on s.[id] = e.[id] and s.state = e.state
>
> "Michael C#" wrote:
>|||Thanks Steve! Excellent solution!
"Steve Kass" <skass@.drew.edu> wrote in message
news:uzjPu6FbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Michael,
> Here's one solution. I've added a computed column to hold the date
> as one unit, so you'll have to stick in the expression if you can't do
> that.
> CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2) NOT NULL,
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
> GO
> ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
> GO
> --Sample Data
> INSERT INTO Coverage VALUES ('NJ', 2001, 12)
> INSERT INTO Coverage VALUES ('NJ', 2002, 1)
> INSERT INTO Coverage VALUES ('NJ', 2002, 2)
> INSERT INTO Coverage VALUES ('NJ', 2002, 3)
> INSERT INTO Coverage VALUES ('NJ', 2002, 5)
> INSERT INTO Coverage VALUES ('NJ', 2002, 9)
> INSERT INTO Coverage VALUES ('NJ', 2002, 10)
> INSERT INTO Coverage VALUES ('NJ', 2002, 11)
> INSERT INTO Coverage VALUES ('NJ', 2002, 12)
> INSERT INTO Coverage VALUES ('NJ', 2003, 1)
> INSERT INTO Coverage VALUES ('NJ', 2003, 5)
> INSERT INTO Coverage VALUES ('NY', 2001, 1)
> INSERT INTO Coverage VALUES ('NY', 2001, 2)
> INSERT INTO Coverage VALUES ('NY', 2002, 7)
> INSERT INTO Coverage VALUES ('NY', 2002, 8)
> GO
> alter table Coverage add ymd as
> dateadd(year,[year]-1990,dateadd(month,[month]-1,'19900101'))
> go
> select
> state,
> min(ymd) as Startym,
> max(ymd) as Endym
> from (
> select
> C1.state,
> C1.ymd,
> count(C2.ymd)-datediff(month,'19900101',C1.ymd) as rk
> from Coverage as C1
> join Coverage as C2
> on C1.state = C2.state
> and C2.ymd <= C1.ymd
> group by
> C1.state,
> C1.ymd
> ) T
> group by state, rk
> order by state, Startym
> go
> drop table Coverage
> -- Steve Kass
> -- Drew University
> Michael C# wrote:
>|||Hey Steve,
I was playing around with your solution, and I added a county column to the
table as well as the state. Problem is that now for some reason it kicks
back many of the dates separately (but not all of them?) For instance:
+-+--+--+--+--+--+--+
|State | County | StartYear | StartMonth | EndYear | EndMonth |
+--+--+--+--+--+--+
| NJ | 001 | 2001 | 12 | 2001 | 12 |
| NJ | 001 | 2002 | 1 | 2002 | 1 |
| NJ | 001 | 2002 | 2 | 2002 | 2 |
| ... | ... | ... | ... | ... | ... |
+--+--+--+--+--+--+
I'm using a CHAR(3) standard 3-digit FIPS code for the County column. For
some reason I'm getting some dates that should be rolled up as one range
coming out as multiple one-month ranges. I can't post the code right now,
but will get it up there as soon as I get a chance. Thanks!
"Steve Kass" <skass@.drew.edu> wrote in message
news:uzjPu6FbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Michael,
> Here's one solution. I've added a computed column to hold the date
> as one unit, so you'll have to stick in the expression if you can't do
> that.
> CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2) NOT NULL,
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
> GO
> ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
> GO
> --Sample Data
> INSERT INTO Coverage VALUES ('NJ', 2001, 12)
> INSERT INTO Coverage VALUES ('NJ', 2002, 1)
> INSERT INTO Coverage VALUES ('NJ', 2002, 2)
> INSERT INTO Coverage VALUES ('NJ', 2002, 3)
> INSERT INTO Coverage VALUES ('NJ', 2002, 5)
> INSERT INTO Coverage VALUES ('NJ', 2002, 9)
> INSERT INTO Coverage VALUES ('NJ', 2002, 10)
> INSERT INTO Coverage VALUES ('NJ', 2002, 11)
> INSERT INTO Coverage VALUES ('NJ', 2002, 12)
> INSERT INTO Coverage VALUES ('NJ', 2003, 1)
> INSERT INTO Coverage VALUES ('NJ', 2003, 5)
> INSERT INTO Coverage VALUES ('NY', 2001, 1)
> INSERT INTO Coverage VALUES ('NY', 2001, 2)
> INSERT INTO Coverage VALUES ('NY', 2002, 7)
> INSERT INTO Coverage VALUES ('NY', 2002, 8)
> GO
> alter table Coverage add ymd as
> dateadd(year,[year]-1990,dateadd(month,[month]-1,'19900101'))
> go
> select
> state,
> min(ymd) as Startym,
> max(ymd) as Endym
> from (
> select
> C1.state,
> C1.ymd,
> count(C2.ymd)-datediff(month,'19900101',C1.ymd) as rk
> from Coverage as C1
> join Coverage as C2
> on C1.state = C2.state
> and C2.ymd <= C1.ymd
> group by
> C1.state,
> C1.ymd
> ) T
> group by state, rk
> order by state, Startym
> go
> drop table Coverage
> -- Steve Kass
> -- Drew University
<<SNIP>>|||>> I was playing around with your solution, and I added a county column to
You can use the same logic as Steve posted for additional columns:
CREATE VIEW Coverages ( state, county, dt, diff )
AS
SELECT state, County,
CAST( STR( c1."month", 2 ) + '/01/'
+ STR( c1."year", 4 ) AS DATETIME ),
( SELECT COUNT( * )
FROM Coverage c2
WHERE c1.state = c2.state AND c1.County = c2.County
AND CAST( STR( c2."month", 2 ) + '/01/'
+ STR( c2."year", 4 ) AS DATETIME )
<= CAST( STR( c1."month", 2 ) + '/01/'
+ STR( c1."year", 4 ) AS DATETIME ) )
FROM Coverage c1
GO
Now you can do:
SELECT state, county,
YEAR( MIN( dt ) ) AS "StartYear",
MONTH( MIN( dt ) ) AS "StartMonth",
YEAR( MAX( dt ) ) AS "EndYear",
MONTH( MAX( dt ) ) AS "EndMonth",
FROM Coverages
GROUP BY state, county, diff - DATEDIFF( m, 0, dt ) ;
Anith|||Just got home and saw your message. I'll try your view tomorrow. I tried
adding the County to Steve's code very similarly to what you did in the
view, but for some reason it kicked out the strange results I posted
previously. I'll post more information and the results of trying your
method when I get into the office tomorrow. I'm thinking that the data I'm
using might be screwy, I'm missing something in the DateDiff operation, or I
just completely screwed the pooch on this one :). I'll post more tomorrow.
Thanks for the help guys!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u$pQceHbFHA.724@.TK2MSFTNGP12.phx.gbl...
> You can use the same logic as Steve posted for additional columns:
> CREATE VIEW Coverages ( state, county, dt, diff )
> AS
> SELECT state, County,
> CAST( STR( c1."month", 2 ) + '/01/'
> + STR( c1."year", 4 ) AS DATETIME ),
> ( SELECT COUNT( * )
> FROM Coverage c2
> WHERE c1.state = c2.state AND c1.County = c2.County
> AND CAST( STR( c2."month", 2 ) + '/01/'
> + STR( c2."year", 4 ) AS DATETIME )
> <= CAST( STR( c1."month", 2 ) + '/01/'
> + STR( c1."year", 4 ) AS DATETIME ) )
> FROM Coverage c1
> GO
> Now you can do:
> SELECT state, county,
> YEAR( MIN( dt ) ) AS "StartYear",
> MONTH( MIN( dt ) ) AS "StartMonth",
> YEAR( MAX( dt ) ) AS "EndYear",
> MONTH( MAX( dt ) ) AS "EndMonth",
> FROM Coverages
> GROUP BY state, county, diff - DATEDIFF( m, 0, dt ) ;
> --
> Anith
>