Click here to Skip to main content
11,930,449 members (56,528 online)

devausha asked:

Open original thread
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
Tags: SQL, Server


When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).

Advertise | Privacy | Mobile
Web02 | 2.8.151126.1 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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