I'm building out a daily delivery report to replace a process that is manually being compiled using a paper tick sheet and Excel. I figured I'd bring my organization into the 21st century. Some of my design choices are locked in place because the report needs to be identical to the existing report. To accomplish this I've created a temp table for each delivery type with counts by hour of the day:

CREATE TABLE #TempTable (
    [Date] DATE,
    HourRange INT,
    Dole INT DEFAULT 0,
    Harborside INT DEFAULT 0,
    Bauxite INT DEFAULT 0,
    Sugar INT DEFAULT 0,
    Fertilizer INT DEFAULT 0,
    Cement INT DEFAULT 0,
    [Auto Carriers] INT DEFAULT 0,
    [High/Wide] INT DEFAULT 0,
    [Flat Bed] INT DEFAULT 0,
    [Non-Cargo] INT DEFAULT 0
);

-- Calculate the date for the previous day
DECLARE @PreviousDay DATE = DATEADD(DAY, -1, GETDATE());

-- Generate hour ranges for the previous day, starting from 0
DECLARE @Hour INT = 0;

WHILE @Hour <= 23 -- Change this to generate hours from 0 to 23
BEGIN
    INSERT INTO #TempTable ([Date], HourRange)
    VALUES (@PreviousDay, @Hour);
    
    SET @Hour = @Hour + 1;
END

This results in a table like this:

Date    HourRange   Dole    Harborside  Bauxite Sugar   Fertilizer  Cement  Auto Carriers   High/Wide   Flat Bed    Non-Cargo
2023-10-21  0   0   0   0   0   0   0   0   0   0   0   
2023-10-21  1   0   0   0   0   0   0   0   0   0   0   
2023-10-21  2   0   0   0   0   0   0   0   0   0   0   
2023-10-21  3   0   0   0   0   0   0   0   0   0   0   
2023-10-21  4   0   0   0   0   0   0   0   0   0   0   
2023-10-21  5   0   0   0   0   0   0   0   0   0   0   
2023-10-21  6   0   0   0   0   0   0   0   0   0   0   
2023-10-21  7   0   0   0   0   0   0   0   0   0   0   
2023-10-21  8   0   0   0   0   0   0   0   0   0   0   
2023-10-21  9   0   0   0   0   0   0   0   0   0   0   
2023-10-21  10  0   0   0   0   0   0   0   0   0   0   
2023-10-21  11  0   0   0   0   0   0   0   0   0   0   
2023-10-21  12  0   0   0   0   0   0   0   0   0   0   
2023-10-21  13  0   0   0   0   0   0   0   0   0   0   
2023-10-21  14  0   0   0   0   0   0   0   0   0   0   
2023-10-21  15  0   0   0   0   0   0   0   0   0   0   
2023-10-21  16  0   0   0   0   0   0   0   0   0   0   
2023-10-21  17  0   0   0   0   0   0   0   0   0   0   
2023-10-21  18  0   0   0   0   0   0   0   0   0   0   
2023-10-21  19  0   0   0   0   0   0   0   0   0   0   
2023-10-21  20  0   0   0   0   0   0   0   0   0   0   
2023-10-21  21  0   0   0   0   0   0   0   0   0   0   
2023-10-21  22  0   0   0   0   0   0   0   0   0   0   
2023-10-21  23  0   0   0   0   0   0   0   0   0   0

One row for each hour of the day, for a count of each cargo type.

I would now like to populate these counts from a query that joins three tables (Visits, VisitCargo, Cargo). Visits contains the time of the visit and vehicle information, Cargo is a table of the types of cargo we track, and VisitCargo is there to mediate a possible many to many relationship in case a visit has more than one cargo associated with it. I'd like to take the count from VisitCargo and apply them to the TempTable I've built above.

To accomplish this, I've constructed a WHILE loop to iterate through the cargo types in order to update the table for each cargo name. With each loop I'm hoping it will update the selected CargoName and update it's column. When I was prototyping out I could get it to update just fine. When I introduced the variable as part of the SET command it all blew up. I tried using dynamicSQL but now I'm getting datatype errors.

DECLARE @CargoOrder INT;

-- Create a cursor to iterate through unique CargoName values
DECLARE CargoCursor CURSOR FOR
SELECT DISTINCT CargoOrder
FROM Cargo
WHERE CargoName not like 'Test%';

-- Open the cursor
OPEN CargoCursor;

-- Fetch the first CargoName
FETCH NEXT FROM CargoCursor INTO @CargoOrder;

-- Start looping through unique CargoName values
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @CargoName NVARCHAR(MAX);
    DECLARE @sql NVARCHAR(MAX);
    SELECT @CargoName = CargoName FROM [dbo].[Cargo] WHERE CargoOrder = @CargoOrder
    -- Construct the dynamic SQL query for each CargoName
    SET @sql = 'UPDATE #TempTable
    SET ' + @CargoName + ' = ISNULL((SELECT CargoCount FROM (
        SELECT ' + @CargoName + ' AS CargoName, DATEPART(HOUR, V.VisitStart_Time) AS HourOfDay,
            COUNT(*) AS CargoCount
        FROM [dbo].[Visit] V
        JOIN [dbo].[VisitCargo] C ON V.GUID = C.VisitGUID
        JOIN [dbo].[Cargo] O ON O.GUID = C.CargoGUID
        WHERE VisitStart_Time >= CAST(GETDATE() - 1 AS DATE) AND VisitStart_Time < CAST(GETDATE() AS DATE)
            AND O.CargoName = ' + @CargoName + '
        GROUP BY DATEPART(HOUR, V.VisitStart_Time)
    ) AS Subquery WHERE Subquery.HourOfDay = #TempTable.HourRange), 0)
WHERE [Date] = CAST(GETDATE() - 1 AS DATE);' -- Adjust the date as needed
    EXEC(@sql);
    -- Fetch the next CargoName
    FETCH NEXT FROM CargoCursor INTO @CargoOrder;
END

-- Close and deallocate the cursor
CLOSE CargoCursor;
DEALLOCATE CargoCursor;

-- Select the results from the temporary table
SELECT * FROM #TempTable;

The error I'm getting from running this query is:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'Dole' to data type int.

Any suggestions?

This doesn't need a temp table, or dynamic SQL, or loops. All of these are slow and difficult to use.

Just use GENERATE_SERIES to generate a list of hours (on older version you can use a numbers generator ). Then left-join your results to that, grouping up by the hour, and take conditional counts for each column.

SELECT
  Date            = CAST(GETDATE() AS date),
  HourRange       = g.value,
  Dole            = COUNT(CASE WHEN c.CargoName = 'Dole'          THEN 1 END),
  Harborside      = COUNT(CASE WHEN c.CargoName = 'c.Harborside'  THEN 1 END),
  [Bauxite Sugar] = COUNT(CASE WHEN c.CargoName = 'Bauxite Sugar' THEN 1 END),
  Fertilizer      = COUNT(CASE WHEN c.CargoName = 'Fertilizer'    THEN 1 END),
  Cement          = COUNT(CASE WHEN c.CargoName = 'Cement'        THEN 1 END),
  [Auto Carriers] = COUNT(CASE WHEN c.CargoName = 'Auto Carriers' THEN 1 END),
  [High/Wide]     = COUNT(CASE WHEN c.CargoName = 'High/Wide'     THEN 1 END),
  [Flat Bed]      = COUNT(CASE WHEN c.CargoName = 'Flat Bed'      THEN 1 END),
  [Non-Cargo]     = COUNT(CASE WHEN c.CargoName = 'Non-Cargo'     THEN 1 END)
FROM GENERATE_SERIES(0, 23) g
LEFT JOIN
  (
    dbo.Visit v
    JOIN dbo.VisitCargo vc ON v.GUID = vc.VisitGUID
    JOIN dbo.Cargo c
       ON c.GUID = vc.CargoGUID
      AND v.VisitStart_Time >= CAST(DATEADD(day, -1, GETDATE()) AS DATE)
      AND v.VisitStart_Time < CAST(GETDATE() AS DATE)
  )
  ON DATEPART(hour, v.VisitStart_Time) = g.value
GROUP BY
  g.value;

Note the direction of the first left-join, and the way all the other joins are nested. You can also use a CTE or subquery for this.

2条答案

按热度按时间

zsohkypk1#

赞(0）回复(0）举报 2023-11-16

1yjd4xko2#

Thank you Dale K Your suggestion to debug what was going into the variable allowed me to find the problem which was this:

AND O.CargoName = ' + @CargoName + '

This is wrong and was resolving to @CargoName being an INT datatype comparison (which it is not). I needed single quotes. So I changed it to:

AND O.CargoName = ''' + @CargoName + '''

and it fixed that issue. Thanks again Dale K.

SQL Server Using a While loop to Update values for each column with the column name defined by a variable in each iteration

2条答案

相关问题

热门标签

最新问答