Count minutes from a DateTime column in SQL

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

SQL Server 2008 Forums

Transact-SQL (2008)

Count minutes from a DateTime column in SQL

Author

Topic

MKz71
Starting Member

30 Posts

Posted - 2011-12-13 : 07:00:48

OK... I have a common DateTime column called "dtTimeStamp" and I am trying to create a report that counts all of the minutes from each month. I cannot simply count the occurances of the dtTimeStamp because there may be more than one instance in the same minute. Is this possible? Below is my current query, but I am getting the same number for the "Total Minutes" as I would if I do a COUNT(dtTimeStamp) instead. Any thoughts are welcomed...

SELECT  YEAR(dtTimeStamp) AS 'Year', MONTH(dtTimeStamp) AS 'Month', 
   COUNT(DISTINCT(DATEADD(hh,DATEPART(hh,dtTimeStamp),DATEADD(mi,DATEPART(mi,dtTimeStamp),DATEADD(dd,0,dtTimeStamp))))) AS 'Total Minutes', 
   SUM(CALC_PIPE_WT) / 2000 AS 'Total Tonage', 
   (SUM(CALC_PIPE_WT) / 2000) / 4 AS 'Average Weekly'
FROM TRK.FCOProduction
GROUP BY YEAR(dtTimeStamp), MONTH(dtTimeStamp)
ORDER BY YEAR(dtTimeStamp), MONTH(dtTimeStamp)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 07:05:59

do you mean this?


SELECT   DATEADD(n,DATEDIFF(n,0,dtTimeStamp),0) AS MinPart,
   COUNT(*) AS 'Total Minutes'
FROM TRK.FCOProduction
GROUP BY DATEADD(n,DATEDIFF(n,0,dtTimeStamp),0)
ORDER BY DATEADD(n,DATEDIFF(n,0,dtTimeStamp),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MKz71
Starting Member

30 Posts

Posted - 2011-12-13 : 07:24:47

Sorry... I didn't really explain my query and that is my fault. Every part of the above query is needed, the only thing that doesn't represent a number that I need is the Total Minutes column. COUNT(*) won't work because there may be more than one record in the same minute. I only want to count each minute once.

EXAMPLE:

'2011-12-13 07:23:15.012'
'2011-12-13 07:23:35.418'
'2011-12-13 07:23:52.379'
'2011-12-13 07:24:41.223'
'2011-12-13 07:25:07.193'
--------------------------
Total Distinct Minutes = 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 07:29:39

[code]
SELECT YEAR(dtTimeStamp) AS 'Year', MONTH(dtTimeStamp) AS 'Month',
COUNT(DISTINCT DATEADD(n,DATEDIFF(n,0,dtTimeStamp),0)) AS 'Total Minutes',
SUM(CALC_PIPE_WT) / 2000 AS 'Total Tonage',
(SUM(CALC_PIPE_WT) / 2000) / 4 AS 'Average Weekly'
FROM TRK.FCOProduction
GROUP BY YEAR(dtTimeStamp), MONTH(dtTimeStamp)
ORDER BY YEAR(dtTimeStamp), MONTH(dtTimeStamp)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MKz71
Starting Member

30 Posts

Posted - 2011-12-13 : 07:38:33

OK... This returned a value and it was different from what my other query gave me (which is a good sign)... Can you please explain to me what "COUNT(DISTINCT DATEADD(n,DATEDIFF(n,0,dtTimeStamp),0)) AS 'Total Minutes'" does? I have no clue what the 'n' is and how the minutes are extracted from this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-12-13 : 07:46:43

Simplify this

COUNT(DISTINCT DATEADD(n,DATEDIFF(n,0,dtTimeStamp),0)) AS 'Total Minutes',

to this
COUNT(DISTINCT DATEDIFF(minute, 0, dtTimeStamp)) AS [Total Minutes],

N 56°04'39.26"
E 12°55'05.63"

MKz71
Starting Member

30 Posts

Posted - 2011-12-13 : 07:54:06

Thanks visakh16 and SwePeso... Both of your responses return the same value, and SwePeso's is more understandable (as in I can follow it better). Thanks again for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources