Description:
SELECT t.Ticket_Number TicketNumber,
t.pnrNumber pnrNumber,
ct.NAME Destination,
Convert(CHAR(10),tr.Trip_Date,105) TripDate,
t.Amount Rate,
TotalAmount = t.Amount + Isnull((SELECT SUM(Tax_Amount)
FROM TicketTaxtxn
WHERE Ticket_Id = t.Id
AND Is_Inclusive = 0
GROUP BY Ticket_Id),0),
BranchName = (SELECT NAME
FROM Branch
WHERE Branch.Id = t.Branch_Id),
AgencyName = (SELECT NAME
FROM Agency
WHERE Agency.Id = t.Agency_Id),
AgentName = (SELECT (First_Name + ' ' + Last_Name)
FROM Agent
WHERE Agent.Id = t.Agent_Id)
FROM Ticket t,
Trip tr,
BookIng b,
ServiceLeg sl,
City ct
WHERE t.Status = 1
AND b.Booked_On >= '2007-02-24 00:00:00 '
AND b.Booked_On <= '2007-02-24 23:59:59 '
AND b.pnrNumber = t.pnrNumber
AND sl.Id = b.Service_Leg_Id
AND sl.To_Stop = ct.Id
AND tr.Id = b.Trip_Id
AND t.Branch_Id = 2
AND t.Agency_Id = 2
AND t.Group_Id = 2
AND t.Mark_For_Delete = 0
AND tr.Mark_For_Delete = 0
AND b.Mark_For_Delete = 0
AND sl.Mark_For_Delete = 0
AND ct.Mark_For_Delete = 0
UNION ALL
SELECT t.Ticket_Number TicketNumber,
t.pnrNumber pnrNumber,
ct.NAME Destination,
Convert(CHAR(10),tr.Trip_Date,105) TripDate,
t.Amount Rate,
TotalAmount = t.Amount + Isnull((SELECT SUM(Tax_Amount)
FROM TicketTaxtxnHistory
WHERE Ticket_Id = t.Ticket_Id
AND Is_Inclusive = 0
GROUP BY Ticket_Id),0),
BranchName = (SELECT NAME
FROM Branch
WHERE Branch.Id = t.Branch_Id),
AgencyName = (SELECT NAME
FROM Agency
WHERE Agency.Id = t.Agency_Id),
AgentName = (SELECT (First_Name + ' ' + Last_Name)
FROM Agent
WHERE Agent.Id = t.Agent_Id)
FROM TicketHistory t,
Trip tr,
BookIng b,
ServiceLeg sl,
City ct
WHERE t.Status = 1
AND b.Booked_On >= '2007-02-24 00:00:00 '
AND b.Booked_On <= '2007-02-24 23:59:59 '
AND b.pnrNumber = t.pnrNumber
AND sl.Id = b.Service_Leg_Id
AND sl.To_Stop = ct.Id
AND tr.Id = b.Trip_Id
AND t.Branch_Id = 2
AND t.Agency_Id = 2
AND t.Group_Id = 2
AND t.Mark_For_Delete = 0
AND tr.Mark_For_Delete = 0
AND b.Mark_For_Delete = 0
AND sl.Mark_For_Delete = 0
AND ct.Mark_For_Delete = 0
ORDER BY AgentName,
ct.NAME
How to repeat:
this query is not working it is showing me error while i had transfer from sqlserver to mysql now it is showing me error in 4th line on convert
so how we write convert in mysql