ALTER PROCEDURE [dbo].[display_sales_detail_bydate] 
@fromdate AS Varchar(max), @todate AS Varchar(max)

As
Begin
	
SELECT transdate,[Last Name] + ', ' + [First Name] + ' ' + [Middle Name] AS FullName,
       salesid,salestype,casheirid,terminal,totalsales
       ,costofsales=(SELECT SUM(sd.supprice * sd.qty) 
                     FROM dbo.tbl_pos_sales_detail sd 
                     WHERE sd.salesid=sm.salesid AND sd.salestype=sm.salestype)
                      
FROM tbl_pos_sales_summary sm

LEFT JOIN tbl_costumers tc
ON tc.[Last Name]=(SELECT tc.[Last Name]
                   WHERE tc.[ID No.] = sm.costumerid)
                                   
WHERE (sm.salestype='cash' or sm.salestype='credit' or sm.salestype='pay' OR sm.salestype='Bad Order')
--AND (sm.transdate >= @fromdate AND transdate < DATEADD(dd,1,@todate))
AND (CONVERT(Date, sm.transdate)) BETWEEN  @fromdate AND @todate
 
ORDER BY sm.transdate,sm.salesid asc

End	

INNER JOIN tbl_supplier sp
ON sp.suppliername=(SELECT sp.suppliername
                   WHERE sp.supplierID = sm.costumerid)

SELECT transdate,[Last Name] + ', ' + [First Name] + ' ' + [Middle Name] AS FullName,
       salesid,salestype,casheirid,terminal,totalsales
       ,costofsales=(SELECT SUM(sd.supprice * sd.qty) 
                     FROM dbo.tbl_pos_sales_detail sd 
                     WHERE sd.salesid=sm.salesid AND sd.salestype=sm.salestype)
                      
FROM tbl_pos_sales_summary sm

LEFT JOIN tbl_costumers tc
ON tc.[Last Name]=(SELECT tc.[Last Name]
                   WHERE tc.[ID No.] = sm.costumerid)
                                   
WHERE (sm.salestype='cash' or sm.salestype='credit' or sm.salestype='pay' )
AND (CONVERT(Date, sm.transdate)) BETWEEN  @fromdate AND @todate

UNION ALL

SELECT transdate,[Last Name] + ', ' + [First Name] + ' ' + [Middle Name] AS FullName,
       salesid,salestype,casheirid,terminal,totalsales
       ,costofsales=(SELECT SUM(sd.supprice * sd.qty) 
                     FROM dbo.tbl_pos_sales_detail sd 
                     WHERE sd.salesid=sm.salesid AND sd.salestype=sm.salestype)
                      
FROM tbl_pos_sales_summary sm

INNER JOIN tbl_supplier sp
ON sp.suppliername=(SELECT sp.suppliername
                   WHERE sp.supplierID = sm.costumerid)
                                   
WHERE (sm.salestype='Bad Order')
AND (CONVERT(Date, sm.transdate)) BETWEEN  @fromdate AND @todate

ORDER BY sm.transdate,sm.salesid asc