We help IT Professionals succeed at work.

SQL 2000 Stored Procedure Advanced search query

casstd
casstd asked
on
883 Views
Last Modified: 2013-11-05
I have table Certificate and Ticket these two tables are one to one relationship. I have company table which has one to many relationship with Ticket. I have one more table called TicketInspection this table has one to many relationship with ticket table.

I have a search page. In this search page I have search condition like dateofInspection and Inspectedby which is from TicketInspection table. This table has one to many relationship with Ticket table. But I want the result should be one ticketID within this inspection date range searched by user. I have few other conditions these conditions are in either Ticket, Certificate table. This will not be a problem to me. The problem is the dateofInspection (from, to is search criteria) and Inspectedby (many person can inspect one ticket)

Certificate Table
CertID          CertNo                   DateofExpiry    TicketID        YearsApproved
   1               Cert123                    10/08/2008           1                         2
   2               Cert234                    31/01/2008           2                         1

Ticket Table
TicketID          CompanyID        DateApproved           TicketNo        SubmissionDate
     1                         1                  11/08/2006                 T00000123        02/07/2006
     2                         1                  01/02/2007                 T00000234        12/11/2006

Company Table
CompanyID               CompanyName
1      KFC
2      CURRY NOODLES

Inspection Table
InspectionID                 dateofInspection                 Inspectedby(UserID)      TicketID
     1                               12/06/2006                                1                                    1
     2                               18/06/2006                                2                                    1
     3                                01/01/2007                               1                                    2
     4                                01/21/2007                               1                                    2
     
UserTable

UserID             LoginID
1      ABC
2      BAC

I have advanced search where I can search for combination of or either one but I want the single SQL Stored procedure to accommodate my search queries.
Certno, TicketNo, CompanyName, Dateof Inspection (from, to range), Inspectedby, DateApproved (from, to range)

I want the result should be distinct ticketID. I have written query but didnt give any result.

CREATE PROCEDURE Select_HalalCertifiedPermises_Report(

@SubmissionDateFrom datetime,                                        @SubmissionDateTo datetime,
@ApprovedDateFrom datetime,                                          @ApprovedDateTo datetime,
@InspectionDateFrom datetime,                                         @InspectionDateTo datetime,
@CertNo varchar(50),                                                         @TicketNo varchar(8),
@TicketTypeID int,                                                             @UserID int,
@EstablishmentName varchar(500),                                   @CustomerCode varchar(15),
@SchemeID int,                                                                  @NoOfYears int,
@CertPrintedDate datetime
)

AS

SELECT     dbo.Cust_Company.CompanyID, dbo.Cust_Company.EstablishmentName, dbo.Cust_Company.CompanyLicenceType,
                      dbo.Cust_Company.CompanyTel1, dbo.Cust_Company.CompanyFax1, dbo.Cust_Company.IsCompanyDeleted, dbo.Cust_Company.IsSuspended,
                      dbo.Master_CertificationStatus.CertificationStatusName, dbo.Ticket_Certifications.CertID, dbo.Ticket_Certifications.CertificationStatusID,
                      dbo.Ticket_Certifications.CertNo, dbo.Ticket_Certifications.NoOfPrints, dbo.Ticket_Certifications.NoOfYearsApproved,
                      dbo.Ticket_Certifications.LastInspection, dbo.Ticket_Certifications.IssuingOfficer, dbo.Ticket_Certifications.IssuedDate,
                      dbo.Ticket_Certifications.ExpiryDate, dbo.Ticket_Certifications.CertCreatedBy, dbo.Ticket.TicketID, dbo.Ticket.TicketNo, dbo.Ticket.SchemeID,
                      dbo.Master_Scheme.SchemeName, dbo.Master_Scheme.SchemeCode, dbo.Ticket.SubmissionDate, dbo.Ticket.DateApproved,
                      dbo.Ticket_Inspection.DateInspected, dbo.Master_TicketType.TicketTypeCode, dbo.Master_TicketType.TicketTypeDesc,
                      dbo.Master_TicketStatus.InternalStatus, dbo.Ticket_Inspection.InspectorUserID, User_1.UserName AS InspectorName,
                      dbo.Ticket_StatusDates.TicketStatusID, dbo.[User].LoginID AS CustomerCode, dbo.Ticket.TicketTypeID
FROM         dbo.Cust_Company INNER JOIN
                      dbo.Ticket ON dbo.Cust_Company.CompanyID = dbo.Ticket.CompanyID INNER JOIN
                      dbo.Ticket_Address ON dbo.Ticket.TicketID = dbo.Ticket_Address.TicketID INNER JOIN
                      dbo.Ticket_Certifications ON dbo.Ticket.TicketID = dbo.Ticket_Certifications.TicketID INNER JOIN
                      dbo.Master_CertificationStatus ON dbo.Ticket_Certifications.CertificationStatusID = dbo.Master_CertificationStatus.CertificationStatusID INNER JOIN
                      dbo.Master_Scheme ON dbo.Ticket.SchemeID = dbo.Master_Scheme.SchemeID INNER JOIN
                      dbo.[User] ON dbo.Cust_Company.CompanyID = dbo.[User].CompanyID INNER JOIN
                      dbo.Ticket_Inspection ON dbo.Ticket.TicketID = dbo.Ticket_Inspection.TicketID INNER JOIN
                      dbo.Master_TicketType ON dbo.Ticket.TicketTypeID = dbo.Master_TicketType.TicketTypeID INNER JOIN
                      dbo.Ticket_StatusDates ON dbo.Ticket.TicketID = dbo.Ticket_StatusDates.TicketID INNER JOIN
                      dbo.Master_TicketStatus ON dbo.Ticket_StatusDates.TicketStatusID = dbo.Master_TicketStatus.TicketStatusID INNER JOIN
                      dbo.[User] User_1 ON dbo.Ticket_Inspection.InspectorUserID = User_1.UserID
WHERE     (dbo.Master_TicketStatus.TicketStatusID = 32 OR
                      dbo.Master_TicketStatus.TicketStatusID = 33)

AND (dbo.Ticket.SubmissionDate <= @SubmissionDateFrom)  AND (dbo.Ticket.SubmissionDate >= @SubmissionDateTo)

AND (dbo.Ticket.DateApproved <= @ApprovedDateFrom) AND (dbo.Ticket.DateApproved >= @ApprovedDateTo)

AND (dbo.Ticket_Inspection.DateInspected <= @InspectionDateFrom) AND (dbo.Ticket_Inspection.DateInspected >= @InspectionDateTo)

AND

(dbo.Ticket.SchemeID = CASE
      WHEN @SchemeID IS NULL
      THEN dbo.Ticket.SchemeID
      WHEN LTRIM(@SchemeID) = '0'
      THEN dbo.Ticket.SchemeID
      ELSE @SchemeID
      END
)

AND

(dbo.Ticket_Certifications.NoOfYearsApproved = CASE
      WHEN @NoOfYears IS NULL
      THEN dbo.Ticket_Certifications.NoOfYearsApproved
      WHEN LTRIM(@NoOfYears) = ''
      THEN dbo.Ticket_Certifications.NoOfYearsApproved
      ELSE @NoOfYears
      END
)

AND

(dbo.Ticket.TicketTypeID = CASE
      WHEN @TicketTypeID IS NULL
      THEN dbo.Ticket.TicketTypeID
      WHEN LTRIM(@TicketTypeID) = '0'
      THEN dbo.Ticket.TicketTypeID
      ELSE @TicketTypeID
      END
)

AND

(dbo.Ticket_Inspection.InspectorUserID = CASE
      WHEN @UserID IS NULL
      THEN dbo.Ticket_Inspection.InspectorUserID
      WHEN LTRIM(@UserID) = '0'
      THEN dbo.Ticket_Inspection.InspectorUserID
      ELSE @UserID
      END
)

AND

(dbo.Cust_Company.EstablishmentName LIKE CASE
      WHEN @EstablishmentName IS NULL
      THEN dbo.Cust_Company.EstablishmentName
      WHEN LTRIM(@EstablishmentName) = ''
      THEN dbo.Cust_Company.EstablishmentName
      ELSE @EstablishmentName
      END + '%'
)

AND
(dbo.Ticket_Certifications.CertNo LIKE CASE
      WHEN @CertNo IS NULL
      THEN dbo.Ticket_Certifications.CertNo
      WHEN LTRIM(@CertNo) = ''
      THEN dbo.Ticket_Certifications.CertNo
      ELSE @CertNo
      END + '%'
)

AND
(dbo.Ticket.TicketNo LIKE CASE
      WHEN @TicketNo IS NULL
      THEN dbo.Ticket.TicketNo
      WHEN LTRIM(@TicketNo) = ''
      THEN dbo.Ticket.TicketNo
      ELSE @TicketNo
      END + '%'
)

AND
(dbo.[User].LoginID LIKE CASE
      WHEN @CustomerCode IS NULL
      THEN dbo.[User].LoginID
      WHEN LTRIM(@CustomerCode) = ''
      THEN dbo.[User].LoginID
      ELSE @CustomerCode
      END + '%'
)
GO
Comment
Watch Question

Author

Commented:
Hi,
    I manged to get related records using the query
This query returs
TicketID  CompanyID  EstablishmentName TicketNo SchemeID SubmissionDate DateApproved TicketTypeID      CustomerCode CertID CertNo NoOfYearsApproved      ExpiryDate InspectorUserID
DateInspected InspectorName
1000091      1083      asd           T0000529          9           14/06/2007          31/07/2007
1                    C1001           1          PRN07080000528  1           07/08/2008      67
07/07/2007     inspector

1000091      1083      asd           T0000529          9           14/06/2007          31/07/2007
1                    C1001           1          PRN07080000528  1           07/08/2008      67
07/20/2007     inspector

if you notice that my query return 2 same ticketid because it gone through 2 inspection. So the result is ok. From this result query returned i have to pass my search criteria
either of combinations CerNo, TicketNo, InspectorUserID, Submitted Date, ApprovedDate, Inspecteddate (dates are in range from, to), no of years apporved, Customercode etc.,

but i want the result to get only one ticket ID from the above

my query is like this
SELECT DISTINCT
                      dbo.Ticket.TicketID, dbo.Cust_Company.CompanyID, dbo.Cust_Company.EstablishmentName, dbo.Cust_Company.CompanyLicenceType,
                      dbo.Cust_Company.CompanyTel1, dbo.Cust_Company.CompanyFax1, dbo.Cust_Company.IsCompanyDeleted, dbo.Cust_Company.IsSuspended,
                      dbo.Ticket.TicketNo, dbo.Ticket.SchemeID, CONVERT(varchar, dbo.Ticket.SubmissionDate, 103) AS SubmissionDate, CONVERT(varchar,
                      dbo.Ticket.DateApproved, 103) AS DateApproved, dbo.Ticket.TicketTypeID, dbo.Ticket_StatusDates.TicketStatusID, dbo.Master_Scheme.SchemeCode,
                      dbo.Master_Scheme.SchemeName, User_1.LoginID AS CustomerCode, dbo.Master_LicenceType.LicenceType, dbo.Ticket.IsTicketDeleted,
                      dbo.Ticket_Certifications.CertID, dbo.Ticket_Certifications.CertNo, dbo.Ticket_Certifications.IsCertDeleted,
                      dbo.Ticket_Certifications.NoOfYearsApproved, CONVERT(varchar, dbo.Ticket_Certifications.ExpiryDate, 103) AS ExpiryDate,
                      dbo.Ticket_Certifications.CertCreatedBy, CONVERT(varchar, dbo.Ticket_Certifications.CertCreatedDate, 103) AS CertCreatedDate, DATEDIFF(Year,
                      GETDATE(), dbo.Ticket_Certifications.ExpiryDate) AS ReminingYears, dbo.Ticket_Inspection.InspectorUserID, dbo.Ticket_Inspection.DateInspected,
                      User_1.UserName AS InspectorName
FROM         dbo.[User] User_1 INNER JOIN
                      dbo.Ticket_Inspection ON User_1.UserID = dbo.Ticket_Inspection.InspectorUserID INNER JOIN
                      dbo.Cust_Company INNER JOIN
                      dbo.Ticket ON dbo.Cust_Company.CompanyID = dbo.Ticket.CompanyID INNER JOIN
                      dbo.Ticket_StatusDates ON dbo.Ticket.TicketID = dbo.Ticket_StatusDates.TicketID INNER JOIN
                      dbo.Master_Scheme ON dbo.Ticket.SchemeID = dbo.Master_Scheme.SchemeID INNER JOIN
                      dbo.[User] User_2 ON dbo.Cust_Company.CompanyID = User_2.CompanyID INNER JOIN
                      dbo.Master_LicenceType ON dbo.Master_Scheme.LicenceTypeID = dbo.Master_LicenceType.LicenceTypeID INNER JOIN
                      dbo.Ticket_Certifications ON dbo.Ticket.TicketID = dbo.Ticket_Certifications.TicketID ON dbo.Ticket_Inspection.TicketID = dbo.Ticket.TicketID
WHERE     (dbo.Ticket_StatusDates.TicketStatusID = 32 OR
                      dbo.Ticket_StatusDates.TicketStatusID = 34) AND (dbo.Ticket.IsTicketDeleted = 0)

// From here i have to write my where condition that passes the values how to do that? any help would be appreciated.

Thank you.
                                                                                                                                                                              
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.