Asked by:
SSRS SQL date order off if cross-year

Question
-
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
--set @StartDate = '07/1/2014'
--set @EndDate = '7/30/2014'
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))
Order By CreatedbyName, CreatedOnMonday, March 9, 2015 9:26 PM
All replies
-
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 CreatedOnand the same with CreatedByName. I'm not sure whether this will work but it may be something to try.
Tuesday, March 10, 2015 3:31 PM -
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
Tuesday, March 10, 2015 7:34 PMModerator -
How would I locate these other views in the database? What are they called?Tuesday, March 10, 2015 8:04 PM
-
I don't think that's the whole issue.
All the 2015s will be in front of the 2014s, even though it's ascending order
The main tablix now has "=Format(Fields!CreatedOn.Value, "yyyyMMdd")" as sorting but it didn't help.
Tuesday, March 10, 2015 8:27 PM -
Sort like this
=Datepart("yyyy",Fields!Datum.Value) & Datepart("m",Fields!Datum.Value) & Datepart("d",Fields!Datum.Value)
Also check if createdon field type in all tables is datetime
Regards, Saad
Wednesday, March 11, 2015 7:15 AM -
The issue was malformed XML. This was a report I inherited. The XML was wrong and had three attempted sorts, yet in SSRS there was only one sort.Wednesday, March 11, 2015 3:19 PM