SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Grouping Row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jysafe
Starting Member

6 Posts

Posted - 11/08/2012 :  11:57:17  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/08/2012 :  12:04:04  Show Profile  Reply with Quote
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 - 11/08/2012 :  17:09:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 11/08/2012 :  23:39:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  07:00:44  Show Profile  Reply with Quote
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 - 11/09/2012 :  11:22:04  Show Profile  Reply with Quote
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 - 11/11/2012 :  15:51:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000