Tech
Forums
Jobs
Books
Events
Interviews
Live
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
1
Reply
How to use order by in union all
Revathi Parvathi
Nov 15 2017 12:24 AM
151
Reply
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
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
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
1
)
Next Recommended Forum
I have one query regarding dynamic database.
how to convert french date to arabic date