Firehose mode: February 2012

Sunday, February 26, 2012

GROUP BY DATEPART issues...

Hi,

I'm trying:

Code Snippet

SELECT SUM(price), DATEPART(month, order_date), DATEPART(year, order_date)

FROM orders

GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)

and, while this executes w/o a problem, I am a bit concerned with my results...

i get a SUM(price) = 2140.21 when running the above SQL for 11/2006

however, when i try:

Code Snippet

SELECT SUM(price)

FROM orders

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

i get a SUM(price) = 1950.45

if i bump the second date up by one day (i.e. '12/01/2006') i get a SUM(price) = 2140.21, the same value as when I used GROUP BY

any help would be greatly appreciated!

Hi,

What is the result of this query

Code Snippet

SELECT SUM(price), month(order_date), year(order_date)

FROM orders

GROUP BY month(order_date), year(order_date)

HAVING month(order_date)=6 AND year(order_date)=2006

If result is 1950.45 then

u try to use like this syntax

else

check your data one by one ..

|||

Thank you for the response.

I have tried

Code Snippet

...

HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006

and received 2140.21

|||

Is the order_date always truncated at midnight? Try replacing "SUM(price)" with "COUNT(*)" with your queries. Do you get the same record counts for November 2006?

Thanks,
Bryan Smith

|||

I'm not sure about order_date being truncated at midnight... order_date is a datetime, so i am assuming that up until 11:59:59.99PM on 11/30, the date parts I am after remain the same.

I ran the COUNT(*) as suggested and get 804 using:

Code Snippet

SELECT COUNT(*)

FROM orders

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

and get 838 when using:

Code Snippet

SELECT COUNT(*), DATEPART(month, order_date), DATEPART(year, order_date)

FROM orders

GROUP BY DATEPART(month, order_date), DATEPART(year, order_date)

the number remains 838 when I add:

Code Snippet

HAVING DATEPART(month, order_date) = 11 AND DATEPART(year, order_date) = 2006

and drops to 804 if i add:

Code Snippet

WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006'

i am baffled, but then again, i'm no expert

thanks for the assistance!

|||

Cool! I think we're on the right track here.

Your original code used "WHERE order_date BETWEEN '11/01/2006' and '11/30/2006'". That means orders created between midnight Nov 1 2006 and midnight at the top of Nov 30 2006. You're dropping orders that occurred from 11/30/2006 12:00:00.001 AM to 11/30/2006 11:59:59.997 PM. That's why the BETWEEN statement gives you 804 records while the DATEPART statement gives you 838 records.

(Please note, SQL Server is only accurate to 3 ms when a datetime data type is used. Times of 11:59:59.998 PM and 11:59:59.999 PM are recorded as 12:00:00.000 AM the next day.)

If you re-write your query to use "WHERE order_date BETWEEN '11/01/2006' AND '11/30/2006 11:59:59.997 PM'" you should get 838 records and your SUM should match the one in the DATEPART query.

Good luck,
Bryan

|||

Ahhh... that makes sense...

would it be correct to assume that the GROUP BY DATEPART... query accurately sums up each months data?

thanks again for your help!

|||

It would. It calculates the month of the date without regard for time, so any orders placed at anytime on 11/30/2006 would fall into November.

Group by Datepart 30 sec interval?

Hello, im starting with sql and i need to group data in the form of:
datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
datepart(30*s,date). Is any way of getting it?
I know i can get the same in vb passing the interval by parameter, by is it
possible to do it this way?
Thanks a lot!!!
Sorry, for my EnglishThis example takes a datetime and truncates it to 30 second intervals.
By grouping on the expression you should get what you need.
select crdate,
dateadd(ms,- (datepart(ms,crdate) +
((datepart(second,crdate) % 30) * 1000)),
crdate)
from sysobjects
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Hello, im starting with sql and i need to group data in the form of:
> datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>datepart(30*s,date). Is any way of getting it?
>I know i can get the same in vb passing the interval by parameter, by is it
>possible to do it this way?
>Thanks a lot!!!
>Sorry, for my English|||Thanks for answering!! As I told before IÂ´m starting so IÂ´m not very good at
this. Would you mind explainig a bit how it works? Cause I do not see it. And
i supposse the ms stands for miliseconds, but vba in excel gives me object
error.
Thanks a lot indeed!!
"Roy Harvey" wrote:
> This example takes a datetime and truncates it to 30 second intervals.
> By grouping on the expression you should get what you need.
> select crdate,
> dateadd(ms,- (datepart(ms,crdate) +
> ((datepart(second,crdate) % 30) * 1000)),
> crdate)
> from sysobjects
> Roy Harvey
> Beacon Falls, CT
> On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> wrote:
> >Hello, im starting with sql and i need to group data in the form of:
> > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
> >datepart(30*s,date). Is any way of getting it?
> >
> >I know i can get the same in vb passing the interval by parameter, by is it
> >possible to do it this way?
> >
> >Thanks a lot!!!
> >
> >Sorry, for my English
>|||Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
work. Only problem, is that it does not accept ms in vba.
Thanks a lot!!!!
"neb" wrote:
> Thanks for answering!! As I told before IÂ´m starting so IÂ´m not very good at
> this. Would you mind explainig a bit how it works? Cause I do not see it. And
> i supposse the ms stands for miliseconds, but vba in excel gives me object
> error.
> Thanks a lot indeed!!
> "Roy Harvey" wrote:
> > This example takes a datetime and truncates it to 30 second intervals.
> > By grouping on the expression you should get what you need.
> >
> > select crdate,
> > dateadd(ms,- (datepart(ms,crdate) +
> > ((datepart(second,crdate) % 30) * 1000)),
> > crdate)
> > from sysobjects
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
> > wrote:
> >
> > >Hello, im starting with sql and i need to group data in the form of:
> > > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
> > >datepart(30*s,date). Is any way of getting it?
> > >
> > >I know i can get the same in vb passing the interval by parameter, by is it
> > >possible to do it this way?
> > >
> > >Thanks a lot!!!
> > >
> > >Sorry, for my English
> >|||What I wrote is Transact-SQL, the language understood by Microsoft SQL
Server. It is not VBA. I suggest getting your queries working in
Query Analyzer (SQL Server 2000) or SQL Management Studio (SQL Server
2005) before trying to run them through any programming tool.
Roy Harvey
Beacon Falls, CT
On Mon, 4 Jun 2007 11:47:00 -0700, neb <neb@.discussions.microsoft.com>
wrote:
>Sorry, i was a bit 'blind'. I do get it now. Thanks a lot, i think it should
>work. Only problem, is that it does not accept ms in vba.
>Thanks a lot!!!!
>"neb" wrote:
>> Thanks for answering!! As I told before I´m starting so I´m not very good at
>> this. Would you mind explainig a bit how it works? Cause I do not see it. And
>> i supposse the ms stands for miliseconds, but vba in excel gives me object
>> error.
>> Thanks a lot indeed!!
>> "Roy Harvey" wrote:
>> > This example takes a datetime and truncates it to 30 second intervals.
>> > By grouping on the expression you should get what you need.
>> >
>> > select crdate,
>> > dateadd(ms,- (datepart(ms,crdate) +
>> > ((datepart(second,crdate) % 30) * 1000)),
>> > crdate)
>> > from sysobjects
>> >
>> > Roy Harvey
>> > Beacon Falls, CT
>> >
>> > On Mon, 4 Jun 2007 09:56:00 -0700, neb <neb@.discussions.microsoft.com>
>> > wrote:
>> >
>> > >Hello, im starting with sql and i need to group data in the form of:
>> > > datepart("yyyy",date), datepart("mm",date), datepart("dd",date),
>> > >datepart(30*s,date). Is any way of getting it?
>> > >
>> > >I know i can get the same in vb passing the interval by parameter, by is it
>> > >possible to do it this way?
>> > >
>> > >Thanks a lot!!!
>> > >
>> > >Sorry, for my English
>> >

Group By Date?

How would I group records by date from a table where each record has a
smalldatetime field called CreationDateTime that is auto-populated using
GetDate().
I need to produce a trends graph and want display the number of records
created per day. Obviously, my CreationDateTime field stored both both date
and time.
Thanks
BenThis isn't going to be very snappy, but...
SELECT
dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime)),
COUNT(*)
FROM
yourTable
WHERE
CreationDateTime >= ?
AND CreationDateTime < ?
GROUP BY
DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime))
ORDER BY
1
Note that if there are days in your date range with no data, they will not
show up in the result set. If you want to have a row for every day, even
when there are no relevant rows, use a calendar table (see
http://www.aspfaq.com/2519 for some examples).
You may want to consider adding a computed or static column that holds the
date only, if you are going to use a lot of queries like this. If you do
that, you will want to experiment with your clustered index, and whether it
resides on the column with both date and time, or on the column with just
the date. Your best scenario depends on whether you are querying by range
or just analyzing the entire table, and what else this table is being used
for...
A
"Ben Fidge" <ben.fidge@.nospambtopenworld.com> wrote in message
news:%23Yd2cbMBGHA.740@.TK2MSFTNGP12.phx.gbl...
> How would I group records by date from a table where each record has a
> smalldatetime field called CreationDateTime that is auto-populated using
> GetDate().
> I need to produce a trends graph and want display the number of records
> created per day. Obviously, my CreationDateTime field stored both both
> date and time.
> Thanks
> Ben
>|||Ben Fidge wrote:

> How would I group records by date from a table where each record has a
> smalldatetime field called CreationDateTime that is auto-populated using
> GetDate().
> I need to produce a trends graph and want display the number of records
> created per day. Obviously, my CreationDateTime field stored both both dat
e
> and time.
> Thanks
> Ben
SELECT MIN(creationdatetime) AS dt, COUNT(*) AS cnt
FROM your_table
GROUP BY DATEDIFF(DAY,'20000101',creationdatetime
) ;
David Portas
SQL Server MVP
--|||Exceelnt, works a treat. Thanks.
Ben
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u0D$9fMBGHA.1032@.TK2MSFTNGP11.phx.gbl...
> This isn't going to be very snappy, but...
>
> SELECT
> dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime)),
> COUNT(*)
> FROM
> yourTable
> WHERE
> CreationDateTime >= ?
> AND CreationDateTime < ?
> GROUP BY
> DATEADD(DAY, 0, DATEDIFF(DAY, 0, CreationDateTime))
> ORDER BY
> 1
>
> Note that if there are days in your date range with no data, they will not
> show up in the result set. If you want to have a row for every day, even
> when there are no relevant rows, use a calendar table (see
> http://www.aspfaq.com/2519 for some examples).
> You may want to consider adding a computed or static column that holds the
> date only, if you are going to use a lot of queries like this. If you do
> that, you will want to experiment with your clustered index, and whether
> it resides on the column with both date and time, or on the column with
> just the date. Your best scenario depends on whether you are querying by
> range or just analyzing the entire table, and what else this table is
> being used for...
> A
>
> "Ben Fidge" <ben.fidge@.nospambtopenworld.com> wrote in message
> news:%23Yd2cbMBGHA.740@.TK2MSFTNGP12.phx.gbl...
>

Group By Date Range

I have the following sql query which I would like to group by date range in weeks. It is a table that records ipAddresses for various pages. I am trying to return total and distinct page hits per week.

SELECT page, count(*) AS total, count(DISTINCT ipAddress) AS ipAddress
FROM tblUniquePageHits
WHERE
date_hit >= '2003-09-03 10:00:00' AND
date_hit < '2003-10-17 10:00:00' AND
page = 'Main'
GROUP BY page

I want to group by date_hit from
'2003-09-03 10:00:00' to '2003-09-10 10:00:00'
'2003-09-10 10:00:00' to '2003-09-17 10:00:00'
and so on...see if this article might help you --

Aggregates for date ranges (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid494805_tax285649,00.html)
(registration required, but it's free)

if you have any questions, let me know

rudy
http://r937.com/

Group By Date Query

Hi,
I want to arrange some of the data retrieved from the database with
respect to a range of date. The tables from which I am retrieving the
data are, namely;
1. Transaction
2. Credit
3. Debit
4. Account
My stored procedure is as follows
CREATE PROCEDURE [dbo].[SP_TransactionInfo]
@.startDate datetime, @.endDate datetime
AS
BEGIN
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
the problem is that I want to add a statement that would use the
@.startDate and @.endDate to as a range of dates according to which all
the data will be arranged in descending order.
Thanking you.
Chao.
Hi
Don't you have a column that identifies the debit, credit..whatever?
Add WHERE condtion like dt_column >=@.startDate AND dt_column
<DATEADD(d,1,@.endDate )
It is probably good to hhave an index of dt_column
<umairsyed19@.gmail.com> wrote in message
news:574964e1-dbc1-4f42-b7a3-cc5f1f6e5a9f@.n75g2000hsh.googlegroups.com...
> Hi,
> I want to arrange some of the data retrieved from the database with
> respect to a range of date. The tables from which I am retrieving the
> data are, namely;
> 1. Transaction
> 2. Credit
> 3. Debit
> 4. Account
> My stored procedure is as follows
> CREATE PROCEDURE [dbo].[SP_TransactionInfo]
> @.startDate datetime, @.endDate datetime
> AS
> BEGIN
> SELECT distinct
> T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
> C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
> AC.Account_Name as Deb_AccName
> FROM Transactions T
> JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
> JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
> JOIN Account AC ON C.Account_ID = AC.Account_ID
> JOIN Account AD ON D.Account_ID = AD.Account_ID
> the problem is that I want to add a statement that would use the
> @.startDate and @.endDate to as a range of dates according to which all
> the data will be arranged in descending order.
>
> Thanking you.
> Chao.
|||Perhaps you mean to filter the Transaction Date (as this is the only date
column based on the name) and order by it descending:
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date BETWEEN @.startDate AND @.endDate
ORDER BY T.Transaction_Date DESC
The above will have the date range including the start and end dates. To
exclude one or both of them you can change as follows (below the end date is
excluded):
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date >= @.startDate
AND T.Transaction_Date < @.endDate
ORDER BY T.Transaction_Date DESC
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Group By Date Query

Hi,
I want to arrange some of the data retrieved from the database with
respect to a range of date. The tables from which I am retrieving the
data are, namely;
1. Transaction
2. Credit
3. Debit
4. Account
My stored procedure is as follows
CREATE PROCEDURE [dbo].[SP_TransactionInfo]
@.startDate datetime, @.endDate datetime
AS
BEGIN
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
the problem is that I want to add a statement that would use the
@.startDate and @.endDate to as a range of dates according to which all
the data will be arranged in descending order.
Thanking you.
Chao.Hi
Don't you have a column that identifies the debit, credit..whatever?
Add WHERE condtion like dt_column >=@.startDate AND dt_column
<DATEADD(d,1,@.endDate )
It is probably good to hhave an index of dt_column
<umairsyed19@.gmail.com> wrote in message
news:574964e1-dbc1-4f42-b7a3-cc5f1f6e5a9f@.n75g2000hsh.googlegroups.com...
> Hi,
> I want to arrange some of the data retrieved from the database with
> respect to a range of date. The tables from which I am retrieving the
> data are, namely;
> 1. Transaction
> 2. Credit
> 3. Debit
> 4. Account
> My stored procedure is as follows
> CREATE PROCEDURE [dbo].[SP_TransactionInfo]
> @.startDate datetime, @.endDate datetime
> AS
> BEGIN
> SELECT distinct
> T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
> C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
> AC.Account_Name as Deb_AccName
> FROM Transactions T
> JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
> JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
> JOIN Account AC ON C.Account_ID = AC.Account_ID
> JOIN Account AD ON D.Account_ID = AD.Account_ID
> the problem is that I want to add a statement that would use the
> @.startDate and @.endDate to as a range of dates according to which all
> the data will be arranged in descending order.
>
> Thanking you.
> Chao.|||Perhaps you mean to filter the Transaction Date (as this is the only date
column based on the name) and order by it descending:
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date BETWEEN @.startDate AND @.endDate
ORDER BY T.Transaction_Date DESC
The above will have the date range including the start and end dates. To
exclude one or both of them you can change as follows (below the end date is
excluded):
SELECT distinct
T.Transaction_ID, T.Transaction_Date, D.Debit_Amount, C.Credit_Amount,
C.Credit_ID, D.Debit_ID, AD.Account_Name as Crd_AccName,
AC.Account_Name as Deb_AccName
FROM Transactions T
JOIN Credit C ON T.Transaction_ID = C.Transaction_ID
JOIN Debit D ON T.Transaction_ID = D.Transaction_ID
JOIN Account AC ON C.Account_ID = AC.Account_ID
JOIN Account AD ON D.Account_ID = AD.Account_ID
WHERE T.Transaction_Date >= @.startDate
AND T.Transaction_Date < @.endDate
ORDER BY T.Transaction_Date DESC
HTH,
Plamen Ratchev
http://www.SQLStudio.com

group by date only

Hi all,
I have a field that has datetime datatype. its data contain hour and minute.
how can I select it and format it as only "mm/dd/yyyy"? I don't want hour
and minute. In short I want to group by date. Something like 1/1/2002 as a
group, 1/2/2003 as a group.
Thanks,
eguyWill something like this work?
USE Northwind
select convert(char(12), OrderDate, 101) date, count(*) NumOrders
from Orders
group by convert(char(12), OrderDate, 101)
order by 1
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"*eguy" <hstandit@.yahoo.com> wrote in message
news:#cfzlW7jDHA.1004@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a field that has datetime datatype. its data contain hour and
minute.
> how can I select it and format it as only "mm/dd/yyyy"? I don't want hour
> and minute. In short I want to group by date. Something like 1/1/2002 as a
> group, 1/2/2003 as a group.
> Thanks,
> eguy
>|||It works.
Thank you very much.
eguy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%236QJda7jDHA.2580@.TK2MSFTNGP10.phx.gbl...
> Will something like this work?
> USE Northwind
> select convert(char(12), OrderDate, 101) date, count(*) NumOrders
> from Orders
> group by convert(char(12), OrderDate, 101)
> order by 1
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "*eguy" <hstandit@.yahoo.com> wrote in message
> news:#cfzlW7jDHA.1004@.TK2MSFTNGP09.phx.gbl...
> >
> > Hi all,
> >
> > I have a field that has datetime datatype. its data contain hour and
> minute.
> > how can I select it and format it as only "mm/dd/yyyy"? I don't want
hour
> > and minute. In short I want to group by date. Something like 1/1/2002 as
a
> > group, 1/2/2003 as a group.
> > Thanks,
> >
> > eguy
> >
> >
>

Group By Date in Stored Procedure

I am trying to to a transaction count (per day) running a stored procedure that does a group by date. The problem is that since the date has a time stamp (I assume) it sees each date as a different group even if it's the same day. Is there a way to format the date in the stored procedure so that it sees all of the transactions on the same day as one or is there another way to do this.

Select count(recordid),transactiondate
from sometable
group by transactiondate

Thanks

in advance

Select count(recordid),CONVERT(NCHAR(10),transactiondate,101)from sometable
group byCONVERT(NCHAR(10),transactiondate,101)

Group By Date

I have an Employees Table, I want to retrieve the data using group by Date.
Select count(*),Joined_Date from Employees Group By Joined_Date.
The problem here is it groups by both date and time. I want to group by only
date.
I want the results for each and every day. Is there someother to do this
other than using groups.
Thanks
KiranHi,
Try something like this. It gives by dates and excludes the time
Select count(*),convert(varchar,Joined_Date,103
)
from Employees Group By convert(varchar,Joined_Date,103)
HTH..
Regards,
Dilip|||Kiran
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'2005-08-02 11:24:14.697')
INSERT INTO #Test VALUES (2,'2005-08-02 12:24:14.697')
INSERT INTO #Test VALUES (3,'2005-08-02 13:24:14.697')
INSERT INTO #Test VALUES (4,'2005-08-02 14:24:14.697')
INSERT INTO #Test VALUES (5,'2005-08-03 11:24:14.697')
INSERT INTO #Test VALUES (6,'2005-08-03 12:24:14.697')
INSERT INTO #Test VALUES (7,'2005-08-03 13:24:14.697')
INSERT INTO #Test VALUES (8,'2005-08-03 14:24:14.697')
SELECT COUNT(*) ,CONVERT(CHAR(10),col2,112) FROM #Test
GROUP BY CONVERT(CHAR(10),col2,112)
Note: Take a look at CONVERT system function that has a style (third)
parameter.
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com...
>I have an Employees Table, I want to retrieve the data using group by Date.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group by
> only
> date.
> I want the results for each and every day. Is there someother to do this
> other than using groups.
> Thanks
> Kiran|||Thanks Uri
"Uri Dimant" wrote:

> Kiran
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 DATETIME NOT NULL
> )
> INSERT INTO #Test VALUES (1,'2005-08-02 11:24:14.697')
> INSERT INTO #Test VALUES (2,'2005-08-02 12:24:14.697')
> INSERT INTO #Test VALUES (3,'2005-08-02 13:24:14.697')
> INSERT INTO #Test VALUES (4,'2005-08-02 14:24:14.697')
> INSERT INTO #Test VALUES (5,'2005-08-03 11:24:14.697')
> INSERT INTO #Test VALUES (6,'2005-08-03 12:24:14.697')
> INSERT INTO #Test VALUES (7,'2005-08-03 13:24:14.697')
> INSERT INTO #Test VALUES (8,'2005-08-03 14:24:14.697')
>
> SELECT COUNT(*) ,CONVERT(CHAR(10),col2,112) FROM #Test
> GROUP BY CONVERT(CHAR(10),col2,112)
> Note: Take a look at CONVERT system function that has a style (third)
> parameter.
>
> "Kiran" <Kiran@.discussions.microsoft.com> wrote in message
> news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com...
>
>|||Thanks Dilip
"dilipn123@.gmail.com" wrote:

> Hi,
> Try something like this. It gives by dates and excludes the time
> Select count(*),convert(varchar,Joined_Date,103
)
> from Employees Group By convert(varchar,Joined_Date,103)
> HTH..
> Regards,
> Dilip
>|||You have to convert the date to a non-time consisting datetime, like
Select CONVERT(VARCHAR(10),GETDATE(),112)
Select count(*),CONVERT(VARCHAR(10),Joined_Date
,112) from Employees Group By
CONVERT(VARCHAR(10),Joined_Date,112).
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kiran" wrote:

> I have an Employees Table, I want to retrieve the data using group by Date
.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group by on
ly
> date.
> I want the results for each and every day. Is there someother to do this
> other than using groups.
> Thanks
> Kiran|||In news:C1ADFD9C-8327-401E-B4C3-4F36EE484C91@.microsoft.com,
Kiran <Kiran@.discussions.microsoft.com> said:
> I have an Employees Table, I want to retrieve the data using group by
> Date.
> Select count(*),Joined_Date from Employees Group By Joined_Date.
> The problem here is it groups by both date and time. I want to group
> by only date.
Why does everyone convert the date to char?
Select count(*),Joined_Date
from Employees
Group By cast(cast(Joined_Date as float) as int)
Casting the datetime to an int doesn't truncate, it rounds, which is IMHO
very odd indeed.
Steve|||> Why does everyone convert the date to char?
Converting dates to a number works well but defies common-sense logic.
I don't know why MS allows this weird conversion between dates and
numerics. Note that they only guarantee the consistency of round-trip
conversions between versions.
Another method:
...
GROUP BY DATEDIFF(DAY,0,joined_date)
David Portas
SQL Server MVP
--|||In news:1122975714.884084.99980@.g44g2000cwa.googlegroups.com,
David Portas <REMOVE_BEFORE_REPLYING_dportas@.acm.org> said:
> Converting dates to a number works well but defies common-sense logic.
> I don't know why MS allows this weird conversion between dates and
> numerics.
It isn't weird when you consider how datetime values are stored. They are
simply a floating point number where the integer part is the number of days
since 1/1/1900 and the fractional part is the fraction through the day (so
.5 is midday). Many programming languages follow this convention, although
the date base is often different and shouldn't be relied upon. It allows
simple calculation of time passage (for example, how many days between x and
y? Answer = y-x).
Steve|||> It isn't weird when you consider how datetime values are stored
Yes of course, but in SQL the datatypes are supposed to insulate us
from the way data is stored. MS might well choose to change the storage
architecture in a future version but that shouldn't change the logical
meaning of our code. That's why I'm uncomfortable with date/numeric
conversions, although I admit that I too find them convenient
sometimes.

> It allows
> simple calculation of time passage (for example, how many days between x a
nd
> y? Answer = y-x).
I would always recommend you use the proper date arithmetic functions
for this rather than rely on the numeric math operators. There's a
world of difference between y-x and DATEDIFF(DAY,x,y). For example:
DECLARE @.dt1 SMALLDATETIME, @.dt2 SMALLDATETIME
SET @.dt1 = '20050101'
SET @.dt2 = '19000101'
SELECT DATEDIFF(DAY,@.dt1,@.dt2)
SELECT @.dt2 - @.dt1
David Portas
SQL Server MVP
--

Group by Date

Hi all,

Firstly: I'm a newbie when it comes to reporting services (previously used to Crystal reports).

This is probably going to appear as a stupid question but here goes.

How do i group on a date time field. I have set up a report with a table and as my group i have selected my datetime field

[code]

=Fields!WeekBegin.Value

[/code]

When i group on this field each record still seems to have its own grouping even though all entries in my test database have the same date. I have noticed that the seconds don't match up on some entries and believe that this may have something to do with it. There are however still duplicates that should be grouped on. I really only want to group on the date part of the Datetime field.

Anyone got any ideas where i am going wrong.

Thanks in advance

Grant

If you only want to group on the date part, you can use the datevalue() function:

=datevalue(Fields!WeekBegin.Value)

or use the Format function to get the date part.

Group By Count * >1?

Can this be used to prevent the repetition of records displayed in a page?

Code Snippet

SELECT T_ProgramGuests, GuestName
FROM T_ProgramGuests
GROUP BY ProgramID, GuestName
HAVING (COUNT(*) > 1)

I'm trying to prevent names being repeated. I only want the name to show once followed by the next name and so on. But only once.

Does this do what you want?

SELECT GuestName, MAX(ProgramID) AS pid

FROM T_ProgramGuests

GROUP BY GuestName

ORDER BY GuestName ASC

The above prints the last programId/guest name pair in your table. If you want the first, you can replace the max with min.

Hope this helps!

John (MSFT)

Group By Count

Dear all

How do i group the count by Top ?

For example, We want to show Top 5 high salary people in my company , but may top 3 people's salary is same. So I want to sperate 5 groups, the output data look like this :
[Top 1 high salary] - Philip , Alex , David
[Top 2 high salary] - Emily
[Top 3 high salary] - Tom, May, Ada
[Top 4 high salary] - Queenie
[Top 5 high salary] - Ellie, Candy, Hazel, Stella, Tanya, Jacky

Hence, the total count of name should be over 5, so how do i output this record ? because it should just show 5 peoples (Philip, Alex, David, Emily and Tom) if i just use "Top 5" .

Thanks you for your helpin SQL server, you could declare table variable, and insert those names into the table variable, then return from the table variable|||This is effectively a pivot on top of aggregation. That means that it is best done on the client.

With that said, it can be done in SQL. Something like:CREATE TABLE dbo.salary (
name VARCHAR(20) NOT NULL
, salary MONEY NOT NULL
)

INSERT dbo.salary (salary, [name]) VALUES (50, 'Phillip')
INSERT dbo.salary (salary, [name]) VALUES (50, 'Alex')
INSERT dbo.salary (salary, [name]) VALUES (50, 'David')
INSERT dbo.salary (salary, [name]) VALUES (40, 'Emily')
INSERT dbo.salary (salary, [name]) VALUES (30, 'Tom')
INSERT dbo.salary (salary, [name]) VALUES (30, 'May')
INSERT dbo.salary (salary, [name]) VALUES (30, 'Ada')
INSERT dbo.salary (salary, [name]) VALUES (20, 'Queenie')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Ellie')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Candy')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Hazel')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Stella')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Tanya')
INSERT dbo.salary (salary, [name]) VALUES (10, 'Jacky')

INSERT dbo.salary (salary, [name]) VALUES ( 5, 'Sam')
INSERT dbo.salary (salary, [name]) VALUES ( 5, 'Sammy')

INSERT dbo.salary (salary, [name]) VALUES ( 3, 'Clyde')

