Business/Post 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

Business/Post Dates

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2010-05-06 : 12:04:53

I'm trying to get the following results and below the results is my query along with the workcalendar table used in the query. My problem is that I can't get the correct Posting Date Range. It would also be fine if just the last posting date was available.

4/19/2010
Posting Year 2010
Posting Month 4
Posting Day 12
Total Posting Days 22
Posting Date Range 4/16/2010 - 4/16/2010

4/20/2010
Posting Year 2010
Posting Month 4
Posting Day 13
Total Posting Days 22
Posting Date Range 4/17/2010 - 4/19/2010

DECLARE @date DATETIME
SELECT @date = '4/19/2010'
SELECT year (CONVERT(VARCHAR(10),@date-1,101)) as PostingYear,
month (CONVERT(VARCHAR(10),@date-1,101)) as PostingMonth,
(SELECT COUNT(*)
FROM workcalendar
WHERE dt BETWEEN C.first AND C.PostDate
AND isweekday = 1) AS BusinessDay,
(SELECT COUNT(*)
FROM workcalendar
WHERE dt BETWEEN C.first AND C.last
AND isweekday = 1) AS TotalBusinessDays,
c.PostDate
FROM
(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@date)-1),@date),101) as First,
CONVERT(VARCHAR(10),@date-1,101) as PostDate,
CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@date))),DATEADD(mm,1,@date)),101) as Last) C

QUERY RESULTS:
PostingYear PostingMonth BusinessDay TotalBusinessDays
2010 4 12 22

PostDate
04/18/2010

WORKCALENDAR TABLE:

dt isWeekDay isWorkDay
2010-04-01 00:00:00 1 1
2010-04-02 00:00:00 1 1
2010-04-03 00:00:00 0 0
2010-04-04 00:00:00 0 0
2010-04-05 00:00:00 1 1
2010-04-06 00:00:00 1 1
2010-04-07 00:00:00 1 1
2010-04-08 00:00:00 1 1
2010-04-09 00:00:00 1 1
2010-04-10 00:00:00 0 0
2010-04-11 00:00:00 0 0
2010-04-12 00:00:00 1 1
2010-04-13 00:00:00 1 1
2010-04-14 00:00:00 1 1
2010-04-15 00:00:00 1 1
2010-04-16 00:00:00 1 1
2010-04-17 00:00:00 0 0
2010-04-18 00:00:00 0 0
2010-04-19 00:00:00 1 1
2010-04-20 00:00:00 1 1
2010-04-21 00:00:00 1 1
2010-04-22 00:00:00 1 1
2010-04-23 00:00:00 1 1
2010-04-24 00:00:00 0 0
2010-04-25 00:00:00 0 0
2010-04-26 00:00:00 1 1
2010-04-27 00:00:00 1 1
2010-04-28 00:00:00 1 1
2010-04-29 00:00:00 1 1
2010-04-30 00:00:00 1 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources