How to remove decimal in reports?

Mohamed_Seddiek · August 16, 2017, 6:40am

Hi support. Please can you tell me how to remove the decimal from reports having quantities?
How to make it 1 instead of 1.000

Mohamed_Seddiek · August 16, 2017, 7:24am

Dears, I made it thanks a lot. please for who having the same trouble here is the solution:

CAST(YOUR_COLUMN AS INTEGER)

when you select the column, Add CAST and define the column as integer. IT WORKS FINE NOW.

RickH · August 16, 2017, 10:00am

Can you post your template for the report please

Mohamed_Seddiek · August 16, 2017, 11:26am

I donno how to post it after writing a message appears “You can only mention a user”!!!

RickH · August 16, 2017, 11:46am

Just copy and paste your report template from samba into the forum

Then highlight it and press the < / > button above to format and the post the reply

Mohamed_Seddiek · August 16, 2017, 2:46pm

@RickH Good, yes it works </> first time to know this. Anyway, here are the reports I hope to be useful with you.

##Report

[Hourly Tickets:3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
    >Date/Hour|8-9|9-10|10-11|11-12|12-13|13-14|14-15|15-16|16-17|17-18|18-19|19-20|20-21|21-22|22-23|23-24|24-01
    @@TicketCountByPeakHours
    >>@@TicketAmount
    [Hourly Item Sales Value:3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
    >Item/Hour|8-9|9-10|10-11|11-12|12-13|13-14|14-15|15-16|16-17|17-18|18-19|19-20|20-21|21-22|22-23|23-24|24-01
    @@isv
    >>@@hiv
    [Hourly Item Sold Qnty:3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
    >Item/Hour|8-9|9-10|10-11|11-12|12-13|13-14|14-15|15-16|16-17|17-18|18-19|19-20|20-21|21-22|22-23|23-24|24-01
    @@his
    >>@@hit
    [Hourly Services Sales Value:3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
    >Service/Hour|8-9|9-10|10-11|11-12|12-13|13-14|14-15|15-16|16-17|17-18|18-19|19-20|20-21|21-22|22-23|23-24|24-01
    @@hssv
    >>@@hiv
    [Hourly Service Sold Qnty:3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
    >Service/Hour|8-9|9-10|10-11|11-12|12-13|13-14|14-15|15-16|16-17|17-18|18-19|19-20|20-21|21-22|22-23|23-24|24-01
    @@hss
    >>@@hit
    [Grand Totals:1,1]
    Tickets Settled|@@TicketCountByPeakHoursTotals
    Items Sold|@@SoldQ
    Total Income|@@TicketAmountByPeakHoursTotal

##SQL SCRIPTS

###@@TicketCountByPeakHours###

    SELECT Convert(nvarchar(20),Date,103),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 7 AND 8 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 8.01 AND 9 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 9.01 AND 10 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 10.01 AND 11 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 11.01 AND 12 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 12.01 AND 13 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 13.01 AND 14 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 14.01 AND 15 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 15.01 AND 16 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 16.01 AND 17 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 17.01 AND 18 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 18.01 AND 19 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 19.01 AND 20 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 20.01 AND 21 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 21.01 AND 22 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 22.01 AND 23 THEN 1 ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [DATE]) BETWEEN 23.01 AND 24 THEN 1 ELSE 0 END)
    FROM Tickets
      Where Date > '{Start}' and Date < '{End}'
      GROUP BY Convert(nvarchar(20),Date,103)

###@@TicketCountByPeakHoursTotals###

    SELECT Count(Id)
    FROM Tickets
    Where Date > '{Start}' and Date < '{End}'

###@@TicketAmount###

    SELECT 'Total of' + ' ' + min(convert(nvarchar,Date,105)),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 7 AND 8 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 8.01 AND 9 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 9.01 AND 10 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 10.01 AND 11 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 11.01 AND 12 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12.01 AND 13 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 13.01 AND 14 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 14.01 AND 15 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 15.01 AND 16 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 16.01 AND 17 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 17.01 AND 18 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 18.01 AND 19 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19.01 AND 20 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 20.01 AND 21 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 21.01 AND 22 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 22.01 AND 23 THEN TotalAmount ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 23.01 AND 24 THEN TotalAmount ELSE 0 END)

    FROM Tickets
    Where Date > '{Start}' and Date < '{End}'
    GROUP BY Convert(nvarchar(20),Date,105)

###@@his###

    SELECT MenuItemName,
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 7 AND 8 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 8.01 AND 9 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 9.01 AND 10 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 10.01 AND 11 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 11.01 AND 12 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 12.01 AND 13 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 13.01 AND 14 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 14.01 AND 15 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 15.01 AND 16 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 16.01 AND 17 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 17.01 AND 18 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 18.01 AND 19 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 19.01 AND 20 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 20.01 AND 21 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 21.01 AND 22 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 22.01 AND 23 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 23.01 AND 24 THEN CAST(Quantity as integer) ELSE 0 END)

    FROM Orders
      Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'
      GROUP BY MenuItemName

###@@hit###

    SELECT 'Total of' + ' ' + min(convert(nvarchar,LASTUPDATEDATETIME,105)),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 7 AND 8 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 8.01 AND 9 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 9.01 AND 10 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 10.01 AND 11 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 11.01 AND 12 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 12.01 AND 13 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 13.01 AND 14 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 14.01 AND 15 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 15.01 AND 16 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 16.01 AND 17 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 17.01 AND 18 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 18.01 AND 19 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 19.01 AND 20 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 20.01 AND 21 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 21.01 AND 22 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 22.01 AND 23 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 23.01 AND 24 THEN CAST(Quantity as integer) ELSE 0 END)
    FROM Orders
    Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'
    GROUP BY Convert(nvarchar(20),LASTUPDATEDATETIME,105)

###@@TicketAmountByPeakHoursTotal###

    SELECT sum(TotalAmount)
    FROM Tickets
    Where Date > '{Start}' and Date < '{End}'

###@@SoldQ###

    Select Sum(CAST(Quantity as integer))
    FROM Orders
    Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'

###@@isv###

    SELECT MenuItemName,
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 7 AND 8 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 8.01 AND 9 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 9.01 AND 10 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 10.01 AND 11 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 11.01 AND 12 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 12.01 AND 13 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 13.01 AND 14 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 14.01 AND 15 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 15.01 AND 16 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 16.01 AND 17 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 17.01 AND 18 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 18.01 AND 19 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 19.01 AND 20 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 20.01 AND 21 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 21.01 AND 22 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 22.01 AND 23 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 23.01 AND 24 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END)

    FROM Orders
      Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'
      GROUP BY MenuItemName

###@@hiv###

    SELECT 'Total of' + ' ' + min(convert(nvarchar,LASTUPDATEDATETIME,105)),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 7 AND 8 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 8.01 AND 9 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 9.01 AND 10 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 10.01 AND 11 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 11.01 AND 12 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 12.01 AND 13 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 13.01 AND 14 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 14.01 AND 15 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 15.01 AND 16 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 16.01 AND 17 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 17.01 AND 18 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 18.01 AND 19 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 19.01 AND 20 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 20.01 AND 21 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 21.01 AND 22 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 22.01 AND 23 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 23.01 AND 24 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END)

    FROM Orders
    Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'
    GROUP BY Convert(nvarchar(20),LASTUPDATEDATETIME,105)

###@@hss###

    SELECT PortionName,
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 7 AND 8 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 8.01 AND 9 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 9.01 AND 10 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 10.01 AND 11 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 11.01 AND 12 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 12.01 AND 13 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 13.01 AND 14 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 14.01 AND 15 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 15.01 AND 16 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 16.01 AND 17 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 17.01 AND 18 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 18.01 AND 19 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 19.01 AND 20 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 20.01 AND 21 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 21.01 AND 22 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 22.01 AND 23 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 23.01 AND 24 THEN CAST(Quantity as integer) ELSE 0 END)
    FROM Orders
      Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'
      GROUP BY PortionName

###@@hst###

    SELECT 'Total of' + ' ' + min(convert(nvarchar,LASTUPDATEDATETIME,105)),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 7 AND 8 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 8.01 AND 9 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 9.01 AND 10 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 10.01 AND 11 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 11.01 AND 12 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 12.01 AND 13 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 13.01 AND 14 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 14.01 AND 15 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 15.01 AND 16 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 16.01 AND 17 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 17.01 AND 18 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 18.01 AND 19 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 19.01 AND 20 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 20.01 AND 21 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 21.01 AND 22 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 22.01 AND 23 THEN CAST(Quantity as integer) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 23.01 AND 24 THEN CAST(Quantity as integer) ELSE 0 END)
    FROM Orders
    Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'
    GROUP BY Convert(nvarchar(20),LASTUPDATEDATETIME,105)

###@@ssq###

    Select Sum(CAST(Quantity as integer))
    FROM Orders
    Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'

###@@hssv###

    SELECT PortionName,
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 7 AND 8 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 8.01 AND 9 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 9.01 AND 10 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 10.01 AND 11 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 11.01 AND 12 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 12.01 AND 13 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 13.01 AND 14 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 14.01 AND 15 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 15.01 AND 16 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 16.01 AND 17 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 17.01 AND 18 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 18.01 AND 19 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 19.01 AND 20 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 20.01 AND 21 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 21.01 AND 22 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 22.01 AND 23 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END),
    Sum(CASE WHEN DATEPART(hour, [LASTUPDATEDATETIME]) BETWEEN 23.01 AND 24 THEN CAST((Quantity*Price) as decimal(6,2)) ELSE 0 END)
    FROM Orders
      Where LASTUPDATEDATETIME > '{Start}' and LASTUPDATEDATETIME < '{End}'
      GROUP BY PortionName

These all reports for same idea I shared, hope to be useful.

RickH · August 16, 2017, 3:09pm

Brill thanks for that ill have a play tonight