| Author |
 Topic  |
|
|
Jysafe
Starting Member
4 Posts |
 Posted - 11/08/2012 : 11:57:17
|
Hi,
My SQL statement gives me the example below output. What I want to do is sort by person and display like the second display below.
SQL Output
Person # Person Name PersonIdent StartDT EndDT PS
1234 Smith, David TMS 2010-01-04 08:30 2010-01-04 18:30 Central
16592 Howard, Rhonda TMS 2010-01-02 09:45 2010-01-02 16:00 Central
16592 Howard, Rhonda TMS 2010-01-04 09:00 2010-01-04 19:15 Central
13718 Cooper, Robyn QMR 2010-01-02 09:45 2010-01-02 16:00 Eastern
13718 Cooper, Robyn QMR 2010-01-04 10:00 2010-01-04 15:00 Eastern
13718 Cooper, Robyn QMR 2010-01-04 16:00 2010-01-04 19:00 Eastern
16437 Arwood, Michael TMS 2010-01-02 09:30 2010-01-02 16:00 Eastern
16437 Arwood, Michael TMS 2010-01-04 09:00 2010-01-04 09:30 Eastern
16437 Arwood, Michael TMS 2010-01-04 09:30 2010-01-04 11:45 Eastern
16437 Arwood, Michael TMS 2010-01-04 11:45 2010-01-04 13:45 Eastern
16437 Arwood, Michael TMS 2010-01-04 14:30 2010-01-04 18:00 E astern
Report Display
Person # Person Name PersonIdent # of Days Hours PS
1234 Smith, David TMS 1 10 Central
16592 Howard, Rhonda TMS 2 16.5 Central
13718 Cooper, Robyn QMR 2 14.25 Eastern
16437 Arwood, Michael TMS 2 14.75 Eastern
I was able to get the # of Days and Hour but it is not grouped, so the Report displays for each row. How can I group the output to display like above?
Thanks,
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/08/2012 : 12:04:04
|
I am guessing the formulas for calculating the # of days and Hours in the query below - if that is not it, reply? SELECT
[Person #],
[Person Name],
PersonIdent,
DATEDIFF(dd,MIN(StartDT),MAX(EndDT)) AS [# of Days],
SUM(DATEDIFF(mi,StartDT,EndDT))/60.0 AS [Hours],
PS
FROM
YourTable
GROUP BY
[Person #],
[Person Name],
PersonIdent,
PS
ORDER BY
[Person #],
[Person Name],
PersonIdent,
PS |
 |
|
|
Jysafe
Starting Member
4 Posts |
Posted - 11/08/2012 : 17:09:01
|
Hi,
Thanks for getting back to me on this. Here is what I got after running the query. The # looks correct except for if the person has one day... it displays 0
Person # Person Name PersonIdent # of Days Hours PS
1234 Smith, David TMS 0 10.00 Central
16592 Howard, Rhonda TMS 2 16.50 Central
13718 Cooper, Robyn QMR 2 14.25 Eastern
16437 Arwood, Michael TMS 2 14.75 Eastern
Thanks |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/08/2012 : 23:39:35
|
Hi,
If you want to display 1 instead of 0..........
CASE DATEDIFF(dd,MIN(StartDT),MAX(EndDT)) WHEN 0 THEN 1
ELSE DATEDIFF(dd,MIN(StartDT),MAX(EndDT)) AS [# of Days],
--
Chandu |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/09/2012 : 07:00:44
|
Of course, the question that comes to my mind then is, if you want to display 1 when StartDT is January 1 and EndDT is January 1, then what should be displayed when StartDT is January 1 and EndDT is January 2?
Perhaps you want to display 2 in that case? If that is the case, always add 1. The DATEDIFF function with the first parameter as day (or dd) counts the number of day boundaries are crossed. |
|
|
|
Jysafe
Starting Member
4 Posts |
Posted - 11/09/2012 : 11:22:04
|
Hi,
Thank you both for getting back to me.
That was a good question about if the StartDT is January 1 and EndDT is January 1, then the display would be 1. |
|
|
|
Jysafe
Starting Member
4 Posts |
Posted - 11/11/2012 : 15:51:57
|
Hi,
Using Expression in SSRS, how can I select Min and MAX dates?
In other words, is it possible to do the below SQL queiry in SSRS expressoin?
CASE DATEDIFF(dd,MIN(CONVERT(VARCHAR(10),StartDT, 101)),MAX(CONVERT(VARCHAR(10),EndDT, 101))) WHEN 0 THEN 1
ELSE DATEDIFF(dd,MIN(CONVERT(VARCHAR(10),StartDT, 101)),MAX(CONVERT(VARCHAR(10),EndDT, 101))) AS [# of Days],
Thanks, |
|
|
| |
Topic |
|
|
|
Grouping Row
