June 4, 2015 at 9:02 am
[font="Comic Sans MS"]NO URGENCY here, Just for learning purposes, the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?
In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:[/font]
[font="Courier New"]
'Document Date (most recent date first)'
'Document Number (highest number first)'
'Document Date (most recent first) and Number'
'Document Number (lowest number first)'[/font]
[font="Comic Sans MS"]
The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.
So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.
The code is:[/font]
[font="Courier New"]
DECLARE @ls_OrderBy varchar(80)
--'Document Number (highest number first)'
--'Customer and Document Date (most recent date first)'
--'Customer and Document Number (highest number first)'
--'Document Date (most recent first) and Number'
--'Document Number (highest number first)'
--'Document Number (lowest number first)'
DECLARE @Invoice TABLE
(
Invoice_Number int,
Invoice_Date date,
Item_Name varchar(40)
)
INSERT INTO @Invoice(Invoice_Number, Invoice_Date, Item_Name)
SELECT 10001, '2015-05-02', 'Tappan Zee' UNION
SELECT 10009, '2015-05-01', 'El Verano' UNION
SELECT 10041, '2015-05-15', 'Where the Wind Blows Free' UNION
SELECT 10042, '2015-05-02', 'Treasure Island' UNION
SELECT 11001, '2015-05-03', 'Nights are Forever Without You' UNION
SELECT 11091, '2015-05-02', 'BJ Four'
DECLARE @ls_OrderBy varchar(80) =
'Document Number (highest number first)'
-- 'Item and Document Date (most recent date first)'
-- 'Item and Document Number (highest number first)'
-- 'Document Date (most recent first) and Number'
-- 'Document Number (highest number first)'
-- 'Document Number (lowest number first)'
SELECT
CASE
WHEN @ls_OrderBy = 'Document Number (lowest number first)' THEN
CONVERT(varchar(10), Invoice_Number)
ELSE 'x'
END as Sort1,
CASE -- ORDER BY CLAUSE USES THIS WITH 'DESC'
WHEN @ls_OrderBy = 'Item and Document Date (most recent date first)'
THEN CONVERT(varchar(10), CONVERT(DATE, I.Invoice_Date))
WHEN @ls_OrderBy = 'Item and Document Number (highest number first)'
THEN CONVERT(varchar(10), Invoice_Number)
WHEN @ls_OrderBy = 'Document Date (most recent first) and Number'
THEN CONVERT(varchar(10), CONVERT(DATE, I.Invoice_Date))
WHEN @ls_OrderBy = 'Document Number (highest number first)'
THEN CONVERT(varchar(10), Invoice_Number)
ELSE 'y'
END AS Sort2,
CASE
WHEN @ls_OrderBy = 'Document Date (most recent first) and Number'
OR @ls_OrderBy = 'Item and Document Date (most recent date first)'
THEN CONVERT(varchar(10), Invoice_Number)
ELSE 'z'
END AS Sort3,
I.Invoice_Number,
I.Invoice_Date,
I.Item_Name
FROM @Invoice I
ORDER BY Sort1, Sort2 DESC, Sort3 DESC
[/font]
June 4, 2015 at 10:03 am
It's not necessarily faster, and it's not likely any better, but here's how you might take the same scenario and use ROW_NUMBER():
DECLARE @Invoice TABLE
(
Invoice_Number int,
Invoice_Date date,
Item_Name varchar(40),
RN_IN int,
RN_ID int
);
INSERT INTO @Invoice(Invoice_Number, Invoice_Date, Item_Name)
SELECT 10001, '2015-05-02', 'Tappan Zee' UNION
SELECT 10009, '2015-05-01', 'El Verano' UNION
SELECT 10041, '2015-05-15', 'Where the Wind Blows Free' UNION
SELECT 10042, '2015-05-02', 'Treasure Island' UNION
SELECT 11001, '2015-05-03', 'Nights are Forever Without You' UNION
SELECT 11091, '2015-05-02', 'BJ Four';
UPDATE I
SET I.RN_IN = I.RIN,
I.RN_ID = I.RID
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Invoice_Number) AS RIN,
ROW_NUMBER() OVER (ORDER BY Invoice_Date) AS RID,
RN_IN, RN_ID
FROM @Invoice
) AS I;
DECLARE @ls_OrderBy varchar(80) =
'Document Number (highest number first)'
-- 'Item and Document Date (most recent date first)'
-- 'Item and Document Number (highest number first)'
-- 'Document Date (most recent first) and Number'
-- 'Document Number (highest number first)'
-- 'Document Number (lowest number first)'
SELECT CASE WHEN @ls_OrderBy = 'Document Number (lowest number first)' THEN RN_IN ELSE 0 END as Sort1,
CASE -- ORDER BY CLAUSE USES THIS WITH 'DESC'
WHEN @ls_OrderBy = 'Item and Document Date (most recent date first)' THEN RN_ID
WHEN @ls_OrderBy = 'Item and Document Number (highest number first)' THEN RN_IN
WHEN @ls_OrderBy = 'Document Date (most recent first) and Number' THEN RN_ID
WHEN @ls_OrderBy = 'Document Number (highest number first)' THEN RN_IN
ELSE 1
END AS Sort2,
CASE
WHEN @ls_OrderBy = 'Document Date (most recent first) and Number'
OR @ls_OrderBy = 'Item and Document Date (most recent date first)'
THEN RN_IN
ELSE 2
END AS Sort3,
I.Invoice_Number,
I.Invoice_Date,
I.Item_Name
FROM @Invoice I
ORDER BY Sort1, Sort2 DESC, Sort3 DESC
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 4, 2015 at 10:39 am
You can use CASE in the OVER clause:
OVER (ORDER BY CASE WHEN @var=1 THEN Orderno ELSE Invoiceno END)
Edit: typing on fone on train
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
June 4, 2015 at 11:12 am
I would go with something simple like:
CREATE TABLE #Invoice
(
Invoice_Number int,
Invoice_Date date,
Item_Name varchar(40)
)
INSERT INTO #Invoice(Invoice_Number, Invoice_Date, Item_Name)
SELECT 10001, '2015-05-02', 'Tappan Zee' UNION
SELECT 10009, '2015-05-01', 'El Verano' UNION
SELECT 10041, '2015-05-15', 'Where the Wind Blows Free' UNION
SELECT 10042, '2015-05-02', 'Treasure Island' UNION
SELECT 11001, '2015-05-03', 'Nights are Forever Without You' UNION
SELECT 11091, '2015-05-02', 'BJ Four'
DECLARE @ls_OrderBy varchar(80) =
'Document Number (highest number first)'
-- 'Item and Document Date (most recent date first)'
-- 'Item and Document Number (highest number first)'
-- 'Document Date (most recent first) and Number'
-- 'Document Number (highest number first)'
-- 'Document Number (lowest number first)'
SELECT @ls_OrderBy = ' ORDER BY ' + CASE @ls_OrderBy
WHEN 'Item and Document Date (most recent date first)' THEN 'Item_Name, Invoice_Date DESC'
WHEN 'Item and Document Number (highest number first)' THEN 'Item_Name, Invoice_Number DESC'
WHEN 'Document Date (most recent first) and Number' THEN 'Invoice_Date DESC'
WHEN 'Document Number (highest number first)' THEN 'Invoice_Number DESC'
WHEN 'Document Number (lowest number first)' THEN 'Invoice_Number'
END + ';';
DECLARE @Query nvarchar(4000);
SET @Query = N'SELECT
I.Invoice_Number,
I.Invoice_Date,
I.Item_Name
FROM #Invoice I
' + @ls_OrderBy;
EXECUTE sp_executesql @Query;
DROP TABLE #Invoice;
There's absolutely no risk of SQL Injection because we're not concatenating unknown strings, we still have the possibility to store execution plans on cache and use them for later queries, and we don't have to reinvent the wheel.
Of course, a best option is to do the order in the front end.
June 4, 2015 at 11:29 am
To: sgmunson and Luis Cazares
Thank you both for your suggestions, both are viable alternatives. (I was really looking for an "Aha ! moment" about these new T-SQL functions to replace old-fashioned T-SQL "tricks" such as fabricating the sort fields, having found no other way to solve a real-life problem - I'll just have to come up with a more interesting problem I guess).
June 4, 2015 at 9:50 pm
Dynamic SQL as Luis showed is the way to go here. I don't know if this helps; I used my own sample data. Just another way to go about this:
DECLARE @table TABLE (val1 varchar(10), val2 varchar(10), val3 int);
INSERT @table VALUES ('a','z',3),('z','a',1),('b','b',0)
DECLARE @sort varchar(20) = 'val3';
SELECT *
FROM @table
ORDER BY
CASE @sort
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN CAST(val3 AS varchar(10))
END
-- Itzik Ben-Gan 2001
June 5, 2015 at 7:33 am
[font="Comic Sans MS"]Thanks.[/font]
June 5, 2015 at 7:56 am
Alan.B (6/4/2015)
Dynamic SQL as Luis showed is the way to go here. I don't know if this helps; I used my own sample data. Just another way to go about this:
DECLARE @table TABLE (val1 varchar(10), val2 varchar(10), val3 int);INSERT @table VALUES ('a','z',3),('z','a',1),('b','b',0)
DECLARE @sort varchar(20) = 'val3';
SELECT *
FROM @table
ORDER BY
CASE @sort
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN CAST(val3 AS varchar(10))
END
Just to be sure, that's not actually dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2015 at 8:07 am
[font="Comic Sans MS"]This also does not help with the added complexity of wanting it sorted up or down![/font]
June 5, 2015 at 8:45 am
Jeff Moden (6/5/2015)
Alan.B (6/4/2015)
Dynamic SQL as Luis showed is the way to go here. I don't know if this helps; I used my own sample data. Just another way to go about this:
DECLARE @table TABLE (val1 varchar(10), val2 varchar(10), val3 int);INSERT @table VALUES ('a','z',3),('z','a',1),('b','b',0)
DECLARE @sort varchar(20) = 'val3';
SELECT *
FROM @table
ORDER BY
CASE @sort
WHEN 'val1' THEN val1
WHEN 'val2' THEN val2
WHEN 'val3' THEN CAST(val3 AS varchar(10))
END
Just to be sure, that's not actually dynamic SQL.
This is not dynamic SQL - I was just showing another way to control the sort order. I should have been more clear.
... And I would probably recommend Dynamic SQL for the OP's requiremennt, specifically what Luis posted, over this approach.
-- Itzik Ben-Gan 2001
June 5, 2015 at 9:11 am
j-1064772 (6/5/2015)
[font="Comic Sans MS"]This also does not help with the added complexity of wanting it sorted up or down![/font]
Nope. And it can't. Perhaps I should have mentioned that. This is why I said that Luis' approach was the way to go.
Your post began with:
...Just for learning purposes
I was just showing another way to dynamically handle sorting in the spirit learning.
I have been writing SSRS reports for a long time and this requirement comes up periodically. I have handled this requirement using all three of the methods mentioned (and a few that have not been). None of the three solutions are best for all scenarios. My response was not geared towards solving your specific problem - I just wanted to add another tool to your toolbox.
On a side note: there is an article posted today titled: Demystifying the use of CASE in an ORDER BY statement[/url]. It's worth a read as it discusses the method I posted in more detail.
-- Itzik Ben-Gan 2001
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply