January 8, 2018 at 2:04 pm
The table looks like this
OrderNo, OrderDate
908, 1/3/2018 13:20:34
909, 1/3/2018 15:18:41
910, 1/4/2018 09:15:21
911, 1/4/2018 17:09:44
912, 1/4/2018 18:20:35
913, 1/5/2018 22:20:35
Expected Result
Date, Count
1/4/2018, 3
1/5/2018, 2
1/6/2018, 1
Any help is really appreciated
Thanks
January 8, 2018 at 2:07 pm
QuietCoder - Monday, January 8, 2018 2:04 PMThe table looks like this
OrderNo, OrderDate
908, 1/3/2018 13:20:34
909, 1/3/2018 15:18:41
910, 1/4/2018 09:15:21
911, 1/4/2018 17:09:44
912, 1/4/2018 18:20:35
913, 1/5/2018 22:20:35Expected Result
Date, Count
1/4/2018, 3
1/5/2018, 2
1/6/2018, 1Any help is really appreciated
Thanks
What have you tried?
January 8, 2018 at 2:35 pm
Here's one way:
SELECT CAST(DATEADD(HOUR, 11, OrderDate) AS date) AS Date,
COUNT(*) AS Count
FROM (VALUES
(908, '1/3/2018 13:20:34'),(
909, '1/3/2018 15:18:41'),(
910, '1/4/2018 09:15:21'),(
911, '1/4/2018 17:09:44'),(
912, '1/4/2018 18:20:35'),(
913, '1/5/2018 22:20:35') ) x(OrderNo, OrderDate)
GROUP BY CAST(DATEADD(HOUR, 11, OrderDate) AS date)
ORDER BY CAST(DATEADD(HOUR, 11, OrderDate) AS date)
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 8, 2018 at 3:01 pm
SELECT CAST(OrderDate AS DATE) AS [Date], COUNT(1) AS [Count]
FROM [OrderTable]
GROUP BY CAST(OrderDate AS DATE)
ORDER BY COUNT(1) DESC
January 8, 2018 at 3:14 pm
RandomStream - Monday, January 8, 2018 3:01 PM
SELECT CAST(OrderDate AS DATE) AS [Date], SUM(1) AS [Count]
FROM [OrderTable]
GROUP BY CAST(OrderDate AS DATE)
ORDER BY SUM(1) DESC
That will sum the orders by date but the poster needs them done based on 1:00 pm previous to 1:00 pm current. So those on the 1/3/2018 would be counted towards the 1/4/2018 as they occurred after 1:00 pm. And there is one before 1:00 pm on 1/4/2018. So 1/3/2018 would be 0, 1/4/2018 would be 3, etc.
Sue
January 8, 2018 at 3:31 pm
Thank you Scott!
January 8, 2018 at 3:39 pm
QuietCoder - Monday, January 8, 2018 3:31 PMThank you Scott!
Too bad you never posted what you had tried.
January 8, 2018 at 3:42 pm
Lynn Pettis - Monday, January 8, 2018 3:39 PMQuietCoder - Monday, January 8, 2018 3:31 PMThank you Scott!Too bad you never posted what you had tried.
Sorry, I had only gotten up to something like Random Stream posted above
Thanks
January 8, 2018 at 4:07 pm
QuietCoder - Monday, January 8, 2018 3:42 PMLynn Pettis - Monday, January 8, 2018 3:39 PMQuietCoder - Monday, January 8, 2018 3:31 PMThank you Scott!Too bad you never posted what you had tried.
Sorry, I had only gotten up to something like Random Stream posted above
Thanks
I posted the request just minutes after you posted your question.
January 8, 2018 at 4:14 pm
Lynn Pettis - Monday, January 8, 2018 4:07 PMQuietCoder - Monday, January 8, 2018 3:42 PMLynn Pettis - Monday, January 8, 2018 3:39 PMQuietCoder - Monday, January 8, 2018 3:31 PMThank you Scott!Too bad you never posted what you had tried.
Sorry, I had only gotten up to something like Random Stream posted above
Thanks
I posted the request just minutes after you posted your question.
I know and I knew what I had wasn't anywhere close to the answer. I'm new here, I'll post whatever I have next time 🙂
January 8, 2018 at 4:20 pm
QuietCoder - Monday, January 8, 2018 4:14 PMLynn Pettis - Monday, January 8, 2018 4:07 PMQuietCoder - Monday, January 8, 2018 3:42 PMLynn Pettis - Monday, January 8, 2018 3:39 PMQuietCoder - Monday, January 8, 2018 3:31 PMThank you Scott!Too bad you never posted what you had tried.
Sorry, I had only gotten up to something like Random Stream posted above
Thanks
I posted the request just minutes after you posted your question.
I know and I knew what I had wasn't anywhere close to the answer. I'm new here, I'll post whatever I have next time 🙂
It still would have been nice to know what you had tried. It is a learning thing. I had an answer in 10 minutes, I just wanted to see what you tried before posting it.
January 8, 2018 at 4:42 pm
Lynn Pettis - Monday, January 8, 2018 4:20 PMQuietCoder - Monday, January 8, 2018 4:14 PMLynn Pettis - Monday, January 8, 2018 4:07 PMQuietCoder - Monday, January 8, 2018 3:42 PMLynn Pettis - Monday, January 8, 2018 3:39 PMQuietCoder - Monday, January 8, 2018 3:31 PMThank you Scott!Too bad you never posted what you had tried.
Sorry, I had only gotten up to something like Random Stream posted above
Thanks
I posted the request just minutes after you posted your question.
I know and I knew what I had wasn't anywhere close to the answer. I'm new here, I'll post whatever I have next time 🙂
It still would have been nice to know what you had tried. It is a learning thing. I had an answer in 10 minutes, I just wanted to see what you tried before posting it.
Thanks Lynn. I have something more complex coming up! Will post soon
January 8, 2018 at 4:50 pm
Sue_H - Monday, January 8, 2018 3:14 PMRandomStream - Monday, January 8, 2018 3:01 PM
SELECT CAST(OrderDate AS DATE) AS [Date], SUM(1) AS [Count]
FROM [OrderTable]
GROUP BY CAST(OrderDate AS DATE)
ORDER BY SUM(1) DESCThat will sum the orders by date but the poster needs them done based on 1:00 pm previous to 1:00 pm current. So those on the 1/3/2018 would be counted towards the 1/4/2018 as they occurred after 1:00 pm. And there is one before 1:00 pm on 1/4/2018. So 1/3/2018 would be 0, 1/4/2018 would be 3, etc.
Sue
You're correct, Sue. My bad.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply