January 8, 2018 at 5:20 pm
I have to count the number of orders created on weekends and the criteria is that any order that drops between 1 pm Friday and 1 pm Monday needs to be counted as an order for Monday
Here is what I have (Thanks to another post I made on this Forum)
select CAST(DATEADD(HOUR, 11, createdate) AS Date) OrderDate, count(1) CreatedCount
from Orders
where Createdate > getdate() - 12
and DATEPART(dw,CAST(DATEADD(HOUR, 11, createdate) AS Date)) in (6,7,1)
group by CAST(DATEADD(HOUR, 11, createdate) AS Date)
OrderDate CreatedCount
2017-12-29 4
2017-12-30 3
2017-12-31 2
2018-01-01 2
2018-01-05 5
2018-01-06 3
2018-01-07 3
2018-01-08 4
How can I roll it up to this?
OrderDate CreatedCount
2018-01-01 11
2018-01-08 15
Thank you
January 9, 2018 at 8:29 am
Here you go...
CREATE TABLE [dbo].[Orders](
[OrderNo] [nvarchar](50) NOT NULL,
[CreateDate] datetime NOT NULL,
)
GO
INSERT INTO Orders VALUES (6106,'2017-12-28 10:28:11');
INSERT INTO Orders VALUES (6106,'2017-12-28 18:28:11');
INSERT INTO Orders VALUES (1889,'2017-12-29 11:28:11');
INSERT INTO Orders VALUES (1799,'2017-12-29 11:28:11');
INSERT INTO Orders VALUES (6106,'2017-12-29 17:28:11');
INSERT INTO Orders VALUES (27889,'2017-12-30 11:28:11');
INSERT INTO Orders VALUES (2789,'2017-12-30 11:20:11');
INSERT INTO Orders VALUES (3889,'2017-12-30 19:28:11');
INSERT INTO Orders VALUES (1791,'2017-12-31 11:10:11');
INSERT INTO Orders VALUES (1782,'2018-01-01 10:15:10');
INSERT INTO Orders VALUES (173,'2018-01-01 10:28:14');
INSERT INTO Orders VALUES (1784,'2018-01-02 10:15:10');
INSERT INTO Orders VALUES (175,'2018-01-02 10:28:14');
INSERT INTO Orders VALUES (176,'2018-01-03 17:15:10');
INSERT INTO Orders VALUES (177,'2018-01-03 14:28:14');
INSERT INTO Orders VALUES (178,'2018-01-04 19:10:10');
INSERT INTO Orders VALUES (179,'2018-01-05 10:28:14');
INSERT INTO Orders VALUES (171,'2018-01-05 19:10:10');
INSERT INTO Orders VALUES (1710,'2018-01-06 09:28:14');
INSERT INTO Orders VALUES (1712,'2018-01-07 05:10:10');
INSERT INTO Orders VALUES (1713,'2018-01-07 05:28:14');
INSERT INTO Orders VALUES (1714,'2018-01-07 09:30:14');
INSERT INTO Orders VALUES (1715,'2018-01-08 03:10:10');
INSERT INTO Orders VALUES (1716,'2018-01-08 04:18:14');
INSERT INTO Orders VALUES (1717,'2018-01-08 08:10:14');
INSERT INTO Orders VALUES (1718,'2018-01-08 09:21:14');
INSERT INTO Orders VALUES (1719,'2018-01-08 20:21:14');
select CAST(DATEADD(HOUR, 11, createdate) AS Date) OrderDate, count(1) CreatedCount
from Orders
where Createdate > getdate() - 12
and DATEPART(dw,CAST(DATEADD(HOUR, 11, createdate) AS Date)) in (6,7,1)
group by CAST(DATEADD(HOUR, 11, createdate) AS Date)
OrderDate CreatedCount
2017-12-29 3
2017-12-30 3
2017-12-31 2
2018-01-01 2
2018-01-05 2
2018-01-06 2
2018-01-07 3
2018-01-08 4
I need to roll it up to
2018-01-01 10
2018-01-08 11
January 9, 2018 at 10:03 am
Probably a better way to accomplish this but with limited time at work here is one way of doing it:
SELECT
CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) IN ('Friday','Saturday','Sunday','Monday') THEN
DATEADD(DAY,CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Friday'
THEN 3
WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Saturday'
THEN 2
WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Sunday'
THEN 1
ELSE 0
END,CAST(DATEADD(HOUR,11,CreateDate) AS DATE))
ELSE CAST(DATEADD(HOUR,11,CreateDate) AS DATE)
END OrderDate
, COUNT(OrderNo) OrderCount
FROM [dbo].[Orders]
GROUP BY
CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) IN ('Friday','Saturday','Sunday','Monday') THEN
DATEADD(DAY,CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Friday' THEN 3 WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Saturday' THEN 2 WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Sunday' THEN 1 ELSE 0 END,CAST(DATEADD(HOUR,11,CreateDate) AS DATE))
ELSE CAST(DATEADD(HOUR,11,CreateDate) AS DATE)
END;
January 9, 2018 at 1:02 pm
Lynn Pettis - Tuesday, January 9, 2018 10:03 AMProbably a better way to accomplish this but with limited time at work here is one way of doing it:
SELECT
CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) IN ('Friday','Saturday','Sunday','Monday') THEN
DATEADD(DAY,CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Friday'
THEN 3
WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Saturday'
THEN 2
WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Sunday'
THEN 1
ELSE 0
END,CAST(DATEADD(HOUR,11,CreateDate) AS DATE))
ELSE CAST(DATEADD(HOUR,11,CreateDate) AS DATE)
END OrderDate
, COUNT(OrderNo) OrderCount
FROM [dbo].[Orders]
GROUP BY
CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) IN ('Friday','Saturday','Sunday','Monday') THEN
DATEADD(DAY,CASE WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Friday' THEN 3 WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Saturday' THEN 2 WHEN DATENAME(WEEKDAY, CAST(DATEADD(HOUR,11,CreateDate) AS DATE)) = 'Sunday' THEN 1 ELSE 0 END,CAST(DATEADD(HOUR,11,CreateDate) AS DATE))
ELSE CAST(DATEADD(HOUR,11,CreateDate) AS DATE)
END;
This worked perfectly. Thanks!!
January 10, 2018 at 11:16 am
I prefer to avoid language dependency when possible, and therefore use a "standard" relative day# for day of week rather than a day name.
SELECT CreateDayAdjusted, COUNT(*)
FROM #Orders
CROSS APPLY (
SELECT CAST(DATEADD(HOUR, 11, CreateDate) AS date) AS CreateDay
) AS ca1
CROSS APPLY (
SELECT DATEDIFF(DAY, 0, CreateDay) % 7 AS CreateDayOfWeek,
4 AS Friday /*0=Mon;1=Tue;...;4=Fri;5=Sat;6=Sun*/
) AS ca2
CROSS APPLY (
SELECT DATEADD(DAY, CASE WHEN CreateDayOfWeek >= Friday
THEN (7 - CreateDayOfWeek) ELSE 0 END, CreateDay) AS CreateDayAdjusted
) AS ca3
GROUP BY CreateDayAdjusted
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".
January 10, 2018 at 12:11 pm
ScottPletcher - Wednesday, January 10, 2018 11:16 AMI prefer to avoid language dependency when possible, and therefore use a "standard" relative day# for day of week rather than a day name.
SELECT CreateDayAdjusted, COUNT(*)
FROM #Orders
CROSS APPLY (
SELECT CAST(DATEADD(HOUR, 11, CreateDate) AS date) AS CreateDay
) AS ca1
CROSS APPLY (
SELECT DATEDIFF(DAY, 0, CreateDay) % 7 AS CreateDayOfWeek,
4 AS Friday /*0=Mon;1=Tue;...;4=Fri;5=Sat;6=Sun*/
) AS ca2
CROSS APPLY (
SELECT DATEADD(DAY, CASE WHEN CreateDayOfWeek >= Friday
THEN (7 - CreateDayOfWeek) ELSE 0 END, CreateDay) AS CreateDayAdjusted
) AS ca3
GROUP BY CreateDayAdjusted
I did say that there is probably a better way.
January 12, 2018 at 2:25 pm
Lynn Pettis - Wednesday, January 10, 2018 12:11 PMScottPletcher - Wednesday, January 10, 2018 11:16 AMI prefer to avoid language dependency when possible, and therefore use a "standard" relative day# for day of week rather than a day name.
SELECT CreateDayAdjusted, COUNT(*)
FROM #Orders
CROSS APPLY (
SELECT CAST(DATEADD(HOUR, 11, CreateDate) AS date) AS CreateDay
) AS ca1
CROSS APPLY (
SELECT DATEDIFF(DAY, 0, CreateDay) % 7 AS CreateDayOfWeek,
4 AS Friday /*0=Mon;1=Tue;...;4=Fri;5=Sat;6=Sun*/
) AS ca2
CROSS APPLY (
SELECT DATEADD(DAY, CASE WHEN CreateDayOfWeek >= Friday
THEN (7 - CreateDayOfWeek) ELSE 0 END, CreateDay) AS CreateDayAdjusted
) AS ca3
GROUP BY CreateDayAdjustedI did say that there is probably a better way.
Wow..This is something else!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply