Working with Date month - 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

Working with Date month

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-06 : 08:21:05

Dear Friends,

I have the table with the below details

username login_date_time
abc 04/03/2009 07:45:30 (DateTime Format)
bcd 04/05/2009 08:23:34 (DateTime Format)

I want to fetch the below details

1.Count of today record
2. count of last 24 hours loggedin users
3. Count of this month loggedin user
4. Count of 30 days loggedin user

Please help me to get this

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-06 : 08:38:07

Sounds like homework.
What have you tried this far?

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

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-06 : 08:59:16

Peso

Thank you for your reply

I tried this for getting the today record, and i can get it

select * from users_tracking where ((DAY(login_date_time)=DAY(GETDATE()) And Year(login_date_time)=Year(GETDATE())))

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 23:48:15

try this one


declare @tab table(username varchar(12), login_date_time datetime)
insert into @tab select 
'abc', '04/03/2009 07:45:30' union all select
'bcd', '04/05/2009 08:23:34' union all select
'def','12/23/2009 14:23:34' union all select
'abc', '03/07/2009 07:45:30' union all select
'jth', '03/25/2009 08:23:34' union all select
'fef','3/7/2009  18:23:34'

SELECT COUNT(username) FROM @tab
WHERE login_date_time>=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND login_date_time<DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

SELECT COUNT(username) FROM @tab
WHERE MONTH(login_date_time) = MONTH(GETDATE()) AND YEAR(login_date_time) = YEAR(GETDATE())

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-07 : 00:02:40

1.todaysRecords
select count(*) from @tab where dateadd(d,datediff(dd,0,login_date_time),0) = dateadd(d,datediff(dd,0,getdate()),0)

2.Last 24 Hours Records
select count(*) from @tab where login_date_time between dateadd(d,-1,getdate()) and getdate()

3.This Month Records
SELECT COUNT(username) FROM @tab
WHERE MONTH(login_date_time) = MONTH(GETDATE()) AND YEAR(login_date_time) = YEAR(GETDATE())

2.Last 30 days Records
select count(*) from @tab where login_date_time between dateadd(d,-30,getdate()) and getdate()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:16:19

quote:
Originally posted by dhinasql

Dear Friends,

I have the table with the below details

username login_date_time
abc 04/03/2009 07:45:30 (DateTime Format)
bcd 04/05/2009 08:23:34 (DateTime Format)

I want to fetch the below details

1.Count of today record
2. count of last 24 hours loggedin users
3. Count of this month loggedin user
4. Count of 30 days loggedin user

Please help me to get this

Thanks in advance


SELECT SUM(CASE WHEN datefield >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AND datefield < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) THEN 1 ELSE 0 END) AS TodayCount,
SUM(CASE WHEN datefield >=DATEADD(hh,-24,GETDATE()) AND datefield <= GETDATE() THEN 1 ELSE 0 END) AS 24hrCount,
SUM(CASE WHEN datefield >=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AND datefield < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1) THEN 1 ELSE 0 END) AS MonthCount,
SUM(CASE WHEN datefield >=DATEADD(dd,-30,GETDATE()) AND datefield <=GETDATE() THEN 1 ELSE 0 END) AS 30daysCount
FROM YourTable

matty
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 05:28:25

MonthCount, it should be

SUM(CASE WHEN datefield >=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AND datefield < DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) THEN 1 ELSE 0 END) AS MonthCount,

quote:
Originally posted by visakh16

quote:
Originally posted by dhinasql

Dear Friends,

I have the table with the below details

username login_date_time
abc 04/03/2009 07:45:30 (DateTime Format)
bcd 04/05/2009 08:23:34 (DateTime Format)

I want to fetch the below details

1.Count of today record
2. count of last 24 hours loggedin users
3. Count of this month loggedin user
4. Count of 30 days loggedin user

Please help me to get this

Thanks in advance

SELECT SUM(CASE WHEN datefield >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AND datefield < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) THEN 1 ELSE 0 END) AS TodayCount,
SUM(CASE WHEN datefield >=DATEADD(hh,-24,GETDATE()) AND datefield <= GETDATE() THEN 1 ELSE 0 END) AS 24hrCount,
SUM(CASE WHEN datefield >=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AND datefield < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1) THEN 1 ELSE 0 END) AS MonthCount,
SUM(CASE WHEN datefield >=DATEADD(dd,-30,GETDATE()) AND datefield <=GETDATE() THEN 1 ELSE 0 END) AS 30daysCount
FROM YourTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 02:30:00

yup. or this

SUM(CASE WHEN datefield >=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AND datefield < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0) THEN 1 ELSE 0 END) AS MonthCount,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources