DatePart Question - 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)

DatePart Question

Author

Topic

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2011-11-21 : 16:04:57

Hello - I have script that currently has a Datepart to look at Friday and Saturdays to see if anyone is missing their time card. The area that I'm stuck is I want to keep the current DatePart and be able to add if the end of a month does not fall on a Friday. FOr example Nov 30th falls on a Wedneday.

Here is the script

SET NOCOUNT ON

DECLARE @weekEndDate datetime

DECLARE @FilterEndDate datetime
SET @FilterEndDate = getdate()

IF DATEPART(weekday,@FilterEndDate) = 6
--if today is a friday, then look for all timecards through the following day/sat
set @FilterEndDate = @FilterEndDate + 1
else
--else look for all timecards through the last week ending period in pjweek that is not set to 0
set @FilterEndDate = @FilterEndDate

DECLARE period_Cursor CURSOR FOR
SELECT we_date
FROM PJWeek where we_date >= '2011-01-01' AND we_date <= @FilterEndDate AND comment <> 0
ORDER BY we_date desc

OPEN period_Cursor

FETCH NEXT FROM period_Cursor
INTO @weekEndDate

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do logic here
INSERT INTO PSADSLETL..TmpMissingTimeCardPeriods
SELECT
employee [EmployeeID],
emp_name [Employee Name],
em_id03 [Email],
@weekEndDate As [Period]

FROM PJEmploy with(nolock) WHERE emp_status = 'A' AND employee NOT IN (
SELECT DISTINCT Employee FROM PJLabHdr Lh with(nolock)
LEFT OUTER JOIN PJLabDet Ld ON Lh.docnbr = Ld.docnbr
WHERE (Lh.pe_date = @weekEndDate AND Lh.le_status <> 'I' AND Lh.le_status <> 'R')
OR (Lh.pe_date = @weekEndDate AND Lh.le_status = 'R' AND Ld.ld_id17='R')
)
AND stdweek > 0
AND UPPER (emp_name) NOT LIKE '(GENERIC)%'
AND UPPER(emp_name) NOT LIKE '(CONT%' AND UPPER (emp_name) NOT LIKE 'ZZ_%'
AND employee <> '00000'
AND LEN (employee) > 4
AND em_id03 <> ''
AND (Date_hired <= @weekEndDate)
AND ((Date_terminated >= @weekEndDate AND Date_terminated <> '1900-01-01 00:00:00') OR Date_terminated = '1900-01-01 00:00:00')

UNION

SELECT
employee [EmployeeID],
emp_name [Employee Name],
em_id03 [Email],
@weekEndDate As [Period]

FROM XXX..PJEmploy with(nolock) WHERE emp_status = 'A' AND employee NOT IN (
SELECT DISTINCT Employee FROM XXX..PJLabHdr Lh with(nolock)
LEFT OUTER JOIN RIL..PJLabDet Ld ON Lh.docnbr = Ld.docnbr
WHERE (Lh.pe_date = @weekEndDate AND Lh.le_status <> 'I' AND Lh.le_status <> 'R')
OR (Lh.pe_date = @weekEndDate AND Lh.le_status = 'R' AND Ld.ld_id17='R')
)
AND stdweek > 0
AND UPPER (emp_name) NOT LIKE '(GENERIC)%'
AND UPPER(emp_name) NOT LIKE '(CONT%' AND UPPER (emp_name) NOT LIKE 'ZZ_%'
AND employee <> '00000'
AND LEN (employee) > 4
AND em_id03 <> ''
AND (Date_hired <= @weekEndDate)
AND ((Date_terminated >= @weekEndDate AND Date_terminated <> '1900-01-01 00:00:00') OR Date_terminated = '1900-01-01 00:00:00')

FETCH NEXT FROM period_Cursor
INTO @weekEndDate
END
CLOSE period_Cursor
DEALLOCATE period_Cursor

Any help would be greatly appreciated.
David

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-11-21 : 20:18:09

This returns the last day of the month, so you could just apply your DATEPART check to that to see if it is Friday:

select LastDayOfMonth = dateadd(mm,datediff(mm,-1,getdate()),-1)

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:28:13

why do you need a cursor for this? looks like what you need is just an extra join with PJWeek to avoid the cursor and do it set based

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2011-12-09 : 10:32:34

Thank you both for the responses. A little background to this one. We have an SSIS package that runs on Friday at 8:00 PM or on last days of the month letting our users know that they are missing a time card. We also have a schedules of Monday and Tuesday the job runs every hour from 9:00 am - 6:00 PM and Wednesday and Thursday the job runs every 2 hours from 10:00 AM to 6:00 PM.

We just recently noticed that when it comes to last days of the month depending on what day that is and what time the above job runs everyone in the agency will get a notice that there time is missing instead of there being logic that states if last day of month wait and send at 8:00 PM.

My questions are...

Is that something needs to be added to either the control flow or data flow in the package?

Do I need to adjust and include SQL logic in my script. Here is my script again?

SET NOCOUNT ON

DECLARE @weekEndDate datetime

DECLARE @FilterEndDate datetime
SET @FilterEndDate = getdate()

IF DATEPART(weekday,@FilterEndDate) = 6
--if today is a friday, then look for all timecards through the following day/sat
      set @FilterEndDate = @FilterEndDate + 1
else
--else look for all timecards through the last week ending period in pjweek that is not set to 0
      set @FilterEndDate = @FilterEndDate

DECLARE period_Cursor CURSOR FOR
SELECT we_date
FROM PJWeek where we_date >= '2011-01-01' AND we_date <= @FilterEndDate AND comment <> 0 
ORDER BY we_date desc

OPEN period_Cursor

FETCH NEXT FROM period_Cursor
INTO @weekEndDate

WHILE @@FETCH_STATUS = 0
BEGIN
      -- Do logic here
      INSERT INTO PSADSLETL..TmpMissingTimeCardPeriods
      SELECT
      employee [EmployeeID], 
      emp_name [Employee Name], 
      em_id03 [Email], 
      @weekEndDate As [Period]
      
      FROM PJEmploy with(nolock) WHERE emp_status = 'A' AND employee NOT IN (
            SELECT DISTINCT Employee FROM PJLabHdr Lh with(nolock)
            LEFT OUTER JOIN PJLabDet Ld ON Lh.docnbr = Ld.docnbr
            WHERE (Lh.pe_date = @weekEndDate AND Lh.le_status <> 'I' AND Lh.le_status <> 'R')
            OR (Lh.pe_date = @weekEndDate AND Lh.le_status = 'R' AND Ld.ld_id17='R')
      )
      AND stdweek > 0
      AND UPPER (emp_name) NOT LIKE '(GENERIC)%'
AND UPPER(emp_name) NOT LIKE '(CONT%'    AND UPPER (emp_name) NOT LIKE 'ZZ_%'
      AND employee <> '00000'
      AND LEN (employee) > 4
      AND em_id03 <> ''
      AND (Date_hired <= @weekEndDate) 
      AND ((Date_terminated >= @weekEndDate AND Date_terminated <> '1900-01-01 00:00:00') OR Date_terminated = '1900-01-01 00:00:00') 
      
      UNION 
      
      SELECT
      employee [EmployeeID], 
      emp_name [Employee Name], 
      em_id03 [Email], 
      @weekEndDate As [Period]
      
      FROM RIL..PJEmploy with(nolock) WHERE emp_status = 'A' AND employee NOT IN (
            SELECT DISTINCT Employee FROM RIL..PJLabHdr Lh with(nolock)
            LEFT OUTER JOIN RIL..PJLabDet Ld ON Lh.docnbr = Ld.docnbr
            WHERE (Lh.pe_date = @weekEndDate AND Lh.le_status <> 'I' AND Lh.le_status <> 'R')
            OR (Lh.pe_date = @weekEndDate AND Lh.le_status = 'R' AND Ld.ld_id17='R')
      )
      AND stdweek > 0
      AND UPPER (emp_name) NOT LIKE '(GENERIC)%'
AND UPPER(emp_name) NOT LIKE '(CONT%'    AND UPPER (emp_name) NOT LIKE 'ZZ_%'
      AND employee <> '00000'
      AND LEN (employee) > 4
      AND em_id03 <> ''
      AND (Date_hired <= @weekEndDate) 
      AND ((Date_terminated >= @weekEndDate AND Date_terminated <> '1900-01-01 00:00:00') OR Date_terminated = '1900-01-01 00:00:00') 
    
    FETCH NEXT FROM period_Cursor 
    INTO @weekEndDate
END 
CLOSE period_Cursor
DEALLOCATE period_Cursor

Our outcome is that when its the last day of the month this job does not run until 8:00 PM that night.

Thoughts?

Thanks,
D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources