How to calculate last (previous) date periods?
Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to demonstrate the creation and test of a stored procedure to calculate last week, last month, last quarter and last year date limits using datetime variable types.
use AdventureWorks2008;
go
create proc sprocLastPeriodRange
@Period varchar(8),
@PeriodStart datetime output,
@PeriodEnd datetime output,
@PeriodEndPlusOneDay datetime output
as
begin
declare @FirstDayOfCurrentPeriod datetime = getdate(),
@FirstDayOfLastPeriod datetime = getdate()
if @Period in ('Week','wk','ww')
begin
set @FirstDayOfCurrentPeriod = DATEADD(DD, 1 - DATEPART(DW, CONVERT(VARCHAR(10),
getdate(), 111)), CONVERT(VARCHAR(10), getdate(), 111))
set @FirstDayOfLastPeriod = dateadd(dd, -7, @FirstDayOfCurrentPeriod)
end
else if @Period in ('Month', 'mm', 'm')
begin
set @FirstDayOfCurrentPeriod = convert(datetime,
left(convert(varchar, getdate(), 111),8) + '01')
set @FirstDayOfLastPeriod = dateadd(mm, -1, @FirstDayOfCurrentPeriod)
end
else if @Period in ('Quarter','qq', 'q')
begin
set @FirstDayOfCurrentPeriod = CONVERT(CHAR(4), YEAR(getdate())) +
CASE WHEN MONTH(getdate()) between 1 and 3 THEN '/01/01'
WHEN MONTH(getdate()) between 4 and 6 THEN '/04/01'
WHEN MONTH(getdate()) between 7 and 9 THEN '/07/01'
ELSE '/10/01'
END
set @FirstDayOfLastPeriod = dateadd(mm, -3, @FirstDayOfCurrentPeriod)
end
else if @Period in ('Year', 'yyyy', 'yy')
begin
set @FirstDayOfCurrentPeriod = convert(datetime,
left(convert(varchar, getdate(), 111),5) + '01/01')
set @FirstDayOfLastPeriod = dateadd(yy, -1, @FirstDayOfCurrentPeriod)
end
set @PeriodStart = @FirstDayOfLastPeriod
set @PeriodEndPlusOneDay = @FirstDayOfCurrentPeriod
set @PeriodEnd = DATEADD (dd,-1, @FirstDayOfCurrentPeriod)
end
go
-- Test
declare @LastPeriodBegin datetime, @LastPeriodEnd datetime
declare @ThisPeriodBegin datetime
exec sprocLastPeriodRange 'qq',
@LastPeriodBegin output,
@LastPeriodEnd output,
@ThisPeriodBegin output
select Period='qq',
LastBegin=@LastPeriodBegin,
LastEnd=@LastPeriodEnd,
ThisBegin=@ThisPeriodBegin
-- To use it for WHERE range filtering
-- Note that < is used at period upper limit
select * from Purchasing.PurchaseOrderHeader
where OrderDate >= @LastPeriodBegin
and OrderDate < @ThisPeriodBegin
go
Partial results:
| Period | LastBegin | LastEnd | ThisBegin |
| qq | 7/1/08 0:00 | 9/30/08 0:00 | 10/1/08 0:00 |
How to upsert with the MERGE statement?
Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to demonstrate UPSERT, update or insert, operation using the MERGE statement. If the row exists as identified by the 3 key columns, UPDATE is performed on OrderQuantity and SalesAmount, otherwise INSERT action is carried out. MERGE can do INSERT, UPDATE and DELETE in a single statement.
use tempdb;
select top (5000) ResellerKey, OrderDateKey, ProductKey, OrderQuantity, SalesAmount
into FactResellerSales
from AdventureWorksDW2008.dbo.FactResellerSales
go
select top (8000) ResellerKey, OrderDateKey, ProductKey, OrderQuantity, SalesAmount
into ResellerSalesTransaction
from AdventureWorksDW2008.dbo.FactResellerSales
go
delete rsc
from ResellerSalesTransaction rsc
join (select top 1000 * from ResellerSalesTransaction order by ResellerKey desc) x
on x.ResellerKey=rsc.ResellerKey
go
update top (6000) ResellerSalesTransaction
set SalesAmount = SalesAmount * 1.1
go
select top (10) * from FactResellerSales
order by ResellerKey, OrderDateKey, ProductKey
go
select BeforeFactCount=COUNT(*) from FactResellerSales
go
--------------------------------------------------------------------
-- Test data sets created, ready for the MERGE (update or insert)
--------------------------------------------------------------------
MERGE FactResellerSales AS fact
USING (
SELECT * FROM ResellerSalesTransaction
) AS feed
ON ( fact.ProductKey = feed.ProductKey
AND fact.ResellerKey = feed.ResellerKey
AND fact.OrderDateKey = feed.OrderDateKey )
WHEN MATCHED THEN
UPDATE SET
fact.OrderQuantity = fact.OrderQuantity + feed.OrderQuantity
,fact.SalesAmount = fact.SalesAmount + feed.SalesAmount
WHEN NOT MATCHED THEN
INSERT (ResellerKey, OrderDateKey, ProductKey, OrderQuantity, SalesAmount)
VALUES (feed.ResellerKey, feed.OrderDateKey, feed.ProductKey,
feed.OrderQuantity, feed.SalesAmount);
--------------------------------------------------------------------
go
select top (10) * from FactResellerSales
order by ResellerKey, OrderDateKey, ProductKey
go
select AfterFactCount=COUNT(*) from FactResellerSales
go
-- Cleanup
use tempdb;
drop table ResellerSalesTransaction
go
drop table FactResellerSales
go
How to create date series with multiple-value assignment?
Execute the following Microsoft SQL Server Transact-SQL script in Management Studio Query Editor to demonstrate the creation of integer and date series with the use of the multiple-value assignment operator within an UPDATE statement. The construct avoids the use of cursor WHILE loop, therefore it is a scalable solution.
use tempdb;
select ID = CONVERT(int,SalesOrderID),
TestDate=convert(date,ModifiedDate) into DateSeries
from AdventureWorks2008.Sales.SalesOrderHeader
go
select top 100 * from DateSeries
go
-- Multi-value assignment UPDATE
declare @Date date = dateadd(day,1,getdate()), @id int = 0
update DateSeries
set @id = ID = @id + 1,
@Date = TestDate = dateadd (Day, -1, @Date)
go
select top 100 * from DateSeries
go
use tempdb;
drop table DateSeries
go
First and second partial results:
| ID | TestDate |
| 43659 | 7/8/2001 |
| 43660 | 7/8/2001 |
| 43661 | 7/8/2001 |
| 43662 | 7/8/2001 |
| 43663 | 7/8/2001 |
| ID | TestDate |
| 1 | 11/26/2008 |
| 2 | 11/25/2008 |
| 3 | 11/24/2008 |
| 4 | 11/23/2008 |
| 5 | 11/22/2008 |
| 6 | 11/21/2008 |
| 7 | 11/20/2008 |
| 8 | 11/19/2008 |
How to design for stored procedure missing (NULL) parameter?
Execute the following Microsoft SQL Server T-SQL script in Query Editor to demonstrate the design for missing filter parameter(s). In case of all 3 parameters missing, the entire JOINed Production.Product table content is returned without the WHERE filter.
USE AdventureWorks2008;
GO
CREATE PROCEDURE sprocProductFilter
@LowPrice SMALLMONEY = NULL,
@SubCategory VARCHAR(32) = NULL,
@Color VARCHAR(16) = NULL
AS
SET nocount ON;
SELECT p.*
FROM Production.Product p
INNER JOIN Production.ProductSubcategory sc
ON p.ProductSubcategoryID = sc.ProductSubcategoryID
WHERE 1 = 1
AND (@LowPrice <= ListPrice
OR @LowPrice IS NULL)
AND (@SubCategory = sc.Name
OR @SubCategory IS NULL)
AND (@Color = Color
OR @Color IS NULL)
GO
exec sprocProductFilter 700.0, 'Road Frames', 'Red'
go
exec sprocProductFilter NULL, 'Road Frames', 'Red'
go
exec sprocProductFilter NULL, NULL, 'Red'
go
exec sprocProductFilter 700.0, 'Road Frames'
go
exec sprocProductFilter 700.0
go
exec sprocProductFilter
go
How to measure stored procedure execution time?
Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor take execution time measurement for a stored procedure. The timing is the average of 10 measurements.
DECLARE @i int = 0, @TestLimit int = 10
DECLARE @Timing TABLE ( Timing int)
WHILE (@i < @TestLimit)
BEGIN
DECLARE @RC int, @ManagerID int =1
DBCC DROPCLEANBUFFERS
DECLARE @Start datetime = getdate()
EXECUTE @RC = [AdventureWorks2008].[dbo].[uspGetManagerEmployees] @ManagerID
INSERT @Timing
SELECT ExecutionMsec = datediff (millisecond, @Start, getdate())
SET @i=@i+1
END
SELECT ExecutionMsec = avg(Timing) FROM @Timing
Go
How to combine detail data with GROUP BY aggregate?
Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor demonstrate the usage of CTE GROUP BY aggregate to JOIN to detail data lines.
Use AdventureWorks2008;
WITH ctePOTotal AS
(
SELECT
PurchaseOrderID,
Sum(OrderQty) as [TotalQty]
FROM Purchasing.PurchaseOrderDetail T2
GROUP BY PurchaseOrderID
)
SELECT
PO = pod.PurchaseOrderID,
LineItem=ROW_NUMBER()OVER (PARTITION BY pod.PurchaseOrderID ORDER BY ProductId),
OrderDate = convert(varchar,OrderDate,110),
ProductId,
UnitPrice,
OrderQty,
TotalQty=ctePOTotal.[TotalQty]
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderId = pod.PurchaseOrderId
INNER JOIN ctePOTotal
ON pod.PurchaseOrderID = ctePOTotal.PurchaseOrderID
WHERE Year(OrderDate)=2004
ORDER BY PO, LineItem
How to list all columns with extended properties?
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor to list all tables column information with extended properties.
USE AdventureWorks2008;
SELECT [Schema] = s.name,
[Table] = object_name(major_id),
[Column] = c.name,
[Type] = t.name,
[Extended] = p.VALUE
FROM sys.extended_properties p
JOIN sys.columns c
ON c.column_id = p.minor_id
AND c.object_id = p.major_id
JOIN sys.objects o
ON o.object_id = p.major_id
AND o.object_id = c.object_id
JOIN sys.schemas s
ON s.schema_id = o.schema_id
JOIN sys.types t
ON c.system_type_id = t.user_type_id
WHERE o.TYPE = 'U'
AND class_desc = 'OBJECT_OR_COLUMN'
ORDER BY [Schema],
[Table],
c.column_id
How to calculate running total fast?
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor to calculate running total without using a loop. In the update statement double assignment is applied.
USE tempdb;
SELECT * INTO POH from AdventureWorks2008.Purchasing.PurchaseOrderHeader
ORDER by PurchaseOrderID
-- select * from POH
ALTER TABLE POH ADD RunningTotal money
GO
SET NOCOUNT ON
GO
DECLARE @RunningTotal MONEY
SET @RunningTotal=0
UPDATE POH
SET @RunningTotal = RunningTotal = @RunningTotal+ISNULL(TotalDue, 0)
GO
SELECT * FROM POH
GO
No comments:
Post a Comment