date question - 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

General SQL Server Forums

New to SQL Server Programming

date question

Author

Topic

iversonmania
Starting Member

5 Posts

Posted - 2009-08-13 : 14:24:20

I have a table

say create table book
(
...
..
..
startdate date,
enddate date
....
...);

from this table book. a guy loans a book for a period of time. all the information is stored.

for example. from january 1 2005 to january 1 2006 he loand specific book.

how can i retireve number of books loaned by a guy per month?
say first he loaned a book in january 1 2005, and now we are january 1 2009. and during this period he loaned 48 books. around 1 book

iversonmania
Starting Member

5 Posts

Posted - 2009-08-13 : 14:27:34

i need somehow to get minimum startdate for a certain book owner.

then do number of books loaned/(systemdate - minimum startdate )

can someone help me with this query?

aareynaga
Starting Member

6 Posts

Posted - 2009-08-13 : 15:57:32

Could you please post your desired outcome

Example:

Filed1 Field2 Field3
-------------------------
Data Data Data

ES QUEUE ELL

iversonmania
Starting Member

5 Posts

Posted - 2009-08-13 : 16:01:36

[code]
CREATE TABLE equipment_loaned (
equipmentid INT,
startdate DATE,
enddate DATE,
customerid INT,
PRIMARY KEY ( eqipmentid ),
FOREIGN KEY ( customerid ) references customer(customerid));

create table customer
(
customerid int,
name varchar(255),
primary key customerid
);

INSERT INTO customer
VALUES (1,
'jack');

INSERT INTO customer
VALUES (2,
'bob');

INSERT INTO equipment_loaned
VALUES (1,
To_date('11/11/2008','MM/DD/YYYY'),
To_date('11/13/2008','MM/DD/YYYY'),
1);

INSERT INTO equipment_loaned
VALUES (2,
To_date('12/12/2008','MM/DD/YYYY'),
To_date('12/13/2008','MM/DD/YYYY'),
1);

[/code]

i need to retrieve how many equipments each customer loaned per month.

the result returned should be

customerid numberofequipmentpermonth
-------------------------
1 0.25
2 0

the answer is 0.25 for the first one. because first equipment was loaned on 11/11/2008 ( i need lowest startdate for a customer) and sysdate ( 11 august 2009).. so 2 equipments over 8 months, which is 0.25.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-08-13 : 16:19:53

select customerid, sum(1)*1.0 / datediff(mm, min(startdate), getdate()) as numberofequipmentpermonth
from equipment_loaned
group by customerid

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-13 : 16:27:12

[code]select a.customerid,coalesce(count(b.equipmentid) * 1.0/datediff(mm,min(b.startdate),getdate()),0)
from customer a left join equipment_loaned b on a.customerid = b.customerid
group by a.customerid[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources