Group Similar records Sql Server 2008

Question

Hi All,

I will be writing a query to achieve something like below, please help me

TableName: Application

AppId (PK,int) AppType (bit)
1               0
2               0
3               0  
4               0
5               1
6               0
7               0
8               0
9               1
10              1
11              0
12              0
13              1
14              0
15              1

I have to sequentially group App_Id based on App_type and create a batch of records. The important point to note is that I have to maintain the SEQUENCE of AppId while creating the batches. The maximum number of records a batch can have depends on batch size parameter (say batch size set to 3 for now). Once batch is created insert the details in a different table say called ApplicationBatch. So I want an output something like:

TableName: ApplicationBatch

BatchId  MinAppId  MaxAppId  AppType

001     1      3       0
002     4      4       0
003     5      5       1
004     6      8       0
005     9      10       1
006     11      12       0
007     13      13       1
008     14      14       0
009     15      15       1

One more thing I have to design the query in a best efficient and optimized way because Application table can have more than million records in it.

Many thanks

Answer 1

the question is not clear, but I understand the next.

you want to have a sequence for aplication and that depends of the number of lines inserted in anohter table?

see you.

Answer 2

Sorry, if i could not explain my question earlier. I will try best to explain it this time.

Currently I have Application table (defined in my original ques above) and I want to populate ApplicaionBatch table based on data from Application table.

Answer 3

It's hard to do this without CURSORS or SQLCLR. Would you consider writting a table-valued function in c# and ambedding the assembly in SQL Server? (SQLCLR) That's what I would do, and then I would do a while loop processing the records sequantially.

Answer 4

Try this. Pretty complicated but works. I didn't test it on so many rows, but it iterates through the table only once.

First you have to do some preporocesing.

-- create temporary table
CREATE TABLE #tmpApp(AppId INT, AppType BIT , BatchId INT)

INSERT INTO #tmpApp(AppId,AppType)
SELECT AppId, AppType FROM Application

-- declare variables
DECLARE @curId INT
DECLARE @oldCurId INT

DECLARE @appType INT
DECLARE @oldAppType INT

DECLARE @batchNo INT
DECLARE @itemsInBatch INT

SET @oldCurId = 0
SET @batchNo = 1
SET @itemsInBatch = 0

SELECT TOP 1 
    @curId = AppId, 
    @appType = AppType 
FROM #tmpApp
WHERE AppId > @oldCurId
ORDER BY AppId 

WHILE @curId IS NOT NULL
BEGIN
    IF @oldAppType <> @appType OR @itemsInBatch >= 3
    BEGIN
        SET @batchNo = @batchNo + 1
        SET @itemsInBatch = 0
    END

    SET @itemsInBatch = @itemsInBatch + 1

    UPDATE #tmpApp 
    SET batchId = @batchNo 
    WHERE AppId = @curId

    SET @oldCurId = @curId
    SET @oldAppType = @appType

    SET @curId = NULL
    SELECT TOP 1 
        @curId = AppId,
        @appType = AppType  
    FROM #tmpApp
    WHERE AppId > @oldCurId
    ORDER BY AppId 
END

And execute the query:

-- the final query
SELECT 
    BatchId, 
    MIN(AppId) AS MinAppId, 
    MAX(AppId) AS MaxAppId, 
    AppType
FROM #tmpApp
GROUP BY BatchId, AppType
ORDER BY BatchId

Answer 5

Thanks very much for your help Lukasz. I really appreciate it. I tested with various data and it worked.

One thing I am bit concerned since Application table will have bulk data (more than million rows), would it be ok to have million rows in temporary table?

Also I need to LOCK the application table while creating the batches because this application (windows service) would be deplyed on multiple machines and I want only one instance of application to create batches at one time.