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 on Dates

I'm new to SQL server...
I'm trying to create a date (only) with no time.
I'm importing the data from navchar to datetime and getting the time stamp. How do I configure the table to show only the date part?
I did not see a date datatype...
ThanksThat creature does not exist. You will have to use the convert function using a style - look at sql server books online under 'Cast and Convert'.
The time value stored when no time is provided is midnight - so you would have a date followed by 00:00:00.000. You might also consider smalldatetime.

For example, using northwind's orders table:

select convert(varchar(10), orderdate,101) from orders|||If my date field is indexed (2-3 million rows), then will the request read all records before converting and selecting?|||/*
--replace "origDateTime" with our datetime value/column

_ You can use datetime column with check constraint
( "origDateTime"=convert(datetime,convert(int,"origDateTime")) )
to ensure that there is no time saved, in the future you could need
time, so you only remove this constraint. Linking to date analysis
table will be done by indexed computed column on table level
( "compOnlyDate" AS convert(int,"origDateTime") )
to get additional precomputed information about date
(year,month,day in week,season,...) faster.
_ If you NEVER use time, consider using only int
or smallint ( convert(int,getdate())-30000 ),
but be prepared on problems with user reports and applications.
*/

Help needed with a query

Hi everyone,
I am implementing a dating club as a project for school.
I have a table called "Dates". It has the columns:
DateID, FirstMember, SecondMember, DateToMeet, Place
The table "MEMBERS" contains many things and of course:
MemberID , Username
Now, I want to make a query where I can join the two tables and display:
USERNAME1, USERNAME 2 (the 2 members that are going to meet), DateToMeet and
PLACE.
But the problem is tha both "FirstMember" and "SecondMember" of the table
"Dates" refer to he same column (namely "MemberID" ) of the table MEMBERS.
How can make such a query?
ThanksYou use a technique called aliasing where you give each logical table in
your query a unique name to which you will refer to it by in the rest of the
query, you can then refer to the same table multiple times.
SELECT Dates.DateToMeet, Dates.Place, First.UserName as Member1,
Second.UserName as Member2
FROM Dates
JOIN Members as First ON First.ID=Dates.FirstMember
JOIN Members as Second ON Second.ID=Dates.SecondMember
the 'as' is optional but it helps you to see where aliasing is used.
Mr Tea
http://mr-tea.blogspot.com
"Silver" <argytzak@.med.auth.gr> wrote in message
news:ct03t1$8p8$1@.nic.grnet.gr...
> Hi everyone,
> I am implementing a dating club as a project for school.
> I have a table called "Dates". It has the columns:
> DateID, FirstMember, SecondMember, DateToMeet, Place
> The table "MEMBERS" contains many things and of course:
> MemberID , Username
> Now, I want to make a query where I can join the two tables and display:
> USERNAME1, USERNAME 2 (the 2 members that are going to meet), DateToMeet
> and
> PLACE.
> But the problem is tha both "FirstMember" and "SecondMember" of the table
> "Dates" refer to he same column (namely "MemberID" ) of the table MEMBERS.
> How can make such a query?
> Thanks
>|||Silver wrote:
> Hi everyone,
> I am implementing a dating club as a project for school.
> I have a table called "Dates". It has the columns:
> DateID, FirstMember, SecondMember, DateToMeet, Place
> The table "MEMBERS" contains many things and of course:
> MemberID , Username
> Now, I want to make a query where I can join the two tables and
> display: USERNAME1, USERNAME 2 (the 2 members that are going to
> meet), DateToMeet and PLACE.
> But the problem is tha both "FirstMember" and "SecondMember" of the
> table "Dates" refer to he same column (namely "MemberID" ) of the
> table MEMBERS.
> How can make such a query?
> Thanks
You can join to the same table as many times as you want, using table
aliases to distinguish them (this example is untested, but should give you
the basic idea):
select
u1,UserName Dater1,
u2.Username Dater2,
DateToMeet,
Place
FROM Dates d
inner join members u1 on d.FirstMember=u1.MemberID
inner join members u2 on d.SecondMember=u1.MemberID
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thank you both for the quick reply.
I tried both ways and managed to get results. The problem is, I get mopre
results than I should. It's like somewhere happens a cartesion product.
My table Dates is like this:
DateID - User1 - User2 - DateToMeet - Place
----
--
1 1 2 1956-10-30 22:00:00.000 Plat.
Aristotelous
2 1 3 1996-10-30 22:00:00.000
Tsimisky,Gounari
3 4 5 2004-01-02 22:00:00.000 Colonial
4 3 6 2003-04-04 22:00:00.000
Terkenli,Aristotel
5 2 5 2003-02-06 21:30:00.000 Ster Century
I should only get 5 results, with the Usernames of the Members.
i'm working on it right now, but if you have any idea, pls share
Thanks again!!|||Silver wrote:
> Thank you both for the quick reply.
> I tried both ways and managed to get results. The problem is, I get
> mopre results than I should. It's like somewhere happens a cartesion
> product.
> My table Dates is like this:
www.aspfaq.com/5006

> DateID - User1 - User2 - DateToMeet - Place
> ----
--
> --
> 1 1 2 1956-10-30 22:00:00.000 Plat.
> Aristotelous
> 2 1 3 1996-10-30 22:00:00.000
> Tsimisky,Gounari
> 3 4 5 2004-01-02 22:00:00.000
> Colonial 4 3 6 2003-04-04 22:00:00.000
> Terkenli,Aristotel
> 5 2 5 2003-02-06 21:30:00.000 Ster
> Century
>
> I should only get 5 results, with the Usernames of the Members.
> i'm working on it right now, but if you have any idea, pls share
> Thanks again!!
Show us your DDL, sample data and current query.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Bob's query has a common copy and paste oversight in it that will give you a
cartesian product, see if you can spot it.. or use mine :)
Mr Tea
http://mr-tea.blogspot.com
"Silver" <argytzak@.med.auth.gr> wrote in message
news:ct06f5$bd7$1@.nic.grnet.gr...
> Thank you both for the quick reply.
> I tried both ways and managed to get results. The problem is, I get mopre
> results than I should. It's like somewhere happens a cartesion product.
> My table Dates is like this:
> DateID - User1 - User2 - DateToMeet - Place
> ----
--
> --
> 1 1 2 1956-10-30 22:00:00.000 Plat.
> Aristotelous
> 2 1 3 1996-10-30 22:00:00.000
> Tsimisky,Gounari
> 3 4 5 2004-01-02 22:00:00.000 Colonial
> 4 3 6 2003-04-04 22:00:00.000
> Terkenli,Aristotel
> 5 2 5 2003-02-06 21:30:00.000 Ster
> Century
>
> I should only get 5 results, with the Usernames of the Members.
> i'm working on it right now, but if you have any idea, pls share
> Thanks again!!
>|||My current query is this
SELECT DISTINCT FirstU.Username as Member1,SecondU.Username as Member2,
D.DateToMeet,D.Place
FROM Dates AS D
INNER JOIN MEMBERS AS FirstU ON FirstMember=D.FirstMember
INNER JOIN MEMBERS AS SecondU ON SecondMember=D.SecondMember
WHERE (FirstU.Username != SecondU.Username)
I have already given the sample data for the table "Dates". It contains 2
columns, "FirstMember" and "SecondMember" that contain an int value, which
refers to the table "MEMBERS", column "MemberID". That is, a "1" in
"FirstMember" corresponds to a tuple in the table "MEMBERS" that has "1" as
MemberID and "Nick" as UserName
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OdXIwvUAFHA.3016@.tk2msftngp13.phx.gbl...
> Silver wrote:
> www.aspfaq.com/5006
>
> ----
--
> Show us your DDL, sample data and current query.
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>|||Silver wrote:
> My current query is this
> SELECT DISTINCT FirstU.Username as Member1,SecondU.Username as
> Member2, D.DateToMeet,D.Place
> FROM Dates AS D
> INNER JOIN MEMBERS AS FirstU ON FirstMember=D.FirstMember
> INNER JOIN MEMBERS AS SecondU ON SecondMember=D.SecondMember
> WHERE (FirstU.Username != SecondU.Username)
> I have already given the sample data for the table "Dates".
If you want me to take a deeper look at this you will provide DDL scripts
(CREATE TABLE statements) and sample data in the form of INSERT statements
(The faq article, www.aspfaq.com/5006, links to a script that shows you how
to generate those INSERT statements) so I can reproduce your problem on my
server. You're asking me for help. Does it seem polite to make me to go to
extra work to provide that help? :-)
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Lee Tudor wrote:
> Bob's query has a common copy and paste oversight in it that will
> give you a cartesian product, see if you can spot it.. or use mine :)
>
Yes it does, darnit. But yours should work, and he says it doesn't ...
Bob
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||On Sun, 23 Jan 2005 15:17:15 +0300, Silver wrote:

>My current query is this
>SELECT DISTINCT FirstU.Username as Member1,SecondU.Username as Member2,
> D.DateToMeet,D.Place
>FROM Dates AS D
> INNER JOIN MEMBERS AS FirstU ON FirstMember=D.FirstMember
> INNER JOIN MEMBERS AS SecondU ON SecondMember=D.SecondMember
>WHERE (FirstU.Username != SecondU.Username)
Hi Silver,
Your join criteria are wrong. The unqualified FirstMember is taken from
the Dates table, as there is no other table with a column of that name;
the other FirstMember is also from Dates because that is what the alias D
is used for. And since all rows will satisfy the requirement that
FirstMember is equal to itself (and ditto for SecondMember), you'll get a
full cartesian product from the join, that is then slightly reduced by the
WHERE clause.
Try this:
SELECT FirstU.Usersname as Member1, SecondU,Username AS Member2,
D.DateToMeet, D.Place
FROM Dates AS D
INNER JOIN Members AS FirstU
ON FirstU.MemberID = D.FirstMember
INNER JOIN Members AS SecondU
ON SecondU.MemberID = D.SecondMember
The WHERE clause is not needed. I assume the dates table doesn't contain
any dates between a member and him-/herself. The only effect of the where
clause would be to remove valid dates between two people who happen to
have the same name.
I also removed the DISTINCT, as it doesn't appear to be necessary.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)