Hi,
I have following table and has wrriten SQL View like this
'Ticket_Certifi cation' one to one 'Ticket'
'Ticket_Certifi cation' one to many 'Ticket_Certifi cation_Internal CertNo'
'Ticket' one to one 'Master_TicketT ype', 'Ticket_StatusD ates'
'Ticket' One to Many 'Fee_Details' (this table contains only the unpaid ticketIDs)
'Fee_Details' one to many 'Fee'
Now I have to calculate the noofPrints for each CertID Where Date issued is null and dateprinted is the latest dateprinted from Ticket_Certific ation_InternalC ertNo,
Now i have to get the amount from Fee_details table (this table only contains unpaid ticketid and ticket typeid is 8 or 10)
i.e i have to get all the ticket that has record in Cert Table (inner join) and for paid amount it should be null and for unpaid ticketid the amount should be displayed from fee_details table.
Ticket_Certific ation
CertID CertNo Expirydate TicketID
1 CI3I4U 09/09/2008 1
2 CO345 08/09/2007 2
Ticket_Certific ation_InternalC ertNo
InternalCertID CertID InternalCertNo DatePrinted DateIssued
1 1 CI3I4U - 5 08/29/2007
2 1 CI3I4U - 4 08/29/2007
3 1 CI3I4U - 3 08/29/2007
4 2 CO345 - 1 08/27/2007
5 1 CI3I4U - 2 08/01/2007 08/05/2007
6 1 CI3I4U - 1 08/02/2007 08/04/2007
Fee_Details (here assumes that TicketID 2 already paid the fee)
FeeDetailsID TicketID FeeID
1 1 1
Fee
FeeID Amount TicketTypeID (i only need to select TicketTypeID 8 or 10)
1 520 8
Result should be
TicketID CertID Amount noofprints dateprinted
1 1 520 3 08/29/2007
2 2 (here either 0 or null) 1 08/27/2007
Thank you.
SELECT i.CertID, MAX(i.DatePrint ed) AS DatePrinted, COUNT(*) AS NoofPrints, C.CertNo, C.ExpiryDate, dbo.Cust_Compan y.Establishment Name,
dbo.Ticket.Tick etID, dbo.Ticket.Tick etNo, dbo.[User].LoginID AS CustomerCode, dbo.Master_Sche me.SchemeCode, dbo.Cust_Compan y.CompanyID,
dbo.Fee.Amount, dbo.Master_Tick etType.TicketTy peID, dbo.Ticket.Date Approved, dbo.Ticket_Stat usDates.TicketS tatusID
FROM dbo.Ticket_Stat usDates INNER JOIN
dbo.[User] INNER JOIN
dbo.Cust_Compan y ON dbo.[User].CompanyID = dbo.Cust_Compan y.CompanyID INNER JOIN
dbo.Ticket INNER JOIN
dbo.Ticket_Cert ifications C ON dbo.Ticket.Tick etID = C.TicketID INNER JOIN
dbo.Master_Sche me ON dbo.Ticket.Sche meID = dbo.Master_Sche me.SchemeID ON dbo.Cust_Compan y.CompanyID = dbo.Ticket.Comp anyID ON
dbo.Ticket_Stat usDates.TicketI D = dbo.Ticket.Tick etID LEFT OUTER JOIN
dbo.Fee INNER JOIN
dbo.Fee_Details ON dbo.Fee.FeeID = dbo.Fee_Details .FeeID INNER JOIN
dbo.Master_Tick etType ON dbo.Fee.TicketT ypeID = dbo.Master_Tick etType.TicketTy peID ON
dbo.Ticket.Tick etID = dbo.Fee_Details .TicketID FULL OUTER JOIN
dbo.Ticket_Cert ification_Inter nalCertNo i ON C.CertID = i.CertID
WHERE (i.DateIssued IS NULL)
GROUP BY i.CertID, C.CertNo, C.ExpiryDate, dbo.Cust_Compan y.Establishment Name, dbo.Ticket.Tick etID, dbo.Ticket.Tick etNo, dbo.[User].LoginID,
dbo.Master_Sche me.SchemeCode, dbo.Cust_Compan y.CompanyID, dbo.Fee.Amount, dbo.Master_Tick etType.TicketTy peID, dbo.Ticket.Date Approved,
dbo.Ticket_Stat usDates.TicketS tatusID
HAVING (i.CertID IS NOT NULL) AND (dbo.Master_Tic ketType.TicketT ypeID IN (10, 8)) AND (dbo.Ticket_Sta tusDates.Ticket StatusID IN (29, 30, 31, 32, 33, 36, 37))
I have following table and has wrriten SQL View like this
'Ticket_Certifi cation' one to one 'Ticket'
'Ticket_Certifi cation' one to many 'Ticket_Certifi cation_Internal CertNo'
'Ticket' one to one 'Master_TicketT ype', 'Ticket_StatusD ates'
'Ticket' One to Many 'Fee_Details' (this table contains only the unpaid ticketIDs)
'Fee_Details' one to many 'Fee'
Now I have to calculate the noofPrints for each CertID Where Date issued is null and dateprinted is the latest dateprinted from Ticket_Certific ation_InternalC ertNo,
Now i have to get the amount from Fee_details table (this table only contains unpaid ticketid and ticket typeid is 8 or 10)
i.e i have to get all the ticket that has record in Cert Table (inner join) and for paid amount it should be null and for unpaid ticketid the amount should be displayed from fee_details table.
Ticket_Certific ation
CertID CertNo Expirydate TicketID
1 CI3I4U 09/09/2008 1
2 CO345 08/09/2007 2
Ticket_Certific ation_InternalC ertNo
InternalCertID CertID InternalCertNo DatePrinted DateIssued
1 1 CI3I4U - 5 08/29/2007
2 1 CI3I4U - 4 08/29/2007
3 1 CI3I4U - 3 08/29/2007
4 2 CO345 - 1 08/27/2007
5 1 CI3I4U - 2 08/01/2007 08/05/2007
6 1 CI3I4U - 1 08/02/2007 08/04/2007
Fee_Details (here assumes that TicketID 2 already paid the fee)
FeeDetailsID TicketID FeeID
1 1 1
Fee
FeeID Amount TicketTypeID (i only need to select TicketTypeID 8 or 10)
1 520 8
Result should be
TicketID CertID Amount noofprints dateprinted
1 1 520 3 08/29/2007
2 2 (here either 0 or null) 1 08/27/2007
Thank you.
SELECT i.CertID, MAX(i.DatePrint ed) AS DatePrinted, COUNT(*) AS NoofPrints, C.CertNo, C.ExpiryDate, dbo.Cust_Compan y.Establishment Name,
dbo.Ticket.Tick etID, dbo.Ticket.Tick etNo, dbo.[User].LoginID AS CustomerCode, dbo.Master_Sche me.SchemeCode, dbo.Cust_Compan y.CompanyID,
dbo.Fee.Amount, dbo.Master_Tick etType.TicketTy peID, dbo.Ticket.Date Approved, dbo.Ticket_Stat usDates.TicketS tatusID
FROM dbo.Ticket_Stat usDates INNER JOIN
dbo.[User] INNER JOIN
dbo.Cust_Compan y ON dbo.[User].CompanyID = dbo.Cust_Compan y.CompanyID INNER JOIN
dbo.Ticket INNER JOIN
dbo.Ticket_Cert ifications C ON dbo.Ticket.Tick etID = C.TicketID INNER JOIN
dbo.Master_Sche me ON dbo.Ticket.Sche meID = dbo.Master_Sche me.SchemeID ON dbo.Cust_Compan y.CompanyID = dbo.Ticket.Comp anyID ON
dbo.Ticket_Stat usDates.TicketI D = dbo.Ticket.Tick etID LEFT OUTER JOIN
dbo.Fee INNER JOIN
dbo.Fee_Details ON dbo.Fee.FeeID = dbo.Fee_Details .FeeID INNER JOIN
dbo.Master_Tick etType ON dbo.Fee.TicketT ypeID = dbo.Master_Tick etType.TicketTy peID ON
dbo.Ticket.Tick etID = dbo.Fee_Details .TicketID FULL OUTER JOIN
dbo.Ticket_Cert ification_Inter nalCertNo i ON C.CertID = i.CertID
WHERE (i.DateIssued IS NULL)
GROUP BY i.CertID, C.CertNo, C.ExpiryDate, dbo.Cust_Compan y.Establishment Name, dbo.Ticket.Tick etID, dbo.Ticket.Tick etNo, dbo.[User].LoginID,
dbo.Master_Sche me.SchemeCode, dbo.Cust_Compan y.CompanyID, dbo.Fee.Amount, dbo.Master_Tick etType.TicketTy peID, dbo.Ticket.Date Approved,
dbo.Ticket_Stat usDates.TicketS tatusID
HAVING (i.CertID IS NOT NULL) AND (dbo.Master_Tic ketType.TicketT ypeID IN (10, 8)) AND (dbo.Ticket_Sta tusDates.Ticket StatusID IN (29, 30, 31, 32, 33, 36, 37))