SQL Server how to get Sum of a column avoiding gaps in SQL

I have two tables: Table 1 has ID, Quantity, FK_FirstItem, FK_LastItem, FK_ShippentID, etc. Table 2 has ItemID, SerialNumber, Barcode etc...

Lets says Table 2 is as follows:
| ItemID | Serialnum | Barcode |
| ------------ | ------------ | ------------ |
| 1 | 101 | B10.. |
| 2 | 102 | B10.. |
| 3 | 103 | B10.. |
| 4 | 104 | B10.. |
| 5 | 105 | B10.. |
| 6 | 106 | B10.. |
| 7 | 107 | B10.. |
| 8 | 108 | B10.. |
| 9 | 109 | B10.. |
| 10 | 110 | B10.. |
| 11 | 111 | B10.. |
| 12 | 112 | B10.. |
| 13 | 113 | B10.. |
| 14 | 114 | B10.. |
| 15 | 115 | B10.. |
| 16 | 116 | B10.. |
| 17 | 117 | B10.. |
| 18 | 118 | B10.. |
| 19 | 119 | B10.. |
| 20 | 120 | B10.. |
| 21 | 121 | B10.. |
| 22 | 122 | B10.. |
| 23 | 123 | B10.. |
| 24 | 124 | B10.. |
| 25 | 125 | B10.. |
| 26 | 126 | B10.. |
| 27 | 127 | B10.. |
| 28 | 128 | B10.. |
| 29 | 129 | B10.. |
| 30 | 130 | B10.. |

and Table 1 :

ID	Quantity	FK_FirstItem	FK_LastItem	FK_ShippentID
1	3	1	3	26
2	3	4	6	Null
3	3	7	9	26
4	3	10	12	26
5	3	13	15	Null
6	3	16	18	Null
7	3	19	21	Null
8	3	22	24	26
9	3	25	27	Null
10	3	28	30	Null

I need to get the total number of cards (SUM (Quantity )), and count of boxes (Count (*)) (table 2 is boxes, each row 1 box), and beginning first item serial number, last item serial number where shippent is null. But exclude the gaps. There are gap in serial numbers when some boxes are shipped. Items serial number is sequential. This query will be called multiple times as a dynamic query.

I am trying window functions, with Lag I can get previous serial number, but I don't know how to group by or partition escaping gaps.

With query:

Select 
  Sum(a.Quantity) over() as TotalQnty, 
  b.Serialnum as FrstItemSer, c.Serialnum as LastItemSer,
  Count(ID) over() as BoxQuantity
from table1 a
join table2 b ON a.FK_FirstItem  = b.ItemID
join table2 c ON a.FK_LastItem  = c.ItemID 
where FK_ShippentID is null

I am getting the total result:

#	TotalQty	FrstItemSer	LastItemSer	BoxQuantity
1	18	104	130	6

in this result total 18 items in 6 boxes were not shipped. And items serial numbers are from 104 to 130. But items w/serial# 101 - 104, 107 - 112, and 122 - 124 are shipped.

The result I want to get is:
| # | TotalQty | FrstItemSer | LastItemSer | BoxQuantity |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 3 | 104 | 106 | 1 |
| 2 | 9 | 113 | 121 | 3 |
| 3 | 6 | 125 | 130 | 2 |

where it says total 3 items from 104 to 106 shipped in 1 box, then total 9 items from 113 to 121 shipped in 3 boxes, then total 6 items from 125 to 130 shipped in 2 boxes

You could use the following query.

WITH cte AS (
    SELECT *,
    SUM(
        CASE WHEN FK_ShippentID IS NOT NULL THEN 1 ELSE 0 END
    ) OVER (ORDER BY ID) as GrpID
    FROM tbl1 AS t1
    INNER JOIN tbl2 AS t2
    ON t2.ItemID between t1.FK_FirstItem and t1.FK_LastItem
)

SELECT TotalQty,
FrstItemSer,
LastItemSer,
BoxQuantity
FROM (
    SELECT COUNT(*) AS TotalQty,
    MIN(SerialNum) AS FrstItemSer,
    MAX(SerialNum) AS LastItemSer,
    COUNT(*) / 3 AS BoxQuantity,
    GrpID
    FROM cte
    WHERE FK_ShippentID IS NULL
    GROUP BY GrpID
) t

+==========+=============+=============+=============+
| TotalQty | FrstItemSer | LastItemSer | BoxQuantity |
+==========+=============+=============+=============+
| 3        | 104         | 106         | 1           |
| 9        | 113         | 121         | 3           |
| 6        | 125         | 130         | 2           |
+----------+-------------+-------------+-------------+

SQL Server how to get Sum of a column avoiding gaps in SQL

1条答案

相关问题

热门标签

最新问答