SQL – Dynamic Sorting

Users like to be in control. They want to define custom columns, sort orders, and basically drag, drop, and pivot chart their way to victory. i this specific case user want to order sales of a specific reporting services report based on different date. This is the specific query:

SELECT SalesOrderNumber, OrderDate, DueDate, ShipDate,

PurchaseOrderNumber, AccountNumber, SubTotal,

TaxAmt, Freight, TotalDue

FROM Sales.SalesOrderHeader

The user want to order data by: ORder date, Due date or shipdate.

To do this we use a stored procedure where we read the selected time attribute and give a query order by it.

The specific query it is :

DECLARE @SortOrder VARCHAR(50) = ‘[Time attribute selected]’;

SELECT rn,

 [list of attributes]

FROM (

SELECT CASE @SortOrder

WHEN ‘OrderDate’ THEN ROW_NUMBER() OVER (ORDER BY OrderDate DESC)

WHEN ‘DueDate’ THEN ROW_NUMBER() OVER (ORDER BY DueDate DESC)

WHEN ‘ShipDate’ THEN ROW_NUMBER() OVER (ORDER BY ShipDate DESC)

END AS rn,

[list of attributes]

FROM Sales.SalesOrderHeader

) AS x

ORDER BY rn ASC;

With this symple query you can order dataset with dynamic variable.

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...