CODEWITHSUNDEEP
sqlsql-serverrow
Xman
Xman

Reputation: 13

Merge Multiple Row into One according to certain condition

I have data like this:

STARTDATE ENDDATE AMOUNT INVNO

and I want them into one row merging like this:

thanks for helping

ps: But if there is a data start date isnt same previous row's end date, it has to start new row.like this:

should be like this:

Upvotes: 1

Views: 258

Answers (2)

Ian Yates
Ian Yates

Reputation: 1354

Here's some sample data - I've changed the invoice number of your second example to ...13 to distinguish it.

--DROP TABLE #LINES
CREATE TABLE #Lines(
STARTDATE datetime, 
ENDDATE datetime, 
AMOUNT money,
INVNO varchar(50)
)

INSERT INTO #Lines
SELECT 
'2012-11-07 09:23:48.000', '2012-11-07 09:23:59.000', 0.00000000, 000000000002012
UNION ALL SELECT
'2012-11-07 09:23:59.000', '2012-11-07 10:14:02.000', 0.00000000, 000000000002012
UNION ALL SELECT
'2012-11-07 10:14:02.000', '2012-11-07 10:15:13.000', 0.00000000, 000000000002012
UNION ALL SELECT
'2012-11-07 10:15:13.000', '2012-11-07 10:34:08.000', 0.00000000, 000000000002012
UNION ALL SELECT
'2012-11-07 10:34:08.000', '2012-11-07 11:09:33.000', 4000.00000000, 000000000002012
UNION ALL SELECT
'2012-11-07 11:09:33.000', '2012-11-07 11:10:35.000', 4000.00000000, 000000000002012

UNION ALL SELECT
'2012-11-07 09:23:48.000', '2012-11-07 09:23:59.000', 20.00000000, 000000000002013
UNION ALL SELECT
'2012-11-07 09:23:59.000', '2012-11-07 10:14:02.000', 30.00000000, 000000000002013
UNION ALL SELECT
'2012-11-07 10:16:09.000', '2012-11-07 10:19:13.000', 40.00000000, 000000000002013
UNION ALL SELECT
'2012-11-07 10:19:13.000', '2012-11-07 10:34:08.000', 50.00000000, 000000000002013

Then try this

;
WITH 
    InvGroupStartDates AS
    (
        select
            InvGroupStartDate = L1.STARTDATE, 
            L1.INVNO, 
            INVGroupNo = ROW_NUMBER() OVER (PARTITION BY L1.INVNO ORDER BY L1.StartDate )
        from #Lines L1
        where not exists(
            select *
            from #Lines L2
            where L1.ENDDATE = L2.STARTDATE
            and L1.INVNO = L2.INVNO
            )
    ),

    InvGroupRanges AS
    (
        select 
            IGSD1.INVNO, 
            IGSD1.InvGroupStartDate, 
            InvGroupEndDateEx = IGSD2.InvGroupStartDate
        from InvGroupStartDates IGSD1
            left join InvGroupStartDates IGSD2
                on IGSD1.INVNO = IGSD2.INVNO
                and IGSD1.INVGroupNo = IGSD2.INVGroupNo - 1
    )
SELECT 
    STARTDATE = InvGroupRanges.InvGroupStartDate,
    INVNO = InvGroupRanges.INVNO,
    ENDDATE = MAX( L.ENDDATE ),
    AMOUNT = SUM( L.Amount )
from #Lines L
    inner join InvGroupRanges
        on L.STARTDATE >= InvGroupRanges.InvGroupStartDate
        and ( InvGroupRanges.InvGroupEndDateEx is NULL OR L.ENDDATE < InvgroupRanges.InvGroupEndDateEx )
group by InvGroupRanges.InvGroupStartDate, InvGroupRanges.INVNO

The code determines the start of the invoice line ranges (same invoice number, only getting those without a previous line). It then gets the end of the invoice line ranges - the end date is NOT inclusive as it's the start of the subsequent range.

Finally it uses these start/end invoice line range date lines to get your data.

Upvotes: 0

valex
valex

Reputation: 24144

select min(startdate),max(enddate),sum(amount),INVNO 
       from T 
       group by INVNO

Upvotes: 1

Related Questions