Having difficulty with grouping

[mlm]

I have a couple of tables where I need to be able to place some filters and run a query. The problem is, I can't think of an easy way to group it.

Filters:
- Date Range
- User ID's (needs to be grouped if multiple)

Groupings on date column:
- Daily (no grouping required)
- Weekly
- Monthly
- Quarterly
- Yearly

I have figured that I could dump everything without grouping into a DataTable and go from there, but the code got ugly really quickly, and I haven't even tackled trying to group the result by userID.

Is there an easier way I can do this with SQL, or am I stuck with manipulating the DataTable?

 

[KLin]

Originally posted by: mlm
I have a couple of tables where I need to be able to place some filters and run a query. The problem is, I can't think of an easy way to group it.

Filters:
- Date Range
- User ID's (needs to be grouped if multiple)

Groupings on date column:
- Daily (no grouping required)
- Weekly
- Monthly
- Quarterly
- Yearly

I have figured that I could dump everything without grouping into a DataTable and go from there, but the code got ugly really quickly, and I haven't even tackled trying to group the result by userID.

Is there an easier way I can do this with SQL, or am I stuck with manipulating the DataTable?

Datepart function

 

[mlm]

Thanks!

Is there a way to take care of the grouping without having to build an intermediate table/view?

This is what I'm doing now:

create view tbl2 as
SELECT
userID
,(datepart(q, Dt) - ((2007 - datepart(yy, Dt)) * 4)) as 'quarter'
,column1
,column2
FROM tbl

SELECT
userID
,quarter
,sum(column1)
,sum(column2)
FROM tbl2
group by userID, quarter

If I try to combine what I'm doing in the second statement with the first, it complains that 'quarter' isn't a valid column name.

 

[KLin]

Create view tbl2 AS
SELECT UserID, Quarter, Column1Total, Column2Total
FROM
(
SELECT UserID, (datepart(q, Dt) - ((2007 - datepart(yy, Dt)) * 4)) as quarter, Sum(Column1) As Column1Total, Sum(Column2) As Column2Total
FROM tbl2
Group By UserID, (datepart(q, Dt) - ((2007 - datepart(yy, Dt)) * 4))
) sub

Try that.