queries problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

queries problem

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2006-01-22 : 09:04:12

hi evryone

i want my freinds name who borrowed ever movies from me and movie name 'titanic'.

I tried like as below

select freinds_name from freinds where movie_name='titanic' and borrowin_date<today'sdate

I want to write like this but i dont know how to write borrowing date must be before today's date.

another question is related like this

i want query that will list the names of my friends who are current borrowing one or more CDs and the number of days since the borrowed it..

select freinds_name from freinds where returned_date = null and today's_date- borrowed date

I want number of days since CDs are borrowed. If returned date is not null means that cds have been returned.

I want both queries solved

Please help me.

Waiting for reply.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-22 : 09:19:43

For current date & time use getdate()

select freinds_name 
from   freinds 
where  movie_name    = 'titanic' 
and    borrowin_date < getdate()

If your borrowin_date contain date & time and you want the result to exclude today's date, then

select freinds_name 
from   freinds 
where  movie_name    = 'titanic' 
and    borrowin_date < dateadd(day, 0, datediff(day, 0, getdate()))

This will return the lists of freinds that borrow more than one CDs.

select freinds_name, count(*) as [no of CDs] 
from   freinds 
where  returned_date is null 
group by freinds_name
having count(*) > 1

This will give you the no of days since CDs are borrowed

select freinds_name, movie_name, datediff(day, borrowin_date, getdate()) as [days borrowed]
from   freinds
where  return_date is null

-----------------
'KH'

asifbhura
Posting Yak Master

165 Posts

Posted - 2006-01-22 : 11:46:08

please sir

last 2 queries i want together.

please do it for me

waiting for reply.

warmest regards,
ASIF

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-22 : 18:17:26

How do you want both query together ? One is an aggregated (sum) the other is not ?

Post some sample data & result

-----------------
'KH'

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-23 : 01:13:55

khtan, this guy crossposted on dbforums, and it sure smells like he is fishing for someone to do his homework assignment for him to me....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 01:22:29

Thanks. Now I remember... he is the video ripper. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60488

-----------------
'KH'

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-23 : 09:38:03

What a great guy.

So he needs someone else to write a database for him to keep track of his stolen property. Sure, I'd be glad to help.....NOT!

I'll make sure people on dbforums know about this guy too.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-23 : 10:35:15

>> "please do it for me...waiting for reply."

Basically, "I'm unwilling to do any work, so hurry up and do it for me."

CODO ERGO SUM

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-23 : 13:15:57

I wonder what's caused his command of English to have deteriorated so badly over the course of four days.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 18:08:08

quote:
Originally posted by Arnold Fribble

I wonder what's caused his command of English to have deteriorated so badly over the course of four days.

Probably due to watching too much of titatic ?

----------------------------------
'KH'
I do work from home but I don't do homework

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-24 : 00:13:52

>>I do work from home but I don't do homework

Tan, did you add this signature only after this post?