SELECT TOP 5 r.salary, Min(s1.[name]) + Coalesce( ', ' + Min(s2.[name]), '')
+ Coalesce( ', ' + Min(s3.[name]), '') + Coalesce( ', ' + Min(s4.[name]), '')
+ Coalesce( ', ' + Min(s5.[name]), '')
FROM (SELECT TOP 5 salary FROM dbo.salary GROUP BY salary ORDER BY salary DESC) AS r
JOIN (SELECT salary, [name] FROM dbo.salary) AS s1
ON (s1.salary = r.salary)
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s2
ON (s2.salary = r.salary
AND s2.[name] > s1.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s3
ON (s3.salary = r.salary
AND s3.[name] > s2.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s4
ON (s4.salary = r.salary
AND s4.[name] > s3.[name])
LEFT JOIN (SELECT salary, [name] FROM dbo.salary) AS s5
ON (s5.salary = r.salary
AND s5.[name] > s4.[name])
GROUP BY r.salary
ORDER BY r.salary DESC-PatP

Group By Constant

I know you can't use an alias in a group by, but is there any way to group b
y
a constant in the select list? See the following northwind sample of my
problem.
use NORTHWIND
select city, 'blah'
from customers
group BY ciy, 'blah'
having count(*) > 5You don't need to put the constant in the GROUP BY list:
select city, 'blah'
from customers
group BY city
having count(*) > 5
If that doesn't answer your question then please show us exactly what
result you want from this query.
David Portas
SQL Server MVP
--|||Why do you need to group by that? It doesn't make any sense. Just use:
select city, 'blah'
from customers
group BY city
having count(*) > 5
(Since 'blah' will be the same in every single row.)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:66E8D62C-144A-4DA2-85EB-EA3ABAE1D17D@.microsoft.com...
>I know you can't use an alias in a group by, but is there any way to group
>by
> a constant in the select list? See the following northwind sample of my
> problem.
> use NORTHWIND
> select city, 'blah'
> from customers
> group BY ciy, 'blah'
> having count(*) > 5|||I just thought everything that wasn't an agragate had to be in the group by
stmt. I tried everything except for leaving the constant out of the group by
.
Thanks!!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why do you need to group by that? It doesn't make any sense. Just use:
> select city, 'blah'
> from customers
> group BY city
> having count(*) > 5
> (Since 'blah' will be the same in every single row.)
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:66E8D62C-144A-4DA2-85EB-EA3ABAE1D17D@.microsoft.com...
>
>

Group By Consecutive Values

Hi I've been puzzling over this one for ages, Imagine a dataset like:
ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
1-Jan 07:00 1-Jan 15:00 00001 Fred S
2-Jan 07:00 2-Jan 15:00 00001 Fred S
3-Jan 07:00 3-Jan 15:00 00001 Fred
4-Jan 07:00 4-Jan 15:00 00001 Fred S
5-Jan 07:00 5-Jan 15:00 00001 Fred S
6-Jan 07:00 6-Jan 15:00 00001 Fred
1-Jan 07:00 1-Jan 15:00 00002 Jane
2-Jan 07:00 2-Jan 15:00 00002 Jane S
3-Jan 07:00 3-Jan 15:00 00002 Jane S
4-Jan 07:00 4-Jan 15:00 00002 Jane S
5-Jan 07:00 5-Jan 15:00 00002 Jane S
6-Jan 07:00 6-Jan 15:00 00002 Jane
I would like to group by consecutive absence codes to generate a report
like:
Fred had 2 occurrences of AbsenceCode S
1-Jan 07:00 to 2-Jan 15:00
4-Jan 07:00 to 5-Jan 15:00
Jane had 1 occurrence of AbsenceCode S
2-Jan 07:00 to 5-Jan 15:00
I have many more codes, and many more records, How can I group by each
occurrence like this?
I've tried using RunningValue with Previous(AbsenceCode) but RS won't allow
nested aggregate functions.
Thanks in advance,
N.Create two groups - one on Name and another one on AbsenceCode. See the
attached example that goes against local pubs database.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>state</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>city</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>address</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="state">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>state</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="city">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>city</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="au_fname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>au_fname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!au_fname.Value & " " &
Fields!au_lname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="address">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>address</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!address.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>pubs</DataSetName>
<Width>5.375in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!state.Value & " [" &
RunningValue(Fields!state.Value, Count, "table1_Group1") & "
authors]"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!state.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!city.Value & " [" &
RunningValue(Fields!city.Value, Count, "table1_Group2") & "
authors]"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!city.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!state.Value</Parent>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
<TableColumn>
<Width>1.375in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>1.125in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>4.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="pubs">
<rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=pubs</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7.125in</Width>
<DataSets>
<DataSet Name="pubs">
<Fields>
<Field Name="au_id">
<DataField>au_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_lname">
<DataField>au_lname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_fname">
<DataField>au_fname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="phone">
<DataField>phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="address">
<DataField>address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="city">
<DataField>city</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="state">
<DataField>state</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="zip">
<DataField>zip</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="contract">
<DataField>contract</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>pubs</DataSourceName>
<CommandText>select * from authors</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nytr0x" <nytr0x@.yahoo.com> wrote in message
news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Hi I've been puzzling over this one for ages, Imagine a dataset like:
> ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> 3-Jan 07:00 3-Jan 15:00 00001 Fred
> 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> 6-Jan 07:00 6-Jan 15:00 00001 Fred
> 1-Jan 07:00 1-Jan 15:00 00002 Jane
> 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> 6-Jan 07:00 6-Jan 15:00 00002 Jane
> I would like to group by consecutive absence codes to generate a report
> like:
> Fred had 2 occurrences of AbsenceCode S
> 1-Jan 07:00 to 2-Jan 15:00
> 4-Jan 07:00 to 5-Jan 15:00
> Jane had 1 occurrence of AbsenceCode S
> 2-Jan 07:00 to 5-Jan 15:00
> I have many more codes, and many more records, How can I group by each
> occurrence like this?
> I've tried using RunningValue with Previous(AbsenceCode) but RS won't
allow
> nested aggregate functions.
> Thanks in advance,
> N.
>|||I haven't had chance to test your example yet. But wouldn't this just group
each AbsenceCode for each Name? In the example given I want two groups for
the AbsenceCode S for Fred and 1 group for Jane.
Thanks,
N
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23VsZSq0aEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Create two groups - one on Name and another one on AbsenceCode. See the
> attached example that goes against local pubs database.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>1in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>15</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>state</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>city</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>address</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="state">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>state</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="city">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>city</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="au_fname">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>au_fname</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!au_fname.Value & " " &
> Fields!au_lname.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="address">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>address</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!address.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>pubs</DataSetName>
> <Width>5.375in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!state.Value & " [" &
> RunningValue(Fields!state.Value, Count, "table1_Group1") & "
> authors]"</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!state.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox13</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!city.Value & " [" &
> RunningValue(Fields!city.Value, Count, "table1_Group2") & "
> authors]"</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox15">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox15</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox16">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox16</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!city.Value</GroupExpression>
> </GroupExpressions>
> <Parent>=Fields!state.Value</Parent>
> </Grouping>
> </TableGroup>
> </TableGroups>
> <TableColumns>
> <TableColumn>
> <Width>1.625in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.375in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.25in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.125in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>4.75in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="pubs">
> <rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>initial catalog=pubs</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>7.125in</Width>
> <DataSets>
> <DataSet Name="pubs">
> <Fields>
> <Field Name="au_id">
> <DataField>au_id</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="au_lname">
> <DataField>au_lname</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="au_fname">
> <DataField>au_fname</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="phone">
> <DataField>phone</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="address">
> <DataField>address</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="city">
> <DataField>city</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="state">
> <DataField>state</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="zip">
> <DataField>zip</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="contract">
> <DataField>contract</DataField>
> <rd:TypeName>System.Boolean</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>pubs</DataSourceName>
> <CommandText>select * from authors</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Nytr0x" <nytr0x@.yahoo.com> wrote in message
> news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > Hi I've been puzzling over this one for ages, Imagine a dataset like:
> >
> > ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> > 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> > 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> > 3-Jan 07:00 3-Jan 15:00 00001 Fred
> > 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> > 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> > 6-Jan 07:00 6-Jan 15:00 00001 Fred
> > 1-Jan 07:00 1-Jan 15:00 00002 Jane
> > 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> > 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> > 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> > 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> > 6-Jan 07:00 6-Jan 15:00 00002 Jane
> >
> > I would like to group by consecutive absence codes to generate a report
> > like:
> >
> > Fred had 2 occurrences of AbsenceCode S
> > 1-Jan 07:00 to 2-Jan 15:00
> > 4-Jan 07:00 to 5-Jan 15:00
> > Jane had 1 occurrence of AbsenceCode S
> > 2-Jan 07:00 to 5-Jan 15:00
> >
> > I have many more codes, and many more records, How can I group by each
> > occurrence like this?
> > I've tried using RunningValue with Previous(AbsenceCode) but RS won't
> allow
> > nested aggregate functions.
> >
> > Thanks in advance,
> > N.
> >
> >
>|||Yes, ths will first group by name and then by absense code. If you want to
do one group per absense code for each name, you'd have to do that in SQL.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nytr0x" <nytr0x@.yahoo.com> wrote in message
news:OCioZO9aEHA.3692@.TK2MSFTNGP09.phx.gbl...
> I haven't had chance to test your example yet. But wouldn't this just
group
> each AbsenceCode for each Name? In the example given I want two groups
for
> the AbsenceCode S for Fred and 1 group for Jane.
> Thanks,
> N
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:%23VsZSq0aEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Create two groups - one on Name and another one on AbsenceCode. See the
> > attached example that goes against local pubs database.
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> >
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> > tion"
> >
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox4">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>15</ZIndex>
> > <rd:DefaultName>textbox4</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>state</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>14</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>city</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>13</ZIndex>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>Name</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>12</ZIndex>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>address</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="state">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>3</ZIndex>
> > <rd:DefaultName>state</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="city">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>city</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="au_fname">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>au_fname</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!au_fname.Value & " " &
> > Fields!au_lname.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="address">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>address</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!address.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>pubs</DataSetName>
> > <Width>5.375in</Width>
> > <TableGroups>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox12">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>11</ZIndex>
> > <rd:DefaultName>textbox12</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!state.Value & " [" &
> > RunningValue(Fields!state.Value, Count, "table1_Group1") & "
> > authors]"</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox11">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>10</ZIndex>
> > <rd:DefaultName>textbox11</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>9</ZIndex>
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox6">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>8</ZIndex>
> > <rd:DefaultName>textbox6</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> > <GroupExpression>=Fields!state.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox13">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>7</ZIndex>
> > <rd:DefaultName>textbox13</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox14">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>6</ZIndex>
> > <rd:DefaultName>textbox14</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!city.Value & " [" &
> > RunningValue(Fields!city.Value, Count, "table1_Group2") & "
> > authors]"</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox15">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>5</ZIndex>
> > <rd:DefaultName>textbox15</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox16">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>4</ZIndex>
> > <rd:DefaultName>textbox16</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group2">
> > <GroupExpressions>
> > <GroupExpression>=Fields!city.Value</GroupExpression>
> > </GroupExpressions>
> > <Parent>=Fields!state.Value</Parent>
> > </Grouping>
> > </TableGroup>
> > </TableGroups>
> > <TableColumns>
> > <TableColumn>
> > <Width>1.625in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.375in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.25in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.125in</Width>
> > </TableColumn>
> > </TableColumns>
> > </Table>
> > </ReportItems>
> > <Style />
> > <Height>4.75in</Height>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="pubs">
> >
> > <rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>initial catalog=pubs</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>7.125in</Width>
> > <DataSets>
> > <DataSet Name="pubs">
> > <Fields>
> > <Field Name="au_id">
> > <DataField>au_id</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="au_lname">
> > <DataField>au_lname</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="au_fname">
> > <DataField>au_fname</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="phone">
> > <DataField>phone</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="address">
> > <DataField>address</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="city">
> > <DataField>city</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="state">
> > <DataField>state</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="zip">
> > <DataField>zip</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="contract">
> > <DataField>contract</DataField>
> > <rd:TypeName>System.Boolean</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>pubs</DataSourceName>
> > <CommandText>select * from authors</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
> > <BottomMargin>1in</BottomMargin>
> > <Language>en-US</Language>
> > </Report>
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Nytr0x" <nytr0x@.yahoo.com> wrote in message
> > news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > > Hi I've been puzzling over this one for ages, Imagine a dataset like:
> > >
> > > ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> > > 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> > > 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> > > 3-Jan 07:00 3-Jan 15:00 00001 Fred
> > > 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> > > 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> > > 6-Jan 07:00 6-Jan 15:00 00001 Fred
> > > 1-Jan 07:00 1-Jan 15:00 00002 Jane
> > > 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> > > 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> > > 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> > > 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> > > 6-Jan 07:00 6-Jan 15:00 00002 Jane
> > >
> > > I would like to group by consecutive absence codes to generate a
report
> > > like:
> > >
> > > Fred had 2 occurrences of AbsenceCode S
> > > 1-Jan 07:00 to 2-Jan 15:00
> > > 4-Jan 07:00 to 5-Jan 15:00
> > > Jane had 1 occurrence of AbsenceCode S
> > > 2-Jan 07:00 to 5-Jan 15:00
> > >
> > > I have many more codes, and many more records, How can I group by each
> > > occurrence like this?
> > > I've tried using RunningValue with Previous(AbsenceCode) but RS won't
> > allow
> > > nested aggregate functions.
> > >
> > > Thanks in advance,
> > > N.
> > >
> > >
> >
> >
>

Sunday, February 26, 2012

GROUP BY DATEPART issues...

Group by Datepart 30 sec interval?

Group by Datepart 30 sec interval?

Group by Datepart 30 sec interval?

Group By Date?

Group By Date Range

Group By Date Query

Group By Date Query

group by date only

Group By Date in Stored Procedure

Group By Date

Group by Date

Group By Count * >1?

Group By Count

Group By Constant

Group By Consecutive Values

Firehose mode

Blog Archive

About Me