| Vendor | Date of Last Check | Date of check for month | Status | Amount |
| Darren Gygi Home Collection | 0 | |||
| Fiddler's Elbow | 0 | |||
| Honey & Me | 0 | |||
| Posies and Such Novelty Paper Goods | 0 | |||
| Rivers Edge Products | 0 | |||
| Wheelhouse Designs | 0 | |||
| Woodlink | 0 | |||
| American Silk Flower | 11/14/2017 | Hold | 72.75 | |
| Boston Warehouse | 11/13/2017 | Hold | 30 | |
| Total Amount | NULL | NULL | TOTAL | 102.75 |
How to use order by in union all
Hi,
I want to add order by in first statement .How to use this.In 2nd statement Total Amount should not order.How is it possible?Can any one help me?
select Vendor,MAX([Date of Last Check]) 'Date of Last Check','' as 'Date of check for month',Status,SUM(Amount) 'Amount' from
(SELECT Vendor,CASE WHEN CONVERT(varchar(50), DateModified, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), DateModified, 101) end
as 'Date of Last Check', CASE WHEN CONVERT(varchar(50), PaycommissionDate, 101)='01/01/1900' THEN '' ELSE CONVERT(varchar(50), PaycommissionDate, 101) end
AS 'Date of check for month',CASE STATUS WHEN 'INPROGRESS' THEN '' WHEN 'SAVED' THEN 'Hold' WHEN 'FINISHED' THEN 'Confirmed' END as Status,SUM([Commission $ paid]) AS 'Amount'
FROM dbo.Tbl_Commission_Reconcilation
WHERE (Vendor IS NOT NULL)
GROUP BY Vendor, PaycommissionDate, CONVERT(varchar(50), DateModified, 101),dbo.Tbl_Commission_Reconcilation.Status
)a group by Vendor,a.Status --order by Vendor
UNION ALL
select 'Total Amount' Vendor,NULL '[Date of Last Check]',NULL 'Date of check for month','TOTAL' 'STATUS',SUM([Commission $ paid]) 'Amount' from Tbl_Commission_Reconcilation
where Vendor is not null
Thanks in advance
Rafnas T PPosted Nov 15, 2017, 12:53 AM