January 17, 2019 at 12:59 pm
All, I've got a query as an embedded data set in a report on SSRS2012. When I run the SQL in SQL Server I get x number of records. When I run the report against the same server and export the records to Excel or CSV, the record count is different.
Here's the code:
DECLARE @StartDate DATE = '11/01/2018'
DECLARE @Enddate DATE = '11/30/2018'
SELECT distinct
pat.PatientFirstName,
pat.PatientLastName,
clm.Patient_UniqueID,
prov.ProviderFullName,
clm.DN_PlaceOfServiceCode,
clm.Specialty_UniqueID,
hp.LineOfBusinessShortName,
hp.HealthPlanShortName,
loc.LocationShortName,
clm.dateofservice,
clm.DateReceived,
pat.PatientStreet1,
pat.PatientStreet2,
pat.PatientCity,
pat.PatientState,
pat.PatientZip,
pat.PatientPhone
from ODS_CLM_CLAIM as clm (readuncommitted)
inner join ODS_PAT_PATIENT as pat (readuncommitted) on pat.Patient_UniqueID = clm.Patient_UniqueID and pat.ods_row_current = 1
inner join ODS_PRV_PROVIDER as prov (readuncommitted) on prov.Provider_UniqueID = clm.RenderingProvider_UniqueID and prov.ods_row_current = 1
inner join ODS_LKP_HEALTHPLAN as hp (readuncommitted) on clm.HealthPlan_UniqueID = hp.HealthPlan_UniqueID and hp.ods_row_current = 1
inner join ODS_LKP_LOCATION as loc (readuncommitted) on clm.Location_UniqueID = loc.Location_UniqueID and loc.ods_row_current = 1
Where clm.DateReceived between @StartDate and @EndDate
and clm.ods_row_current = 1
and patientstate not in ('CA','TX')
The SQL Server gives me 8070 records While the SSRS report export gives me 8063 records. The only difference is the Embedded SQL is missing the declare statements as the variables are parameters in the report.
Is there a bug in SSRS that would cause this discrepancy?
January 17, 2019 at 1:20 pm
craig.bobchin - Thursday, January 17, 2019 12:59 PMAll, I've got a query as an embedded data set in a report on SSRS2012. When I run the SQL in SQL Server I get x number of records. When I run the report against the same server and export the records to Excel or CSV, the record count is different.Here's the code:
DECLARE @StartDate DATE = '11/01/2018'
DECLARE @Enddate DATE = '11/30/2018'SELECT distinct
pat.PatientFirstName,
pat.PatientLastName,
clm.Patient_UniqueID,
prov.ProviderFullName,
clm.DN_PlaceOfServiceCode,
clm.Specialty_UniqueID,
hp.LineOfBusinessShortName,
hp.HealthPlanShortName,
loc.LocationShortName,
clm.dateofservice,
clm.DateReceived,
pat.PatientStreet1,
pat.PatientStreet2,
pat.PatientCity,
pat.PatientState,
pat.PatientZip,
pat.PatientPhonefrom ODS_CLM_CLAIM as clm (readuncommitted)
inner join ODS_PAT_PATIENT as pat (readuncommitted) on pat.Patient_UniqueID = clm.Patient_UniqueID and pat.ods_row_current = 1
inner join ODS_PRV_PROVIDER as prov (readuncommitted) on prov.Provider_UniqueID = clm.RenderingProvider_UniqueID and prov.ods_row_current = 1
inner join ODS_LKP_HEALTHPLAN as hp (readuncommitted) on clm.HealthPlan_UniqueID = hp.HealthPlan_UniqueID and hp.ods_row_current = 1
inner join ODS_LKP_LOCATION as loc (readuncommitted) on clm.Location_UniqueID = loc.Location_UniqueID and loc.ods_row_current = 1Where clm.DateReceived between @StartDate and @EndDate
and clm.ods_row_current = 1
and patientstate not in ('CA','TX')The SQL Server gives me 8070 records While the SSRS report export gives me 8063 records. The only difference is the Embedded SQL is missing the declare statements as the variables are parameters in the report.
Is there a bug in SSRS that would cause this discrepancy?
If the DateReceived column is a datetime data type, and the data contains a time portion, then you will be cutting off the last day of values.
In this example, the end date is 11/30/2018. If there a rows that contain 11-30-2018 11;43:000, then that will not be included in the results.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 17, 2019 at 3:18 pm
Michael L John - Thursday, January 17, 2019 1:20 PMIf the DateReceived column is a datetime data type, and the data contains a time portion, then you will be cutting off the last day of values.
In this example, the end date is 11/30/2018. If there a rows that contain 11-30-2018 11;43:000, then that will not be included in the results.
I think that's the answer. But what's the best way of fixing it? Setting the parameters to include the time? I tried reformatting the two date columns to include the time, but still did not get all the records.
January 17, 2019 at 3:23 pm
craig.bobchin - Thursday, January 17, 2019 3:18 PMI think that's the answer. But what's the best way of fixing it? Setting the parameters to include the time? I tried reformatting the two date columns to include the time, but still did not get all the records.
If you are looking for all the data in a given month then you should use a semi-open interval for your date criteria. That means a closed end on the lower boundery and an open end on the upper end, like this:
declare @StartDate date = '2018-11-01',
@EndDate date = '2018-12-01';
...
Where
clm.DateReceived >= @StartDate and
clm.DateReceived < @EndDate
January 17, 2019 at 6:30 pm
Lynn Pettis - Thursday, January 17, 2019 3:23 PMcraig.bobchin - Thursday, January 17, 2019 3:18 PMI think that's the answer. But what's the best way of fixing it? Setting the parameters to include the time? I tried reformatting the two date columns to include the time, but still did not get all the records.If you are looking for all the data in a given month then you should use a semi-open interval for your date criteria. That means a closed end on the lower boundery and an open end on the upper end, like this:
declare @StartDate date = '2018-11-01',
@EndDate date = '2018-12-01';...
Where
clm.DateReceived >= @StartDate and
clm.DateReceived < @EndDate
Nope that didn't work, Still getting the same results
January 18, 2019 at 7:17 am
Can you identify the rows that are different? I'm assuming that you are querying the same database that the report is running against.
Does the report have any grouping in it?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 4, 2019 at 11:27 am
All, I'm finally getting back to this. I solved the issue. Someone changed the data source I was using for the report to point to another server without informing any of us developers.
Thanks for all your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply