February 14, 2013 at 8:11 am
ANy ideas how I can set this up?
I need to assign Fiscalstart 7/1/(Year Prior if Month <=6 else 7/1(Current year)
Thanks
Joe
ALTER PROCEDURE [dbo].[rpt_DCF_Utilization_sp]
(
@ENDdate datetime
)
as
DECLARE @Fiscalstart datetime;
IF month(@ENDdate) <= 6
Datetime(year(@Fiscalstart) -1 , 7,1);
ELSE
Date(year(@Fiscalstart) , 7,1) ;
END ;
GO
SELECT
February 14, 2013 at 11:32 am
tHINK iM CLOSE BUT GETTING A CONVERSION ERROR
DECLARE @FISCALDATE DATE
DECLARE @ENDDATE DATE
set @ENDdate = '02/01/2012'
SELECT CASE
WHEN datepart(MM,@ENDdate)<= 6
THEN
CONVERT(datetime, '01-' + '07-'+ (DATEPART(YY, @ENDDATE) -1), 103)
--DATEPART(YY, @ENDDATE) -1
ELSE
CONVERT(datetime, '01-' + '07-'+ (DATEPART(YY,@ENDDATE)), 103)
END AS fiscalDATE
February 14, 2013 at 12:23 pm
This should give you what you're looking for:
DECLARE @FISCALDATE DATE
DECLARE @ENDDATE DATE
set @ENDdate = '02/01/2012'
SELECT CASE
WHEN datepart(MM,@ENDdate)<= 6
THEN CAST ('01-'+'07-'+ cast((DATEPART(YY,@ENDDATE)-1) as CHAR(4)) as datetime)
ELSE CAST ('01-'+'07-'+ cast((DATEPART(YY,@ENDDATE)) as CHAR(4)) as datetime) end as FiscalDate
February 14, 2013 at 12:49 pm
Or this:
declare @TestDate date = '20120612';
select
@TestDate,
dateadd(mm, 6, dateadd(yy, datediff(yy, cast('19000101' as date), dateadd(mm, 6, @TestDate)) - 1, cast('19000101' as date)));
set @TestDate = '20120812';
select
@TestDate,
dateadd(mm, 6, dateadd(yy, datediff(yy, cast('19000101' as date), dateadd(mm, 6, @TestDate)) - 1, cast('19000101' as date)));
February 14, 2013 at 12:56 pm
these both work great, but I get Must declare the scalar variable "@fiscalstart". in my where
I wont scare u with the horrid code between... 🙂
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_DCF_Utilization_sp]
(
@ENDdate datetime,
@FISCALDATE DATEtime
)
as
--DECLARE @FISCALDATE DATE
--DECLARE @ENDDATE DATE
set @ENDdate = '02/01/2013'
SELECT CASE
WHEN datepart(MM,@ENDdate)<= 6
THEN CAST ('07-'+'01-'+ cast((DATEPART(YY,@ENDDATE)-1) as CHAR(4)) as datetime)
ELSE CAST ('07-'+'01-'+ cast((DATEPART(YY,@ENDDATE)) as CHAR(4)) as datetime) end as FiscalDate
SELECT TOP (100) PERCENT Client.ID, Client.FName, Client.LName, APPOINTMENT.Expdate, APPOINTMENT.EffDate, Eliot_Appt_to_Unit.Code, Eliot_Appt_to_Unit.FullName,
Eliot_Appt_to_Unit.capacity, APPTSTATUS.STATUS
FROM dbo.APPOINTMENT AS APPOINTMENT LEFT OUTER JOIN..........
then in the where
get the error???? Thanks
WHERE (APPOINTMENT.EffDate < @fiscalstart AND APPOINTMENT.EffDate >= @Enddate OR
APPOINTMENT.EffDate >= @fiscalstart AND APPOINTMENT.EffDate < @enddate OR
APPOINTMENT.EffDate < @fiscalstart AND APPOINTMENT.EffDate < @enddate) AND (Eli
February 14, 2013 at 1:05 pm
Looks like you need to add @fiscalstart datetime to your procedure parameter block
February 14, 2013 at 1:07 pm
This??
ALTER PROCEDURE [dbo].[rpt_DCF_Utilization_sp]
(
@ENDdate datetime,
@FISCALDATE DATEtime
)
as
DECLARE @ENDDATE DATE, @fiscalstart DATE;
set @ENDdate = '02/01/2013'
set @fiscalstart = dateadd(mm, 6, dateadd(yy, datediff(yy, cast('19000101' as date), dateadd(mm, 6, @ENDdate)) - 1, cast('19000101' as date)))
SELECT TOP (100) PERCENT Client.ID, Client.FName, Client.LName, APPOINTMENT.Expdate, APPOINTMENT.EffDate, Eliot_Appt_to_Unit.Code, Eliot_Appt_to_Unit.FullName,
Eliot_Appt_to_Unit.capacity, APPTSTATUS.STATUS
FROM dbo.APPOINTMENT AS APPOINTMENT LEFT OUTER JOIN..........
--then in the where
--get the error???? Thanks
WHERE (APPOINTMENT.EffDate < @fiscalstart AND APPOINTMENT.EffDate >= @Enddate OR
APPOINTMENT.EffDate >= @fiscalstart AND APPOINTMENT.EffDate < @enddate OR
APPOINTMENT.EffDate < @fiscalstart AND APPOINTMENT.EffDate < @enddate) AND (Eli
February 14, 2013 at 2:03 pm
WORKS!!
Thanks so much
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply