CODEWITHSUNDEEP
sql-servert-sql
mohan111
mohan111

Reputation: 8865

how to show null When values are not there

i have a query where i need to show wise Months like 3,6,9,12 months and it is actually Month Plans like Gold,Silver,Classic etc if there is membership amount need to show value or other need to show null value.... Here is my query :

IF OBJECT_ID(N'Tempdb..#months') IS NOT NULL
    DROP TABLE  #months;
        IF OBJECT_ID(N'Tempdb..#membership') IS NOT NULL
    DROP TABLE  #membership;
DECLARE @i_FromCustID INT = 1
DECLARE  @i_BranchId INT,@i_CasteId INT,@i_GenderID INT
    CREATE TABLE #membership
    (
     MembershipName VARCHAR(50),
     Duration INT,
     MemberShipAmount INT
    )

    CREATE TABLE #months(ID IDENTITY()Duration INT)

     SELECT
        @i_BranchId = BranchID,@i_GenderID = GenderID
    FROM 
        Cust_BasicInfo  
    WHERE Cust_ID = @i_FromCustID

    SELECT 
        @i_CasteId = CasteID
    FROM 
        Cust_Details
    WHERE Cust_ID = @i_FromCustID   
        SELECT  @i_BranchId,@i_GenderID,@i_CasteId

        INSERT INTO #months
        SELECT 3
        UNION 
        SELECT 6
        UNION
        SELECT 9
        UNION 
        SELECT 12

Select * From   #months

        --INSERT INTO #membership 
            SELECT CASE 
        WHEN CHARINDEX('_', MembershipName) > 0
            THEN SUBSTRING(MembershipName, 1, CHARINDEX('_', MembershipName) - 1)
        ELSE MembershipName
        END AS NAME,
    MemberShipDuration,
    MembershipAmount,
    ms.Duration
FROM #months ms
LEFT JOIN Emp_MembershipTypes em ON ms.Duration = em.MemberShipDuration
    AND MemberShipTypeID = 236
    AND BranchID = @i_BranchId
    AND CasteID = @i_CasteId
    AND GenderID = @i_GenderID
    AND MembershipName IS NOT NULL
ORDER BY MembershipName

My outPut coming Like this :

NAME    MemberShipDuration  MembershipAmount    Duration
Classic 12  1500    12
Classic 12  1500    12
Gold    3   1000    3
Gold    6   1000    6
Gold    9   1000    9
Gold    12  1000    12
Silver  3   1000    3
Silver  6   1   6

But how can i get output like this 

NAME    MemberShipDuration  MembershipAmount    Duration
Classic 3   1500    3
Classic 6   NULL    6
Classic 9   NULL    9
Classic 12  NULL    12
Gold    3   1000    3
Gold    6   1000    6
Gold    9   1000    9
Gold    12  1000    12
Silver  3   1000    3
Silver  6   1   6
Silver  9   NULL    9
Silver  12  NULL    12

Upvotes: 1

Views: 199

Answers (1)

gbn
gbn

Reputation: 432180

You need to extend months to include the types, and LEFT JOIN onto both columns

Something like this.

CREATE TABLE #months(Name varchar(20), Duration INT);
..
INSERT INTO #months
SELECT
    X.Name, Y.Duration
FROM
    (VALUES ('Classic'),('Gold'),('Silver')) X(Name)
    CROSS JOIN
    (VALUES (3),(6),(9),(12)) Y(Duration)
...
SELECT
    ms.Name,
    ms.Duration,
    MembershipAmount,
    MemberShipDuration
FROM
    #months ms
    LEFT JOIN Emp_MembershipTypes em ON ms.Duration = em.MemberShipDuration AND
            CASE
                WHEN CHARINDEX('_', MembershipName) > 0 THEN SUBSTRING(MembershipName, 1, CHARINDEX('_', MembershipName) - 1)
                ELSE MembershipName
            END = ms.Name
                    AND MemberShipTypeID = 236
                    AND BranchID = @i_BranchId
                    AND CasteID = @i_CasteId
                    AND GenderID = @i_GenderID
                    AND MembershipName IS NOT NULL
ORDER BY
    ms.Name, ms.Duration

And you may need to SUM(MembershipAmount) too (with a GROUP BY) but it's hard to tell...

Upvotes: 1

Related Questions