SELECT [CarLicense] as 'code', [CarType] as 'name',[fa_desc] as 'des' ,
[Rate],rc.[Resource] as 'employee',rc.[DayStartRent] as 'start' , rc.[DayEndRent] as 'end'
FROM [dbo].[VW_RentCar] tc
LEFT JOIN [dbo].[TB_CarCentralRequest] rc on rc.[Car] = tc.CarLicense
WHERE ([CarLicense] like '%%' OR [CarType] like '%%')
and (
((rc.DayStartRent = '2016-07-18' or (rc.DayStartRent < '2016-07-18' and '2016-07-18' = rc.DayEndRent))
and (rc.DayEndRent = '2016-07-19' or rc.DayEndRent >= rc.DayStartRent))
or (rc.DayStartRent is null and rc.DayEndRent is null))
SELECT [CarLicense] as 'code', [CarType] as 'name',[fa_desc] as 'des' ,
[Rate],rc.[Resource] as 'employee',rc.[DayStartRent] as 'start' , rc.[DayEndRent] as 'end'
FROM [dbo].[VW_RentCar] tc
LEFT JOIN [dbo].[TB_CarCentralRequest] rc on rc.[Car] = tc.CarLicense
WHERE ([CarLicense] like '%%' OR [CarType] like '%%')
and (
((rc.DayStartRent = '2016-07-15' or (rc.DayStartRent < '2016-07-15' and '2016-07-15' = rc.DayEndRent))
and (rc.DayEndRent = '2016-07-15' or rc.DayEndRent >= rc.DayStartRent))
or (rc.DayStartRent is null and rc.DayEndRent is null))
SELECT [CarLicense] as 'code', [CarType] as 'name',[fa_desc] as 'des' ,
[Rate],rc.[Resource] as 'employee',rc.[DayStartRent] as 'start' , rc.[DayEndRent] as 'end'
FROM [dbo].[VW_RentCar] tc
LEFT JOIN [dbo].[TB_CarCentralRequest] rc on rc.[Car] = tc.CarLicense
WHERE ([CarLicense] like '%%' OR [CarType] like '%%')
and (rc.DayStartRent is null or rc.[DayStartRent] <> '2016-07-15') and (rc.[DayEndRent] is null or rc.[DayEndRent] <> '2016-07-15')
and [CarLicense] not in (select [CarLicense] FROM [dbo].[VW_RentCar] tc
LEFT JOIN [dbo].[TB_CarCentralRequest] sc on sc.[Car] = tc.CarLicense where (sc.[DayStartRent] < '2016-07-15' AND sc.[DayEndRent] > '2016-07-15'))
งั้น select * from mycar มาเลยครับ
มา left join กับตารางการจองซึ่งก็มาถูกทางแล้วครับ แต่ยังไม่ทั้งหมด
Code (SQL)
and [CarLicense] not in (select [CarLicense] FROM [dbo].[VW_RentCar] tc
LEFT JOIN [dbo].[TB_CarCentralRequest] sc on sc.[Car] = tc.CarLicense where (sc.[DayStartRent] < '2016-07-15' AND sc.[DayEndRent] > '2016-07-15'))
SELECT [CarLicense] as 'code', [CarType] as 'name',[fa_desc] as 'des' ,
[Rate] as 'rate',rc.[Resource] as 'employee',rc.[DayStartRent] as 'start' , rc.[DayEndRent] as 'end'
FROM [dbo].[VW_RentCar] tc
LEFT JOIN [dbo].[TB_CarCentralRequest] rc on rc.[Car] = tc.CarLicense
AND (((rc.DayStartRent = '2016-07-12' or (rc.DayStartRent < '2016-07-12' and '2016-07-12' = rc.DayEndRent))
and (rc.DayEndRent = '2016-07-19' or rc.DayEndRent >= rc.DayStartRent)) OR
(CONVERT(DATE,rc.DayStartRent) BETWEEN '2016-07-12' and '2016-07-19')
AND (CONVERT(DATE,rc.DayEndRent) BETWEEN '2016-07-12' and '2016-07-19'))
WHERE ([CarLicense] like '%%' OR [CarType] like '%%' OR [fa_desc] like '%%')
Load balance : Server 05
