The knowledge base Browse
 
Tools
Categories

Slab Manager: Find Slabs Consumed Last Month

Reference Number: AA-00298 Views: 538 Created: 2018-02-21 10:34 Last Updated: 2018-02-21 10:41
Description

Slab Manager SQL query to find slabs that were consumed last month

Example:
You want to know what slabs were consumed last month and you are running the query sometime in the current month.

How: 
This is a Slab Manager SQL query. If your normal slab status isn't "Received", then you will need to modify the script.

DB Versions supported by this script

2015.2 or later

How to run a script

Use the "SQL" button in the Slab Manager.



Copy the script below

--Slab Manager query to get all slab consumed last month. Assumes current date is the month after the one we are interested in.
declare @beforeStatus nvarchar(max) = 'Received' --modify this if your default slab status isn't Received
declare @afterStatus nvarchar(max) = 'Consumed' --modify this if you want to look for a different target status
declare @today DATE = GETDATE() --modify this if you want to assume a different date as the current date. For example you want slabs consumed January 2017, use '2017-2-1' (year, month, day)

declare @lastMonth int = DATEPART(m, @today)-1
declare @lastMonthYear int = CONVERT(nchar(4), DATEPART(yyyy, @today))
IF @lastMonth = 0
BEGIN
    SET @lastMonth = 12
    SET @lastMonthYear = @lastMonthYear-1
END

declare @startDate DATE = CONVERT(nchar(4), @lastMonthYear) + '-' + CONVERT(nvarchar(2), @lastMonth) + '-1'
declare @endDate DATE = CONVERT(nchar(4), DATEPART(yyyy, @today)) + '-' + CONVERT(nchar(4), DATEPART(m, @today)) + '-1'

SELECT (SlabID) FROM [SSSlabBrowser].[All Slabs] join audit.slab on slabid = slabuid

WHERE ([SSSlabBrowser].[All Slabs].[Template] = 0) and Timestamp>=@startDate and Timestamp<@endDate and Before=@beforeStatus and After=@afterStatus
ORDER BY [Audit].[Slab].[Timestamp] desc