help on query

Hi,
I don't know if this is durable without cursor.
I have following record set: TABLEA have one column "Date"
All the dates are in order DESC.
Assuming every year need to have 4 quarters,
however in this example 1995 year only have 3 quarters ( missing one quarter
- 1995-06-30)
I want to write a query against this table to find out the missing quarter's
year, in this case, it's 1995
how can I do that?
Date
--
1999-12-31 00:00:00.000
1999-09-30 00:00:00.000
1999-06-30 00:00:00.000
1999-03-31 00:00:00.000
1998-12-31 00:00:00.000
1998-09-30 00:00:00.000
1998-06-30 00:00:00.000
1998-03-31 00:00:00.000
1997-12-31 00:00:00.000
1997-09-30 00:00:00.000
1997-06-30 00:00:00.000
1997-03-31 00:00:00.000
1996-12-31 00:00:00.000
1996-09-30 00:00:00.000
1996-06-30 00:00:00.000
1996-03-31 00:00:00.000
1995-12-31 00:00:00.000
1995-09-30 00:00:00.000
1995-03-31 00:00:00.000
1994-12-31 00:00:00.000
1994-09-30 00:00:00.000
1994-06-30 00:00:00.000
1994-03-31 00:00:00.000
1993-12-31 00:00:00.000
1993-09-30 00:00:00.000
1993-06-30 00:00:00.000
1993-03-31 00:00:00.000
1992-12-31 00:00:00.000
1992-09-30 00:00:00.000
1992-06-30 00:00:00.000
1992-03-31 00:00:00.000
1991-12-31 00:00:00.000
1991-09-30 00:00:00.000
1991-06-30 00:00:00.000
1991-03-31 00:00:00.000
1990-12-31 00:00:00.000
1990-09-30 00:00:00.000
1990-06-30 00:00:00.000
1990-03-31 00:00:00.000If you use a calendar table, you can join the two tables to find the
missing rows. The calendar table should have every quarter from every
year.
David Gugick
Quest Software
www.imceda.com
www.quest.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eZtpTiqoFHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I don't know if this is durable without cursor.
I have following record set: TABLEA have one column "Date"
All the dates are in order DESC.
Assuming every year need to have 4 quarters,
however in this example 1995 year only have 3 quarters ( missing one
quarter- 1995-06-30)
I want to write a query against this table to find out the missing
quarter's year, in this case, it's 1995
how can I do that?
Date
--
1999-12-31 00:00:00.000
1999-09-30 00:00:00.000
1999-06-30 00:00:00.000
1999-03-31 00:00:00.000
1998-12-31 00:00:00.000
1998-09-30 00:00:00.000
1998-06-30 00:00:00.000
1998-03-31 00:00:00.000
1997-12-31 00:00:00.000
1997-09-30 00:00:00.000
1997-06-30 00:00:00.000
1997-03-31 00:00:00.000
1996-12-31 00:00:00.000
1996-09-30 00:00:00.000
1996-06-30 00:00:00.000
1996-03-31 00:00:00.000
1995-12-31 00:00:00.000
1995-09-30 00:00:00.000
1995-03-31 00:00:00.000
1994-12-31 00:00:00.000
1994-09-30 00:00:00.000
1994-06-30 00:00:00.000
1994-03-31 00:00:00.000
1993-12-31 00:00:00.000
1993-09-30 00:00:00.000
1993-06-30 00:00:00.000
1993-03-31 00:00:00.000
1992-12-31 00:00:00.000
1992-09-30 00:00:00.000
1992-06-30 00:00:00.000
1992-03-31 00:00:00.000
1991-12-31 00:00:00.000
1991-09-30 00:00:00.000
1991-06-30 00:00:00.000
1991-03-31 00:00:00.000
1990-12-31 00:00:00.000
1990-09-30 00:00:00.000
1990-06-30 00:00:00.000
1990-03-31 00:00:00.000|||if you have and @.@.identity this will give you above which you are missing
quarter.
that should be in the order.Try this
SELECT p1.IDNO
FROM dbo.Table1 p INNER JOIN dbo.Table1 p1 ON p.IDNO = p1.IDNO
where DATEDIFF(MONTH,p.DATE,(SELECT p1.[DATE] FROM Table1 p1 WHERE
p.IDNO = P1.IDNO + 1 )) > 3
Regards
R.D
"David Gugick" wrote:

> If you use a calendar table, you can join the two tables to find the
> missing rows. The calendar table should have every quarter from every
> year.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:eZtpTiqoFHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I don't know if this is durable without cursor.
> I have following record set: TABLEA have one column "Date"
> All the dates are in order DESC.
> Assuming every year need to have 4 quarters,
> however in this example 1995 year only have 3 quarters ( missing one
> quarter- 1995-06-30)
> I want to write a query against this table to find out the missing
> quarter's year, in this case, it's 1995
> how can I do that?
>
> Date
> --
> 1999-12-31 00:00:00.000
> 1999-09-30 00:00:00.000
> 1999-06-30 00:00:00.000
> 1999-03-31 00:00:00.000
> 1998-12-31 00:00:00.000
> 1998-09-30 00:00:00.000
> 1998-06-30 00:00:00.000
> 1998-03-31 00:00:00.000
> 1997-12-31 00:00:00.000
> 1997-09-30 00:00:00.000
> 1997-06-30 00:00:00.000
> 1997-03-31 00:00:00.000
> 1996-12-31 00:00:00.000
> 1996-09-30 00:00:00.000
> 1996-06-30 00:00:00.000
> 1996-03-31 00:00:00.000
> 1995-12-31 00:00:00.000
> 1995-09-30 00:00:00.000
> 1995-03-31 00:00:00.000
> 1994-12-31 00:00:00.000
> 1994-09-30 00:00:00.000
> 1994-06-30 00:00:00.000
> 1994-03-31 00:00:00.000
> 1993-12-31 00:00:00.000
> 1993-09-30 00:00:00.000
> 1993-06-30 00:00:00.000
> 1993-03-31 00:00:00.000
> 1992-12-31 00:00:00.000
> 1992-09-30 00:00:00.000
> 1992-06-30 00:00:00.000
> 1992-03-31 00:00:00.000
> 1991-12-31 00:00:00.000
> 1991-09-30 00:00:00.000
> 1991-06-30 00:00:00.000
> 1991-03-31 00:00:00.000
> 1990-12-31 00:00:00.000
> 1990-09-30 00:00:00.000
> 1990-06-30 00:00:00.000
> 1990-03-31 00:00:00.000
>|||I Mean IDENTITY COLUMN. If you dont have one, you can generate on the fly.
"R.D" wrote:
> if you have and @.@.identity this will give you above which you are missing
> quarter.
> that should be in the order.Try this
> SELECT p1.IDNO
> FROM dbo.Table1 p INNER JOIN dbo.Table1 p1 ON p.IDNO = p1.IDNO
> where DATEDIFF(MONTH,p.DATE,(SELECT p1.[DATE] FROM Table1 p1 WHERE
> p.IDNO = P1.IDNO + 1 )) > 3
> Regards
> R.D
> "David Gugick" wrote:
>