May 14, 2018 at 1:27 pm
Hi All,
I have an application where we will be showing the invoice and Plan backup reports for each recipient in the below order :
Recipient 1 :
1. Invoice report hyperlink
2. Plan A - SeleniumOne - 02-03-1998
2.1 Plan A Backup Report 1 hyperlink
2.2 Plan A Backup Report 2 hyperlink
3. Plan B - SeleniumTwo - 02-03-1999
2.1 Plan B Backup Report 1 hyperlink
2.2 Plan B Backup Report 2 hyperlink
Recipient 2 :
1. Invoice report hyperlink
2. Plan A - SeleniumOne - 02-03-1998
2.1 Plan A Backup Report 1 hyperlink
2.2 Plan A Backup Report 2 hyperlink
3. Plan B - SeleniumTwo - 02-03-1999
2.1 Plan B Backup Report 1 hyperlink
2.2 Plan B Backup Report 2 hyperlink
I have written a stored procedure for picking the appropriate data from the db to display in the online screen but the drawback is, it is taking almost 25 seconds to 2 minutes to complete depending upon the number of reports.
My design to execute the stored procedure is like below .
I thought this design is ideal.
1. First get the list of recipients for the invoice.
2. For each recipient get list of plans.
3. For each plan get the list of reports.
If the number of plan is high like 15 or 20 . the stored procedure will get executed multiple times which is causing poor response time.
Is there anyway to fetch the data for a recipient in one shot instead of looping through for each plan.
Please guide me on this. My stored procedure is below for reference.
CREATE PROCEDURE [dbo].[pGetInvoiceDtlsReports] @AccountNumber char(10),
@InvoiceNumber char(7),
@PlanCode char(1),
@PlanNumber int,
@RecipientDesc varchar(30),
@CimEffectiveDate datetime2(3), @SortOrder int,
@CanUploadToHyperLink char(1),
@RequestType varchar(20)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQRReportID int,
@Recipient int,
@MailingGroupNumber int,
@count int
SELECT
@count = 0
SELECT
@Recipient = Recipient
FROM Recipient
WHERE LOWER(RecipientDesc) = LOWER(@RecipientDesc)
SELECT
@MailingGroupNumber = MailingGroupNumber
FROM InvoicePrint
WHERE InvoiceNumber = @InvoiceNumber
AND AccountNumber = @AccountNumber
SELECT DISTINCT
MGA.AddressId,
MGA.SortOrder INTO #GetRecipient_SortOrder
FROM MailingGroupAddress MGA,
MailingGroupReports MGR
WHERE 1 = 2
IF (SUBSTRING(@InvoiceNumber, 1, 2) = 'RB')
BEGIN
INSERT INTO #GetRecipient_SortOrder
SELECT DISTINCT
MGA.AddressId,
MGA.SortOrder
FROM MailingGroupAddress MGA,
MailingGroupReports MGR,
Recipient re
WHERE MGA.AccountNumber = @AccountNumber
AND MGA.MailingGroupNumber = @MailingGroupNumber
AND MGA.AddressId = 2
AND MGA.AccountNumber = MGR.AccountNumber
AND MGA.MailingGroupNumber = MGR.MailingGroupNumber
AND MGA.AddressId = MGR.AddressId
END
ELSE
BEGIN
INSERT INTO #GetRecipient_SortOrder
SELECT DISTINCT
MGA.AddressId,
MGA.SortOrder
FROM MailingGroupAddress MGA,
MailingGroupReports MGR,
Recipient re
WHERE MGA.AccountNumber = @AccountNumber
AND MGA.MailingGroupNumber = @MailingGroupNumber
AND MGA.AddressId <= 4
AND MGA.AccountNumber = MGR.AccountNumber
AND MGA.MailingGroupNumber = MGR.MailingGroupNumber
AND MGA.AddressId = MGR.AddressId
END
CREATE TABLE #FinalResult (
RecipientDesc varchar(30),
SortOrder int,
AccountNumber char(10),
InvoiceNumber char(7),
CIMEffectiveDate datetime2(3),
PlanCode char(1),
PlanNumber int,
SQRReportID int,
SQRReportName varchar(255),
HyperLinkLink varchar(255),
GeneratedDateTime datetime2(3),
StatusID int,
frcReportBreakdownInstanceID numeric(10, 0)
)
IF (@RequestType = 'GetMergeReports')
BEGIN
IF NOT EXISTS (SELECT
1
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29
AND SQRReportID = 306
AND StatusID <> 4003)
BEGIN
SELECT DISTINCT
r.RecipientDesc,
t.frcReportBreakdownInstanceID,
t.SortOrder,
t.Recipient,
t.HyperLinkLink,
t.GeneratedDateTime,
t.StatusID INTO #tempFinalMergedRes
FROM TempfrcReportBreakdown t,
Recipient r,
MailingGroupAddress MGA,
#GetRecipient_SortOrder rs,
SortOrder s
WHERE MGA.AccountNumber = @AccountNumber
AND MGA.MailingGroupNumber = @MailingGroupNumber
AND t.Recipient = r.Recipient
AND rs.SortOrder = t.SortOrder
AND rs.AddressId = t.Recipient
AND SUBSTRING(t.InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND t.SQRReportID = 306
AND t.StatusID = 4003
AND t.HyperLinkLink <> 'Null'
ORDER BY t.Recipient
IF EXISTS (SELECT
1
FROM #GetRecipient_SortOrder gs
WHERE NOT EXISTS (SELECT
*
FROM #tempFinalMergedRes te
WHERE te.Recipient = gs.AddressId))
BEGIN
SELECT
*
FROM #FinalResult
END
ELSE
BEGIN
SELECT
*
FROM #tempFinalMergedRes
END
END
ELSE
BEGIN
SELECT
*
FROM #FinalResult
END
END
IF (@RequestType = 'GetRecipients')
BEGIN
IF EXISTS (SELECT
1
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND StatusID = 4003)
BEGIN
SELECT DISTINCT
re.RecipientDesc,
s.SortDesc,
t.Recipient,
t.SortOrder
FROM TempfrcReportBreakdown t,
frcCalculationsHistory h,
SortOrder s,
Recipient re
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.frcCalculationID = t.frcCalculationID
AND t.Recipient = re.Recipient
AND t.SortOrder = s.SortOrder
AND t.Recipient NOT IN (5, 7)
AND t.CanUploadToHyperLink = 'Y'
ORDER BY t.Recipient
END
ELSE
BEGIN
IF (SUBSTRING(@InvoiceNumber, 1, 2) = 'RB')
BEGIN
SELECT DISTINCT
re.RecipientDesc,
s.SortDesc,
re.Recipient,
rs.SortOrder
FROM #GetRecipient_SortOrder rs,
SortOrder s,
Recipient re
WHERE rs.AddressId = re.Recipient
AND rs.SortOrder = s.SortOrder
END
ELSE
BEGIN
SELECT DISTINCT
re.RecipientDesc,
s.SortDesc,
re.Recipient,
rs.SortOrder
FROM #GetRecipient_SortOrder rs,
SortOrder s,
Recipient re
WHERE rs.AddressId = re.Recipient
AND rs.SortOrder = s.SortOrder
END
END
END
IF (@RequestType = 'GetPlans')
BEGIN
IF EXISTS (SELECT
1
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND StatusID = 4003
AND SQRReportID NOT IN (101, 306))
BEGIN
SELECT DISTINCT
h.PlanCode,
h.PlanNumber,
f.PlanDesc,
h.CIMEffectiveDate,
rc.RecipientDesc,
h.AccountNumber,
h.InvoiceNumber
FROM TempfrcReportBreakdown t,
frcCalculationsHistory h,
FundingPlan f,
Recipient rc
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.frcCalculationID = t.frcCalculationID
AND h.PlanCode = f.PlanCode
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND rc.Recipient = t.Recipient
AND t.SortOrder = @SortOrder
ORDER BY h.CIMEffectiveDate, h.PlanCode, h.PlanNumber
END
ELSE
BEGIN
SELECT DISTINCT
h.PlanCode,
h.PlanNumber,
f.PlanDesc,
h.CIMEffectiveDate,
rc.RecipientDesc,
h.AccountNumber,
h.InvoiceNumber
FROM InvoicePrint h,
FundingPlan f,
Recipient rc,
SortOrder s
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = f.PlanCode
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND s.SortOrder = @SortOrder
ORDER BY h.CIMEffectiveDate, h.PlanCode, h.PlanNumber
END
END
IF (@RequestType = 'GetInvoice')
BEGIN
IF EXISTS (SELECT
1
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND SQRReportID = 101
AND StatusID = 4003)
BEGIN
SELECT DISTINCT
rc.RecipientDesc,
s.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
r.SQRReportID,
r.SQRReportName,
t.HyperLinkLink,
t.GeneratedDateTime,
t.StatusID,
t.frcReportBreakdownInstanceID INTO #Reports_GetInvoice
FROM frcCalculationsHistory h,
TempfrcReportBreakdown t,
WMRSQRReports r,
Recipient rc,
SortOrder s
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.frcCalculationID = t.frcCalculationID
AND r.SQRReportName = 'Invoice Page'
AND t.SQRReportID = r.SQRReportID
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND rc.Recipient = t.Recipient
AND s.SortOrder = @SortOrder
AND t.Recipient NOT IN (5, 7)
ORDER BY t.StatusID DESC
DECLARE ReportId_cursor CURSOR FOR
SELECT
SQRReportID
FROM #Reports_GetInvoice
GROUP BY SQRReportID
HAVING COUNT(*) >= 2
OPEN ReportId_cursor
FETCH ReportId_cursor INTO @SQRReportID
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS (SELECT
StatusID
FROM #Reports_GetInvoice
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011)
BEGIN
IF EXISTS (SELECT
StatusID
FROM #Reports_GetInvoice
WHERE SQRReportID = @SQRReportID
AND StatusID = 4003)
BEGIN
DELETE FROM #Reports_GetInvoice
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011
END
END
FETCH ReportId_cursor INTO @SQRReportID
END
CLOSE ReportId_cursor
DEALLOCATE ReportId_cursor
SELECT
*
FROM #Reports_GetInvoice
END
ELSE
BEGIN
SELECT DISTINCT
rc.RecipientDesc,
s.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
r.SQRReportID,
r.SQRReportName,
NULL HyperLinkLink,
GETDATE() GeneratedDateTime,
4001 StatusID,
NULL frcReportBreakdownInstanceID INTO #Reports_GetInvoice1
FROM InvoicePrint h,
WMRSQRReports r,
Recipient rc,
SortOrder s
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND r.SQRReportName = 'Invoice Page'
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND s.SortOrder = @SortOrder
AND rc.Recipient NOT IN (5, 7)
ORDER BY StatusID DESC
DECLARE ReportId_cursor CURSOR FOR
SELECT
SQRReportID
FROM #Reports_GetInvoice1
GROUP BY SQRReportID
HAVING COUNT(*) >= 2
OPEN ReportId_cursor
FETCH ReportId_cursor INTO @SQRReportID
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS (SELECT
StatusID
FROM #Reports_GetInvoice1
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011)
BEGIN
IF EXISTS (SELECT
StatusID
FROM #Reports_GetInvoice1
WHERE SQRReportID = @SQRReportID
AND StatusID = 4003)
BEGIN
DELETE FROM #Reports_GetInvoice1
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011
END
END
FETCH ReportId_cursor INTO @SQRReportID
END
CLOSE ReportId_cursor
DEALLOCATE ReportId_cursor
SELECT
*
FROM #Reports_GetInvoice1
END
END
IF (@RequestType = 'GetReports')
BEGIN
IF EXISTS (SELECT
1
FROM frcGenerateAddtlReports
WHERE InvoiceNumber = @InvoiceNumber
AND Recipient = @Recipient
AND SortOrder = @SortOrder)
BEGIN -- 11
SELECT DISTINCT
SQRReportID INTO #generate_reports
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 31
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND (StatusID = 4001
OR StatusID = 2003)
AND SQRReportID NOT IN (101, 100, 121, 306)
IF EXISTS (SELECT
1
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND StatusID = 4003
AND SQRReportID NOT IN (101, 306))
BEGIN --1
SELECT
rm.SQRReportID INTO #SQRReportID
FROM frcGenerateAddtlReports cr,
WMRSQRRptRqstMonitor rm
WHERE cr.InvoiceNumber = @InvoiceNumber
AND cr.Recipient = @Recipient
AND cr.SortOrder = @SortOrder
AND cr.SQRReportInstanceID = rm.SQRReportInstanceID
AND CONVERT(datetime, CONVERT(varchar, cr.CreatedDate, 112)) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
IF NOT EXISTS (SELECT
1
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 31
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND StatusID = 4003
AND GeneratedDateTime > CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
AND CanUploadToHyperLink = 'Y'
AND SQRReportID NOT IN (101, 306) AND SQRReportID IN (SELECT
SQRReportID
FROM #SQRReportID))
BEGIN -- 2
SELECT
rc.RecipientDesc,
s.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
r.SQRReportID,
r.SQRReportName,
t.HyperLinkLink,
t.GeneratedDateTime,
t.StatusID,
t.frcReportBreakdownInstanceID INTO #Reports1_AdditionalReports
FROM frcCalculationsHistory h,
TempfrcReportBreakdown t,
WMRSQRReports r,
Recipient rc,
SortOrder s
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = @PlanCode
AND h.PlanNumber = @PlanNumber
AND h.CIMEffectiveDate = @CimEffectiveDate
AND h.frcCalculationID = t.frcCalculationID
AND t.SQRReportID = r.SQRReportID
AND r.SQRReportName NOT IN ('Invoice Page', 'Merged Documents') AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND rc.Recipient = t.Recipient
AND s.SortOrder = @SortOrder
AND s.SortOrder = t.SortOrder
AND CanUploadToHyperLink = @CanUploadToHyperLink
UNION
SELECT
@RecipientDesc RecipientDesc,
@SortOrder SortOrder,
@AccountNumber AccountNumber,
@InvoiceNumber InvoiceNumber,
rm.SQRReportID,
rm.SQRReportName,
NULL HyperLinkLink,
GETDATE() GeneratedDateTime,
4001 StatusID,
NULL frcReportBreakdownInstanceID
FROM frcGenerateAddtlReports cr,
WMRSQRRptRqstMonitor rm
WHERE CONVERT(datetime, CONVERT(varchar, cr.CreatedDate, 112)) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
AND cr.InvoiceNumber = @InvoiceNumber
AND cr.Recipient = @Recipient
AND cr.SortOrder = @SortOrder
AND cr.SQRReportInstanceID = rm.SQRReportInstanceID
AND (cr.IsProcessed = 'N'
OR rm.SQRReportID IN (SELECT
SQRReportID
FROM #generate_reports)
)
DECLARE ReportId_cursor CURSOR FOR
SELECT
SQRReportID
FROM #Reports1_AdditionalReports
GROUP BY SQRReportID
HAVING COUNT(*) >= 2
OPEN ReportId_cursor
FETCH ReportId_cursor INTO @SQRReportID
WHILE (@@FETCH_STATUS = 0)
BEGIN --3
SELECT
@count = 0
IF EXISTS (SELECT
StatusID
FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011)
BEGIN
IF EXISTS (SELECT
StatusID
FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND StatusID = 4003)
BEGIN
DELETE FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011
END
END
IF EXISTS (SELECT
1
FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL)
BEGIN
IF EXISTS (SELECT
1
FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL)
BEGIN
SELECT
@count = COUNT(*)
FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL
SET ROWCOUNT 0
END
SELECT
@count = COUNT(*)
FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL
SET ROWCOUNT 0
END
ELSE
BEGIN
SELECT
@count = COUNT(*)
FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #Reports1_AdditionalReports
WHERE SQRReportID = @SQRReportID
SET ROWCOUNT 0
END
FETCH ReportId_cursor INTO @SQRReportID
END --3
CLOSE ReportId_cursor
DEALLOCATE ReportId_cursor
SELECT
*
FROM #Reports1_AdditionalReports
ORDER BY StatusID DESC, SQRReportID ASC
END --2
ELSE
BEGIN --4
SELECT DISTINCT
rc.RecipientDesc,
s.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
h.CIMEffectiveDate,
h.PlanCode,
h.PlanNumber,
r.SQRReportID,
r.SQRReportName,
t.HyperLinkLink,
t.GeneratedDateTime,
t.StatusID,
t.frcReportBreakdownInstanceID INTO #Reports1_AdditionalReports1
FROM frcCalculationsHistory h,
TempfrcReportBreakdown t,
WMRSQRReports r,
Recipient rc,
SortOrder s
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = @PlanCode
AND h.PlanNumber = @PlanNumber
AND h.CIMEffectiveDate = @CimEffectiveDate
AND h.frcCalculationID = t.frcCalculationID
AND t.SQRReportID = r.SQRReportID
AND r.SQRReportName NOT IN ('Invoice Page', 'Merged Documents') AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND rc.Recipient = t.Recipient
AND s.SortOrder = @SortOrder
AND s.SortOrder = t.SortOrder
AND CanUploadToHyperLink = @CanUploadToHyperLink
UNION
SELECT
@RecipientDesc RecipientDesc,
@SortOrder SortOrder,
@AccountNumber AccountNumber,
@InvoiceNumber InvoiceNumber,
@CimEffectiveDate CIMEffectiveDate,
@PlanCode PlanCode,
@PlanNumber PlanNumber,
rm.SQRReportID,
rm.SQRReportName,
NULL HyperLinkLink,
GETDATE() GeneratedDateTime,
4001 StatusID,
NULL frcReportBreakdownInstanceID
FROM frcGenerateAddtlReports cr,
WMRSQRRptRqstMonitor rm
WHERE CONVERT(datetime, CONVERT(varchar, cr.CreatedDate, 112)) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
AND cr.InvoiceNumber = @InvoiceNumber
AND cr.Recipient = @Recipient
AND cr.SortOrder = @SortOrder
AND cr.SQRReportInstanceID = rm.SQRReportInstanceID
AND (cr.IsProcessed = 'N'
OR rm.SQRReportID IN (SELECT
SQRReportID
FROM #generate_reports)
)
ORDER BY StatusID DESC, SQRReportID ASC
DECLARE ReportId_cursor CURSOR FOR
SELECT
SQRReportID
FROM #Reports1_AdditionalReports1
GROUP BY SQRReportID
HAVING COUNT(*) >= 2
OPEN ReportId_cursor
FETCH ReportId_cursor INTO @SQRReportID
WHILE (@@FETCH_STATUS = 0)
BEGIN --5
SELECT
@count = 0
IF EXISTS (SELECT
StatusID
FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011)
BEGIN
IF EXISTS (SELECT
StatusID
FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND StatusID = 4003)
BEGIN
DELETE FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011
END
END
IF EXISTS (SELECT
1
FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL)
BEGIN
IF EXISTS (SELECT
1
FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL)
BEGIN
SELECT
@count = COUNT(*)
FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL
SET ROWCOUNT 0
END
SELECT
@count = COUNT(*)
FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL
SET ROWCOUNT 0
END
ELSE
BEGIN
SELECT
@count = COUNT(*)
FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #Reports1_AdditionalReports1
WHERE SQRReportID = @SQRReportID
SET ROWCOUNT 0
END
FETCH ReportId_cursor INTO @SQRReportID
END --5
CLOSE ReportId_cursor
DEALLOCATE ReportId_cursor
SELECT
*
FROM #Reports1_AdditionalReports1
ORDER BY StatusID DESC, SQRReportID ASC
END --4
END --1
/*This Else Part is for Natural Release and Generate addtional Report is Present*/ ELSE
BEGIN --9
IF NOT EXISTS (SELECT
1
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 31
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND StatusID = 4003
AND SQRReportID NOT IN (100, 121, 306) AND CanUploadToHyperLink = 'Y')
BEGIN --99
SELECT
rc.RecipientDesc,
a.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
h.CIMEffectiveDate,
h.PlanCode,
h.PlanNumber,
cm.SQRReportID,
cm.SQRReportName,
NULL HyperLinkLink,
GETDATE() GeneratedDateTime,
4001 StatusID,
NULL frcReportBreakdownInstanceID
FROM InvoicePrint h,
MailingGroupAddress a,
MailingGroupReports b,
Recipient rc,
frcReportsMapping cm
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = @PlanCode
AND h.PlanNumber = @PlanNumber
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND h.CIMEffectiveDate = @CimEffectiveDate
AND b.AccountNumber = @AccountNumber
AND b.AccountNumber = a.AccountNumber
AND cm.ReportId = b.ReportId
AND b.MailingGroupNumber = @MailingGroupNumber
AND b.MailingGroupNumber = a.MailingGroupNumber
AND b.AddressId = a.AddressId
AND a.SortOrder = @SortOrder
UNION
SELECT
@RecipientDesc RecipientDesc,
@SortOrder SortOrder,
@AccountNumber AccountNumber,
@InvoiceNumber InvoiceNumber,
@CimEffectiveDate CIMEffectiveDate,
@PlanCode PlanCode,
@PlanNumber PlanNumber,
rm.SQRReportID,
rm.SQRReportName,
NULL HyperLinkLink,
GETDATE() GeneratedDateTime,
4001 StatusID,
NULL frcReportBreakdownInstanceID
FROM frcGenerateAddtlReports cr,
WMRSQRRptRqstMonitor rm
WHERE CONVERT(datetime, CONVERT(varchar, cr.CreatedDate, 112)) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
AND cr.InvoiceNumber = @InvoiceNumber
AND cr.Recipient = @Recipient
AND cr.SortOrder = @SortOrder
AND cr.SQRReportInstanceID = rm.SQRReportInstanceID
AND (cr.IsProcessed = 'N'
OR rm.SQRReportID IN (SELECT
SQRReportID
FROM #generate_reports)
)
ORDER BY StatusID DESC, SQRReportID ASC
END --99
ELSE
BEGIN --999
SELECT
rc.RecipientDesc,
s.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
r.SQRReportID,
r.SQRReportName,
t.HyperLinkLink,
t.GeneratedDateTime,
t.StatusID,
t.frcReportBreakdownInstanceID INTO #temp
FROM frcCalculationsHistory h,
TempfrcReportBreakdown t,
WMRSQRReports r,
Recipient rc,
SortOrder s
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = @PlanCode
AND h.PlanNumber = @PlanNumber
AND h.CIMEffectiveDate = @CimEffectiveDate
AND h.frcCalculationID = t.frcCalculationID
AND t.SQRReportID = r.SQRReportID
AND r.SQRReportName NOT IN ('Invoice Page', 'Merged Documents') AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND rc.Recipient = t.Recipient
AND s.SortOrder = @SortOrder
AND s.SortOrder = t.SortOrder
AND CanUploadToHyperLink = @CanUploadToHyperLink
UNION
SELECT
rc.RecipientDesc,
a.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
cm.SQRReportID,
cm.SQRReportName,
NULL HyperLinkLink,
GETDATE() GeneratedDateTime,
4001 StatusID,
NULL frcReportBreakdownInstanceID
FROM InvoicePrint h,
MailingGroupAddress a,
MailingGroupReports b,
Recipient rc,
frcReportsMapping cm
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = @PlanCode
AND h.PlanNumber = @PlanNumber
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND h.CIMEffectiveDate = @CimEffectiveDate
AND b.AccountNumber = @AccountNumber
AND b.AccountNumber = a.AccountNumber
AND cm.ReportId = b.ReportId
AND b.MailingGroupNumber = @MailingGroupNumber
AND b.MailingGroupNumber = a.MailingGroupNumber
AND b.AddressId = a.AddressId
AND a.SortOrder = @SortOrder
ORDER BY StatusID DESC, SQRReportID ASC
DECLARE Natural_Generate_Reports CURSOR FOR
SELECT
SQRReportID
FROM #temp
GROUP BY SQRReportID
HAVING COUNT(*) >= 2
OPEN Natural_Generate_Reports
FETCH Natural_Generate_Reports INTO @SQRReportID
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS (SELECT
1
FROM #temp
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL)
BEGIN
IF EXISTS (SELECT
1
FROM #temp
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL)
BEGIN
SELECT
@count = COUNT(*)
FROM #temp
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #temp
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NOT NULL
SET ROWCOUNT 0
END
SELECT
@count = COUNT(*)
FROM #temp
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #temp
WHERE SQRReportID = @SQRReportID
AND frcReportBreakdownInstanceID IS NULL
SET ROWCOUNT 0
END
ELSE
BEGIN
SELECT
@count = COUNT(*)
FROM #temp
WHERE SQRReportID = @SQRReportID
SELECT
@count = @count - 1
SET ROWCOUNT @count
DELETE FROM #temp
WHERE SQRReportID = @SQRReportID
SET ROWCOUNT 0
END
FETCH Natural_Generate_Reports INTO @SQRReportID
END
CLOSE Natural_Generate_Reports
DEALLOCATE Natural_Generate_Reports
SELECT
*
FROM #temp
ORDER BY StatusID DESC, SQRReportID ASC
END --999
END --9
END --11
/*This Else Part is for Natural Release and no Generate addtional Report is Present*/ ELSE
BEGIN --6
DECLARE recipient_sqrreport CURSOR FOR
SELECT DISTINCT
cm.SQRReportID
FROM MailingGroupAddress a,
MailingGroupReports b,
frcReportsMapping cm
WHERE b.AccountNumber = @AccountNumber
AND b.MailingGroupNumber = @MailingGroupNumber
AND b.AddressId = @Recipient
AND b.ReportId = cm.ReportId
AND b.AccountNumber = a.AccountNumber
AND b.MailingGroupNumber = a.MailingGroupNumber
AND b.AddressId = a.AddressId
OPEN recipient_sqrreport
FETCH recipient_sqrreport INTO @SQRReportID
WHILE (@@FETCH_STATUS = 0)
BEGIN --2
IF EXISTS (SELECT DISTINCT
SQRReportID,
StatusID
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND StatusID = 4003
AND SQRReportID = @SQRReportID)
BEGIN
INSERT INTO #FinalResult
SELECT DISTINCT
rc.RecipientDesc,
s.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
h.CIMEffectiveDate,
h.PlanCode,
h.PlanNumber,
r.SQRReportID,
r.SQRReportName,
t.HyperLinkLink,
t.GeneratedDateTime,
t.StatusID,
t.frcReportBreakdownInstanceID
FROM frcCalculationsHistory h,
TempfrcReportBreakdown t,
WMRSQRReports r,
Recipient rc,
SortOrder s
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = @PlanCode
AND h.PlanNumber = @PlanNumber
AND h.CIMEffectiveDate = @CimEffectiveDate
AND h.frcCalculationID = t.frcCalculationID
AND t.SQRReportID = r.SQRReportID
AND r.SQRReportName NOT IN ('Invoice Page', 'Merged Documents') AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND rc.Recipient = t.Recipient
AND s.SortOrder = @SortOrder
AND s.SortOrder = t.SortOrder
AND CanUploadToHyperLink = @CanUploadToHyperLink
AND r.SQRReportID = @SQRReportID
END
ELSE
IF ((SELECT
DATEDIFF(mi, MAX(GeneratedDateTime), GETDATE())
FROM TempfrcReportBreakdown
WHERE SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29
AND Recipient = @Recipient
AND SortOrder = @SortOrder
AND StatusID = 4003
AND SQRReportID NOT IN (101, 100, 121, 306))
> 15)
BEGIN
INSERT INTO #FinalResult
SELECT DISTINCT
rc.RecipientDesc,
s.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
h.CIMEffectiveDate,
h.PlanCode,
h.PlanNumber,
r.SQRReportID,
r.SQRReportName,
t.HyperLinkLink,
t.GeneratedDateTime,
t.StatusID,
t.frcReportBreakdownInstanceID
FROM frcCalculationsHistory h,
TempfrcReportBreakdown t,
WMRSQRReports r,
Recipient rc,
SortOrder s
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = @PlanCode
AND h.PlanNumber = @PlanNumber
AND h.CIMEffectiveDate = @CimEffectiveDate
AND h.frcCalculationID = t.frcCalculationID
AND t.SQRReportID = r.SQRReportID
AND r.SQRReportName NOT IN ('Invoice Page', 'Merged Documents') AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND rc.Recipient = t.Recipient
AND s.SortOrder = @SortOrder
AND s.SortOrder = t.SortOrder
AND CanUploadToHyperLink = @CanUploadToHyperLink
AND t.SQRReportID = @SQRReportID
END
ELSE
BEGIN
INSERT INTO #FinalResult
SELECT DISTINCT
rc.RecipientDesc,
a.SortOrder,
h.AccountNumber,
h.InvoiceNumber,
h.CIMEffectiveDate,
h.PlanCode,
h.PlanNumber,
cm.SQRReportID,
cm.SQRReportName,
NULL HyperLinkLink,
NULL GeneratedDateTime,
4001 StatusID,
NULL frcReportBreakdownInstanceID
FROM InvoicePrint h,
MailingGroupAddress a,
MailingGroupReports b,
Recipient rc,
frcReportsMapping cm
WHERE h.AccountNumber = @AccountNumber
AND h.InvoiceNumber = @InvoiceNumber
AND h.PlanCode = @PlanCode
AND h.PlanNumber = @PlanNumber
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
AND h.CIMEffectiveDate = @CimEffectiveDate
AND b.AccountNumber = @AccountNumber
AND b.AccountNumber = a.AccountNumber
AND cm.ReportId = b.ReportId
AND b.MailingGroupNumber = @MailingGroupNumber
AND b.MailingGroupNumber = a.MailingGroupNumber
AND b.AddressId = a.AddressId
AND a.SortOrder = @SortOrder
AND cm.SQRReportID = @SQRReportID
ORDER BY StatusID DESC, cm.SQRReportID ASC
END
FETCH recipient_sqrreport INTO @SQRReportID
END --2
CLOSE recipient_sqrreport
DEALLOCATE recipient_sqrreport
SELECT DISTINCT
*
FROM #FinalResult
ORDER BY StatusID DESC, SQRReportID ASC
END --6
END
SET NOCOUNT OFF
END
May 14, 2018 at 1:59 pm
If most of your code looks like this, I suggest that you hire a consultant that can not only improve the code but that will teach you how to fix it and prevent getting more trouble in the future.
I'm including an example on how you can remove cursors based on a piece of your code.
Instead of writing this:
DECLARE ReportId_cursor CURSOR FOR
SELECT SQRReportID
FROM #Reports_GetInvoice
GROUP BY SQRReportID
HAVING COUNT(*) >= 2;
OPEN ReportId_cursor;
FETCH ReportId_cursor
INTO @SQRReportID;
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS
(
SELECT StatusID
FROM #Reports_GetInvoice
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011
)
BEGIN
IF EXISTS
(
SELECT StatusID
FROM #Reports_GetInvoice
WHERE SQRReportID = @SQRReportID
AND StatusID = 4003
)
BEGIN
DELETE FROM #Reports_GetInvoice
WHERE SQRReportID = @SQRReportID
AND StatusID >= 4011;
END;
END;
FETCH ReportId_cursor
INTO @SQRReportID;
END;
CLOSE ReportId_cursor;
DEALLOCATE ReportId_cursor;
SELECT *
FROM #Reports_GetInvoice;
You can simply write as a single DELETE
DELETE r
FROM #Reports_GetInvoice r
WHERE StatusID >= 4011
AND EXISTS(
SELECT StatusID
FROM #Reports_GetInvoice x
WHERE x.SQRReportID = r.SQRReportID
AND StatusID = 4003
);
You should also consider changing the JOINs to the current syntax. Instead of this:
SELECT DISTINCT
MGA.AddressId,
MGA.SortOrder
FROM MailingGroupAddress AS MGA,
MailingGroupReports AS MGR,
Recipient AS re
WHERE MGA.AccountNumber = @AccountNumber
AND MGA.MailingGroupNumber = @MailingGroupNumber
AND MGA.AddressId = 2
AND MGA.AccountNumber = MGR.AccountNumber
AND MGA.MailingGroupNumber = MGR.MailingGroupNumber
AND MGA.AddressId = MGR.AddressId;
You can write it like this:
SELECT DISTINCT
MGA.AddressId,
MGA.SortOrder
FROM MailingGroupAddress AS MGA
JOIN MailingGroupReports AS MGR ON MGA.AccountNumber = MGR.AccountNumber
AND MGA.MailingGroupNumber = MGR.MailingGroupNumber
AND MGA.AddressId = MGR.AddressId
--JOIN Recipient AS re /*This is never referenced on the query and it's only generating additional rows*/
WHERE MGA.AccountNumber = @AccountNumber
AND MGA.MailingGroupNumber = @MailingGroupNumber
AND MGA.AddressId = 2;
May 14, 2018 at 2:04 pm
Ok, first things first, those implicit joins need to go. It's 2018, you've no excuse for using 30 year old syntax: Bad habits to kick : using old-style JOINs
There a lot going on in that query, and very little formatting. You'll find that people will be much more willing to help you if you make your SQL more readable. Having the majority of your SQL left aligned doesn't make it easy to read. You should really, for example, be indenting between a BEGIN and END, so that others can easily tell where one begins. Take these simple examples, and you'll easily see which is more readable:
And now, for a formatted version (and using JOIN syntax):
Notice, for starters, how easy it is to see where the first IF ends, and the ELSE begins, and ends?
There's a hell of a lot of SQL in your post here (1102 lines), so you need to do something about that first. You can't expect anyone here (who are all volunteers), to attempt to answer your question unless you put some effort in first to make your SQL more "welcoming".
Once you've done that, reply with your formatted code, and you'll likely find a lot of the users will be more than happy to help you, and likely find a much more efficient data set approach.
(Grrr, i forgot SSC likes to kill Whitespace.)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 14, 2018 at 2:31 pm
Hi Luis,
Thanks very much for the advise !!!
I will modify the cursors and joins as per your suggestion and Thom's.
Hi Thom,
I am trying to format the Stored procedure for better reading but even if i leave space in the beginning the website removes all the white space and makes it left aligned.
Can i attach the formatted stored procedure as a file . Please advise.
May 14, 2018 at 3:04 pm
Jackson89 - Monday, May 14, 2018 2:31 PMHi Thom,
I am trying to format the Stored procedure for better reading but even if i leave space in the beginning the website removes all the white space and makes it left aligned.
Can i attach the formatted stored procedure as a file . Please advise.
SSC, unfortunately, has a habit of binning whitespace, but if you have at least some that's better. For some reason SSC changes 2 spaces into 1; it's been a bug bare of mine since they changed forum software and has been an outstanding ever since. 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 14, 2018 at 3:57 pm
Hi Thom,
Thanks for the info !!!
You mean even if i attach it as a file , if you open the file in your machine, will it still be left aligned ?
May 15, 2018 at 5:12 am
All these calculations on columns
SUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29
and
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)
and
AND CONVERT(datetime, CONVERT(varchar, cr.CreatedDate, 112)) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
Are all going to lead to the inability of the optimizer to use statistics or indexes to assist in the performance of the query. You need to eliminate them. Store the right values in the right data types. If it's a date or datetime, make the column a date or datetime. Compare the strings as is, or format them on entry, not in your T-SQL code. This is one of the single most common anti-patterns when it comes to T-SQL and bad performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2018 at 10:52 am
For testing a datetime/datetime2 column for the current day, you should do this:
--good way
cr.CreatedDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
cr.CreatedDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
and not this:
--bad way
AND CONVERT(datetime, CONVERT(varchar, cr.CreatedDate, 112)) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 15, 2018 at 11:28 am
ScottPletcher - Tuesday, May 15, 2018 10:52 AMFor testing a datetime/datetime2 column for the current day, you should do this:--good way
cr.CreatedDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
cr.CreatedDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)and not this:
--bad way
AND CONVERT(datetime, CONVERT(varchar, cr.CreatedDate, 112)) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
There seems to be a problem with estimations when doing those calculations in the query, so I'd rather keep them separate. Let me try to recreate what Itzik Ben-Gan did.
May 15, 2018 at 11:29 am
Grant Fritchey - Tuesday, May 15, 2018 5:12 AMAll these calculations on columnsSUBSTRING(InvoiceNumberMapping, 2, 7) = @InvoiceNumber
AND LEN(InvoiceNumberMapping) = 29and
AND LOWER(rc.RecipientDesc) = LOWER(@RecipientDesc)and
AND CONVERT(datetime, CONVERT(varchar, cr.CreatedDate, 112)) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 112))
Are all going to lead to the inability of the optimizer to use statistics or indexes to assist in the performance of the query. You need to eliminate them. Store the right values in the right data types. If it's a date or datetime, make the column a date or datetime. Compare the strings as is, or format them on entry, not in your T-SQL code. This is one of the single most common anti-patterns when it comes to T-SQL and bad performance.
Further to this, do you need to use LOWER? I assume you're using it bceause you're in a Case Sensitive collation, so 'Hello' = 'hello' would evaluation to false. If, however, you're in a case insensitive collation, then there's no need to use LOWER, as 'HELLO WORLD' = 'hello world' would evaluate to true.
Completely agree on the dates, as Grant has pointed out. If Created Date has a time, as well as a date then use the > and < operators instead. For example:AND cr.CreatedDate >= CONVERT(date, GETDATE()) AND cr.CreatedDate < CONVERT(date, DATEADD(DAY 1, GETDATE()))
The above would be SARGable, as the manipulation is on the GETDATE() function, not your column cr.CreatedDate.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 16, 2018 at 3:11 pm
Thanks Luis, Scott, Thom, Grant for all your Suggestions !!!
I will follow all your suggestions . I think, instead of making changes to this stored procedure which is having many mistakes , planning to remove this sp all together and start fresh with new one.
Please let me know if that is not the correct approach. I will post the new query here shortly. Thanks !!!
May 18, 2018 at 10:46 pm
Hi All,
I am trying to re-write the query with all your suggestions. I think am almost there, only last join condition is not working. Below is what im trying to retrieve.
Table 1 : InvoicePrint (This table has the invoice number and plan details).
Table 2 : MailingGroupReports (This table has the individual report needs to be generated by system for each plan )
Table 3 : BillReports (This table has the report description like Name of the report)
Table 4: MRWSQRReports (This table also has the report description like Name of the report)
Table 5: CRFCalculationsHistory (This table has unique calculation ID for each plan and effective date combination)
Table 6 : TempCRFReportBreakdown (This is the final table where the generated reports hyperlink gets saved)
What im trying to achieve is say for example I have an invoice which has 5 different plan and effective date combinations
For each plan, five different reports has to be generated.
My final result set should have 25 records by joining the above 5 tables with final tempcrfreportbreakdown.
If no reports are generated, then obviously there will be no entries present in tempcrfreport break down.
In that case, the result set should still have 25 rows with Null values for tempcrfreportbreakdown columns. So that the application will use the Null value and display the message like
reports not generated.
Below is the query which i formed. It was returning exactly 25 rows before joining TempCRFReportBreakdown table. But after joining that table, the records im getting is totally messed up.
It was not related to the invoice which im querying and also it is returning 64 records. I am not sure what is wrong. Please guide me in correcting the query. Thanks !!!
select DISTINCT T.CRFCalculationID,I.InvoiceNumber,I.PlanCode,I.PlanNumber,I.CIMEffectiveDate,I.PlanEffectiveDate,B.ReportDesc,R.SQRReportID,T.FilenetLink
from InvoicePrint I
join MailingGroupReports M
on M.AccountNumber=I.AccountNumber
join BillReports B
on B.ReportId=M.ReportId
JOIN MRWSQRReports R
ON B.ReportDesc=R.SQRReportName
JOIN CRFCalculationsHistory C
ON C.AccountNumber=I.AccountNumber
AND C.PlanCode=I.PlanCode
AND I.PlanNumber=C.PlanNumber
AND I.CIMEffectiveDate=C.CIMEffectiveDate
LEFT JOIN TempCRFReportBreakdown T
ON T.CRFCalculationID=C.CRFCalculationID
AND T.SQRReportID=R.SQRReportID
WHERE I.InvoiceNumber='F22616G'
AND M.MailingGroupNumber=1
AND M.AddressId=2
AND R.SQRReportID <> 215
ORDER BY I.PlanCode,I.PlanNumber,I.CIMEffectiveDate ASC
May 22, 2018 at 10:32 am
If once you do that LEFT JOIN, you get more records than you started with, then you must have multiple rows in the LEFT JOINed table that match any given pair of values that meet the JOIN condition. Figure out why that happens, and you'll know where the problem is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 30, 2018 at 5:53 am
I do a lot of DB redesigns, reverse engineering and process reengineering. I'm amazed at how many databases in enterprise level environments are created by someone who just woke up one day and said "I want to be a DBA today". Most of the time I find that scrapping a procedure like that and starting over is your best bet.
What I usually do is start by analyzing the stored procedure and pulling out all the "business rules" that are embedded. In cases of very large stored procedures or a complex process, documentation may be required. IE: write a technical design document ... Write down, what the thing is supposed to do and what the requirements are and then go through the procedure and document what needs to happen. Ignore the bad code and how it should or shouldn't be written and just pull out the logic.
If the client/customer is sure that the process is producing accurate results, then you can have something to compare the results to when you rewrite the procedure. More often then not though, I find errors in the code logic and have to point it out to the customer to show them hey this calculation is not right. 🙂
When you rewrite it, look for opportunities to simplify logic. My motto in coding is "just because you can doesn't mean you should". I find a lot of DBAs will try to cram as much as humanly possible into one query... cause hey, its cool to do it all at once right?!? While it is possible to do stuff like that, its not necessarily efficient and murder to troubleshoot. I have legacy code I have to maintain now that if something needs to be added to it or if something turns up broken it takes me like 2 days just to figure out how to troubleshoot the damn thing cause the goof head that wrote it decided to nest 500 queries.
So, do things in steps... I try to consider the people that will come after me that have to read my code cause I know how much I hate fixing awfully written code. Also, try to find ways to use views or create custom functions instead of making the stored procedure overly complicated. I also find a lot of procedures will try to manipulate one value at a time or have tons of IF statements to do tiny little things. Most situations can be handled in tables/views or in "batches" ...even if they are temporary tables.
Anyhoo sorry if I'm off base but I read your post and just felt inspired to provide some of my experience. The key to rewriting your procedure is just to really understand the data and what the procedure is supposed to be doing. In most situations I find its way easier to rewrite something fresh than trying to bandaid a procedure or fix pieces of it.
May 30, 2018 at 6:11 am
amy26 - Wednesday, May 30, 2018 5:53 AMI do a lot of DB redesigns, reverse engineering and process reengineering. I'm amazed at how many databases in enterprise level environments are created by someone who just woke up one day and said "I want to be a DBA today". Most of the time I find that scrapping a procedure like that and starting over is your best bet.
What I usually do is start by analyzing the stored procedure and pulling out all the "business rules" that are embedded. In cases of very large stored procedures or a complex process, documentation may be required. IE: write a technical design document ... Write down, what the thing is supposed to do and what the requirements are and then go through the procedure and document what needs to happen. Ignore the bad code and how it should or shouldn't be written and just pull out the logic.
If the client/customer is sure that the process is producing accurate results, then you can have something to compare the results to when you rewrite the procedure. More often then not though, I find errors in the code logic and have to point it out to the customer to show them hey this calculation is not right. 🙂
When you rewrite it, look for opportunities to simplify logic. My motto in coding is "just because you can doesn't mean you should". I find a lot of DBAs will try to cram as much as humanly possible into one query... cause hey, its cool to do it all at once right?!? While it is possible to do stuff like that, its not necessarily efficient and murder to troubleshoot. I have legacy code I have to maintain now that if something needs to be added to it or if something turns up broken it takes me like 2 days just to figure out how to troubleshoot the damn thing cause the goof head that wrote it decided to nest 500 queries.
So, do things in steps... I try to consider the people that will come after me that have to read my code cause I know how much I hate fixing awfully written code. Also, try to find ways to use views or create custom functions instead of making the stored procedure overly complicated. I also find a lot of procedures will try to manipulate one value at a time or have tons of IF statements to do tiny little things. Most situations can be handled in tables/views or in "batches" ...even if they are temporary tables.
Anyhoo sorry if I'm off base but I read your post and just felt inspired to provide some of my experience. The key to rewriting your procedure is just to really understand the data and what the procedure is supposed to be doing. In most situations I find its way easier to rewrite something fresh than trying to bandaid a procedure or fix pieces of it.
I think that you're completely on point. I've been through that process myself (and sometimes people don't know what the process should do so the original procedure is the only source of truth). It also reminded me something that I learned in these forums and it's one of my basic principles when coding:
“Make it work, make it fast, then make it pretty… and it isn’t finished until it is pretty!â€
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply