Grouping Row - SQL Server Forums

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

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

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

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.

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.

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,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources