Click here to Skip to main content
13,596,997 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am doing library management. I have three tables which is for Issue books to student and renewal or return books
Issue has two table which is following
<pre lang="SQL">

Issueid Code    Issuedate               Student staff   Remarks
1	ISC00	2010-09-17 00:00:00.000	18	0	ad
2	ISC02	2011-09-23 00:00:00.000	15	0	
8	ISC08	2010-09-27 00:00:00.000	0	50	A
34	ISC034	2010-09-27 00:00:00.000	0	4	ad
35	ISC034	2010-09-27 00:00:00.000	0	4	ad
36	ISC034	2010-09-27 00:00:00.000	0	4	ad

detid   Issueid Category bookid Qty   Returnorrenewaldate       Reserveid descr
119	65	1	1	 3	2010-09-20 00:00:00.000	NULL	ad
120	78	4	4	1	2010-09-22 00:00:00.000	NULL	
121	78	9	4	1	2010-10-16 00:00:00.000	NULL	gg
122	79	1	1	3	2010-10-30 00:00:00.000	NULL	hhhghh
123	80	5	1	6	2010-10-02 00:00:00.000	NULL	NULL
124	81	9	3	4	2010-09-20 00:00:00.000	NULL	NULL
125	82	3	3	3	2010-10-10 00:00:00.000	NULL	NULL


ReID ReCode ReDate StaffID StudentID  IssueDetID BookID IsFurtherRenewalAllowed   ReturrnDate   Remarks CategoryID    NumberOfTimeRenewed IsReturned

These are the columns of the Renewal table.
First of all when the students or staff get the books from the library their details or save itinto the tblissue and tblissuedetails. And then when the book is returned or renewal that datas are stored in tblrenewal table.

I want get the following column from that three tables for particular studentid or staffid. When i give the student or staffid it gets the list of all the books which is not returned and no of renewal from corresponding books if the extended date is greater than todays date display the fine amount. If the book is not renewal it gets the details from issue table only or it gets the details from issue and renewal table.
that is look like following
issueid issuecode renewalid renewalcode noofrenewals Extendeddate bookname fine 

If the book is not renewal renewalid and renewalcode is not must noofrenewal will be zero extended date is calculating add 12 from issuedate.

If the book is already renewal it it displays the last renewalid,code, Extendeddate is calculating from last renewaldate which is added to 12 and noofrenewals already made, if it is not renewal that particular date they will be collecting fine, that is one day fine amt is 50. So we calculating howmany days*50

How to get the details help me please?

I have try this

Declare @count as int
Declare @temptable1 as table
IssueID		nvarchar(20),
IssueDetailsID nvarchar(20),
Issuecode	nvarchar(20),
--RenewalId	nvarchar(15),
Titlename	nvarchar(max),
TitleID		nvarchar(20),
IssueDate	datetime,
Retdate		datetime

Declare @temptable2 as table
IssueDetailsID	bigint,
RenewalID	nvarchar(20),
Renewalcode	nvarchar(20),
RenewalDate datetime,
Titelname	nvarchar(max),
TitleID		nvarchar(20),
Isallowed	bit,
ReturnDate	datetime,
IsReturned	bit,
NooftimeRenewal int

Declare @finaltable as table
IssueId	bigint,
Issuedate	datetime,
RenewalID	bigint,
Renewaldate	datetime,
Titlename	nvarchar(max),
TitleID		nvarchar(20),
Isallowed	bit,
Returndate	datetime,
IsReturned	bit,
NooftimeRenewal int

insert into @temptable1
Select T1.IssueID,T2.IssueDetailsID,T1.IssueCode, T4.TitleName,T4.TitleID,T1.IssueDate,T2.ReturnorRenewalDate
from tblLibIssue T1
Left outer join tblLibIssueDetails T2 on T2.IssueID=T1.IssueID
Left outer join tblLibStock T3  on T3.StockID=T2.StockID
Left outer join tblLibTitle T4  on T4.TitleID=T2.StockId
where  T1.StudentProfileID=18

Insert into @temptable2
select T1.IssueDetID,T1.RenewalID,T1.RenewalCode,T1.RenewalDate,T2.Titlename,T2.TitleID,T1.IsFurtherRenewalAllowed,T1.Returndate,
from tbllibrenewal T1
Left outer join tblLibStock T3 on T3.StockID=T1.StockId
Left outer join tblLibTitle T2 on T2.TitleID=T3.StockID
Where T1.StudentProfileID=18

Select * from @temptable1
Select * from @temptable2

Select t1.issueid,t1.issuedetailsid,t1.titlename,t1.titleid,t1.issuedate,t1.retdate,
t2.renewalid,t2.renewaldate,(Select renewaldate from @temptable2 where issuedetailsid having max(issueddetailsid) group by t1.issueid),(select count(*) from @temptable2  group by t1.issuedetailsid)
from @temptable1 t1
left outer join @temptable2 t2 on t1.issuedetailsid=t2.issuedetailsid

but error occured select renewal date which is recently renewaled
Posted 3-Feb-13 22:45pm
Updated 4-Feb-13 0:11am
Irbaz Haider Hashmi 4-Feb-13 4:49am
Provide the query please
devausha 4-Feb-13 5:00am
I confused how i fetch it. So please help me.. for find the answers

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Update this line

(select count(1) from @temptable2 t3  where t1.issuedetailsid=t3.issuedetailsid)
devausha 4-Feb-13 6:06am
Ok thank u. I already try this. But I want renewaldate which is recently renewal by that book in the same query. See the updated question
Irbaz Haider Hashmi 4-Feb-13 6:57am
Use Max(renewaldate) and add group by condition. this will give you the latest renewaldate.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.180621.3 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100