I have to get a count of orders placed between 1 pm yesterday and 1 pm today by day

Question

I have to get a count of orders placed between 1 pm yesterday and 1 pm today by day

QuietCoder

SSChasing Mays

Points: 621
More actions
January 8, 2018 at 2:04 pm

#327123

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

QuietCoder - Monday, January 8, 2018 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

What have you tried?

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

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) A socialist is someone who will give you the shirt off *someone else's* back.

RandomStream Hall of Fame Points: 3735 More actions · Answer 3

SELECT CAST(OrderDate AS DATE) AS [Date], COUNT(1) AS [Count] FROM [OrderTable] GROUP BY CAST(OrderDate AS DATE) ORDER BY COUNT(1) DESC

Sue_H SSC Guru Points: 90860 More actions · Answer 4

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

QuietCoder SSChasing Mays Points: 621 More actions · Answer 5

QuietCoder

SSChasing Mays

Points: 621

January 8, 2018 at 3:31 pm

#1974985

Thank you Scott!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

QuietCoder - Monday, January 8, 2018 3:31 PM
Thank you Scott!

Too bad you never posted what you had tried.

QuietCoder SSChasing Mays Points: 621 More actions · Answer 7

Lynn Pettis - Monday, January 8, 2018 3:39 PM
QuietCoder - Monday, January 8, 2018 3:31 PM
Thank 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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

QuietCoder - Monday, January 8, 2018 3:42 PM
Lynn Pettis - Monday, January 8, 2018 3:39 PM
QuietCoder - Monday, January 8, 2018 3:31 PM
Thank 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.

QuietCoder SSChasing Mays Points: 621 More actions · Answer 9

Lynn Pettis - Monday, January 8, 2018 4:07 PM
QuietCoder - Monday, January 8, 2018 3:42 PM
Lynn Pettis - Monday, January 8, 2018 3:39 PM
QuietCoder - Monday, January 8, 2018 3:31 PM
Thank 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 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

QuietCoder - Monday, January 8, 2018 4:14 PM
Lynn Pettis - Monday, January 8, 2018 4:07 PM
QuietCoder - Monday, January 8, 2018 3:42 PM
Lynn Pettis - Monday, January 8, 2018 3:39 PM
QuietCoder - Monday, January 8, 2018 3:31 PM
Thank 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.

QuietCoder SSChasing Mays Points: 621 More actions · Answer 11

Lynn Pettis - Monday, January 8, 2018 4:20 PM
QuietCoder - Monday, January 8, 2018 4:14 PM
Lynn Pettis - Monday, January 8, 2018 4:07 PM
QuietCoder - Monday, January 8, 2018 3:42 PM
Lynn Pettis - Monday, January 8, 2018 3:39 PM
QuietCoder - Monday, January 8, 2018 3:31 PM
Thank 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

RandomStream Hall of Fame Points: 3735 More actions · Answer 12

Sue_H - Monday, January 8, 2018 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

You're correct, Sue. My bad.