Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: how to loop everyday for the whole year

    Hi everybody wanted to get those who are absent everyday 365 days for the whole year of 2011. the procedure below is only for the present day. How could i get the list of all the days and those that are absent for each day for the whole year? how could i loop through it everyday. thanks

    DECLARE @TODAY DATETIME
    DECLARE @TOMMOROW DATETIME
    DECLARE @FUTURE DATETIME

    SET @TODAY=GETDATE()
    SET @TOMMOROW=DateAdd("d",0,@Today)
    SET @FUTURE=(SELECT CAST(FLOOR(CAST( @TOMMOROW AS float)) AS datetime))

    SELECT v.strViagID,
    v.dtViagSDate,
    v.dtViagEDate,
    v.strDescription
    FROM tblViaggi v LEFT JOIN tblEmp e ON v.strEmpIDFK = e.strEmpID
    WHERE (@FUTURE <= v.dtViagEDate) AND (@FUTURE >= v.dtViagSDate)

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I made a table that holds all the days since the start of our company, till today and and those of the next 40 years. For each day it holds the year, month, week, quarter, weekday/weekend day, workday/holiday, Easter, ... It's so easy to use, especially in our data warehouse.

    You could use something similar as base table and get the other data with a LEFT OUTER JOIN.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •