We have situations where using SSRS and SQL on premise, we order by after several unions (set up in proper alignment) by 'createdon' which is type 'datetime.'
However a user is complaining that when the date is 'cross-year' in the start date and end date parameters, the order is off. Here is an example of one of the report fetch queries:
----
--declare @StartDate as datetime
--declare @EndDate as datetime
SELECT sc.CreatedOn,sc.CreatedByName, CONVERT(nvarchar(10), sc.xxxbase_SalesCallDate, 101) AS xxxbase_SalesCallDate, sc.xxxbase_accountidName, a.Address1_City,
a.Address1_StateOrProvince, sm.Value AS xxxbase_accounttype, null as xxxbase_productidName, null AS 'Estimated Sales',
sc.xxxbase_Description, NULL AS mileage, 'Sales Call' AS ReasonCode, sc.xxxbase_salescallId
FROM xxxbase_salescall AS sc
left outer JOIN Account AS a ON sc.xxxbase_accountid = a.AccountId
LEFT OUTER JOIN StringMapBase AS sm ON sm.AttributeValue = a.xxxbase_AccountType AND sm.AttributeName = 'xxxbase_accounttype' and sm.ObjectTypeCode = 1
WHERE (sc.xxxbase_SalesCallDate BETWEEN @StartDate AND DATEADD(dd, 1, @EndDate))
UNION ALL
SELECT CreatedOn,CreatedByName, CONVERT(nvarchar(10), xxxbase_weekof, 101) AS date, '' AS Expr1, '' AS Expr2, '' AS Expr3, NULL AS Expr4, '' AS Expr5, '' AS Expr6,
NULL, xxxbase_TotalWorkMiles, 'Mileage' AS Expr8, null
FROM xxxbase_Mileage AS m
WHERE (xxxbase_weekof BETWEEN @StartDate AND DATEADD(dd, 1, @EndDate)) AND (xxxbase_TotalWorkMiles IS NOT NULL)
UNION
SELECT nst.CreatedOn, nst.CreatedByName, convert(nvarchar(10),nst.xxxBase_Date,101) as [date] , null, Null, Null, Null,
'' AS Expr1, '' AS Expr2, nst.xxxBase_Comments, NULL AS Expr3, sm.Value, null
FROM xxxBase_NonSalesTime AS nst
LEFT OUTER JOIN StringMapBase AS sm ON sm.AttributeValue = nst.xxxBase_ReasonCode AND sm.AttributeName = 'xxxbase_reasoncode'
WHERE (nst.xxxBase_Date BETWEEN @StartDate AND DATEADD(dd, 1, @EndDate))
--UNION
--SELECT wpb.CreatedOn, wpb.CreatedByName, convert(nvarchar(10),wpb.xxxBase_Date,101) as [date] , null, Null, Null, Null,
-- '' AS Expr1, '' AS Expr2, NULL, NULL AS Expr3, 'Weekly Planner', null
--from xxxbase_weeklyplanner wpb
--WHERE (wpb.xxxBase_Date BETWEEN @StartDate AND DATEADD(dd, 1, @EndDate))
Nothing looks particularly wrong here but there may be some abiguity with the column names. Instead of including sc.CreatedOn, nst.CreatedOn and CreatedOn, you might want to try unifying these so the 3 statements begin with
SELECT sc.CreatedOn as CreatedOn
SELECT CreatedOn (this one seems fine)
SELECT nst.CreatedOn as CreatedOn
and the same with CreatedByName. I'm not sure whether this will work but it may be something to try.
Your query is referencing the underlying SQL views, rather than the filtered views. This means that you're comparing the dates against dates stored as UTC, rather than the user's time zone, which may be the issue. If you use the corresponding filtered
views, the date fields will be converted based on the user's time zone
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
