0

I have two tables say - Plans and Discounts. Below are the sample data in Plans table.

Plans Table

Below are the sample data in the Discounts table.

Discounts Table

These two tables are related using PlanName column. I am looking for a query in the most optimized way to get a resultset in such a way that a discount for a particular plan should come below the corresponding plans. Sample data below.

Result Set

0
DECLARE @t table(id int identity(1,1),PlanName varchar(50),Amount int
                 ,startdate Datetime,enddate Datetime)

insert into @t values('ABC',100,'2019-01-01','2019-12-31')
,('ABC',100,'2019-04-01','2019-11-30'),('XYZ',300,'2019-04-01','2019-11-30')

 DECLARE @d table(DiscountName varchar(50)
,PlanName varchar(50),Amount int,startdate Datetime,enddate Datetime)
    insert into @d values('ABC discount','ABC',20,'2019-01-01','2019-12-31')


    ;

    WITH CTE
    AS (
        SELECT *
            ,DENSE_RANK() OVER (
                ORDER BY PlanName
                ) RowNum
        FROM @t
        )
    --select * from CTE
    --return
    SELECT ordercol
        ,PlanName
        ,Amount
        ,startdate
        ,enddate
    FROM (
        SELECT cast(RowNum AS DECIMAL(5, 2)) ordercol
            ,PlanName
            ,Amount
            ,startdate
            ,enddate
        FROM CTE

        UNION ALL

        SELECT cast(RowNum AS DECIMAL(5, 2)) + 0.1
            ,d.DiscountName
            ,d.Amount
            ,d.startdate
            ,d.enddate
        FROM @d d
        CROSS APPLY (
            SELECT TOP 1 RowNum
            FROM CTE t
            WHERE d.PlanName = t.PlanName
            ) t
        ) t4
    ORDER BY t4.ordercol

In my script main idea is Ordercol, so depending upon your real data Window Function may or may not be require.

If I assume that your REAL TABLE design and data is as seen, then if you use INNER JOIN then it will give duplicate records.To eliminate duplicate records you do some thing like Distinct or use Window Function which will degrade the performance.

So CROSS APPLY will perform ok in this case.Or you can test yourself with million records.

If your table is Normalize then it will perform the BEST.

Keep PlaName and Discount Name both in seperate table and refer the respective ID.

DECLARE @P table(PlanId int identity(1,1),PlanName varchar(50))
insert into @P values ('ABC'),('XYZ')

DECLARE @t table(id int identity(1,1),PlanId int,Amount int,startdate Datetime,enddate Datetime)
insert into @t values(1,100,'2019-01-01','2019-12-31'),(1,100,'2019-04-01','2019-11-30')
,(2,300,'2019-04-01','2019-11-30')

-- One to one relation between Plan Table and discount table
DECLARE @d table( DiscountName varchar(50),PlanId int,Amount int,startdate Datetime,enddate Datetime)
insert into @d values('ABC discount',1,20,'2019-01-01','2019-12-31')

Notice : DiscountName should be in separate table. Also, notice how Planid is populated in Discount Table ?

It is ok for all other queries and requirement.In this requirement also you do not need Window Function So naturally performance improve tremendously.

SELECT ordercol
    ,PlanName
    ,Amount
    ,startdate
    ,enddate
FROM (
    SELECT cast(t.PlanId AS DECIMAL(5, 2)) ordercol
        ,P.PlanName
        ,Amount
        ,startdate
        ,enddate
    FROM @t T
    inner join @P P on t.PlanId=p.PlanId
    UNION ALL

    SELECT cast(PlanId AS DECIMAL(5, 2)) + 0.1
        ,d.DiscountName
        ,d.Amount
        ,d.startdate
        ,d.enddate
    FROM @d d
    --CROSS APPLY (
    --  SELECT TOP 1 RowNum
    --  FROM CTE t
    --  WHERE d.PlanName = t.PlanName
    --  ) t
    ) t4
ORDER BY t4.ordercol

Planid should be Clustered Index in both table.

  • I tried this and it worked for me. Also , it worked for me when I tried with INNER JOIN instead of CROSS APPLY. Which is better in this case?. – inDiscover Apr 17 at 15:08
  • @inDiscover, check my edited answer. – KumarHarsh Apr 18 at 3:33
1

What you need is not a join which combines tables horizontally, you need a union set operator to join them vertically. In your images all tables are named 'table 1' so I changed one of them to 'table 2' to make the query valid.

SELECT PlanName AS ProductName, Amount, StartDate, EndDate
FROM Table1
UNION ALL
SELECT DiscountName, Amount, StartDate, EndDate
FROM Table2
ORDER BY ProductName;

HTH

  • This will just club data from two tables. Also , as i mentioned clearly in my question, it will not show a discount for a particular plan under that particular plan as shown in the sample result set. Also we dont need to show all the discounts if PlanName in the discounts table does not exist in plan table. I have mentioned this also in my question. – inDiscover Apr 17 at 4:47
  • Did you try it? The order by will make the data sort per your request. And you didn't mention anything about " we dont need to show all the discounts if PlanName in..." see m.wikihow.com/Ask-a-Question-on-Stack-Overflow – SQLRaptor Apr 17 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.