September 22, 2015 at 5:11 am
In SQL, i'm getting this output:
12 amNULL
1 amNULL
2 amNULL
3 amNULL
4 amNULL
5 amNULL
6 amNULL
7 amNULL
8 amInduction
9 amInduction
10 amInduction
11 amInduction
12 pmInduction
1 pmInduction
2 pmInduction
3 pmInduction
4 pmInduction
5 pmInduction
6 pmNULL
7 pmNULL
8 pmNULL
9 pmNULL
10 pmNULL
11 pmNULL
The above is correct. I have no sorting on the Query.
However, when I put the query in a Reporting Services report and run it, I'm getting this
12 am
1 am
2 am
3 am
4 am
5 am
6 am
7 am
6 pm
7 pm
8 pm
9 pm
10 pm
11 pm
8 am induction
9 am induction
10 am induction
11 am induction
12 pm induction
1 pm induction
2 pm induction
3 pm induction
4 pm induction
5 pm induction
I want the report to be sorted (or rather the same as the SQL query), so that the report show 12am and ends at 11pm
Any ideas folks?
September 22, 2015 at 5:17 am
you must have an explicit ORDER BY in your query to guarantee the data appears in a specific order. you stated you have no order by, so that is the rock solid fix to the problem....add an explicit order by.
SQL is tasked with getting the data in the fastest way it can, and the plan to do that can change due to a huge variety of factors....different ANSI settings, connection settings,indexing, statistics, etc.
getting the same results, in the same order is just coincidence, and could change. you need to get an ORDER BY in there.
Lowell
September 22, 2015 at 5:20 am
This the SQL (that J Livingston helped me with over on the SQL forum)
with roomhours (hour_number ,hour_description) as
(
SELECT 0, '12 am' UNION ALL
SELECT 1, '1 am' UNION ALL
SELECT 2, '2 am' UNION ALL
SELECT 3, '3 am' UNION ALL
SELECT 4, '4 am' UNION ALL
SELECT 5, '5 am' UNION ALL
SELECT 6, '6 am' UNION ALL
SELECT 7, '7 am' UNION ALL
SELECT 8, '8 am' UNION ALL
SELECT 9, '9 am' UNION ALL
SELECT 10, '10 am' UNION ALL
SELECT 11, '11 am' UNION ALL
SELECT 12, '12 pm' UNION ALL
SELECT 13, '1 pm' UNION ALL
SELECT 14, '2 pm' UNION ALL
SELECT 15, '3 pm' UNION ALL
SELECT 16, '4 pm' UNION ALL
SELECT 17, '5 pm' UNION ALL
SELECT 18, '6 pm' UNION ALL
SELECT 19, '7 pm' UNION ALL
SELECT 20, '8 pm' UNION ALL
SELECT 21, '9 pm' UNION ALL
SELECT 22, '10 pm' UNION ALL
SELECT 23, '11 pm' )
,cte as (
SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT data.V_ACTIVITY.Description, data.V_ACTIVITY_DATETIME.StartDateTime, data.V_ACTIVITY_DATETIME.EndDateTime,
data.V_LOCATION.Name
FROM data.V_ACTIVITY INNER JOIN
data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room)
)
SELECT
r.hour_description
, cte.description
, cte.StartDateTime
, cte.EndDateTime
, cte.Name
FROM roomhours AS r LEFT OUTER JOIN
cte ON r.hour_number >= datepart(hh,cte.StartDateTime)
and r.hour_number < datepart(hh,cte.EndDateTime);
Can I put an ORDER by in there (can you order the hour description? can you sort am and pms?
September 22, 2015 at 5:23 am
if you were gettign help in another thread, you probably should have continued in that thread, instead of a new post.
looks like this would work to me:
ORDER BY cte.StartDateTime,r.hour_number
Lowell
September 22, 2015 at 5:28 am
The guy was only helping with SQL, he barked at me when I mentioned reporting services in one my replies.
Thanks for your replies L.
Where do I add the ORDER BY cte_1.StartDateTime, r.hour_description?
I put in at the end of the query, but it produces this output in the Query Designer
1 am
10 pm
11 pm
12 am
2 am
3 am
Still not right, any other ideas?
September 22, 2015 at 5:32 am
whoops i menat by hour and not description... changed above.
ORDER BY cte.StartDateTime,r.hour_number
Lowell
September 22, 2015 at 5:34 am
Added that ORDER by at the end of the query, still getting this in Query designer:
12 am
1 am
2 am
3 am
4 am
5 am
6 am
7 am
6 pm
7 pm
8 pm
9 pm
10 pm
11 pm
8 am induction
9 am induction
10 am induction
11 am induction
12 pm induction
1 pm induction
2 pm induction
3 pm induction
4 pm induction
5 pm induction
It's as if its sorting by the second column with NULLS first and then any text..... Should be by first column, 12pm to 11pm
September 22, 2015 at 5:54 am
try
ORDER BY r.hour_number
....and I wasn't "barking" 😀
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 22, 2015 at 6:13 am
Hi J, I was only joking about the bark, you've been more than helpful 🙂
I just tried adding ORDER BY r.hour_number to the end, and it works FINE in SQL Query Designer and the order is correct (sorted by hour with AM and PM) Same as it worked fine without the ORDER by line.
However when I use the same query in Reporting Services Designer, it still comes out as this:
12 am
1 am
2 am
3 am
4 am
5 am
6 am
7 am
6 pm
7 pm
8 pm
9 pm
10 pm
11 pm
8 am induction
9 am induction
10 am induction
11 am induction
12 pm induction
1 pm induction
2 pm induction
3 pm induction
4 pm induction
5 pm induction
September 22, 2015 at 7:25 am
rkelly58 (9/22/2015)
Hi J, I was only joking about the bark, you've been more than helpful 🙂I just tried adding ORDER BY r.hour_number to the end, and it works FINE in SQL Query Designer and the order is correct (sorted by hour with AM and PM) Same as it worked fine without the ORDER by line.
However when I use the same query in Reporting Services Designer, it still comes out as this:
12 am
1 am
2 am
3 am
4 am
5 am
6 am
7 am
6 pm
7 pm
8 pm
9 pm
10 pm
11 pm
8 am induction
9 am induction
10 am induction
11 am induction
12 pm induction
1 pm induction
2 pm induction
3 pm induction
4 pm induction
5 pm induction
its ok...I appreciated the joke about barking.
as I said on your other thread...I really cant help with RS....a quick google throws up a lot of options, but we really need someone with experience to step in here.
sorry I cant help more.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 22, 2015 at 7:33 am
Got the sorting working...... I added J's r.hour_number parameter to the Report fields, and then was able to choose that field in Report Designer and its sorted them correctly.
Thank you, thank you thank you.
J, could I possibility skip back to my original SQL thread and ask you a couple more questions on the SQL code you gave me?
As far as this thread goes, hats off to you J and Lowell for your help.
September 22, 2015 at 7:39 am
rkelly58 (9/22/2015)
Got the sorting working...... I added J's r.hour_number parameter to the Report fields, and then was able to choose that field in Report Designer and its sorted them correctly.Thank you, thank you thank you.
J, could I possibility skip back to my original SQL thread and ask you a couple more questions on the SQL code you gave me?
As far as this thread goes, hats off to you J and Lowell for your help.
glad you sorted it....as for more q's on original thread...pls do (cant promise any solutions though:-))
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply