No Records found

  • jsshivalik

    SSC-Addicted

    Points: 491

    Hi

    I have below query & i want if No record exists then it should display as below

    Supplies made to Unregistered Persons        0 0
    Supplies made to Composition Taxable Persons 0 0
    Supplies made to UIN holders 0 0
    select "Description" as "Nature of Supplies",
    PoS as "Place of Supply(State/UT)",
    BaseSum as "Total Taxable value",
    IGSTSum as "Integrated Tax"
    from
    (select 'Supplies made to Unregistered Persons' as "Description",
    isnull(PoS, '') as "PoS",
    convert(numeric(19, 2), isnull(sum(BaseSum), 0)) as "BaseSum",
    convert(numeric(19, 2), isnull(sum(IGSTSum), 0)) as "IGSTSum",
    1 as TabOrder
    from GST_Report_GSTR3B_AR_Detail_Tab
    where BpGSTType = 0
    group by PoS
    union all
    select 'Supplies made to Composition Taxable Persons' as "Description",
    isnull(PoS, '') as "PoS",
    convert(numeric(19, 2), isnull(sum(BaseSum), 0)) as "BaseSum",
    convert(numeric(19, 2), isnull(sum(IGSTSum), 0)) as "IGSTSum",
    2 as TabOrder
    from GST_Report_GSTR3B_AR_Detail_Tab
    where BpGSTType = 3
    group by PoS
    union all
    select 'Supplies made to UIN holders' as "Description",
    isnull(PoS, '') as "PoS",
    convert(numeric(19, 2), isnull(sum(BaseSum), 0)) as "BaseSum",
    convert(numeric(19, 2), isnull(sum(IGSTSum), 0)) as "IGSTSum",
    3 as TabOrder
    from GST_Report_GSTR3B_AR_Detail_Tab
    where BpGSTType = 6
    group by PoS) t0
    order by TabOrder

    Thanks

  • Mr. Brian Gale

    SSC-Insane

    Points: 22471

    I am sure there are multiple ways to solve this problem, but one way (possibly not the most efficient) would be to add 3 more UNIONs, one per possible "blank" result.  That being said, I think there may be some optimizations that could be done to the original query.   So what to "Add" would be something like this added into t0 right after the last "group by PoS" statement:

    UNION ALL
    SELECT 'Supplies made to Unregistered Persons','',0,0,1
    WHERE NOT EXISTS (select 1
    from GST_Report_GSTR3B_AR_Detail_Tab
    where BpGSTType = 0)
    UNION ALL
    SELECT 'Supplies made to Composition Taxable Persons','',0,0,2
    WHERE NOT EXISTS (select 1
    from GST_Report_GSTR3B_AR_Detail_Tab
    where BpGSTType = 3)
    UNION ALL
    SELECT 'Supplies made to UIN holders','',0,0,3
    WHERE NOT EXISTS (select 1
    from GST_Report_GSTR3B_AR_Detail_Tab
    where BpGSTType = 6)

    Basically, if there is no result for that BpGSTType (0, 3 or 6), then return the 0 values.  May get a performance improvement too by changing your 3 unions into CASE statements, but without having actual data to work with, I cannot say with any certainty that it will give the same results or won't require some weird manipulation to make it work as expected.

  • Jeff Moden

    SSC Guru

    Points: 995467

    I try to avoid to have hit the table for each section of code and then try to avoid more than 1 section of code.  With a little help from some reference tables, we can make it so that we don't need to change the code for the inevitable "we need another similar report" panic.  Documentation is in the code the follows.

    Here are the reference tables...

    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    -- Create and populate some obviously named reference tables and populate them.
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    --===== This creates the table that will hold BpGSTypes. We can add easily add to to this table to generate different
    -- reports. It''s highly normalized.
    CREATE TABLE dbo.BpGSTType
    (
    BpGSTType TINYINT NOT NULL
    ,TypeDescription VARCHAR(50) NOT NULL
    ,CONSTRAINT PK_BpGSTType PRIMARY KEY CLUSTERED (BpGSTType)
    )
    ;
    INSERT INTO dbo.BpGSTType WITH (TABLOCK)
    (BpGSTType,TypeDescription)
    VALUES
    (0,'Supplies made to Unregistered Persons')
    ,(1,'Test Category 1')
    ,(2,'Test Category 2')
    ,(3,'Supplies made to Composition Taxable Persons')
    ,(4,'Test Category 1')
    ,(5,'Test Category 2')
    ,(6,'Supplies made to UIN holders')
    ;
    --===== This creates and populates the "controls" for all manner of reports.
    -- I denormalized this one a bit just to make it easier to understand.
    CREATE TABLE dbo.ReportControl
    (
    ReportName VARCHAR(50) NOT NULL
    ,BpGSTType TINYINT NOT NULL
    ,TabOrder TINYINT NOT NULL
    ,CONSTRAINT PK_ReportControl PRIMARY KEY CLUSTERED (ReportName,BpGSTType)
    ,CONSTRAINT AK_ReportControl UNIQUE (ReportName,TabOrder)
    )
    ;
    INSERT INTO dbo.ReportControl WITH (TABLOCK)
    (ReportName,BpGSTType,TabOrder)
    VALUES ('GST_Report_GSTR3B_AR',0,1)
    ,('GST_Report_GSTR3B_AR',3,2)
    ,('GST_Report_GSTR3B_AR',6,3)
    ,('Cat 3,1 Report' ,3,1)
    ,('Cat 3,1 Report' ,1,3)
    ,('Cat 6 Report' ,6,1)
    ;

    Here's some test data... please get into the habit of providing such readily consumable data to help us help you much more quickly and better in the future.

    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    -- Create and populate some test data (we''re simulating 50 State Abbreviations in this).
    -- Get the "fnTally" function from the named link in my signature line below.
    --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    CREATE TABLE dbo.GST_Report_GSTR3B_AR_Detail_Tab
    (
    PoS CHAR(2)
    ,BpGSTType TINYINT
    ,BaseSum NUMERIC(19,2)
    ,IGSTSum NUMERIC(19,2)
    )
    ;
    INSERT INTO dbo.GST_Report_GSTR3B_AR_Detail_Tab WITH (TABLOCK)
    (PoS,BpGSTType,BaseSum,IGSTSum)
    SELECT Pos = CHAR(ABS(CHECKSUM(NEWID())%10)+65) + CHAR(ABS(CHECKSUM(NEWID())%5)+65) --Simulated state abv's.
    ,BpGSTType = ABS(CHECKSUM(NEWID())%7) --includes 0 thru 6 just to show that it works with future data.
    ,BaseSum = RAND(CHECKSUM(NEWID()))*1000
    ,IGSTSum = RAND(CHECKSUM(NEWID()))*10
    FROM dbo.fnTally(1,1000000)
    ;
    GO

     

    Crete a function that works like a programmable view to return whatever report we want by name...

     CREATE FUNCTION dbo.GenReport
    /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    Purpose:
    Given a report name, produce the report according to the settings in the dbo.ReportControl table using descriptions
    for the BpGSTType from the dbo.BpGSTType type table.

    Example Usage:
    SELECT [Nature of Supplies]
    ,[Place of Supply(State/UT)]
    ,[Total Taxable Value]
    ,[Integrated Tax]
    FROM dbo.GenReport('GST_Report_GSTR3B_AR')
    ORDER BY TabOrder, [Place of Supply(State/UT)]
    ;

    Revision History:
    Rev 00 - 14 Jan 2020 - Jeff Moden
    - Initial creation and unit test.
    - REF: https://www.sqlservercentral.com/forums/topic/no-records-found#post-3715055
    @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/
    --===== Declare the I/O for this function.
    (@pReportName VARCHAR(50))
    RETURNS TABLE AS
    RETURN
    --===== Return the report output according to the name of the report that was selected.
    SELECT [Nature of Supplies] = typ.TypeDescription
    ,[Place of Supply(State/UT)] = ISNULL(dtl.PoS, '')
    ,[Total Taxable Value] = CONVERT(NUMERIC(19,2), ISNULL(SUM(dtl.BaseSum), 0))
    ,[Integrated Tax] = CONVERT(NUMERIC(19,2), ISNULL(SUM(dtl.IGSTSum), 0))
    ,ctl.TabOrder
    FROM dbo.GST_Report_GSTR3B_AR_Detail_Tab dtl
    RIGHT JOIN dbo.ReportControl ctl ON dtl.BpGSTType = ctl.BpGSTType
    AND ctl.ReportName = @pReportName
    LEFT JOIN dbo.BpGSTType typ ON typ.BpGSTType = ctl.BpGSTType
    GROUP BY ctl.TabOrder, ISNULL(PoS,''), typ.TypeDescription
    ;
    GO

    Life gets wicked easy after that little bit of extra work.  Here's how to call the report you want (example from the code)...

     

         SELECT  [Nature of Supplies]       
    ,[Place of Supply(State/UT)]
    ,[Total Taxable Value]
    ,[Integrated Tax]
    FROM dbo.GenReport('GST_Report_GSTR3B_AR')
    ORDER BY TabOrder, [Place of Supply(State/UT)]
    ;

    If we want one of the other reports according to what is in the control table, just call it by name...

     SELECT  [Nature of Supplies]       
    ,[Place of Supply(State/UT)]
    ,[Total Taxable Value]
    ,[Integrated Tax]
    FROM dbo.GenReport('Cat 3,1 Report')
    ORDER BY TabOrder, [Place of Supply(State/UT)]
    ;

    You should see what we can do with ROLLUP, CUBE, and GROUPING with this.  I'm out of steam though and going to hit the hay.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply