How to get one output table from select in while-loop

Question

Edited to live up to the rules here. Sorry about my first attempt.

I got the following sample data:

CREATE TABLE SampleData(
    [Time] [time](7) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO SampleData([Time]) VALUES ('01:00:00')
INSERT INTO SampleData([Time]) VALUES ('02:00:00')
INSERT INTO SampleData([Time]) VALUES ('02:00:00')
INSERT INTO SampleData([Time]) VALUES ('03:00:00')
INSERT INTO SampleData([Time]) VALUES ('03:00:00')
INSERT INTO SampleData([Time]) VALUES ('03:00:00')
INSERT INTO SampleData([Time]) VALUES ('04:00:00')
INSERT INTO SampleData([Time]) VALUES ('04:00:00')
INSERT INTO SampleData([Time]) VALUES ('04:00:00')
INSERT INTO SampleData([Time]) VALUES ('04:00:00')
GO

This is my query:

DECLARE @Counter INT
SET @Counter = 1

WHILE (@Counter <= 4)
BEGIN
    SELECT Count([Time]) AS OrdersAmount
        FROM SampleData
        WHERE DATEPART(HOUR, [Time]) = @Counter

    SET @Counter = @Counter + 1
END

This is the result of the query:

OrdersAmount
1
-----
OrdersAmount
2
-----
OrdersAmount
3
-----
OrdersAmount
4

So 4 seperate tables. What I need is one table, with alle values in it, on each their own row, like this:

OrdersAmount
1
2
3
4

I tried with cte and declaring a temp table, but I just can't make it work.

What is "RPA"? What results do you want? What actually is your question? — Gordon Linoff
– Gordon Linoff, Commented Mar 18, 2021 at 15:08
Sorry, RPA is Robotics Process Automation. So basically a "robot" pressing buttons for you in a certain order to get a specifik result. My question is how I can loop through my select and get the desired output in one table — Henrik
– Henrik, Commented Mar 18, 2021 at 15:11
. . I don't see the desired output in the query. I see results from a current query and then lots and lots of explanation. — Gordon Linoff
– Gordon Linoff, Commented Mar 18, 2021 at 15:17
While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Mar 18, 2021 at 15:20

Pradeep Kumar · Accepted Answer · 2021-03-18 15:42:05Z

1

I don't have the data so can't test.

But if I get your problem right, this should work for you.

select PromisedPickupDt = cast(PromisedPickupDate as date), 
        [Hour] = datepart(hour, PromisedPickupDate), 
        HourlyAmount = sum(OrdersAmount) 
from [FLX].[dbo].[NDA_SAP_Bestillinger]
where cast(PromisedPickupDate as date) = cast(getdate() as date) 
group by cast(PromisedPickupDate as date), datepart(hour, PromisedPickupDate)

answered Mar 18, 2021 at 15:42

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2021-03-18 16:02:38Z

0

You need an Hours table to join and group against. You can create this using a VALUES constructor:

SELECT
    Count(*) AS OrdersAmount
FROM (VALUES 
    (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)
) AS v(Hour)

LEFT JOIN [FLX].[dbo].[NDA_SAP_Bestillinger]
 ON v.Hour = DATEPART(hour, PromisedPickupTime)
   AND PromisedPickupDate >= CAST(CAST(GETDATE() AS date) AS datetime)
   AND PromisedPickupDate < CAST(DATEADD(day, 1, CAST(GETDATE() AS date)) AS datetime)
GROUP BY v.Hour;

What is going on here is that we start with a filter by the current date.

Then we join a constructed Hours table against the hour-part of the date. Because it is on the left-side of a LEFT JOIN, we now have all the hours whether or not there is any value in that hour. We can now group by it.

Note the correct method to compare to a date range: a half-open interval >= AND <

Do not use YEAR\MONTH etc in join and filter predicates, as indexes cannot be used for this.

edited Mar 18, 2021 at 16:02

answered Mar 18, 2021 at 15:29

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

4 Comments

Henrik Over a year ago

I had to edit the v.Hour = HOUR(PromisedPickupDate) to v.Hour = DATEPART(HOUR, PromisedPickupTime), but then it worked. That's amazing. Now I have to figure out how you made that work. Thank you so much

Henrik Over a year ago

I've tested it pretty thoroughly now, and while it does work really well, there is a slight issue, where it will not create an entry if there is no orders. In my current dataset I have no orders at 3am. This query simply skips that then

Charlieface Over a year ago

Yeah forgot that was why I wanted an Hours table in the first place. Have fixed now with a LEFT JOIN

Henrik Over a year ago

Again thank you. Yes that does make sense, both the part why you wanted a table (which I could just make) and the left join. For some reason, is returns 1 in the 3am output which it skipped before because it doesn't exist

Collectives™ on Stack Overflow

How to get one output table from select in while-loop

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related