March 23, 2018 at 12:49 pm
Hello,
I have an Orders table with Start DateTimes(when the order was created) and Stop DateTimes.
If the Stop DateTime column IS NULL, then the order is considered "Active".
I'm trying to look at a month at a time and was hoping to see how many orders were "Active" on any given day for the month.
My code is basically this below, but right now this is only calculating how many orders were stopped/closed on each day of the month, day, year.
I'm looking for some assistance so that
Start Date Order = 3/1/2018 and has a Stop Date of 3/21. Everyday from 3/1 to 3/21, it will count it as 1 Active order(3/1, 3/2, 3/3, and so on), but on 3/22 it would drop off. Any suggestions on how to make that type of calculation on all the orders in a month?
SELECT
IDCode
,StartDtm
,StopDtm
INTO #Temp
FROM OrderTable
WHERE StartDtm > GETDATE()-365
SELECT
CAST(DATEPART(YEAR, StopDtm)AS nvarchar(4)) AS 'Year'
,CAST(DATEPART(MONTH, StopDtm)AS nvarchar(2)) AS 'Month'
,CASE
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '1' Then '01' --this helps sort the 1-9 'days' better
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '2' Then '02'
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '3' Then '03'
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '4' Then '04'
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '5' Then '05'
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '6' Then '06'
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '7' Then '07'
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '8' Then '08'
WHEN CAST(DATEPART(DAY, StopDtm)AS nvarchar(2)) = '9' Then '09'
ELSE CAST(DATEPART(DAY, StopDtm)AS nvarchar(2))
END AS 'Day'
,CAST(COUNT(IDCode)AS INT) AS ActiveOrdersPerDay
FROM #temp
GROUP BY
DATEPART(DAY, StopDtm)
,DATEPART(MONTH, StopDtm)
,DATEPART(YEAR, StopDtm)
ORDER BY [Year], [Month], [Day] ASC
Result would look something similar to:
YearMonthDayActiveOrdersPerDay
201830139
201830245
201830327
201830431
PresentDayHere # of StopDtm = IS NULL
Seems kind of simple, but I'm having a hard time getting anything to work properly. Any assistance would be greatly appreciated.
Thank you!!
March 23, 2018 at 1:17 pm
Got some data? Sounds like a DAX question, but maybe I've been doing too much PowerBI.
Should be easy if you join your data to a Calendar table... then you don't have to recalculate all that stuff on the Date column... (it's stored in your Calendar table).
March 23, 2018 at 1:51 pm
Thank you pietlinden for that idea...DAX Calendar function seems like it might work well, but I think that was introduced in SQL 2016. I'm on SQL 2014 still. :-(.
I don't have the data sample setup(yet), was hoping since I'm really only looking at two DATETIME columns, that someone might have some ideas to try without a sample. Fingers crossed anyways. 😉
March 23, 2018 at 2:31 pm
cor_dog, you can do all the DAX stuff in Excel, if you want. If you're lost, I would read Rob Collie's book. It's $30. If you're new to DAX, it's ABSOLUTELY worth it.
If you could post some data (or a query against AdventureWorks or similar (publicly available database), I can do it and upload, I think. DAX is really weird if you're not used to thinking like that... but really cool once you get your head around it.
March 23, 2018 at 2:40 pm
Much harder without consumable sample data, but something like below should be really close at least. I'm not 100% sure on the specific end date determination you want, but you can adjust that as needed.
You don't really need a calendar table, an in-line tally table will do just fine and is much less overhead.
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(YEAR, -1, GETDATE())
SET @end_date = GETDATE()
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
CROSS JOIN cteTally10 c3
)
SELECT
CONVERT(char(4), date, 112) AS Year,
CONVERT(char(2), date, 1) AS Month,
CONVERT(char(2), date, 3) AS Day,
ActiveOrdersCount
FROM (
SELECT
date, COUNT(*) AS ActiveOrdersCount
FROM cteTally1000 days
CROSS APPLY (
SELECT DATEADD(DAY, days.number, @start_date) AS date
) AS calc_next_date
INNER JOIN dbo.OrderTable ot ON ot.StartDtm >= date AND
(ot.StopDtm IS NULL OR ot.StopDtm > date)
WHERE days.number BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date) - 1
) AS derived
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2018 at 3:12 pm
ScottPletcher, that is exactly what I was looking for!!! With a few minor tweaks, I now have this is working beautifully.
You're awesome!! Thank you so much...very much appreciated!! 🙂
March 28, 2018 at 10:23 am
Great, I'm very glad it helped!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply