0

Currently when I look at the expiry date section for insurance on my MSReport Builder it is 03/04/2017, but the expiry date section for my insurance in CRM is 04/04/2017 which is the correct and right, however in the report it is 1 day behind, therefore I was wondering why and what might be the fix to this? because I want it to show the same as CRM 04/04/2017, I've been researching and some articles said use UTC THE ONE THAT DONT START WITH 23 hours, not entirely sure how to put this in my query, I’m in the UK, and CRM options are for already set for UK as I checked it already. Again, please advise fix to this?

SELECT 'PAS ' + 

SectionName AS SectionName, 
SectionKey, 
FormName,
ItemName, 
ImportSequenceNumber, 
ExpiryDate, 
ExpiresOn, 
@Param_MonthlyStatement_EntityRecordId AS AccountId

FROM            
(SELECT 
sect.mm_name AS SectionName, 
sect.mm_key AS SectionKey, 
frm.mm_name AS FormName, 
frm.mm_name AS ItemName, 
frm.mm_importsequencenumber AS ImportSequenceNumber, 
MAX(frmans.mm_expires) AS ExpiryDate, 
DATEADD(m, 2, GETDATE()) AS ExpiresOn

FROM Filteredmm_section AS sect INNER JOIN

mm_form AS frm ON sect.mm_sectionid = frm.mm_section INNER JOIN
mm_formanswer AS frmans ON frmans.mm_form = frm.mm_formId INNER JOIN
Account AS acc ON frmans.mm_AccountID = acc.AccountId 
WHERE (sect.mm_name LIKE '%-%') 
AND (sect.mm_parentsection IS NULL) 
AND (CONVERT(NVARCHAR(250), frmans.mm_AccountID) 
= @Param_MonthlyStatement_EntityRecordId)
AND ( acc.mm_supplier = 1)

GROUP BY sect.mm_name, sect.mm_key, frm.mm_name, frm.mm_importsequencenumber
HAVING (MAX(frmans.mm_expires) BETWEEN GETDATE() AND DATEADD(m, 2, GETDATE()))) AS t1
WHERE (NOT EXISTS (SELECT TOP (1) mm_accountid FROM Filteredmm_formanswer
WHERE (mm_formname = t1.FormName) AND (mm_accountid = @Param_MonthlyStatement_EntityRecordId) AND (mm_statusname = 'Awaiting Verification')))
ORDER BY SectionName, FormName, ImportSequenceNumber
Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
  • Can you provide sample data and expected results and the results you are getting? – SS_DBA Jun 19 '17 at 14:18
  • What is the datatype of `frmans.mm_expires`? you may have to convert `GetDate()` to a Date datatype. GetDate() will have a timestamp of the current time the proc is run. This may be causing the date to be 1 day before. – SS_DBA Jun 19 '17 at 14:20
  • @WEI_DBA 'frmans.mm_expires' is form answer expires, how will I convert this to a datatype? –  Jun 19 '17 at 14:24

1 Answers1

0

You might want to get the expiry date from the filtered table, because I can see there is a filtering used, perhaps try 

INNER JOIN Filteredmm_formanswer AS frmans ON frmans.mm_form = frm.mm_formId