Prev: Using sp in SSRS
Next: Is there any way to audit login/logout for specific users ?
From: Paulo Roberto Bianchi de Oliveira on 14 Jul 2010 13:03
Is it possible to convert a sql2005 features to sql2000?
Because the ISP server there's no way to install the 2k5 server. Or do you
have a statment that can generate a table filled only with the workdays of a
year on ms sql 2000. THank you very much
------------------------------------------------------------------------------------------

SET STATISTICS IO ON
GO

SET DATEFIRST 7 -- Default � 7 (Domingo)
GO

DECLARE @StartYear AS INT
DECLARE @EndYear AS INT

SET @StartYear = 2010;
SET @EndYear = 2010;

WITH Holidays
AS ( -- Alimentar com os Feriados
SELECT Date = CAST('19000101' AS DATETIME)
UNION ALL
SELECT Date = CAST('19001225' AS DATETIME)
UNION ALL
SELECT Date = CAST('19001102' AS DATETIME)
UNION ALL
SELECT Date = CAST('19001115' AS DATETIME)
) ,
Years
AS ( SELECT YYYY = @StartYear
UNION ALL
SELECT YYYY + 1
FROM Years
Where YYYY < @EndYear
) ,
Months
AS ( SELECT MM = 1
UNION ALL
SELECT MM + 1
FROM Months
WHERE MM < 12
) ,
Days
AS ( SELECT DD = 1
UNION ALL
SELECT DD + 1
FROM Days
WHERE DD < 31
) ,
DatesRaw
AS ( SELECT YYYY = YYYY,
MM = MM,
DD = DD,
ID_Date = YYYY * 10000 + MM * 100 + DD,
DateString = CAST(YYYY * 10000 + MM * 100 + DD AS
VARCHAR),
Date = CASE WHEN ISDATE(YYYY * 10000 + MM * 100 +
DD) = 1
THEN CAST(CAST(YYYY * 10000 + MM * 100 +
DD AS VARCHAR) AS DATETIME)
ELSE NULL

END
FROM Years
CROSS JOIN Months
CROSS JOIN Days
WHERE ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
)
SELECT DatesRaw.*,
DayOfWeek = DATEPART(dw, DatesRaw.Date),
CalendarDaySequential = CAST(DatesRaw.Date AS INT),
WorkingDay = CAST(CASE DATEPART(dw, DatesRaw.Date)
WHEN 1 THEN 0 -- Sunday
WHEN 7 THEN 0 -- Saturday
ELSE CASE
WHEN recurring.Date IS NULL AND
fixed.Date IS NULL THEN 1
ELSE 0
END
END AS BIT)
INTO #Calendar
FROM DatesRaw
LEFT JOIN Holidays recurring
ON recurring.Date = DATEADD(Year, 1900 - YEAR(DatesRaw.Date),
DatesRaw.Date)
LEFT JOIN Holidays fixed
ON fixed.Date = DatesRaw.Date
GO

-----------------------------------------------------------------------------------------------------------

select Date
FROM #Calendar wd1 where workingDay=1 and date >= '20100701' and date <=
'20100731' --->mostra apenas July/2010
ORDER BY ID_Date
GO

drop table #Calendar


From: Erland Sommarskog on 14 Jul 2010 18:23
Paulo Roberto Bianchi de Oliveira (poliveira(a)intectecnologia.local) writes:
> Is it possible to convert a sql2005 features to sql2000? Because the ISP
> server there's no way to install the 2k5 server. Or do you have a
> statment that can generate a table filled only with the workdays of a
> year on ms sql 2000. THank you very much

But for crying out loud! Find a new ISP, and the current rot away
with its SQL 2000!

In this particular case, it's fairly easy. Rather than using a number
of CTEs, use a number nested derived tables:

FROM (SELECT ...
FROM (SELECT ...
FROM ...) AS Years
CROSS JOIN (SELECT FROM ... ) AS Months
...) AS DatesRaw
LEFT JOIN ( ) As Holidays

But you cannot do recursive derived tables. But you can easily replace
them if you use a table number of numbers to span years, months and
days. See http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Using sp in SSRS
Next: Is there any way to audit login/logout for specific users ?