Check Sequential Numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Check Sequential Numbers

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2007-12-26 : 17:21:16

I have written a stored procedure that contains a query similar to the following.


SELECT CAST(MIN(CheckNumber)AS VARCHAR(10))+ '-' + CAST(MAX(CheckNumber)AS VARCHAR(10)) AS CheckRun, Company, SUM(CheckAmount) AS ChecksTotal 
FROM CheckTable
WHERE CheckDate = @CheckDate
GROUP BY Company

It Generates a result set similar to the following


1234-1456,  One Of Our Companies,  50,000,000.00

I am looking for a way "In a single Statement" to seperate the result set by check number if the check numbers are not consecutive. For example the above result set may resemble the following.


1234-1288, One Of Our Companies,  10,000,000.00
1293-1456, One Of Our Companies,  40,000,000.00

I have done this with a table variable, but am racking my brain for a way to do this in a single statement.

Can it be done?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 01:59:40

Can you give more detail on the sample data and structure of your table?

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-27 : 03:11:14

Assuming you have a tally table:

DECLARE @T TABLE (CheckNumber int, CheckAmount int)

INSERT INTO @T SELECT 1234, 10000000 - 54
UNION SELECT 1235, 1
UNION SELECT 1236, 1
UNION SELECT 1237, 1
UNION SELECT 1238, 1
UNION SELECT 1239, 1
UNION SELECT 1240, 1
UNION SELECT 1241, 1
UNION SELECT 1242, 1
UNION SELECT 1243, 1
UNION SELECT 1244, 1
UNION SELECT 1245, 1
UNION SELECT 1246, 1
UNION SELECT 1247, 1
UNION SELECT 1248, 1
UNION SELECT 1249, 1
UNION SELECT 1250, 1
UNION SELECT 1251, 1
UNION SELECT 1252, 1
UNION SELECT 1253, 1
UNION SELECT 1254, 1
UNION SELECT 1255, 1
UNION SELECT 1256, 1
UNION SELECT 1257, 1
UNION SELECT 1258, 1
UNION SELECT 1259, 1
UNION SELECT 1260, 1
UNION SELECT 1261, 1
UNION SELECT 1262, 1
UNION SELECT 1263, 1
UNION SELECT 1264, 1
UNION SELECT 1265, 1
UNION SELECT 1266, 1
UNION SELECT 1267, 1
UNION SELECT 1268, 1
UNION SELECT 1269, 1
UNION SELECT 1270, 1
UNION SELECT 1271, 1
UNION SELECT 1272, 1
UNION SELECT 1273, 1
UNION SELECT 1274, 1
UNION SELECT 1275, 1
UNION SELECT 1276, 1
UNION SELECT 1277, 1
UNION SELECT 1278, 1
UNION SELECT 1279, 1
UNION SELECT 1280, 1
UNION SELECT 1281, 1
UNION SELECT 1282, 1
UNION SELECT 1283, 1
UNION SELECT 1284, 1
UNION SELECT 1285, 1
UNION SELECT 1286, 1
UNION SELECT 1287, 1
UNION SELECT 1288, 1
UNION SELECT 1293, 40000000 - 163
UNION SELECT 1294, 1
UNION SELECT 1295, 1
UNION SELECT 1296, 1
UNION SELECT 1297, 1
UNION SELECT 1298, 1
UNION SELECT 1299, 1
UNION SELECT 1300, 1
UNION SELECT 1301, 1
UNION SELECT 1302, 1
UNION SELECT 1303, 1
UNION SELECT 1304, 1
UNION SELECT 1305, 1
UNION SELECT 1306, 1
UNION SELECT 1307, 1
UNION SELECT 1308, 1
UNION SELECT 1309, 1
UNION SELECT 1310, 1
UNION SELECT 1311, 1
UNION SELECT 1312, 1
UNION SELECT 1313, 1
UNION SELECT 1314, 1
UNION SELECT 1315, 1
UNION SELECT 1316, 1
UNION SELECT 1317, 1
UNION SELECT 1318, 1
UNION SELECT 1319, 1
UNION SELECT 1320, 1
UNION SELECT 1321, 1
UNION SELECT 1322, 1
UNION SELECT 1323, 1
UNION SELECT 1324, 1
UNION SELECT 1325, 1
UNION SELECT 1326, 1
UNION SELECT 1327, 1
UNION SELECT 1328, 1
UNION SELECT 1329, 1
UNION SELECT 1330, 1
UNION SELECT 1331, 1
UNION SELECT 1332, 1
UNION SELECT 1333, 1
UNION SELECT 1334, 1
UNION SELECT 1335, 1
UNION SELECT 1336, 1
UNION SELECT 1337, 1
UNION SELECT 1338, 1
UNION SELECT 1339, 1
UNION SELECT 1340, 1
UNION SELECT 1341, 1
UNION SELECT 1342, 1
UNION SELECT 1343, 1
UNION SELECT 1344, 1
UNION SELECT 1345, 1
UNION SELECT 1346, 1
UNION SELECT 1347, 1
UNION SELECT 1348, 1
UNION SELECT 1349, 1
UNION SELECT 1350, 1
UNION SELECT 1351, 1
UNION SELECT 1352, 1
UNION SELECT 1353, 1
UNION SELECT 1354, 1
UNION SELECT 1355, 1
UNION SELECT 1356, 1
UNION SELECT 1357, 1
UNION SELECT 1358, 1
UNION SELECT 1359, 1
UNION SELECT 1360, 1
UNION SELECT 1361, 1
UNION SELECT 1362, 1
UNION SELECT 1363, 1
UNION SELECT 1364, 1
UNION SELECT 1365, 1
UNION SELECT 1366, 1
UNION SELECT 1367, 1
UNION SELECT 1368, 1
UNION SELECT 1369, 1
UNION SELECT 1370, 1
UNION SELECT 1371, 1
UNION SELECT 1372, 1
UNION SELECT 1373, 1
UNION SELECT 1374, 1
UNION SELECT 1375, 1
UNION SELECT 1376, 1
UNION SELECT 1377, 1
UNION SELECT 1378, 1
UNION SELECT 1379, 1
UNION SELECT 1380, 1
UNION SELECT 1381, 1
UNION SELECT 1382, 1
UNION SELECT 1383, 1
UNION SELECT 1384, 1
UNION SELECT 1385, 1
UNION SELECT 1386, 1
UNION SELECT 1387, 1
UNION SELECT 1388, 1
UNION SELECT 1389, 1
UNION SELECT 1390, 1
UNION SELECT 1391, 1
UNION SELECT 1392, 1
UNION SELECT 1393, 1
UNION SELECT 1394, 1
UNION SELECT 1395, 1
UNION SELECT 1396, 1
UNION SELECT 1397, 1
UNION SELECT 1398, 1
UNION SELECT 1399, 1
UNION SELECT 1400, 1
UNION SELECT 1401, 1
UNION SELECT 1402, 1
UNION SELECT 1403, 1
UNION SELECT 1404, 1
UNION SELECT 1405, 1
UNION SELECT 1406, 1
UNION SELECT 1407, 1
UNION SELECT 1408, 1
UNION SELECT 1409, 1
UNION SELECT 1410, 1
UNION SELECT 1411, 1
UNION SELECT 1412, 1
UNION SELECT 1413, 1
UNION SELECT 1414, 1
UNION SELECT 1415, 1
UNION SELECT 1416, 1
UNION SELECT 1417, 1
UNION SELECT 1418, 1
UNION SELECT 1419, 1
UNION SELECT 1420, 1
UNION SELECT 1421, 1
UNION SELECT 1422, 1
UNION SELECT 1423, 1
UNION SELECT 1424, 1
UNION SELECT 1425, 1
UNION SELECT 1426, 1
UNION SELECT 1427, 1
UNION SELECT 1428, 1
UNION SELECT 1429, 1
UNION SELECT 1430, 1
UNION SELECT 1431, 1
UNION SELECT 1432, 1
UNION SELECT 1433, 1
UNION SELECT 1434, 1
UNION SELECT 1435, 1
UNION SELECT 1436, 1
UNION SELECT 1437, 1
UNION SELECT 1438, 1
UNION SELECT 1439, 1
UNION SELECT 1440, 1
UNION SELECT 1441, 1
UNION SELECT 1442, 1
UNION SELECT 1443, 1
UNION SELECT 1444, 1
UNION SELECT 1445, 1
UNION SELECT 1446, 1
UNION SELECT 1447, 1
UNION SELECT 1448, 1
UNION SELECT 1449, 1
UNION SELECT 1450, 1
UNION SELECT 1451, 1
UNION SELECT 1452, 1
UNION SELECT 1453, 1
UNION SELECT 1454, 1
UNION SELECT 1455, 1
UNION SELECT 1456, 1

