Click here to Skip to main content
15,891,431 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
SQL
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
---INNER JOIN T_TARGET T ON TU.ManagerCode=T.managercode

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
Posted
Updated 29-Jul-18 19:01pm
v3

1 solution

you can use Cross join in this query
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900