Date Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date Query

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-16 : 11:38:45

I want to write a query that takes data from a table based on each month in a year and tells me the number of hours difference between the 2 dates:

Current Data
ID Date1 Date2
1 2008-01-31 18:02:18.000 2008-01-31 19:10:18.000
1 2008-02-31 18:02:18.000 2008-01-31 20:10:18.000
1 2008-03-31 18:02:18.000 2008-03-31 21:10:18.000
2 2008-01-31 18:02:18.000 2008-01-31 19:10:18.000
2 2008-02-31 18:02:18.000 2008-01-31 20:10:18.000
2 2008-03-31 18:02:18.000 2008-03-31 21:10:18.000

After Query Each month contains the number of hours
ID Jan Feb March
1 1 2 3
2 1 2 3

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-16 : 11:51:32

What is the difference between 2008-02-31 18:02:18.000 2008-01-31 20:10:18.000, especially considering that 2008-02-31 is not a valid date.

What do you do with the leftover minutes with these times? Round up, down, show a fraction, or other?
2008-01-31 18:02:18.000 2008-01-31 19:10:18.000

CODO ERGO SUM

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-16 : 11:54:03

Sorry that date was just a mistype (manually) typed them in as an example.

Leftover minutes would like to round up if possible

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-16 : 12:22:59

This shows how to total up minutes from elapsed time differences. I'm sure you can figure out the rest.


select
	[Month]		= month(ST),
	Total_Minutes	=
		(sum(datediff(day,0,ET-ST))	* 1440.00) +	-- Days
		(sum(datepart(Hour,ET-ST))	* 60.00) +	-- Hours
		(sum(datepart(Minute,ET-ST))	* 1.00) +	-- Minutes
		(sum(datepart(Second,ET-ST))	/ 60.00) +	-- Seconds
		(sum(datepart(millisecond,ET-ST)) /60000.00)	-- Milliseconds
from
	(-- Test Data
	select
		ST = convert(datetime,'2008-01-27 16:02:18.000'),
		ET = convert(datetime,'2008-01-27 19:10:18.047')
	union all
	select
		ST = convert(datetime,'2008-01-31 18:02:18.033'),
		ET = convert(datetime,'2008-01-31 19:10:18.000')
	union all
	select
		ST = convert(datetime,'2008-03-27 15:02:18.000'),
		ET = convert(datetime,'2008-03-27 19:11:18.047')
	union all
	select
		ST = convert(datetime,'2008-03-31 17:02:19.033'),
		ET = convert(datetime,'2008-03-31 21:10:45.000')
	) aa
group by
	month(ST)

Results:


Month       Total_Minutes                 
----------- ----------------------------- 
1           256.00023300
3           497.43356600

(2 row(s) affected)

CODO ERGO SUM

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-16 : 12:42:13

The script below is what I have at the moment which gives me the No Total number of hours.

SELECT ID,SUM(NOHOURS) AS NOHOURS
--INTO USERSNOHOURS
FROM
(
SELECT distinct ID, sum(isnull(DATEDIFF([HOUR], TimeAllocated, TimeLastAccessed),0)) AS NOHOURS
FROM Sessions
WHERE TIMEALLOCATED
GROUP BY ID, TimeAllocated, TimeLastAccessed
)t
GROUP BY ID
ORDER BY 1

What I would like to do is use the above in line with a query such as but adapt it to change "CY" to be JAN. But I dont know how to combine the 2 queries

DECLARE @START_CY DATETIME
SET @START_CY = CONVERT(DATETIME,('01/'+ CONVERT(CHAR(2),MONTH(GETDATE()))+'/'+CONVERT(CHAR(4),YEAR(GETDATE()))))

SELECT @START_CY,(DATEADD(MONTH,0,@START_CY))-1,(DATEADD(MONTH,-12,@START_CY))

-- CREATES MASTER CUSTOMER FILE
SELECT DISTINCT ID,
JAN = SUM(ISNULL(CASE WHEN RECORDDATE BETWEEN (DATEADD(MONTH,-12,@START_CY)) AND (DATEADD(MONTH,0,@START_CY))-1
THEN REVENUE$
ELSE 0
END),
PY = SUM(CASE WHEN RECORDDATE BETWEEN (DATEADD(MONTH,-24,@START_CY)) AND (DATEADD(MONTH,-12,@START_CY))-1
THEN REVENUE$
ELSE 0
END),

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:35:47

can you explain what you're looking for with some sample data and expected output. that would be much easier than providing the query.

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-10-16 : 16:09:40

I want to write a query that takes the data and sums it based on each month in a year and tells me the number of hours difference between the 2 dates:

Current Data
ID Date1 Date2
1 2008-01-31 18:02:18.000 2008-01-31 19:10:18.000
1 2008-02-31 18:02:18.000 2008-02-31 20:10:18.000
1 2008-03-31 18:02:18.000 2008-03-31 21:10:18.000
2 2008-01-31 18:02:18.000 2008-01-31 19:10:18.000
2 2008-02-31 18:02:18.000 2008-02-31 20:10:18.000
2 2008-03-31 18:02:18.000 2008-03-31 21:10:18.000

After Query Each month contains the number of hours
ID Jan Feb March
1 1 2 3
2 1 2 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources