Home 2019 July SQL Puzzle: SQL Advance Query – Generate the range of data basis on common combination

SQL Puzzle: SQL Advance Query – Generate the range of data basis on common combination

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to generate the report on range basis on the common combination of SendFlag and ReceiveFlag.

Input Data:

Expected Output:

Create a table with sample data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
Database Theory: What is CAP theorem - Consistency, Availability, PartitionPostgreSQL: Create Index on Full Text Search tsvector Data
Comments: 6
  1. Dinesh
    July 17, 2019 at 7:02 am

    –we can get result below method alos.

    Select Min(ID) As FromID,Max(ID) ToID,SendFlag,ReceiveFlag
    From Flags
    Group By SendFlag,ReceiveFlag
    Order By FromID

    ReplyCancel
  2. Samrat
    October 24, 2019 at 6:52 am

    SELECT ISNULL((SELECT MIN(I.ID) FROM dbo.Flags I WHERE I.SendFlag=O.SendFlag AND I.ReceiveFlag=O.ReceiveFlag),0) AS ST,
    ISNULL((SELECT MAX(I.ID) FROM dbo.Flags I WHERE I.SendFlag=O.SendFlag AND I.ReceiveFlag=O.ReceiveFlag),0) AS ED,
    SendFlag,ReceiveFlag
    FROM dbo.Flags O
    GROUP BY SendFlag,ReceiveFlag
    ORDER BY ISNULL((SELECT MIN(I.ID) FROM dbo.Flags I WHERE I.SendFlag=O.SendFlag AND I.ReceiveFlag=O.ReceiveFlag),0)

    ReplyCancel
  3. Hareesh
    January 3, 2020 at 4:30 am

    select min(ID) as startID, Max(ID) as EndID, 0 as SendFlag, 0 as ReceivedFlag
    from Flags
    where SendFlag = 0 and ReceiveFlag = 0
    Union
    select min(ID) as startID, Max(ID) as EndID, 1 as SendFlag, 0 as ReceivedFlag
    from Flags
    where SendFlag = 1 and ReceiveFlag = 0
    union
    select min(ID) as startID, Max(ID) as EndID, 1 as SendFlag, 1 as ReceivedFlag
    from Flags
    where SendFlag = 1 and ReceiveFlag = 1
    Union
    select min(ID) as startID, Max(ID) as EndID, 0 as SendFlag, 1 as ReceivedFlag
    from Flags
    where SendFlag = 0 and ReceiveFlag = 1

    ReplyCancel
  4. Bramhateja Kompala
    February 27, 2020 at 3:02 pm

    SELECT
    SUBSTRING(X.IDs,1,1) StartID,
    CASE WHEN LEN(X.IDs) =1 THEN SUBSTRING(X.IDs,1,1)
    ELSE RIGHT(X.IDs,1)
    END AS EndID,
    SendFlag,
    ReceiveFlag
    FROM
    (
    SELECT STRING_AGG(CAST (ID AS VARCHAR(256)),’,’) IDs, SendFlag, ReceiveFlag FROM Flags GROUP BY SendFlag, ReceiveFlag
    )X
    ORDER BY StartID

    ReplyCancel
  5. BRAMHATEJA
    February 28, 2020 at 11:05 am

    SUBSTRING(X.IDs,1,1) StartID,
    CASE WHEN LEN(X.IDs) =1 THEN SUBSTRING(X.IDs,1,1)
    ELSE RIGHT(X.IDs,1)
    END AS EndID,
    SendFlag,
    ReceiveFlag
    FROM
    (
    SELECT STRING_AGG(CAST (ID AS VARCHAR(256)),’,’) IDs, SendFlag, ReceiveFlag FROM Flags GROUP BY SendFlag, ReceiveFlag
    )X
    ORDER BY StartID

    ReplyCancel
  6. Satish Kumar
    February 29, 2020 at 8:45 am

    SELECT * FROM #Flags
    select
    min(id) as startId,max(id) as EndID,SendFlag,ReceiveFlag
    from
    #Flags
    where SendFlag = 0 and ReceiveFlag = 0
    group by SendFlag,ReceiveFlag

    UNION

    select
    min(id) as startId,max(id) as EndID,SendFlag,ReceiveFlag
    from
    #Flags
    where SendFlag = 1 and ReceiveFlag = 0
    group by SendFlag,ReceiveFlag

    UNION

    select
    min(id) as startId,max(id) as EndID,SendFlag,ReceiveFlag
    from
    #Flags
    where SendFlag = 1 and ReceiveFlag = 1
    group by SendFlag,ReceiveFlag

    UNION

    select
    min(id) as startId,max(id) as EndID,SendFlag,ReceiveFlag
    from
    #Flags
    where SendFlag = 0 and ReceiveFlag = 1
    group by SendFlag,ReceiveFlag

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

July 15, 2019 6 Comments SQL Puzzle, , , , , , , , , , ,