Freight Reports

You can execute any of the queries below using Excel Query Builder in RentalPoint.....
Remember to change the date values (highlighted in red below) as needed.

Freight Report for Date Range

DECLARE @fromdate DATETIME
DECLARE @todate DATETIME

SET @fromdate = '2021-01-01'
SET @todate = '2021-12-31'

SELECT i.invoice_cred_no,
       b.booking_no,
       CASE
         WHEN b.delivery_viav71 = -1 THEN 'Customer'
         WHEN b.delivery_viav71 = -2 THEN 'We Deliver'
         ELSE f.freightdesc
       END                                     AS 'Delivery',
       Isnull(f.service, '')                AS [Service],
       CASE
         WHEN b.pickup_viav71 = -1 THEN 'Customer'
         WHEN b.pickup_viav71 = -2 THEN 'We Pickup'
         ELSE f1.freightdesc
       END                                     AS 'Pickup',
       Isnull(f1.service, '')              AS [Service],
       Cast(i.invoice_amount AS DECIMAL(9, 2)) AS [Invoice Amount],
       Cast(i.invdate AS DATE)         AS [Invoice Date]
FROM   tblinvhead I
       LEFT OUTER JOIN tblbookings b
                    ON i.booking_no = b.booking_no
       LEFT OUTER JOIN tblfreight f
                    ON f.id = b.delivery_viav71
       LEFT OUTER JOIN tblfreight f1
                    ON f1.id = b.pickup_viav71
WHERE  ( Isnull(b.delivery_viav71, '') <> ''
         AND Isnull(b.pickup_viav71, '') <> '' )
       AND i.invdate BETWEEN @fromdate AND @todate
ORDER  BY i.invoice_cred_no

Summary of Freight Deliveries for Date Range

DECLARE @fromdate DATETIME
DECLARE @todate DATETIME

SET @fromdate = '2021-01-01'
SET @todate = '2021-12-31'

SELECT Count(i.invoice_cred_no)  AS [# Deliveries],
       CASE
         WHEN b.delivery_viav71 = -1 THEN 'Customer'
         WHEN b.delivery_viav71 = -2 THEN 'We Deliver'
         ELSE f.freightdesc
       END                                      AS [Delivery Type],
       Isnull(f.service, '')                 AS [Service],
       CONVERT(MONEY, COALESCE(Sum(I.invoice_amount), 0)) AS [Invoice Total]
FROM   tblinvhead I
       LEFT OUTER JOIN tblbookings b
                    ON i.booking_no = b.booking_no
       LEFT OUTER JOIN tblfreight f
                    ON f.id = b.delivery_viav71
WHERE  ( Isnull(b.delivery_viav71, '') <> '' )
       AND i.invdate BETWEEN @fromdate AND @todate
GROUP  BY f.service,
          f.freightdesc,
          b.delivery_viav71

Summary of Freight Pickups for Date Range

DECLARE @fromdate DATETIME
DECLARE @todate DATETIME

SET @fromdate = '2021-01-01'
SET @todate = '2021-12-31'

SELECT Count(i.invoice_cred_no)             AS [# Pickups],
       CASE
         WHEN b.pickup_viav71 = -1 THEN 'Customer'
         WHEN b.pickup_viav71 = -2 THEN 'We Pickup'
         ELSE f.freightdesc
       END                                                AS [Pickup Type],
       Isnull(f.service, '')                           AS [Service],
       CONVERT(MONEY, COALESCE(Sum(I.invoice_amount), 0)) AS [Invoice Total]
FROM   tblinvhead I
       LEFT OUTER JOIN tblbookings b
                    ON i.booking_no = b.booking_no
       LEFT OUTER JOIN tblfreight f
                    ON f.id = b.pickup_viav71
WHERE  ( Isnull(b.pickup_viav71, '') <> '' )
       AND i.invdate BETWEEN @fromdate AND @todate
GROUP  BY f.service,
          f.freightdesc,
          b.pickup_viav71