December 27, 2010 at 2:26 am
hi all !
i need to restrict the numeric values of a DB in such a way the first column holds the values for current yr and the second column holds the values of the previous yr.
Select (Case when Date between '01/04/2010' and '31/03/2011' then Sum(Values) else 0 end) 'Current',
(Case When Date between '01/04/2009' and '31/03/2010' then Sum(Values) else 0 end) 'Previous'
From Table1
here the provided date values has to be calculated directly from the getdate value.
kindly help me if you know..
Thanks !
December 27, 2010 at 2:47 am
You can add the validation to an INSTEAD OF INSERT trigger and raise an error if the input is not within the required range.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
December 27, 2010 at 3:11 am
Sorry i didn't get you.
Actually the query willo be run at any time and it has to take care by itself to result with the current yr data and Previous yr data.
like the query has to check with the GetDate funct to carry on the results for current and previous finance yr.
is that possible...?? how to arrive at the solution??
December 27, 2010 at 6:53 am
Is this what you are looking for?
DECLARE @CYBeginDate VARCHAR(10)
DECLARE @CYEndDate VARCHAR(10)
DECLARE @LYBeginDate VARCHAR(10)
DECLARE @LYEndDate VARCHAR(10)
DECLARE @CYDate DATETIME
DECLARE @LYDate DATETIME
SET @CYDate = GETDATE()
--Test Data
--SET @CYDate = '04/01/2010'
SET @LYDate = DATEADD(YYYY,-1,@CYDate)
SELECT @CYBeginDate = CONVERT(VARCHAR(2),DATEPART(DD,@CYDate)) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,@CYDate)) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,@CYDate)),
@CYEndDate = CONVERT(VARCHAR(2),DATEPART(DD,DATEADD(DD,-1,@CYDate))) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,DATEADD(DD,-1,@CYDate))) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,DATEADD(DD,-1,@CYDate))),
@LYBeginDate = CONVERT(VARCHAR(2),DATEPART(DD,@LYDate)) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,@LYDate)) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,@LYDate)),
@LYEndDate = CONVERT(VARCHAR(2),DATEPART(DD,DATEADD(DD,-1,@LYDate))) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,DATEADD(DD,-1,@LYDate))) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,DATEADD(DD,-1,@LYDate)))
SELECT @CYBeginDate AS 'CurrentYearBeginDate',
@CYEndDate AS 'CurrentYearEndDate',
@LYBeginDate AS 'LastYearBeginDate',
@LYEndDate AS 'LastYearEndDate'
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
December 27, 2010 at 8:32 am
New Commer (12/27/2010)
Sorry i didn't get you.Actually the query willo be run at any time and it has to take care by itself to result with the current yr data and Previous yr data.
like the query has to check with the GetDate funct to carry on the results for current and previous finance yr.
is that possible...?? how to arrive at the solution??
It is quite a simple thing actually. Have you tried something on your own? If YES, where are you stuck? If we simply give the answer to you, it would not be of much help to you in the long run as you will not be learning much.
As a hint i would suggest you to look at the MONTH and YEAR function to find the Current Month and Year given the Current Date. Based on this you can find the Current Financial Year Start Date and End Date. Then you can use the DATEADD function to get the Previous Financial Year Start and End Dates.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 27, 2010 at 11:39 pm
Hi
Just a bit simplified version of Nakul Vachhrajani's code
DECLARE @CYBeginDate datetime, @CYEndDate datetime, @LYBeginDate datetime, @LYEndDate datetime
select @CYBeginDate = dateadd(year, datepart(year, getdate()) - 1900, '1900-04-01')
select @CYEndDate = dateadd(day, -1, dateadd(month, 12, @CYBeginDate))
select @LYBeginDate = dateadd(year, -1, @CYBeginDate)
select @LYEndDate = dateadd(year, -1, @CYEndDate)
SELECT @CYBeginDate AS 'CurrentYearBeginDate', @CYEndDate AS 'CurrentYearEndDate',
@LYBeginDate AS 'LastYearBeginDate', @LYEndDate AS 'LastYearEndDate'
December 27, 2010 at 11:58 pm
Nakul Vachhrajani (12/27/2010)
Is this what you are looking for?
DECLARE @CYBeginDate VARCHAR(10)
DECLARE @CYEndDate VARCHAR(10)
DECLARE @LYBeginDate VARCHAR(10)
DECLARE @LYEndDate VARCHAR(10)
DECLARE @CYDate DATETIME
DECLARE @LYDate DATETIME
SET @CYDate = GETDATE()
--Test Data
--SET @CYDate = '04/01/2010'
SET @LYDate = DATEADD(YYYY,-1,@CYDate)
SELECT @CYBeginDate = CONVERT(VARCHAR(2),DATEPART(DD,@CYDate)) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,@CYDate)) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,@CYDate)),
@CYEndDate = CONVERT(VARCHAR(2),DATEPART(DD,DATEADD(DD,-1,@CYDate))) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,DATEADD(DD,-1,@CYDate))) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,DATEADD(DD,-1,@CYDate))),
@LYBeginDate = CONVERT(VARCHAR(2),DATEPART(DD,@LYDate)) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,@LYDate)) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,@LYDate)),
@LYEndDate = CONVERT(VARCHAR(2),DATEPART(DD,DATEADD(DD,-1,@LYDate))) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,DATEADD(DD,-1,@LYDate))) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,DATEADD(DD,-1,@LYDate)))
SELECT @CYBeginDate AS 'CurrentYearBeginDate',
@CYEndDate AS 'CurrentYearEndDate',
@LYBeginDate AS 'LastYearBeginDate',
@LYEndDate AS 'LastYearEndDate'
Nakul ! Thks for your query, i did a little change in yours like the below,
DECLARE @CYBeginDate VARCHAR(10)
DECLARE @CYEndDate VARCHAR(10)
DECLARE @LYBeginDate VARCHAR(10)
DECLARE @LYEndDate VARCHAR(10)
DECLARE @CYDate DATETIME
DECLARE @LYDate DATETIME
SET @CYDate = GETDATE()
--Test Data
SET @CYDate = (Select Case When MONTH(GETDATE()) between 1 and 3 Then '01/04/' + convert(varchar,year(getdate())-1) else '04/01/'+
convert(varchar,year(getdate())) end)
SET @LYDate = DATEADD(YYYY,-1,@CYDate)
SELECT @CYBeginDate = CONVERT(VARCHAR(2),DATEPART(DD,@CYDate)) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,@CYDate)) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,@CYDate)),
@CYEndDate = CONVERT(VARCHAR(2),DATEPART(DD,DATEADD(DD,-1,@CYDate))) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,DATEADD(DD,-1,@CYDate))) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,DATEADD(YYYY,+1,@CYDate))),
@LYBeginDate = CONVERT(VARCHAR(2),DATEPART(DD,@LYDate)) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,@LYDate)) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,@LYDate)),
@LYEndDate = CONVERT(VARCHAR(2),DATEPART(DD,DATEADD(DD,-1,@LYDate))) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,DATEADD(DD,-1,@LYDate))) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,DATEADD(YYYY,+1,@LYDate)))
For the further below query the data is not fetched correctly.. kindly advice..
Select distinct [Posting Date], CONVERT(varchar(20),[Posting Date], 103),
Case when CONVERT(varchar(20),[Posting Date], 103) Between @CYBeginDate and @CYEndDate Then CONVERT(varchar(20),[Posting Date], 103) else Null end,
Case when CONVERT(varchar(20),[Posting Date], 103) Between @LYBeginDate and @LYEndDate Then CONVERT(varchar(20),[Posting Date], 103) else Null end
from [G_L_Entry]
2009-12-31 23:59:59.00031/12/200931/12/200931/12/2009
2010-01-01 00:00:00.00001/01/2010NULLNULL
2010-01-31 00:00:00.00031/01/201031/01/201031/01/2010
2010-02-01 00:00:00.00001/02/2010NULLNULL
2010-02-28 00:00:00.00028/02/201028/02/201028/02/2010
2010-03-01 00:00:00.00001/03/2010NULLNULL
2010-03-31 00:00:00.00031/03/201031/03/201031/03/2010
2010-04-01 00:00:00.00001/04/2010NULLNULL
2010-04-30 00:00:00.00030/04/201030/04/201030/04/2010
2010-05-01 00:00:00.00001/05/2010NULLNULL
2010-05-31 00:00:00.00031/05/201031/05/201031/05/2010
2010-06-01 00:00:00.00001/06/2010NULLNULL
2010-06-30 00:00:00.00030/06/201030/06/201030/06/2010
2010-07-01 00:00:00.00001/07/2010NULLNULL
2010-07-31 00:00:00.00031/07/201031/07/201031/07/2010
2010-08-01 00:00:00.00001/08/2010NULLNULL
2010-08-31 00:00:00.00031/08/201031/08/201031/08/2010
2010-09-01 00:00:00.00001/09/2010NULLNULL
Like even the all dates getting displayed in both current yr column as well as in Previous Column.
Thanks !!
December 28, 2010 at 12:22 am
Some changes
DECLARE @CYBeginDate smalldatetime, @CYEndDate smalldatetime,
@LYBeginDate smalldatetime, @LYEndDate smalldatetime
select @CYBeginDate = dateadd(year, datepart(year, getdate()) - 1900, '1900-04-01')
select @CYEndDate = dateadd(day, -1, dateadd(month, 12, @CYBeginDate))
select @LYBeginDate = dateadd(year, -1, @CYBeginDate)
select @LYEndDate = dateadd(year, -1, @CYEndDate)
SELECT CONVERT(VARCHAR(10), @CYBeginDate, 103) AS 'CurrentYearBeginDate',
CONVERT(VARCHAR(10), @CYEndDate, 103) AS 'CurrentYearEndDate',
CONVERT(VARCHAR(10), @LYBeginDate, 103) AS 'LastYearBeginDate',
CONVERT(VARCHAR(10), @LYEndDate, 103) AS 'LastYearEndDate'
December 28, 2010 at 12:38 am
New Commer (12/27/2010)
DECLARE @CYBeginDate VARCHAR(10)DECLARE @CYEndDate VARCHAR(10)
DECLARE @LYBeginDate VARCHAR(10)
DECLARE @LYEndDate VARCHAR(10)
DECLARE @CYDate DATETIME
DECLARE @LYDate DATETIME
SET @CYDate = GETDATE()
--Test Data
SET @CYDate = (Select Case When MONTH(GETDATE()) between 1 and 3 Then '01/04/' + convert(varchar,year(getdate())-1) else '04/01/'+
convert(varchar,year(getdate())) end)
SET @LYDate = DATEADD(YYYY,-1,@CYDate)
SELECT @CYBeginDate = CONVERT(VARCHAR(2),DATEPART(DD,@CYDate)) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,@CYDate)) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,@CYDate)),
@CYEndDate = CONVERT(VARCHAR(2),DATEPART(DD,DATEADD(DD,-1,@CYDate))) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,DATEADD(DD,-1,@CYDate))) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,DATEADD(YYYY,+1,@CYDate))),
@LYBeginDate = CONVERT(VARCHAR(2),DATEPART(DD,@LYDate)) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,@LYDate)) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,@LYDate)),
@LYEndDate = CONVERT(VARCHAR(2),DATEPART(DD,DATEADD(DD,-1,@LYDate))) + '/' +
CONVERT(VARCHAR(2),DATEPART(MM,DATEADD(DD,-1,@LYDate))) + '/' +
CONVERT(VARCHAR(4),DATEPART(YYYY,DATEADD(YYYY,+1,@LYDate)))
The above code will fail for dates between the months January and March, the End Dates are shown as 03rd January instead of 31st March
I am glad that you tried something and i must say you are in the right direction, so check the below code that will give you proper dates
DECLARE@CurrentDate DATETIME
DECLARE@CYBeginDate DATETIME
DECLARE@CYEndDate DATETIME
DECLARE@LYBeginDate DATETIME
DECLARE@LYEndDate DATETIME
SET@CurrentDate = GETDATE()
IF MONTH( @CurrentDate ) > 3
BEGIN
SET@CYBeginDate = '01-April-' + CAST( YEAR( @CurrentDate ) AS VARCHAR(4) )
SET@CYEndDate = DATEADD( DAY, -1, DATEADD( YEAR, 1, @CYBeginDate ) )
END
ELSE
BEGIN
SET@CYEndDate = '31-March-' + CAST( YEAR( @CurrentDate ) AS VARCHAR(4) )
SET@CYBeginDate = DATEADD( DAY, 1, DATEADD( YEAR, -1, @CYEndDate ) )
END
SET@LYBeginDate = DATEADD( YEAR, -1, @CYBeginDate )
SET@LYEndDate = DATEADD( YEAR, -1, @CYEndDate )
Now for your second query below, I would advise you to always treat dates as DATETIME or SMALLDATETIME or the new DATE datatypes in SQL Server 2008. Never treat them as VARCHAR for comparison purposes. It is sure to give erroneous results. Even for display purpose its better if you handle the formatting of dates in front end.
Select distinct [Posting Date], CONVERT(varchar(20),[Posting Date], 103),
Case when CONVERT(varchar(20),[Posting Date], 103) Between @CYBeginDate and @CYEndDate Then CONVERT(varchar(20),[Posting Date], 103) else Null end,
Case when CONVERT(varchar(20),[Posting Date], 103) Between @LYBeginDate and @LYEndDate Then CONVERT(varchar(20),[Posting Date], 103) else Null end
from [G_L_Entry]
So the revised query should be
SELECTDISTINCT [Posting Date], CONVERT( VARCHAR(20), [Posting Date], 103 ),
CASE WHEN [Posting Date] BETWEEN @CYBeginDate AND @CYEndDate THEN CONVERT( VARCHAR(20), [Posting Date], 103 ) ELSE NULL END,
CASE WHEN [Posting Date] BETWEEN @LYBeginDate AND @LYEndDate THEN CONVERT( VARCHAR(20), [Posting Date], 103 ) ELSE NULL END
FROM[G_L_Entry]
Hope this helps. Let us know in case you have any more doubts.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 28, 2010 at 12:41 am
Abhijeet Chavan (12/28/2010)
Some changes
DECLARE @CYBeginDate smalldatetime, @CYEndDate smalldatetime,@LYBeginDate smalldatetime, @LYEndDate smalldatetime
select @CYBeginDate = dateadd(year, datepart(year, getdate()) - 1900, '1900-04-01')
select @CYEndDate = dateadd(day, -1, dateadd(month, 12, @CYBeginDate))
select @LYBeginDate = dateadd(year, -1, @CYBeginDate)
select @LYEndDate = dateadd(year, -1, @CYEndDate)
SELECT CONVERT(VARCHAR(10), @CYBeginDate, 103) AS 'CurrentYearBeginDate',
CONVERT(VARCHAR(10), @CYEndDate, 103) AS 'CurrentYearEndDate',
CONVERT(VARCHAR(10), @LYBeginDate, 103) AS 'LastYearBeginDate',
CONVERT(VARCHAR(10), @LYEndDate, 103) AS 'LastYearEndDate'
Wow Abhijeet Thanks ! That worked very fine to me ...Thanks !!
Also Thanks for All who helped to get out of this issue !
Thanks All !
December 28, 2010 at 12:42 am
Thanks Kingston ! for such good explanation ... it really helps me to understand better the query .. ThkQ !!
December 28, 2010 at 12:45 am
New Commer (12/28/2010)
Abhijeet Chavan (12/28/2010)
Some changes
DECLARE @CYBeginDate smalldatetime, @CYEndDate smalldatetime,@LYBeginDate smalldatetime, @LYEndDate smalldatetime
select @CYBeginDate = dateadd(year, datepart(year, getdate()) - 1900, '1900-04-01')
select @CYEndDate = dateadd(day, -1, dateadd(month, 12, @CYBeginDate))
select @LYBeginDate = dateadd(year, -1, @CYBeginDate)
select @LYEndDate = dateadd(year, -1, @CYEndDate)
SELECT CONVERT(VARCHAR(10), @CYBeginDate, 103) AS 'CurrentYearBeginDate',
CONVERT(VARCHAR(10), @CYEndDate, 103) AS 'CurrentYearEndDate',
CONVERT(VARCHAR(10), @LYBeginDate, 103) AS 'LastYearBeginDate',
CONVERT(VARCHAR(10), @LYEndDate, 103) AS 'LastYearEndDate'
Wow Abhijeet Thanks ! That worked very fine to me ...Thanks !!
Also Thanks for All who helped to get out of this issue !
Thanks All !
Are you sure the above method will give proper results if the date falls between the months of January and March?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 28, 2010 at 2:11 am
Yes Kingston it does work...
December 28, 2010 at 2:23 am
New Commer (12/28/2010)
Yes Kingston it does work...
When i run the code with a date of 15-January-2010, I get the CurrentYearBeginDate as 01-April-2010. Should it not be 01-April-2009?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 28, 2010 at 4:16 am
Kingston u r right
this code will not work if the date fall in first 3 months of the year
Thanks for pointing it out
I will put an updated code
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply