Get an hour record for each group of records

I need to get rows for each hour in the day on each type of part that has any data. I have tried several attempts but seem to only be able to get the HOUR column to show each hour but it will not show the location. I have the data I need in a query and I have tried to join it to a table of Hours by the Hour Time stamp of the records to get each group of parts to have a row for each hour in the day but still nothing seems to work

I have tried the following to get the Hours columns to show for each part regardless of data in the hour

DECLARE @Date DATETIME = GETDATE()
--Gets an hour field for each hour in day 12AM - 11PM
;WITH Dates AS
(
    SELECT FORMAT(DATEADD(HOUR, -1, @Date),'h tt') [Hour], 
           DATEADD(HOUR,-1,@Date) [Date], 
           1 Num
    UNION ALL
    SELECT FORMAT(DATEADD(HOUR, -1, [Date]),'h tt'), 
           DATEADD(HOUR,-1,[Date]), 
           Num+1
    FROM Dates
    WHERE Num <= 23
)
, PickedData AS
(SELECT [Hour], x.group_area, x.HC, x.HCSum, x.picked_user, x.pickedDateTime, x.rate, x.reaches, x.ShiftOutput
    FROM Dates
    LEFT OUTER JOIN (PICKEDDATA that includes an hour stamp with other columns) x
    ON x.hourTimestamp = [Dates].Hour
 )

SELECT group_area, PD.HC, PD.reaches, PD.Hour
FROM PickedData PD
JOIN Dates D
    ON D.[Hour] = PD.[Hour]
ORDER BY PD.pickedDateTime

What I am getting:

For example what I would want to get for 6AM to 5PM

PARTS

The general solution to this kind of problem is to start by making a projection of all possible Part/Hour combinations. A CROSS JOIN is commonly used, and the projection can be described in a CTE. Then take that projection and LEFT JOIN back to the raw data to fill in the details.

So it will be something like this:

WITH Hours As (
   --  <short query to get all possible hours>
   -- (based on exmaple returns 12 rows with values from '6 AM' to '5 PM')
)
, Parts As (
   --  <short query to get key for all possible parts>
   -- (based on example returns 2 rows, for LOSTT, and GOAT PEN)
)
, PartHours As (
    SELECTS Parts.Key, Hours.Hour
    FROM Hours
    CROSS JOIN Parts
)
, PickedData As (
   -- base data here
         -- needs to include a column to match for both the Part and the Hour values 
         -- ideally there will only be one row for each part/hour pair
         -- you may find it useful to do this earlier if you need it to produce the Parts our Hours pieces
)
SELECT ph.Part,
    <pd columns>,
    ph.Hour
FROM PartHours ph
LEFT JOIN PickedData pd ON pd.Hour = ph.Hour AND pd.Part = ph.Part
ORDER BY ph.Part, ph.Hour

If more than one row from the source data could match, you can use aggregation, an APPLY lateral join, or windowing to roll them up.

---

Finally, I see a few expressions like this:

 CONVERT(DATE,pck_dtl2.picked_datetime) = FORMAT (GETDATE(), 'yyyy-MM-dd')

Assuming picked_datetime is a reasonable data type (and, btw, varchar is not reasonable for columns with datetime in the name), the expression can be many times more efficient like this:

pck_dtl2.picked_datetime >= cast(getdate() as date)
    and pck_dtl2.picked_datetime < cast(dateadd(day, 1, getdate()) as date)

There are several reasons why that is both faster and more accurate. But the most important thing to understand is, thanks to cultural/internationalization issues, converting date (or number) types to and from strings types is not the quick or simple operation we often expect. Instead, it tends to be slow and error prone: something to avoid. That is, what you have is about the worst possible way to do this is in common use.

I further see the next expression looks like this:

DATEPART(HOUR,pck_dtl2.picked_datetime) >= 6 --@StartTime  

Which we can roll into the same check as the prior suggestion like this:

pck_dtl2.picked_datetime >= dateadd(hour, 6, cast(cast(getdate() as date) as datetime))
    and pck_dtl2.picked_datetime < cast(dateadd(day, 1, getdate()) as date)

The most recent SQL Server 2022 can also simplify this via the DATETRUNC() function.

As the query is currently written, you have to convert these values to string for every row in the source set, even rows you will not need (which, again, is even slower than you'd expect), and you will not be able to use indexes for the comparisons, which cuts to the core of database performance. The code I suggest here fixes BOTH of those issues.

The tl;dr version is, whenever you have a comparison with a column on one side and a common value like getdate() or 6 on the other, but you need to make an adjustment so the sides match, you want to find a way to make the adjustment to the common value, rather than the column. This reduces the number of computations from one per row to one TOTAL, and preserves your ability to use indexes with the column.