SQL Server - Tips II

Uso de CTE

Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común (CTE). Se deriva de una consulta simple y se define en el ámbito de ejecución de una sola instrucción SELECT, INSERT, UPDATE o DELETE. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucción SELECT que la define. Una expresión de tabla común puede incluir referencias a ella misma. Esto se conoce como expresión de tabla común recursiva.

Ejemplo

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;
GO

Using multiple CTEs

WITH Sales_CTE 
AS

(


) ,New CTE
as (


)
select * 

from Sales_CTE , New CTE 

Using parameters

DECLARE @somevar DECIMAL;
WITH SomeName AS (SELECT....)
SELECT @somevar = ...
FROM SomeName

Sum a previous record

insert into table1 
values
(1,100.00 , date '2013-01-01'),  
(1,150.00 , date '2013-01-02'),
(1,200.00 , date '2013-01-03'),
(2,75.00  , date '2013-01-01'),

(2,100.00 , date '2013-01-02');

account_number	bill	bill_date	sum_to_date	account_total
1	100	January, 01 2013 00:00:00	100	450
1	150	January, 02 2013 00:00:00	250	450
1	200	January, 03 2013 00:00:00	450	450
2	75	January, 01 2013 00:00:00	75	175
2	100	January, 02 2013 00:00:00	175	175

SELECT Account_Number, 

       Bill, 

       Bill_Date, 

       sum(bill) over (partition by account_number order by bill_date) as sum_to_date,

       SUM(Bill) over (partition by account_Number) as account_total

FROM Table1

order by account_number, bill_date;

Pivot - Display horizontal rows to vertical align 

Change the order of the columns to an horizontal view

 ->

This is an example for a static case, due to the number of kpis_rem_prov_tgt_vol is known

select [reme_rem_target_month] , [1], [0]
from (
    INTERN SELECT ....
    select
Analysis.[reme_rem_target_month]
,sum ([kpis_crs_vol]) as CSR_Vol
,[kpis_rem_prov_tgt_vol]
    from ....


)Results //Alias of the intern select
pivot (
sum (CSR_Vol)  for [kpis_rem_prov_tgt_vol] in ([0], [1])
)t

In this case [kpis_rem_prov_tgt_vol] is the field to be used as pivot, and their results will be the new headers.
sum (CSR_Vol) are the values to be grouped, in this case any add is performed.
[reme_rem_target_month] this field will be grouped automatically.

CASE using like in the conditions

case  

when t.[resto_rst_target_month] like '%January%' then 1

when t.[resto_rst_target_month] like '%February%' then 2

when t.[resto_rst_target_month] like '%March%' then 3

when t.[resto_rst_target_month] like '%April%' then 4

when t.[resto_rst_target_month] like '%May%' then 5

when t.[resto_rst_target_month] like '%June%' then 6

end