Group By question

Hello!
I would like to get my result grouped by 15 min or 30 min instead of
1 min like I have now, see below.
Can anyone help me ? Any ideas?
select count(*)AS Count_Case, MIN(CAS_DT_OPE) AS TIME_DATE
from s_case (nolock)
where CAS_USG_PK_OPEN=255543
AND day(CAS_DT_OPE)=day(getdate())
AND month(CAS_DT_OPE)=month(getdate())
AND year(CAS_DT_OPE)=year(getdate())
GROUP BY datepart(mi,CAS_DT_OPE)
order by 2
****Result*******************
2 2006-04-27 06:52:54.783
1 2006-04-27 07:22:54.973
1 2006-04-27 07:23:56.493
2 2006-04-27 08:05:04.217
1 2006-04-27 08:07:41.723
1 2006-04-27 08:27:04.600
1 2006-04-27 08:29:37.840
1 2006-04-27 08:33:13.347
1 2006-04-27 08:39:23.620
1 2006-04-27 08:41:35.240
1 2006-04-27 09:12:00.930
2 2006-04-27 09:13:06.540
1 2006-04-27 09:16:34.030
1 2006-04-27 09:18:00.717
2 2006-04-27 09:26:51.957
1 2006-04-27 09:31:31.507
1 2006-04-27 09:53:32.873
regards
MikeFor group by 15 minutes use
GROUP BY datepart(mi,CAS_DT_OPE) /15
For group by 30 minutes use
GROUP BY datepart(mi,CAS_DT_OPE) /30|||Below is an example using an interval table variable. This technique has
the benefit of reporting intervals even when no related data exists. If
this is something you do often, consider creating a permanent table for this
purpose:
SET NOCOUNT ON
DECLARE @.IntervalInSeconds int
DECLARE @.StartTime datetime
DECLARE @.EndTime datetime
---
-- **** specify interval size and range below ***
---
SET @.IntervalInSeconds = 900 -- 15 minutes
SET @.StartTime = '20060427 00:00:00'
SET @.EndTime = '20060428 00:00:00'
---
-- create and load Intervals table
DECLARE @.Intervals TABLE
(
StartTime datetime NOT NULL
PRIMARY KEY,
EndTime datetime NOT NULL
)
WHILE @.StartTime < @.EndTime
BEGIN
INSERT INTO @.Intervals VALUES(@.StartTime, DATEADD(ss,
@.IntervalInSeconds, @.StartTime))
SET @.StartTime = DATEADD(ss, @.IntervalInSeconds, @.StartTime)
END
SELECT
COUNT(*) AS Count_Case,
i.StartTime AS IntervalStart,
MIN(CAS_DT_OPE) AS TIME_DATE
FROM s_case (NOLOCK)
JOIN @.Intervals i ON
s_case.CAS_DT_OPE >= i.StartTime
AND s_case.CAS_DT_OPE < i.EndTime
WHERE
CAS_USG_PK_OPEN=255543
AND CAS_DT_OPE >= CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)
AND CAS_DT_OPE < CAST(CONVERT(char(8), GETDATE(), 112) AS datetime) + 1
GROUP BY
i.StartTime
ORDER BY 3
Hope this helps.
Dan Guzman
SQL Server MVP
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1146139098.374595.30350@.v46g2000cwv.googlegroups.com...
> Hello!
> I would like to get my result grouped by 15 min or 30 min instead of
> 1 min like I have now, see below.
> Can anyone help me ? Any ideas?
> select count(*)AS Count_Case, MIN(CAS_DT_OPE) AS TIME_DATE
> from s_case (nolock)
> where CAS_USG_PK_OPEN=255543
> AND day(CAS_DT_OPE)=day(getdate())
> AND month(CAS_DT_OPE)=month(getdate())
> AND year(CAS_DT_OPE)=year(getdate())
> GROUP BY datepart(mi,CAS_DT_OPE)
> order by 2
> ****Result*******************
> 2 2006-04-27 06:52:54.783
> 1 2006-04-27 07:22:54.973
> 1 2006-04-27 07:23:56.493
> 2 2006-04-27 08:05:04.217
> 1 2006-04-27 08:07:41.723
> 1 2006-04-27 08:27:04.600
> 1 2006-04-27 08:29:37.840
> 1 2006-04-27 08:33:13.347
> 1 2006-04-27 08:39:23.620
> 1 2006-04-27 08:41:35.240
> 1 2006-04-27 09:12:00.930
> 2 2006-04-27 09:13:06.540
> 1 2006-04-27 09:16:34.030
> 1 2006-04-27 09:18:00.717
> 2 2006-04-27 09:26:51.957
> 1 2006-04-27 09:31:31.507
> 1 2006-04-27 09:53:32.873
> regards
> Mike
>|||Didn't do the trick...
/Mike|||Thanks Dan that did the trick!!
/Mike|||On 27 Apr 2006 04:58:18 -0700, McA wrote:

>Hello!
>I would like to get my result grouped by 15 min or 30 min instead of
>1 min like I have now, see below.
>Can anyone help me ? Any ideas?
GROUP BY DATEDIFF(minute, '20060101', CAS_DT_OPE) / 15
Hugo Kornelis, SQL Server MVP|||Thanks Hugo that worked 2!!
And very easy to!!
/McA

Group by month

Hello
I got this table (for testing)... I'm struggeling to create a view that
displays the number of "entries" each person has for each month. Maybe you
guys could show a proper way of dealing with this.
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-01-01 2005-01-31 A 5
2005-02-01 2005-02-28 A 4
2005-01-01 2005-01-31 B 2
*/
DROP TABLE #TestTry this:
SELECT DATEADD(MONTH,mth,'20000101') AS startdate,
DATEADD(MONTH,mth,'20000131') AS enddate,
person, COUNT(*) AS cnt
FROM
(SELECT DATEDIFF(MONTH,'20000101',datecreated) AS mth, person
FROM #Test) AS T
GROUP BY mth, person ;
If you want to include rows in the result for months that have no data
in your table then join the above query with a calendar table or
numbers table to generate the extra months.
David Portas
SQL Server MVP
--|||Thanx for posting DDL and INSERT's:
select
cast (convert (char (6), DateCreated, 112) + '01' as datetime) StartDate
, dateadd (dd, -1, dateadd (mm, 1, convert (char (6), DateCreated, 112) +
'01')) EndDate
, Person
, count (*)
from
#Test
group by
cast (convert (char (6), DateCreated, 112) + '01' as datetime)
, dateadd (dd, -1, dateadd (mm, 1, convert (char (6), DateCreated, 112) +
'01'))
, Person
order by
Person
, StartDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:uGIPKzOyFHA.1856@.TK2MSFTNGP12.phx.gbl...
Hello
I got this table (for testing)... I'm struggeling to create a view that
displays the number of "entries" each person has for each month. Maybe you
guys could show a proper way of dealing with this.
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-01-01 2005-01-31 A 5
2005-02-01 2005-02-28 A 4
2005-01-01 2005-01-31 B 2
*/
DROP TABLE #Test|||Try,
-- for each year and month
SELECT
min(cast(convert(varchar(6), Datecreated, 112) + '01' as datetime)) as
Startdate,
dateadd(day, -1, dateadd(month, 1, min(cast(convert(varchar(6),
Datecreated, 112) + '01' as datetime)))) as Enddate,
Person,
count(*) as [Count]
FROM
#Test
group by
convert(varchar(6), Datecreated, 112),
Person
go
AMB
"Lasse Edsvik" wrote:

> Hello
> I got this table (for testing)... I'm struggeling to create a view that
> displays the number of "entries" each person has for each month. Maybe you
> guys could show a proper way of dealing with this.
>
> CREATE TABLE #Test (
> SomePk int identity(1,1) NOT NULL,
> Person char(1) NOT NULL,
> Datecreated datetime NOT NULL
> )
>
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
>
> SELECT * FROM #Test
>
> /*
> Desired result:
> Startdate Enddate Person Count
> 2005-01-01 2005-01-31 A 5
> 2005-02-01 2005-02-28 A 4
> 2005-01-01 2005-01-31 B 2
>
> */
> DROP TABLE #Test
>
>|||This is pretty much the same as Tom's one, but with less conversions
SELECT
MonthAdded AS StartDate
, DATEADD( d , -1 , DATEADD( m , 1 , MonthAdded ) ) AS EndDate
, Person
, Total AS Count
FROM
(
SELECT
CONVERT( DATETIME , CONVERT( CHAR(7) , Datecreated , 121 ) + '-01' ,
121 ) AS MonthAdded
, Person
, COUNT(*) AS Total
FROM
#Test
GROUP BY
CONVERT( DATETIME , CONVERT( CHAR(7) , Datecreated , 121 ) + '-01' ,
121 )
, Person
) vwResults
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:uGIPKzOyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Hello
> I got this table (for testing)... I'm struggeling to create a view that
> displays the number of "entries" each person has for each month. Maybe you
> guys could show a proper way of dealing with this.
>
> CREATE TABLE #Test (
> SomePk int identity(1,1) NOT NULL,
> Person char(1) NOT NULL,
> Datecreated datetime NOT NULL
> )
>
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-05')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-01-06')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-11')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-14')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-15')
> INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-02-16')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-01')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-04')
> INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-01-05')
>
> SELECT * FROM #Test
>
> /*
> Desired result:
> Startdate Enddate Person Count
> 2005-01-01 2005-01-31 A 5
> 2005-02-01 2005-02-28 A 4
> 2005-01-01 2005-01-31 B 2
>
> */
> DROP TABLE #Test
>
>

GROUP BY

Hello!
I am using following dynamic stored procedure to return search results.
Evrything works fine... Now I would like to group results by IsFeatured
field, but I am receiving error message: "Column 'Table1.Id' is invalid in
the select list because it is not contained in either an aggregate function
or the GROUP BY clause."
Please help me!
James
----
CREATE PROCEDURE [_Search]
@.PriceMin Money = 0,
@.PriceMax Money = 0,
@.DistanceMin int = 0,
@.DistanceMax int = 0,
@.HighwayIds varchar(50) = NULL
AS
DECLARE @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql = 'SELECT
Table1.* FROM [Table1]'
IF @.HighwayIds IS NOT NULL
SELECT @.sql = @.sql + 'JOIN IntlistToTable(@.HighwayIds) S ON
Table1.HighwayId = S.number '
SELECT @.sql = @.sql + 'WHERE 1 = 1 '
IF (@.PriceMin <> 0) AND (@.PriceMax <> 0)
SELECT @.sql = @.sql + ' AND (Price Between @.PriceMin And @.PriceMax)'
IF (@.PriceMin = 0) AND (@.PriceMax <> 0)
SELECT @.sql = @.sql + ' AND (Price Between 0 And @.PriceMax)'
IF (@.DistanceFromMKADMin <> 0) AND (@.DistanceFromMKADMax <> 0)
SELECT @.sql = @.sql + ' AND (Distance Between @.DistanceMin And
@.DistanceMax)'
IF (@.DistanceFromMKADMin = 0) AND (@.DistanceFromMKADMax <> 0)
SELECT @.sql = @.sql + ' AND (Distance Between 0 And @.DistanceMax)'
SELECT @.sql = @.sql + ' And (IsActive = 1)'
SELECT @.paramlist = '@.PriceMin Money,
@.PriceMax Money,
@.DistanceMin int,
@.DistanceMax int,
@.HighwayIds varchar(50)'
EXEC sp_executesql @.sql, @.paramlist, @.PriceMin, @.PriceMax, @.DistanceMin,
@.DistanceMax, @.HighwayIdsPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Try using SET instead of SELECT in assignments, so it will look like
SQL/PSM.
Why do you write such procedural code? Think about this for two
seconds:
IF (@.price_min <> 0) AND (@.price_max <> 0)
SELECT @.sql = @.sql + ' AND (price BETWEEN @.price_min AND @.price_max)'
IF (@.price_min = 0) AND (@.price_max <> 0)
SELECT @.sql = @.sql + ' AND (price BETWEEN 0 AND @.price_max)'
Have you ever worked with a compiled language like SQL before? I see
that you confuse fields and columns, so you are VERY new to SQL.
Did you notice that you used the proprietary MONEY data type that has
funny arithmetic results? Then you use INTEGER in the predicates
(above) to force a type conversion. You give a procedure a useless
name with a leading underscore to destroy portability and create
maintenance problems. In the United States we have highway numbers
and not Highwayid; always use the standard terminology.
Of course you never try to pass a table as a list. You would load a
table with the highway numbers. Did you mean somethign like this?
CREATE PROCEDURE SearchForFoobars -- better name?
(@.price_MIN DECIMAL (12,4) = 0.00,
@.price_MAX DECIMAL (12,4) = 0.00,
@.distance_MIN INTEGER = 0,
@.distance_MAX INTEGER = 0)
AS
BEGIN - Validate parameters
IF @.price_min < 0.00 RAISERROR (..);
IF @.price_max < 0.00 RAISERROR (..);
IF @.distance_min < 0 RAISERROR (..);
IF @.distance_max < 0 RAISERROR (..);
SELECT Table1.* -- never use * in production code
FROM Table1, -- need a real name
WHERE Table1.highway_nbr
IN (SELECT highway_nbr FROM HighwayList)
AND price BETWEEN @.price_min AND @.price_max
AND distance BETWEEN @.distance_min AND @.distance_max
AND is_active = 1; -- flags! Just like assembly language
END;
Another alternative would be to put the highways into the parameter
list.
CREATE PROCEDURE SearchForFoobars
(..h1 INTEGER, h2 INTEGER,.., hn INTEGER)
..
WHERE Table1.highway_nbr
IN (SELECT COALESCE(h1, 0)
UNION ALL COALESCE(h2, 0)
.
SELECT COALESCE(hn, 0)) AS S(highway_nbr)
Now the proc can be compiled and maintained by a SQL programmer.|||Hi Joe,
A bit unrelated to the original post, but seeing your discussion of the
parameters to be passed to the stored proc, and a perceived deficiency
in my view recently:
Is there (or has there ever been proposed) a means of applying
constraints to parameters of procedures? By which I mean, a great many
stored procs get passed primary keys for particular tables, numeric
parameters which must fit within certain ranges, etc. I'd love to be
able to have:
Create Procedure Blah
@.BorisPK int,
@.LowerRange int,
@.HigherRange int
as
..
go
alter procedure Blah add constraint
Blah_BorisPK foreign key
(
@.BorisPK
)
references Boris
(
PK
)
go
alter procedure Blah add constraint
Blah_RangeWellDefined check
(
@.LowerRange > 0 and
@.HigherRange < 100 and
@.LowerRange <= @.HigherRange
)
go
The constraint system seems so good, I'd love to use it in more places
(and avoid having to write lots of checks within my SP). So, has this
ever been considered?
Damien

Gridview and DropDownList

Hello:

I have add a DropDownList to my GridView and binded the dropdownlist to a field from a select statement in the SQLDataSource. I have EnabledEditing for my GridView. The GridView is populated with information from the select statement. Some of the information returned from the select statement is null. The field where the dropdownlist is binded it is null in some cases and does not have a value that is in the dropdownlist so I get and error when I attempt to do an update.

'DropDownList1' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

Is there a way to get around this besides initializing all the columns in the table that are going to be binded to a dropdownlist to a value in the dropdownlist?

You can exclude the NULL from your SELECT statement by adding a where clause like: SELECT thefield FROm yourTable WHERE thefield IS NOT NULL. You don't need to worry about this NULL value for your test..