group by just date not time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

group by just date not time

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-14 : 16:12:24

This is my stored procedure and i am trying to insert the data into a table , i need to get the data grouped by only date and not time ,
can any one suggest me how i could do that ,

thanks..

----------------------------------------------------------------------------
USE [ASWTrans]
GO

/****** Object: StoredProcedure [dbo].[PROC_EC_JOB_COUNT_USER] Script Date: 01/14/2010 13:09:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PROC_EC_JOB_COUNT_USER]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert into
dbo.DW_T_JOB_COUNT_USER
(
EC_LAUNCHED_BY_USER,
EC_START_TIME,
EC_JOB_COUNT

)
Select
LAUNCHED_BY_USER,
START_TIME,
COUNT(DISTINCT(JOB_ID))
From DW_T_EC_JOB
GROUP BY START_TIME,LAUNCHED_BY_USER

END

GO

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-14 : 16:15:17

[code]Select
LAUNCHED_BY_USER,
dateadd(d, datediff(d, 0, START_TIME), 0)
COUNT(DISTINCT(JOB_ID))
From DW_T_EC_JOB
GROUP BY LAUNCHED_BY_USER,dateadd(d, datediff(d, 0, START_TIME), 0)[/code]

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-14 : 16:36:49

THANK YOU!

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-14 : 16:47:46

Similarly , can you please let me know how i can group by start time(only date) , the total run time and avg runtime also...

here is the syntax i used for calculating the run time ..

please help ..

--------------------------------------------------------
Select
RESOURCE_NAME,
START_TIME,
SUM(DATEDIFF(second,RUNNABLE_TIME,START_TIME)),
AVG(DATEDIFF(second,RUNNABLE_TIME,START_TIME))
From DBO.DW_T_EC_JOB WHERE (STEP_INDEX IS NOT NULL) AND (RUNNABLE_TIME IS NOT NULL)
group by RESOURCE_NAME,START_TIME

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-01-14 : 22:18:16

Hope if i have understood your requirement this is how it has to be.

Select
START_TIME,
SUM(DATEDIFF(second,RUNNABLE_TIME,START_TIME)),
AVG(DATEDIFF(second,RUNNABLE_TIME,START_TIME))
From DBO.DW_T_EC_JOB WHERE (STEP_INDEX IS NOT NULL) AND (RUNNABLE_TIME IS NOT NULL)
group by START_TIME

Lets unLearn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 23:56:05

quote:
Originally posted by rds207

Similarly , can you please let me know how i can group by start time(only date) , the total run time and avg runtime also...

here is the syntax i used for calculating the run time ..

please help ..

--------------------------------------------------------
Select
RESOURCE_NAME,
START_TIME,
SUM(DATEDIFF(second,RUNNABLE_TIME,START_TIME)),
AVG(DATEDIFF(second,RUNNABLE_TIME,START_TIME))
From DBO.DW_T_EC_JOB WHERE (STEP_INDEX IS NOT NULL) AND (RUNNABLE_TIME IS NOT NULL)
group by RESOURCE_NAME,START_TIME

as suggested before change START_TIME to dateadd(d, datediff(d, 0, START_TIME), 0)

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 02:45:26

I recommend not using an abbreviation for the first parameter for DateAdd / DateDiff functions - the full name "Day" is more easily recognised.

Do you think "m" would be Month? Minute? or Millisecond?

Does Y stand for year? (Answer=no!)

Month, Minute, Millisecond, Week, Day, Year, Quarter are all valid first parameters.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 04:28:18

quote:
Originally posted by Kristen

I recommend not using an abbreviation for the first parameter for DateAdd / DateDiff functions - the full name "Day" is more easily recognised.

Do you think "m" would be Month? Minute? or Millisecond?

Does Y stand for year? (Answer=no!)

Month, Minute, Millisecond, Week, Day, Year, Quarter are all valid first parameters.

This is what BOL says. See how confusing it to use


Datepart  Abbreviations  
year         yy, yyyy 
 
quarter      qq, q 
 
month        mm, m 
 
dayofyear    dy, y 
 
day          dd, d 
 
week         wk, ww 
 
hour         hh 
 
minute       mi, n 
 
second       ss, s 
 
millisecond  ms

You can use
d and dd for day
m and mm for month
q and qq for Quarter
s and ss fro second

so one can easily conclude
y and yy for year
h and hh for hour

The abbreviation n for minute is the most confused one

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources