List the dates for the Month...?

in my table i have a Date filed and the employee code. But for the monthly report i need all the date for the month.But in the table all the date wont be there(bcas of sat and sundays). but i need the result in the Query like follows....(while i pass the MONTH and YEAR value)

01-01-2003     Mr.X
02-01-2003     Mr.X
03-01-2003     Mr.X
04-01-2003     Mr.X
05-01-2003     Mr.X
.
.
.
30-01-2003     Mr.X
31-01-2003     Mr.X

My code will put all the days for a given date range into a temp table.  You could then do a cross join to that table to produce the results you want.

Now I'm realy puzzled.
So you have a table like this:

date           code
01-01-2003     Mr.X
02-01-2003     Mr.X
03-01-2003     Mr.X
06-01-2003     Mr.X ---5 and 6 misses because is Saturday&Sunday
...
31-01-2003     Mr.X

And you want a select that filles up the gap and obtaines:
01-01-2003     Mr.X
02-01-2003     Mr.X
03-01-2003     Mr.X
04-01-2003     Mr.X
05-01-2003     Mr.X
06-01-2003     Mr.X ---5 and 6 misses because is Saturday&Sunday
...
31-01-2003     Mr.X

That's what you want????

create function dbo.ufn_DaysRange(@From datetime,@Till datetime)
returns @t table (DayInRange datetime primary key clustered) with schemabinding as begin
declare @Count   int set @Count=datediff(Day,@From,@Till)+1
declare @Counter int set @Counter=0
while @Counter<@Count begin
 insert @t(DayInRange) values (dateadd(Day,@Counter,@From))
 set @Counter=@Counter+1
end
return
end
GO

Good luck !

What you need is three UDFs that I've include below.

udf_DT_DaysTAB gives you a resultset with one row for each day in a range.  The other two UDFs are used to construct the start date and end date from the month and year number.


Given that you have the month and year then your script would be something like:

DECLARE @Year int       , @Month int
SELECT @Year = 2002     , @MOnth = 11

DECLARE @MonthStartDATE datetime
      , @MonthEndDATE datetime

SELECT @MonthStartDATE = dbo.udf_DT_FromYMD (@Year, @Month, 1)

SELECT @MonthEndDATE = dbo.udf_DT_MonthEndDATE (@MonthStartDATE)

select * from udf_DT_DaysTAB (@MonthStartDate, @MonthEndDate)
GO

It's possible to roll the three functions together so you don't have to make the more complex call.  

Since I'm making these public, I'll publish them in my free T-SQL UDF of the week Newsletter.  You can subscribe at:
http://www.NovickSoftware.com/UDFofWeek/UDFofWeekSignup.htm


Hope that helps.

Regards,
Andy

Andrew Novick
http://www.NovickSoftware.com




SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_DT_DaysTAB (

    @dtFrom datetime -- The first date to consider
  , @dtTo datetime -- The last date to consider
)   RETURNS  @Days TABLE  ( -- Days in range
         [Date] datetime -- The day in question
        )
/*
* Returns one row for each day that falls in a date range.
* Each date is the SOD.
*
* Example:
select * FROM udf_DT_DaysTAB('2002-1-1', '2002-2-3')
*      
* ) Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
****************************************************************/

AS BEGIN

   DECLARE @WorkDT smalldatetime -- Date we're working with

    -- Start by eliminating the time portion of the start date.
    SET @WorkDT =  DATEADD(dd
                            , DATEPART(dy, @dtFrom) -1
                            , CONVERT(datetime
                                    , CONVERT(char(4)
                                         , DATEPART(yyyy,@dtFrom)
                                             )  
                                      + '-01-01'
                                     )
                             )

    -- Insert one record for each day
    WHILE @WorkDT <= @dtTo BEGIN

        INSERT INTO @Days VALUES (@WorkDT)
        SET @WorkDT = DATEADD (dd, 1, @WorkDT)

    END
   
    RETURN
END
GO
GRANT SELECT on dbo.udf_DT_DaysTAB to PUBLIC
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER      FUNCTION dbo.udf_DT_FromYMD (
                    
    @Year as int -- 4 place year, ie 2002
  , @Month as int -- Month Number 1 to 12
  , @Day as int -- Day of the Month 1 to 31 or what ever
)   RETURNS SMALLDATETIME -- Date from the 3 parameters
    WITH SCHEMABINDING -- to allow determinism
/*
* Returns a smalldatetime given the Year, Month and Day of
* the month.
*  
* Example:
select  dbo.udf_DateFromYMD(2002, 2, 14) -- Valentines Day
*
* Test:
SELECT 'Test 1 ' + case when dbo.udf_DT_FromYMD(2002,2, 14) =
                CONVERT(smalldatetime,'2002-02-14')
                then 'Worked' else 'ERROR' END
*
* ) Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
****************************************************************/
AS BEGIN

   RETURN CONVERT (SMALLDATETIME
                   , CONVERT(CHAR(4), @Year)
                    + '-' + CONVERT(VARCHAR(2), @Month)
                              + '-' + CONVERT(VARCHAR(2), @Day)
                   , 110 -- required for determinism
                   )
END
GO
GRANT EXEC on dbo.udf_DT_FromYMD to PUBLIC
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION udf_DT_MonthEndDATE (
   
    @Date as datetime -- Date you want the end of
)   RETURNS SMALLDATETIME -- Start of day on last day of month
/*
* Returns the start of day on the last day of the month that
* @Date falls in.
*
* Example:
SELECT dbo.udf_DT_MonthEndDATE ('2002-02-14 12:34:33')
*
* Test:
PRINT 'Test 1 ' + CASE WHEN '2002-02-28' =
         dbo.udf_DT_MonthEndDATE ('2002-02-14 12:24:44')
            THEN 'WORKED' ELSE 'ERROR' END
*
* ) Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
****************************************************************/
AS BEGIN

DECLARE @TheDate smalldatetime

SET @TheDate = CONVERT(SMALLDATETIME
                              , LEFT(CONVERT(char(10)
                                            , @Date, 20)
                                    , 10)
                                )
     
RETURN DATEADD (day, -1,
            DATEADD(month, 1,
                DATEADD(day
                      , (-1 * DATEPART(day, @TheDate )) + 1
                      , @TheDate
                        )
                    )
               )
END
go

grant exec on dbo.udf_DT_MonthEndDATE to PUBLIC
GO

u2kim:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: xenon_je http:#8158004, ScottPletcher http:#8159252

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer