Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
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">
tblissue
 
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
 

tblissuedetails
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
 
tblrenewal
 
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
               
begin 
 
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
)
 
Begin
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,
T1.IsReturned,T1.NumberofTimeRenewed
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
End
 
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
end
 
but error occured select renewal date which is recently renewaled
Posted 3-Feb-13 22:45pm
devausha1.4K
Edited 4-Feb-13 0:11am
v5
Comments
Irbaz Haider Hashmi at 4-Feb-13 4:49am
   
Provide the query please
devausha at 4-Feb-13 5:00am
   
I confused how i fetch it. So please help me.. for find the answers

1 solution

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

Solution 1

Update this line
 

(select count(1) from @temptable2 t3  where t1.issuedetailsid=t3.issuedetailsid)
  Permalink  
Comments
devausha at 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 at 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
0 OriginalGriff 278
1 Maciej Los 245
2 Nguyen.H.H.Dang 230
3 Peter Leow 195
4 arvind mepani 187


Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid