query day date week 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

query day date week month

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-24 : 10:59:33

Hi Friends

I have one table Request

Tbl_Request

ReqId
Topic
Description
Createdate
Modifydate

Result

with LastDay
week
Month
All time

Thanks and Regards
E.sambath kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 11:02:32

do you mean shocounts grouped by day,week month?

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-24 : 11:08:20

quote:
Originally posted by visakh16

do you mean shocounts grouped by day,week month?

Hi visakh16,

yes i need the result day,week month

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-24 : 11:24:06

quote:
Originally posted by visakh16

do you mean shocounts grouped by day,week month?

anybody help this query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 12:48:33

quote:
Originally posted by esambath

quote:
Originally posted by visakh16

do you mean shocounts grouped by day,week month?

anybody help this query

can you show expected output format?

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-24 : 15:14:50

quote:
Originally posted by visakh16

quote:
Originally posted by esambath

quote:
Originally posted by visakh16

do you mean shocounts grouped by day,week month?

anybody help this query

can you show expected output format?

OUTPUT

Last day Record
RequestName Date

This week Record
RequestName Date

This month Record
RequestName Date

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 16:51:44

Let me make this into a diagram so everyone can see the Picture in my head:
[CODE]
Tbl_Request

ReqId Topic Description Createdate Modifydate
-------- -------- ------------- ------------- ----------------
1 A Desc for A 2008-1-1 2008-10-10
2 B Desc for B 2008-2-1 2008-10-20
3 C Desc for C 2008-3-1 2008-10-21
4 D Desc for D 2008-4-1 2008-10-24

OUTPUT:

Last Day Record Date
---------------- -------------
Desc for D 2008-10-24

This week record Date
----------------- -------------
Desc for B 2008-10-20
Desc for C 2008-10-21
Desc for D 2008-10-24

This month record Date
----------------- -------------
Desc for A 2008-10-10
Desc for B 2008-10-20
Desc for C 2008-10-21
Desc for D 2008-10-24

[/CODE]
solution:
[CODE]declare @tab1 table(REQID INT,
TOPIC VARCHAR(50),
DESCRIPTION VARCHAR(50),
CREATEDATE DATETIME, MODIFYDATE DATETIME)

INSERT INTO @TAB1
select 1,'A','Desc for A','2008-1-1','2008-10-10' UNION ALL
select 2,'B','Desc for B','2008-2-1','2008-10-20' UNION ALL
select 3,'C','Desc for C','2008-3-1','2008-10-21' UNION ALL
select 4,'D','Desc for D','2008-4-1','2008-10-24'

select 'LAST DAY RECORD', 'DATE' UNION ALL
SELECT TOP 1 Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
WHERE MODIFYDATE = (SELECT MAX(MODIFYDATE) FROM @TAB1)
UNION ALL
select 'THIS WEEK RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where datepart(week,modifydate) = datepart(week,getdate())
and datepart(yy,modifydate) = datepart(yy,getdate())
UNION ALL
select 'THIS MONTH RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where datepart(month,modifydate) = datepart(month,getdate())
and datepart(yy,modifydate) = datepart(yy,getdate())[/CODE]

RESULT


LAST DAY RECORD                                    DATE
Desc for D                                         10/24/2008
THIS WEEK RECORD                                   DATE
Desc for B                                         10/20/2008
Desc for C                                         10/21/2008
Desc for D                                         10/24/2008
THIS MONTH RECORD                                  DATE
Desc for A                                         10/10/2008
Desc for B                                         10/20/2008
Desc for C                                         10/21/2008
Desc for D                                         10/24/2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:09:05

just in case you've an index om modifydate use this:-

select 'LAST DAY RECORD', 'DATE' UNION ALL
SELECT TOP 1 Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
WHERE MODIFYDATE = (SELECT MAX(MODIFYDATE) FROM @TAB1)
UNION ALL
select 'THIS WEEK RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where modifydate >=datedd(week,datediff(week,0,getdate()),0)
and modifydate <=datedd(week,datediff(week,0,getdate()),1)
UNION ALL
select 'THIS MONTH RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where  modifydate >=datedd(mm,datediff(mm,0,getdate()),0)
and modifydate <=datedd(mm,datediff(mm,0,getdate()),1)

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-25 : 02:32:54

quote:
Originally posted by hanbingl

Let me make this into a diagram so everyone can see the Picture in my head:
[CODE]
Tbl_Request

ReqId Topic Description Createdate Modifydate
-------- -------- ------------- ------------- ----------------
1 A Desc for A 2008-1-1 2008-10-10
2 B Desc for B 2008-2-1 2008-10-20
3 C Desc for C 2008-3-1 2008-10-21
4 D Desc for D 2008-4-1 2008-10-24

OUTPUT:

Last Day Record Date
---------------- -------------
Desc for D 2008-10-24

This week record Date
----------------- -------------
Desc for B 2008-10-20
Desc for C 2008-10-21
Desc for D 2008-10-24

This month record Date
----------------- -------------
Desc for A 2008-10-10
Desc for B 2008-10-20
Desc for C 2008-10-21
Desc for D 2008-10-24

[/CODE]
solution:
[CODE]declare @tab1 table(REQID INT,
TOPIC VARCHAR(50),
DESCRIPTION VARCHAR(50),
CREATEDATE DATETIME, MODIFYDATE DATETIME)

INSERT INTO @TAB1
select 1,'A','Desc for A','2008-1-1','2008-10-10' UNION ALL
select 2,'B','Desc for B','2008-2-1','2008-10-20' UNION ALL
select 3,'C','Desc for C','2008-3-1','2008-10-21' UNION ALL
select 4,'D','Desc for D','2008-4-1','2008-10-24'

select 'LAST DAY RECORD', 'DATE' UNION ALL
SELECT TOP 1 Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
WHERE MODIFYDATE = (SELECT MAX(MODIFYDATE) FROM @TAB1)
UNION ALL
select 'THIS WEEK RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where datepart(week,modifydate) = datepart(week,getdate())
and datepart(yy,modifydate) = datepart(yy,getdate())
UNION ALL
select 'THIS MONTH RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where datepart(month,modifydate) = datepart(month,getdate())
and datepart(yy,modifydate) = datepart(yy,getdate())[/CODE]

RESULT

LAST DAY RECORD DATE
Desc for D 10/24/2008
THIS WEEK RECORD DATE
Desc for B 10/20/2008
Desc for C 10/21/2008
Desc for D 10/24/2008
THIS MONTH RECORD DATE
Desc for A 10/10/2008
Desc for B 10/20/2008
Desc for C 10/21/2008
Desc for D 10/24/2008

Thank you for your reply.i have get the result

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-25 : 02:33:47

quote:

Originally posted by visakh16

just in case you've an index om modifydate use this:-

select 'LAST DAY RECORD', 'DATE' UNION ALL
SELECT TOP 1 Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
WHERE MODIFYDATE = (SELECT MAX(MODIFYDATE) FROM @TAB1)
UNION ALL
select 'THIS WEEK RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where modifydate >=datedd(week,datediff(week,0,getdate()),0)
and modifydate <=datedd(week,datediff(week,0,getdate()),1)
UNION ALL
select 'THIS MONTH RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where  modifydate >=datedd(mm,datediff(mm,0,getdate()),0)
and modifydate <=datedd(mm,datediff(mm,0,getdate()),1)

Thank you for the reply

tosscrosby
Aged Yak Warrior

676 Posts

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

Sing, please don't hijack other threads. Creating a new one will usually get you a quicker response, especially from the repsonders who may have already replied/resolved this issue.

Terry

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-14 : 03:57:32

declare @tab1 table(REQID INT,
TOPIC VARCHAR(50),
DESCRIPTION VARCHAR(50),
CREATEDATE DATETIME, MODIFYDATE DATETIME)

INSERT INTO @TAB1
select 1,'A','Desc for A','2008-1-1','2008-10-10' UNION ALL
select 2,'B','Desc for B','2008-2-1','2008-10-20' UNION ALL
select 3,'C','Desc for C','2008-3-1','2008-10-21' UNION ALL
select 4,'D','Desc for D','2008-4-1','2008-10-24'

select 'LAST DAY RECORD', 'DATE' UNION ALL
SELECT TOP 1 Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
WHERE MODIFYDATE = (SELECT MAX(MODIFYDATE) FROM @TAB1)
UNION ALL
select 'THIS WEEK RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where modifydate between ( select max(DATEADD(dd,-(DATEPART(dw, Modifydate) - 1),Modifydate)) from @tab1) and (select max(DATEADD(dd,-(DATEPART(dw, Modifydate) - 7),Modifydate)) from @tab1)
UNION ALL
select 'THIS MONTH RECORD', 'DATE' UNION ALL
SELECT Description, CONVERT(varchar(max), Modifydate,101) FROM @TAB1
where modifydate<= DATEADD(d, -DAY(DATEADD(m,1,modifydate)),DATEADD(m,1,modifydate))

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-11-18 : 03:36:03

SELECT 'Last Day Record','Date'
UNION ALL
SELECT [description],CONVERT(VARCHAR(11),modifydate,101)
FROM tbl_request WHERE modifydate = (SELECT max(modifydate) FROM tbl_request)
UNION ALL
SELECT 'Last Week Record','Date'
UNION ALL
SELECT [description],CONVERT(VARCHAR(11),modifydate,101)
FROM tbl_request WHERE DATEDIFF(ww,modifydate,(SELECT MAX(modifydate) FROM tbl_request)) = 0
UNION ALL
SELECT 'Last Month Record','Date'
UNION ALL
SELECT [description],CONVERT(VARCHAR(11),modifydate,101)
FROM tbl_request WHERE DATEDIFF(mm,modifydate,(SELECT MAX(modifydate) FROM tbl_request)) = 0

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources