January 13, 2020 at 4:48 pm
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
January 13, 2020 at 8:04 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 15, 2020 at 4:55 am
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply