Database

AND (project.status = 'construction' OR project.status = 'finished')

AND project.status IN ('construction', 'finished')

SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE (proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044')
-- vendor without matching proj_cost
OR proj_cost.Vend_ID IS NULL

SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost 
ON proj_cost.Vend_ID = vendor.Vendor_ID
AND proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044'

WITH cte As
(
    SELECT 
        Vend_ID,
        cost,
        pref_vendor
    FROM
        proj_cost 
    WHERE proj_id = '4077'
    AND proj_stage = 'construction'
    AND vers_id = '8'
    AND task_Oper = '6'
    AND vend_id = '54'
    AND task_id = 'TK_EX_044'
)
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
cte.cost,
cte.pref_vendor
FROM vendor
LEFT OUTER JOIN cte ON cte.Vend_ID = vendor.Vendor_ID

SELECT
    proj_invoice.Recnum,
    proj_job.Recnum,    
    proj_invoice.Proj_ID,
    proj_invoice.Invoice_Class,
    proj_invoice.Invoice_Oper,
    proj_invoice.Invoice_Number, 
    proj_invoice.Invoice_Date, 
    proj_invoice.Invoice_Desc, 
    proj_invoice.Invoice_Amt, 
    proj_invoice.Due_Date,
    proj_invoice.Vendor_Id,
    proj_job.EST_COST, 
    proj_job.ACT_COST,            
    Catalog.Cat_ID,
    Catalog.Cat_Name,
    Catalog.Default_Vendor            
FROM proj_invoice
RIGHT JOIN Proj_Job ON Proj_Job.Proj_ID = proj_invoice.Proj_ID AND Proj_Job.Job_Oper = Proj_Invoice.Invoice_Oper
RIGHT JOIN catalog ON catalog.cat_id = Proj_Job.Job_Oper            
RIGHT JOIN vendor ON proj_job.Vendor_id = proj_invoice.Vendor_ID        
WHERE proj_invoice.Proj_ID = '$projectNumber'
AND CONVERT(VARCHAR, proj_invoice.Delete_Flag) <> '1' 
AND Proj_Job.EST_COST <> 0.00 
AND Proj_Job.ACT_COST = proj_invoice.Invoice_Amt 
AND vendor.Vendor_ID = proj_invoice.Vendor_ID
ORDER BY CASE 
    WHEN Proj_Job.job_type = 'FX' THEN 0
    WHEN Proj_Job.job_type = 'OP' THEN 1
    WHEN Proj_Job.job_type = 'EQ' THEN 2
END

SELECT 
    proj_cost.Vend_ID, 
    proj_cost.pref_vendor,
    vendor.Delete_Flag
FROM proj_cost 
INNER JOIN vendor ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE proj_cost.Proj_ID = '$projectNumber' 
AND proj_cost.Proj_Stage = '$projectStage' 
AND proj_cost.Vers_ID = '$versionNumber' 
AND proj_cost.Task_Oper = '$taskOperator'
GROUP BY proj_cost.Vend_Id, proj_cost.pref_vendor, vendor.Delete_Flag 
ORDER BY CASE 
    WHEN vendor.Delete_Flag = '1' THEN 0
    WHEN proj_cost.pref_vendor <> 'Y' THEN 1
    WHEN proj_cost.pref_vendor = 'Y' THEN 2

END

jkirkerx wrote:

ORDER BY CASE
WHEN vendor.Delete_Flag = '1' THEN 0
WHEN proj_cost.pref_vendor <> 'Y' THEN 1
WHEN proj_cost.pref_vendor = 'Y' THEN 2

ORDER BY
   vendor.Delete_Flag desc,   -- '1' then '0'
   proj_cost.pref_vendor asc  -- 'N' then 'Y'

ORDER BY CASE 
    WHEN Proj_Job.job_type = 'FX' THEN 0
    WHEN Proj_Job.job_type = 'OP' THEN 1
    WHEN Proj_Job.job_type = 'EQ' THEN 2
END

ORDER BY CASE 
    WHEN Proj_Job.job_type = 'FX' THEN 0
    WHEN Proj_Job.job_type = 'OP' THEN 1
    ELSE 2
END

ORDER BY 
   CASE Proj_Job.job_type
      WHEN 'FX' THEN 0
      WHEN 'OP' THEN 1
      ELSE 2
   END