Grouping by date

Hi,
I need to group the result set on a weekly basis.
The input has only the start date and end date and i need to group the
result on weekly basis...
Thanks in advance for ur advice...I generally do this in the query...
add a column to the select statement
datename( wk,end_date) as Week
then group on that... If you span years add year and week instead of just
week..
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:338BCC62-7FFA-46B1-ABCE-91EB0C471DD2@.microsoft.com...
> Hi,
> I need to group the result set on a weekly basis.
> The input has only the start date and end date and i need to group the
> result on weekly basis...
> Thanks in advance for ur advice...
>|||Thanks Wayne ,
That really helped me...
now im caught in a new issue.
when i insert a group in the table for the weekenddate.the alternate
coloring of rows has disappeared...
i cant get where im going wrong...
im grouping the record based on the weekend date...
Thanks
"Wayne Snyder" wrote:
> I generally do this in the query...
> add a column to the select statement
> datename( wk,end_date) as Week
> then group on that... If you span years add year and week instead of just
> week..
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "CCP" <CCP@.discussions.microsoft.com> wrote in message
> news:338BCC62-7FFA-46B1-ABCE-91EB0C471DD2@.microsoft.com...
> > Hi,
> > I need to group the result set on a weekly basis.
> > The input has only the start date and end date and i need to group the
> > result on weekly basis...
> >
> > Thanks in advance for ur advice...
> >
> >
>
>

Grouping based on input parameters

I'm not even sure if this is possible. Our requirement is that the user can input whether to view hourly, daily, or monthly data. Originally I had created three separate reports, but I've been asked to try again.

Is it possible to specify when grouping to group by the whole operation date, or group by days, or group by weeks?

Thanks.Its possible if the grouping is done in logical way say days->weeks->months->years.
Trying to say that if you want to group by Monday or tuesday then the answer is no.
Now what you do is in one report you start from inside grouping by days, then by weeks, then by months and then by years.
Make a parameter through which you can suppress the section accordingly.
Hope you can understand my theory.|||Thank you for your explanation. That was a lot easier than I thought!

:)|||I've been re-doing one of my other reports and noticed that my solution still has one problem with it. I have grouped by (from outer level) : Year, Month, Product Type.

When I view the product type by month, all are listed as one would expect. However, when viewing by year they are not grouped correctly, ie :

Jan 2005
prod x - 5
prod y - 3
prod z - 4

Feb 2005
prod x - 1
prod y - 7

So when viewing by year, I would like to see Prod x - 6. Instead, I get the two individual listings of prod x, etc.

Is there a way around this?

Thanks.|||You should expect only one entry per year if you have done gouping by year->month->type.
Right click on Group Producttype and click on change group and make sure that Order is in ascending and not Original order.

B.thakkar|||Thanks, though everything is grouped with order ascending. I think the problem is that they are essentially still grouped by month (we just choose not to acknowledge/display this by suppressing the header).

Group by Query Insert the Results into Different Table

I am trying to do a select statement and input the result to a different table how can this be done in one step? Now I am just coping to excel and importing back in this is a real pain.

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

Here it is,

Code Snippet

--For One Time

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate into TargetTable from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

--For Multiple Times

Create table Targettable

(

Trip int,

Destination varchar(10),

LeaveDate datetime,

LeaveTime varchar(10),

ReturnDate datetime,

ReturnTime varchar(10),

Comment text,

RescheduleDate datetime

)

Insert into Targettable

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

|||

Just create a table with fields that match those of your query. Then you can run

Code Snippet

INSERT INTO newTable

Select Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate from Trips

Group by Trip, Destination, LeaveDate, LeaveTime, ReturnDate, ReturnTime, Comment, RescheduleDate

Order by LeaveDate, LeaveTime, Trip

|||Sweet Thanks!!!

group by qst

In the following query I want to return as a last column the aggregate max
value of the OrderInList field.
input >>
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by *
order by OrderInList
output >>
Column 'tbl_activities.Activity_ID' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY clause.
Can I achieve what I want by including the extra MaxOrderInList column
instead of returning an output param?
TIAalto wrote:
> In the following query I want to return as a last column the aggregate max
> value of the OrderInList field.
> input >>
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by *
> order by OrderInList
> output >>
> Column 'tbl_activities.Activity_ID' is invalid in the select list because
it
> is not contained in either an aggregate function or the GROUP BY clause.
> Can I achieve what I want by including the extra MaxOrderInList column
> instead of returning an output param?
> TIA
>
Don't be lazy, specify your field list and stop using SELECT *. Any
non-aggregate field that is listed in your field list must also be
included in your GROUP BY clause, like this:
SELECT col1, col2, col3, MAC(col4)
FROM table
GROUP BY col1, col2, col3
It's generally considered bad practice to use "SELECT *".|||You can't GROUP BY *, and you shouldn't use SELECT * in production code
anyway.
It's really not that hard to generate a list of the columns in the table,
however what do you expect to happen here? If you have data like this:
ActivityID OrderInList
1 1
1 2
What is your desired output?
If you group by ALL columns, then your max(OrderInList) is meaningless.
Try it:
CREATE TABLE dbo.floob
(
ActivityID INT,
OrderInList INT
);
SET NOCOUNT ON;
INSERT dbo.floob(ActivityID,OrderInList)
SELECT 1,1
UNION ALL
SELECT 1,2;
SELECT
ActivityID, OrderInList,
MAXOrderInList = MAX(OrderInList)
FROM
dbo.floob
GROUP BY
ActivityID, OrderInList;
-- maybe what you meant was:
SELECT
ActivityID,
MAXOrderInList = MAX(OrderInList)
FROM
dbo.floob
GROUP BY
ActivityID;
GO
DROP TABLE dbo.floob;
GO
Without proper DDL, sample data and desired results, I have little else to
offer, except that grouping by all columns in the table doesn't make any
sense.
http://www.aspfaq.com/5006
"alto" <altodorov@.hotmail.com> wrote in message
news:%23VHcK%23emGHA.2204@.TK2MSFTNGP03.phx.gbl...
> In the following query I want to return as a last column the aggregate max
> value of the OrderInList field.
> input >>
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by *
> order by OrderInList
> output >>
> Column 'tbl_activities.Activity_ID' is invalid in the select list because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> Can I achieve what I want by including the extra MaxOrderInList column
> instead of returning an output param?
> TIA
>|||Last column is to return the same value for all records, like this
ActivityID, other cols..., MaxOrderInList
1 1,... 2
1 2,... 2
And BTW, in response to Tracy I'd already tried w/ all the fields in the
goup by - it wouldn't allow me to include an aggregate column in the list.
Again I could return the max OrderInList in an output param; I'm just asking
if the alternative above is possible.
Txs!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uX8gRLfmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> You can't GROUP BY *, and you shouldn't use SELECT * in production code
> anyway.
> It's really not that hard to generate a list of the columns in the table,
> however what do you expect to happen here? If you have data like this:
> ActivityID OrderInList
> 1 1
> 1 2
> What is your desired output?
> If you group by ALL columns, then your max(OrderInList) is meaningless.
> Try it:
> CREATE TABLE dbo.floob
> (
> ActivityID INT,
> OrderInList INT
> );
> SET NOCOUNT ON;
> INSERT dbo.floob(ActivityID,OrderInList)
> SELECT 1,1
> UNION ALL
> SELECT 1,2;
> SELECT
> ActivityID, OrderInList,
> MAXOrderInList = MAX(OrderInList)
> FROM
> dbo.floob
> GROUP BY
> ActivityID, OrderInList;
> -- maybe what you meant was:
> SELECT
> ActivityID,
> MAXOrderInList = MAX(OrderInList)
> FROM
> dbo.floob
> GROUP BY
> ActivityID;
> GO
> DROP TABLE dbo.floob;
> GO
>
> Without proper DDL, sample data and desired results, I have little else to
> offer, except that grouping by all columns in the table doesn't make any
> sense.
> http://www.aspfaq.com/5006
>
> "alto" <altodorov@.hotmail.com> wrote in message
> news:%23VHcK%23emGHA.2204@.TK2MSFTNGP03.phx.gbl...
>|||"alto" <altodorov@.hotmail.com> wrote in message
news:OT2ZkSfmGHA.4868@.TK2MSFTNGP04.phx.gbl...
> Last column is to return the same value for all records, like this
> ActivityID, other cols..., MaxOrderInList
> 1 1,... 2
> 1 2,... 2
> And BTW, in response to Tracy I'd already tried w/ all the fields in the
> goup by - it wouldn't allow me to include an aggregate column in the list.
What doesn't "wouldn't allow me" mean? Did you get an error message? What
was it? What code did you try?
I think you would have to do this with a correlated subquery, not group by.
For example, using the DDL and sample data I provided earlier:
SELECT
ActivityID,
OrderInList,
MaxOrderInList = (
SELECT MAX(OrderInList)
FROM dbo.floob
WHERE ActivityID = f.ActivityID
)
FROM
dbo.floob f;
A|||>> Last column is to return the same value for all records [sic], like this ..<<
Try a scalar subquery expression
SELECT a, b, c, ...
(SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
FROM Foobar;|||I mean I tried this query
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated ,
max(OrderInList)
order by OrderInList
w/ this result:
Server: Msg 144, Level 15, State 1, Line 26
Cannot use an aggregate or a subquery in an expression used for the group by
list of a GROUP BY clause.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJsIHWfmGHA.856@.TK2MSFTNGP03.phx.gbl...
> "alto" <altodorov@.hotmail.com> wrote in message
> news:OT2ZkSfmGHA.4868@.TK2MSFTNGP04.phx.gbl...
> What doesn't "wouldn't allow me" mean? Did you get an error message?
> What was it? What code did you try?
> I think you would have to do this with a correlated subquery, not group
> by. For example, using the DDL and sample data I provided earlier:
> SELECT
> ActivityID,
> OrderInList,
> MaxOrderInList = (
> SELECT MAX(OrderInList)
> FROM dbo.floob
> WHERE ActivityID = f.ActivityID
> )
> FROM
> dbo.floob f;
>
> A
>|||Bingo, it worked:
input >>
select Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated,
(select max(OrderInList) from tbl_activities where Center_ID = 81 and
Fiscal_Year = '2006-2007') as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated ,
--(select max(OrderInList) from tbl_activities where Center_ID = 81 and
Fiscal_Year = '2006-2007')
OrderInList
order by OrderInList
output (see last column returned) >>
1309 81 1 NULL activite 1 emplacement 1 NULL NULL NULL NULL 2006-2007 1
1 2006-06-19 00:00:00.000 2006-06-24 00:00:00.000 2006-06-27 09:24:57.007 2
1310 81 1 NULL activite 2 emplacement 2 NULL NULL NULL NULL 2006-2007 1
4 2006-06-05 00:00:00.000 2006-06-17 00:00:00.000 2006-06-27 09:25:21.550 2
Txs!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151418422.623163.134150@.x69g2000cwx.googlegroups.com...
> Try a scalar subquery expression
> SELECT a, b, c, ...
> (SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
> FROM Foobar;
>|||>I mean I tried this query
> select *, max(OrderInList) as MaxOrderInList
And like I said before, selecting all columns and then aggregating one of
them doesn't make sense.
A|||alto wrote:
> I mean I tried this query
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by Activity_ID,
> Center_ID,
> Category_ID,
> Type_of_Activity ,
> [Description] ,
> Location_Activity ,
> Date_Activity ,
> Total_number_of_attendies ,
> Purpose_activity ,
> Fully_organized_by_org ,
> Partial_assistance_of_org ,
> BeginingDate_FY ,
> Fiscal_Year ,
> memo ,
> IsRequest ,
> ProposedGuest ,
> ActivityType ,
> DateStart ,
> DateEnd ,
> DateCreated ,
> max(OrderInList)
> order by OrderInList
> w/ this result:
> Server: Msg 144, Level 15, State 1, Line 26
> Cannot use an aggregate or a subquery in an expression used for the group
by
> list of a GROUP BY clause.
>
**THINK** about what you're doing here. Your aggregate value (MAX) is
calculated by grouping together like values from the table. The value
of MAX is unknown until that grouping has been done, so HOW can you
include it in the grouping criteria?
As I said before, STOP USING SELECT *, be specific about the fields that
you want returned by the SELECT, and include those fields (excluding the
aggregate) in your GROUP BY.