UNION SELECT 1500, 10000000
UNION SELECT 1501, 10000000
UNION SELECT 1502, 10000000
UNION SELECT 1503, 10000000
UNION SELECT 1504, 10000000

--SELECT CAST(MIN(CheckNumber)AS VARCHAR(10))+ '-' + CAST(MAX(CheckNumber)AS VARCHAR(10)) AS CheckRun, SUM(CheckAmount) AS ChecksTotal FROM @T

SELECT CAST(A.MinC AS varchar(10)) + '-' + CAST(A.MaxC AS varchar(10)), (SELECT SUM(Z.CheckAmount) FROM @T Z WHERE Z.CheckNumber BETWEEN A.MinC AND A.MaxC)
FROM
(
SELECT MinC = A.N,
MaxC = (SELECT MIN(Z.N)
FROM (SELECT A.N
FROM master.dbo.Tally A INNER JOIN @T B ON B.CheckNumber = A.N LEFT OUTER JOIN @T C ON C.CheckNumber = A.N + 1
WHERE C.CheckNumber IS NULL) Z
WHERE Z.N > A.N)
FROM master.dbo.Tally A INNER JOIN @T B ON B.CheckNumber = A.N LEFT OUTER JOIN @T C ON C.CheckNumber = A.N - 1
WHERE C.CheckNumber IS NULL
) A
ORDER BY A.MinC

TSQLMan
Posting Yak Master

160 Posts

Posted - 2007-12-27 : 07:54:13

quote:
Originally posted by visakh16

Can you give more detail on the sample data and structure of your table?

The Relevant Fields are:

CheckNumber INT,
Company VARCHAR(55),
CheckAmount DECIMAL(18,4)

I am Converting and concatenating the Minimum and Maximum Check Number, (111-222), Company (Group By), CheckAmount (SUM).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources