In this blog you will find my t-sql notes for specific scenario's. Use them if you like!
----------------------------------------------------------------------------------------
--Get the latest date for a specific item in a table
--in this example the latest order per customer
create table #orders
(
orderid int,
customerid int,
orderdate date,
quantity int
)
INSERT INTO #orders VALUES (1,1,'2018-01-01',10)
INSERT INTO #orders VALUES (2,1,'2018-02-01',10)
INSERT INTO #orders VALUES (3,1,'2018-03-01',10)
INSERT INTO #orders VALUES (4,2,'2018-07-01',10)
INSERT INTO #orders VALUES (5,2,'2018-08-01',10)
INSERT INTO #orders VALUES (6,3,'2018-10-01',10)
;WITH CTE_LatestOrderPerCustomer AS (
SELECT
orderid,
customerid,
orderdate As LatestOrderdate
FROM (
SELECT
orderid,
customerid,
orderdate,
ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY orderdate DESC) As rownumber
FROM #orders
) orders
WHERE rownumber = 1
)
SELECT * FROM CTE_LatestOrderPerCustomer
DROP TABLE #orders
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--Generate an end date for a table which only contain begin dates
--This example will generate a ValidUntil date where the name of a product changes overtime
create table #products
(
productid int,
productname varchar(100),
ValidFrom date
)
INSERT INTO #products VALUES (1,'Apples','2017-01-01')
INSERT INTO #products VALUES (1,'Red Apples','2018-02-01')
INSERT INTO #products VALUES (1,'Red Delicious Apples','2018-07-01')
INSERT INTO #products VALUES (1,'Apples','2018-12-01')
;WITH CTE_Products AS (
SELECT
productid,
productname,
ValidFrom,
DATEADD(day,-1,LEAD(ValidFrom,1,'2999-12-31') OVER (PARTITION BY productid ORDER BY ValidFrom ASC)) ValidUntil
FROM #products
)
SELECT * FROM CTE_Products
DROP TABLE #products
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--Generate the first day for a specific month and year
SELECT DATEFROMPARTS(2018,12,1) FirstDayInMonth
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--Generate the last day in for a specific date
SELECT EOMONTH('2018-12-01') LastDayInMonth
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--Get compression type for all tables and indexes in a database
select
t.[name] TableName
,i.[name] IndexName
,p.data_compression_desc Compression
from
sys.partitions p join sys.tables T
ON T.object_id = P.object_id
LEFT JOIN sys.indexes I
ON P.object_id = I.object_id AND P.index_id = I.index_id
ORDER BY 1,2
----------------------------------------------------------------------------------------
select
t.[name] TableName
,i.[name] IndexName
,p.data_compression_desc Compression
from
sys.partitions p join sys.tables T
ON T.object_id = P.object_id
LEFT JOIN sys.indexes I
ON P.object_id = I.object_id AND P.index_id = I.index_id
ORDER BY 1,2
----------------------------------------------------------------------------------------