July 11, 2012 at 4:04 am
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,
(CASEwhen ELEM_START_DATE < @START_DATE then @START_DATE
else ELEM_START_DATE
END),
(CASEwhen 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
whereelem_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,
(CASEwhen ELEM_START_DATE < @START_DATE then @START_DATE
else ELEM_START_DATE
END),
(CASEwhen 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
whereelem_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,
(CASEwhen ELEM_START_DATE < @START_DATE then @START_DATE
else ELEM_START_DATE
END),
(CASEwhen 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
whereelem_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,
(CASEwhen ELEM_START_DATE < @OLD_START_DATE then @OLD_START_DATE
else ELEM_START_DATE
END),
(CASEwhen 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
whereelem_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,
(CASEwhen ELEM_START_DATE < @OLD_START_DATE then @OLD_START_DATE
else ELEM_START_DATE
END),
(CASEwhen 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
whereelem_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,
(CASEwhen ELEM_START_DATE < @OLD_START_DATE then @OLD_START_DATE
else ELEM_START_DATE
END),
(CASEwhen 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
whereelem_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)
July 11, 2012 at 6:46 am
Have you considered creating a calendar table and using this to identify the leap years?
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 11, 2012 at 7:07 am
I dont want to create a table to hold and highlight the leap years, I'm assuming there must be a way to do it without the use of an extra table.
July 11, 2012 at 7:36 am
naeemkhan72 (7/11/2012)
I dont want to create a table to hold and highlight the leap years, I'm assuming there must be a way to do it without the use of an extra table.
There is no system function in SQL Server that will highlight the leap year
You can create some user defined function for it or else you can use one of the methods you suggested
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 11, 2012 at 7:50 am
I dont want to create a table to hold and highlight the leap years, I'm assuming there must be a way to do it without the use of an extra table
Yes there are but not without scripting it yourself which come with an overhead at runtime.
The quickest and most efficient way is a calendar table (which you have disregarded) therefore your option remaining is to incorporate a UDF or logic within your SQL script.
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 11, 2012 at 8:00 am
This may be of help..
http://www.blackwasp.co.uk/SQLIsLeapYear.aspx
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 11, 2012 at 8:09 am
Thank you
July 11, 2012 at 9:00 am
I am confused slightly by your initial post. Your procedure takes two dates as input parameters, a start date and an end date. As you did not provide any DDL (CREATE TABLE statements) or sample data (as a series of INSERT INTO statements) for any tables, it is difficult to know exactly what you are trying to accomplish and what the actual problem is other than you can't seem to get last years end date for February if it happened to be a leap year.
Here is what I noticed looking at your code, you are using closed endded comparisions for your date ranges. While this may be fine when you know for certain that there is no time portion in the datetime data, the safest way to query a date range is to use a closed ended comparision on the lower end (for example MyDateCol >= '20110301) and an open ended comparision on the upper end of the range (for example MyDateCol < '20120301'). The range given in the examples I gave will ensure that you get all records for March 2011 through February 2012.
Any questions?
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply