Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Grouping Row

Author  Topic 

Jysafe
Starting Member

6 Posts

Posted - 2012-11-08 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 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
Go to Top of Page

Jysafe
Starting Member

6 Posts

Posted - 2012-11-08 : 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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-08 : 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
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 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.
Go to Top of Page

Jysafe
Starting Member

6 Posts

Posted - 2012-11-09 : 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.
Go to Top of Page

Jysafe
Starting Member

6 Posts

Posted - 2012-11-11 : 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,
Go to Top of Page
   

- Advertisement -