This is My SQL SERVER QUERY
Here table1 is giving all the data made in per day by Salesman whereas table2 is giving special data of 2 special item which they ar selling
So i need all the invoice and sum from table1 and from table2 i need special item invoice,
Can we optimise this query where i dont have to write table2 query , Is there any casae query if i write in same tabel1 statement.
select m.*,Of_Order_Invoices_SpecialItem from (
select SUPERVISOR,Salesman_name,staffid,
ROUND(sum(Carton),0) AS O_CARTON,ROUND(SUM(Amount),0) AS ORD_AMNT
ROUND(COUNT(DISTINCT(ORDER_ID)),2) as [#Of_Order_Invoices] ,ordt
from t_opdata_3 OP
INNER JOIN T_TREE_ITEMS TI ON OP.ITEM=TI.ITEM_CODE
INNER JOIN daily_total_shoproute ON STAFFID=SALESMANCODE
WHERE order_date =format(getdate() ,'yyyy-MM-dd')
Order_month =MONTH(GETDATE()) and year =YEAR(GETDATE())
GROUP by SUPERVISOR,Salesman_name ,staffid
) table1
left join
(
select SUPERVISOR,Salesman_name,staffid,
ROUND(sum(Carton),0) AS O_CARTON,ROUND(SUM(Amount),0) AS ORD_AMNT ,
ROUND(COUNT(DISTINCT(ORDER_ID)),2) as Of_Order_Invoices_SpecialItem,ordt
from t_opdata_3 OP
INNER JOIN T_TREE_ITEMS TI ON OP.ITEM=TI.ITEM_CODE
INNER JOIN daily_total_shoproute ON STAFFID=SALESMANCODE
WHERE order_date =format(getdate() ,'yyyy-MM-dd') and O_Item in ('4567','23478')
Order_month =MONTH(GETDATE()) and year =YEAR(GETDATE())
GROUP by SUPERVISOR,Salesman_name ,staffid
) table2 on table1.staffid=table2.staffid
What I have tried:
I Tried by putting case if in table1 where item in ('',''),But then I need to do group by item and giving Wrong results