Hi,

I hope someone can help.

I have a legacy report to amend. The report needs a start date and an end date for the report, and displays results for that date range and for the year previous to range entered - so two sets of results, 1st for the range entered and 2nd for the same dates in the previous year . This crystal report is run on-demand by many users throughout the day for various date ranges. I need to amend the existing code for the report as it doesn't account for leap years. I have code that identifies a leap year but I am not sure how I can use that within this existing code to change the report so that leap years are accounted for.

The report shows values for the date range entered and also for one year previous to that range entered. Currently the DATEADD function will minus one year from the date entered, but as I say that doesn't account for leap years and does a straighforward minus 365 days.

Do I need to use the options I have here to identify leap years or is there a function within SQL that I can use instead. How can I incorporate them within the main code I am using SQL 2005. Thanks.

--code for leap years

--option 1
declare @year int
set @year=2012

select
case
when @year%400=0 then 1
when @year%100=0 then 0
when @year%4=0 then 1
else 0
end as is_leap_year


--option 2
DECLARE @Year INT
SET @Year = 2012
select isdate(cast(@year as char(4))+'0229') as LeapYear --This is the code for the existing report as it stands that I need to change, --the rest of the code simply pulls out data according to date ranges.USE [CFOURtrain]
GO
/****** Object: StoredProcedure [dbo].[CustomerReportOccupancy] Script Date: 07/09/2012 13:40:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[CustomerReportOccupancyNK]
(
@START_DATE datetime,
@END_DATE datetime,
@AS_AT datetime,
@SP_NAME varchar(50)
)
as
declare @OLD_START_DATE datetime,
@OLD_END_DATE datetime,
@OLD_AS_AT datetime

--set @START_DATE = '1-jan-2008'
--set @END_DATE = '1-jan-2009'
--set @AS_AT = '1-jan-2009'

set @OLD_START_DATE = dateadd(year,-1,@START_DATE)
set @OLD_END_DATE = dateadd(year,-1,@END_DATE)
set @OLD_AS_AT = dateadd(year,-1,@AS_AT)
select
prod_name,
datediff(d,@START_DATE,@END_DATE)+1 as total,

-- This gets the results for this year

(SELECT
isnull(
sum
(datediff(d,
(CASE when ELEM_START_DATE < @START_DATE then @START_DATE
else ELEM_START_DATE
END),
(CASE when ELEM_END_DATE > @END_DATE then @END_DATE
else ELEM_END_DATE
END))+1),0)
from
dbo.element inner join dbo.booking on book_id = elem_book_id
where elem_end_date >= @START_DATE
and elem_start_date <= @END_DATE
and elem_add_date <= @AS_AT
and elem_status = 1
and elem_type = 24
and elem_prod_id = prod_id
and isnull(book_booking_methd,'00') not like '20%'
and isnull(book_booking_methd,'00') not like '30%') as booked,

(SELECT
isnull(
sum
(datediff(d,
(CASE when ELEM_START_DATE < @START_DATE then @START_DATE
else ELEM_START_DATE
END),
(CASE when ELEM_END_DATE > @END_DATE then @END_DATE
else ELEM_END_DATE
END))+1),0)
from
dbo.element inner join dbo.booking on book_id = elem_book_id
where elem_end_date >= @START_DATE
and elem_start_date <= @END_DATE
and elem_add_date <= @AS_AT
and elem_status = 1
and elem_type = 24
and elem_prod_id = prod_id
and isnull(book_booking_methd,'00') like '20%') as maintenance,

(SELECT
isnull(
sum
(datediff(d,
(CASE when ELEM_START_DATE < @START_DATE then @START_DATE
else ELEM_START_DATE
END),
(CASE when ELEM_END_DATE > @END_DATE then @END_DATE
else ELEM_END_DATE
END))+1),0) as Booked_Nights
from
dbo.element inner join dbo.booking on book_id = elem_book_id
where elem_end_date >= @START_DATE
and elem_start_date <= @END_DATE
and elem_add_date <= @AS_AT
and elem_status = 1
and elem_type = 24
and elem_prod_id = prod_id
and isnull(book_booking_methd,'00') like '30%') as unavailable,


-- Last years data



datediff(d,@OLD_START_DATE,@OLD_END_DATE)+1 as old_total,


(SELECT
isnull(
sum
(datediff(d,
(CASE when ELEM_START_DATE < @OLD_START_DATE then @OLD_START_DATE
else ELEM_START_DATE
END),
(CASE when ELEM_END_DATE > @OLD_END_DATE then @OLD_END_DATE
else ELEM_END_DATE
END))+1),0)
from
dbo.element inner join dbo.booking on book_id = elem_book_id
where elem_end_date >= @OLD_START_DATE
and elem_start_date <= @OLD_END_DATE
and elem_add_date <= @OLD_AS_AT
and elem_status = 1
and elem_type = 24
and elem_prod_id = prod_id
and isnull(book_booking_methd,'00') not like '20%'
and isnull(book_booking_methd,'00') not like '30%') as old_booked,

(SELECT
isnull(
sum
(datediff(d,
(CASE when ELEM_START_DATE < @OLD_START_DATE then @OLD_START_DATE
else ELEM_START_DATE
END),
(CASE when ELEM_END_DATE > @OLD_END_DATE then @OLD_END_DATE
else ELEM_END_DATE
END))+1),0)
from
dbo.element inner join dbo.booking on book_id = elem_book_id
where elem_end_date >= @OLD_START_DATE
and elem_start_date <= @OLD_END_DATE
and elem_add_date <= @OLD_AS_AT
and elem_status = 1
and elem_type = 24
and elem_prod_id = prod_id
and isnull(book_booking_methd,'00') like '20%') as old_maintenance,

(SELECT
isnull(
sum
(datediff(d,
(CASE when ELEM_START_DATE < @OLD_START_DATE then @OLD_START_DATE
else ELEM_START_DATE
END),
(CASE when ELEM_END_DATE > @OLD_END_DATE then @OLD_END_DATE
else ELEM_END_DATE
END))+1),0)
from
dbo.element inner join dbo.booking on book_id = elem_book_id
where elem_end_date >= @OLD_START_DATE
and elem_start_date <= @OLD_END_DATE
and elem_add_date <= @OLD_AS_AT
and elem_status = 1
and elem_type = 24
and elem_prod_id = prod_id
and isnull(book_booking_methd,'00') like '30%') as old_unavailable

from product
inner join sys_params as Prod_Sell_Co on Prod_Sell_Co.sp_id = PROD_SELCO_SP_ID
where prod_sy_product = 24
and prod_qty_ac = 1
and prod_state = 1
and SP_SELL_COMP_NAME = @SP_NAME



option (robust plan)