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

Help me in this Query

hello all,
I have a table with the Following structure. .. . .

Catid CategoryName Desc
_________________________________________
1 A Hello all Welcome
2 A Thanks For all
3 B Did u come yesterday ?
4 B Shall we got out.. ?

now i need a procedure which will accept a Categoryname as Arguement and will return the "desc " First time First record, second time second record,and so on depending on the N number of entries in each and evry category..
Please help me how do i go about..
I guess some how i am not getting any ideas..
Help me..

saiIS this what you are looking for?

create table #Category(CatID int,CategoryName varchar(10),[Desc] varchar(30))
go
insert into #Category values(1, 'A', 'Hello all Welcome')
insert into #Category values(2, 'A', 'Thanks For all')
insert into #Category values(3, 'B', 'Did u come yesterday ?')
insert into #Category values(4, 'B', 'Shall we got out.. ?')
go
select * from #Category
go
create procedure #GetCat(
@.CategoryName varchar(10),
@.Desc varchar(30) = Null)
as
select min([Desc])
from #Category
where CategoryName = @.CategoryName
and ([Desc] > @.Desc or @.Desc is null)
return 0
go
exec #GetCat 'A'
exec #GetCat 'A','Hello all Welcome'
exec #GetCat 'A','Thanks For all'
exec #GetCat 'B'
exec #GetCat 'B','Did u come yesterday ?'
exec #GetCat 'B','Shall we got out.. ?'
go|||hi Paul,
Thanks for the reply.

yeah i am looking for something similar but not the same..

I need like below..

exec #getdat 'A'
should give me 'Hello all WElcome'
and again if i execute the same (i.e.) exec #getdat 'A'
then it should give me 'Thanks .. '
and again if i execute 'hello all welcome '
like this...
i am looking for something like Rand function do we have any ?

Thanks
sai|||what's a RAND function?|||What about

select top 1 *
from #Category
where CategoryName=@.CategoryName
order by NEWID()

You also can order by CHECKSUM(NEWID()) for more random distribution of results.|||Thanks a lot Ispaleny... It solved my purpose...

thanks and regards
SAI