Instert found records into DB

here, this query produces 21 days for @TotalPostingDays ...the days left after pulling out weekend days (sat and sun) and holidays. What I want to do is instead of pulling the total amount for

@TotalPostingDays I want to insert the actual dates that are contained within the @TotalPostingDays into a table

DECLARE @TotalDaysInMonth int,< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

@today datetime,

@TotalWeekendDays int,

@TotalHolidaysThisMonth int,

@TotalPostingDays int

SET @today = GETDATE()

-- TOTAL DAYS THIS MONTH

SET @TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN

ELSE

DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))

END

SELECT @TotalDaysInMonth

-- TOTAL HOLIDAYS THIS MONTH

SELECT @TotalHolidaysThisMonth = (SELECT COUNT(*) FROM Apex_ReportingServer.dbo.Holidays

WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today))

AND (DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today))))

SELECT @TotalHolidaysThisMonth

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @date DATETIME

SET @date = '20060101'

SELECT @TotalWeekendDays = 8 +

CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '29') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) + '01') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '30') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) + '02') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @date, 112) + '31') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @date, 112) + '03') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END

SELECT @TotalWeekendDays

SELECT @TotalPostingDays = @TotalDaysInMonth - (@TotalHolidaysThisMonth + @TotalWeekendDays)

SELECT @TotalPostingDays

Answer this question

Instert found records into DB

ValKu
Hi Robert, the 2 casts is to convert long date into short date (without time).
The 2 drops is just for clean up as I was running the script repeatedly when constructing it. In real implementation we would keep the 2 tables.
Vincent
MetrologyGuy
Thanks -Dave
skinnyl
yea, it's already checking my calendar table...look above
mickan
Vincint, why wouldn't you keep the tables instead of dropping them I would want to keep some of the data for reporting purposes...just wondering.
JIM.H.
db123, below is the script that you requested.
Vincent

/*
Script to select working days.
Written by Vincent Rainardi, 31/1/2006
*/

-- Create Holiday table and populate
if exists (select name from sysobjects where name = 'Holidays')
drop table Holidays
create table Holidays (HolidayDate smalldatetime)

insert into Holidays (HolidayDate) values ('2/1/2006')

-- Create output table
if exists (select name from sysobjects where name= 'PostingDays')
drop table PostingDays
create table PostingDays (PostingDate smalldatetime)

-- Declare variables
declare @today smalldatetime,
@firstdate smalldatetime,
@lastdate smalldatetime,
@date smalldatetime,
@day varchar(9)

set @today = getdate()
set @firstdate = cast(cast(dateadd(day, -datepart(day, @today) + 1, @today) as varchar(12)) as smalldatetime)
set @lastdate = dateadd(day, -1, dateadd(month, 1, @firstdate))

-- Loop for the whole month
set @date = @firstdate
while @date <= @lastdate
begin
set @day = datename(weekday, @date)
if @day not in ('Saturday', 'Sunday')
and cast(cast(@date as varchar(12)) as smalldatetime) not in (select HolidayDate from Holidays)
insert into PostingDays (PostingDate) values (@date)
set @date = dateadd(day, 1, @date)
end

select cast(PostingDate as varchar(12)) from PostingDays

drop table Holidays
drop table PostingDays
Ned Friend - Microsoft
impresssive...thanks for the solution and insight vincent!
jojolimited
I don't have time for BOL, all it's gonna tell me is basic syntax. Right now I need to know how to pull the dates...my query only pulls numbers. Any help from anyone
Chirag Patel
You can use the INSERT..SELECT statement to do what you want. See Books Online for more examples. Alternatively, you should build a calendar table that contains all the dates, columns to indicate holidays, day of week etc. You can generate the data for the table from say Excel and then import the data into a table. Or you can write some TSQL code to generate the data for the table.
CJ Butcher

this is all I'm attempting to do:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21716366.html

now that I can undestand an approach like that, all I need to do is check against my calendar table and remove the dates from my table.
Jan-Kristian Markiewicz

why the 2 casts

cast(cast(@date as varchar(12))
Instert found records into DB

Answer this question