Find Sunday's Dates - 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

Find Sunday's Dates

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-09-02 : 13:25:00

create table #tmp (Empcd varchar(3) ,AbsentDate datetime)

insert into #tmp values ('E1','20080719')
insert into #tmp values ('E1','20080720')
insert into #tmp values ('E1','20080721')
insert into #tmp values ('E1','20080722')
insert into #tmp values ('E1','20080723')
insert into #tmp values ('E1','20080725')
insert into #tmp values ('E1','20080726')
insert into #tmp values ('E1','20080727')
insert into #tmp values ('E1','20080729')
insert into #tmp values ('E1','20080730')
insert into #tmp values ('E2','20080708')
insert into #tmp values ('E2','20080714')
insert into #tmp values ('E2','20080715')
insert into #tmp values ('E2','20080718')

Declare @FDM Datetime
Set @FDM='2008/09/01'

Select Empcd,Convert(Datetime,Min(Absentdate),111) as MiAD,Convert(Datetime,Max(Absentdate),111) as MaAD from #tmp Where AbsentDate>=Dateadd(day,-15,@FDM) and AbsentDate<@FDM Group By Empcd

And query which yields Empcd,min & max absentdate.

Using this result I want to find out the empcd and the dates of Sundays with Empcd.

Eg
Empcd Dts
E1 24/08/2008
E2 10/08/2008
E2 17/08/2008
Thanks in advance

Nirene

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-02 : 21:23:48

use datename(weekday, daetcol ) to check for 'Sunday'

KH
[spoiler]Time is always against us[/spoiler]

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-09-02 : 22:09:14

Hai Khtan,
Thanks for your reply,I know using datename function we can fetch the sundays.All I want is fetch all dates of sundays for all employees whose min & max absentdate are given in a single SELECT statement.

Nirene

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-02 : 22:30:37

make use of F_TABLE_DATE http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE
to list the date and left join to your table

KH
[spoiler]Time is always against us[/spoiler]

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-09-02 : 23:10:03

Totally messed up after seeing MVJ's UDF.Could you do me favour by writing the query for me Khtan.

Nirene

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-02 : 23:15:01

quote:
Originally posted by nirene

Totally messed up after seeing MVJ's UDF.Could you do me favour by writing the query for me Khtan.

Nirene

ha ha

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-02 : 23:21:07

I don't know what do you want with your query but this will give the the dates of the Sunday


Declare @FDM 	Datetime,
	@FDM1	datetime

select	@FDM	= '2008/09/01'
select	@FDM1	= dateadd(day, -15, @FDM)

select	DATE
from	F_TABLE_DATE(@FDM1, @FDM) d
where	WEEKDAY_NAME = 'Sun'

KH
[spoiler]Time is always against us[/spoiler]

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-09-03 : 00:49:09

If Result Is
Empcd # MiAD # MaAd
E1 # 2008-07-18 00:00:00.000 # 2008-07-30 00:00:00.000
E2 # 2008-07-23 00:00:00.000 # 2008-07-31 00:00:00.000

Now I want to display all dates of Sundays which falls between MiAD & MaAD

Output Shoud be
Empcd # Dts
E1 # 24/08/2008
E2 # 24/08/2008
E2 # 31/08/2008

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-03 : 01:04:43

quote:
Originally posted by nirene

If Result Is
Empcd # MiAD # MaAd
E1 # 2008-07-18 00:00:00.000 # 2008-07-30 00:00:00.000
E2 # 2008-07-23 00:00:00.000 # 2008-07-31 00:00:00.000

Now I want to display all dates of Sundays which falls between MiAD & MaAD

Output Shoud be
Empcd # Dts
E1 # 24/08/2008
E2 # 24/08/2008
E2 # 31/08/2008

How can August date falls between the MiAD & MaAD date of July ?

KH
[spoiler]Time is always against us[/spoiler]

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-09-03 : 03:12:31

Extremely sorry I have posted the wrong output.It should be for July month.

Corrected Output should be
E1 # 20/07/2008
E1 # 27/07/2008
E2 # 27/07/2008

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-03 : 03:33:03

[code]SELECT e.Empcd, e.MiAD, e.MaAD, d.[DATE]
FROM
(
SELECT Empcd,
CONVERT(datetime,MIN(AbsentDate),111) AS MiAD,
CONVERT(datetime,MAX(AbsentDate),111) AS MaAD
FROM #tmp
GROUP BY Empcd
) e
INNER JOIN F_TABLE_DATE('20080701', '20080731') d
ON d.WEEKDAY_NAME = 'Sun'
AND d.DATE >= e.MiAD
AND d.DATE <= MaAD
[/code]

KH
[spoiler]Time is always against us[/spoiler]

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-09-03 : 04:48:40

Thanks a lot.That solved my problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources