identifying leap year in report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

identifying leap year in report

Author

Topic

NaeemK
Starting Member

10 Posts

Posted - 2012-07-09 : 10:59:54

Hi,

I hope someone can help. I need to amend the existing code for a 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 incorporated within the main code or is there a function within SQL that I can use instead. 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

--existing report
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)