May 19, 2010 at 4:46 am
pls try this:
declare @CurDate varchar(25)
set @CurDate='13 jul 2010'
declare @TmpTbl table(qId int,qFrom varchar(15),qTo varchar(15),startDate datetime,
toDate datetime,Mnths varchar(50))
insert into @TmpTbl values(1,'January','March',Null,Null,'January,February,March')
insert into @TmpTbl values(2,'April','june',Null,Null,'April,May,June')
insert into @TmpTbl values(3,'July','September',Null,Null,'July,August,September')
insert into @TmpTbl values(4,'October','December',Null,Null,'October,November,December')
select qId,datename(mm,@CurDate)
from @TmpTbl where datename(MM,@CurDate)in (qFrom,datename(mm,dateadd(m,1,'01 '+qFrom+' 2010')),qTo)
select qId--,datename(MM,dateadd(m,-1,@CurDate))
from @TmpTbl where datename(MM,dateadd(m,-1,@CurDate))in (qFrom,datename(mm,dateadd(m,1,'01 '+qFrom+' 2010')),qTo)
I have used a temporary table just for example.u can apply this in your context.verify the query by changing the values of @CurDate.ok?
regards,
sunitha
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
May 19, 2010 at 4:50 am
Thanks Sunitha,
Before trying this query, i have some doubt, currdate is hard coded? is that not possible to get the system current date?
And also i found some hardcoding of 2010 in the query, is that not possible to change to current year..
Thanks
May 19, 2010 at 4:58 am
you can use the getdate() function.I hard coded ot just to test ..
🙂
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
May 19, 2010 at 4:58 am
instead of 2010,you can use year(getdate())
🙂
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
May 19, 2010 at 5:02 am
you can change it like this:
select qId,datename(mm,@CurDate)
from @TmpTbl where datename(MM,@CurDate)in (qFrom,datename(mm,dateadd(m,1,'01 '+qFrom+' '+cast(year(@CurDate) as varchar))),qTo)
select qId--,datename(MM,dateadd(m,-1,@CurDate))
from @TmpTbl where datename(MM,dateadd(m,-1,@CurDate))in (qFrom,datename(mm,dateadd(m,1,'01 '+qFrom+' '+cast(year(@CurDate) as varchar))),qTo)
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
May 19, 2010 at 6:13 am
Thanks once again Sunitha, it works, need small change
The first query works as expected, but the second query, i expect the previous quarter value
the below one has to give "4", it gives me still "1",
declare @CurDate DATETIME
set @CurDate=CONVERT(datetime, '2010-01-1', 102)
--select @CurDate as dateOut
select QuarterId,datename(mm,@CurDate)
from QuarterDefinition where datename(MM,@CurDate)in (QuarterFromMonth,datename(mm,dateadd(m,1,'01 '+QuarterFromMonth+' '+cast(year(@CurDate) as varchar))),QuarterToMonth)
Can you please look in to this and help me
May 19, 2010 at 6:31 am
Ummm, the code you were given does what you asked for.
(cleaned up below)
--Create test data
DECLARE @CurDate VARCHAR(25)
SET @CurDate='1 jan 2010'
DECLARE @QuarterDefinition TABLE(
quarterid INT,
quarterfrommonth VARCHAR(15),
quartertomonth VARCHAR(15),
startdate DATETIME,
todate DATETIME,
mnths VARCHAR(50))
INSERT INTO @QuarterDefinition
VALUES (1,
'January',
'March',
NULL,
NULL,
'January,February,March')
INSERT INTO @QuarterDefinition
VALUES (2,
'April',
'june',
NULL,
NULL,
'April,May,June')
INSERT INTO @QuarterDefinition
VALUES (3,
'July',
'September',
NULL,
NULL,
'July,August,September')
INSERT INTO @QuarterDefinition
VALUES (4,
'October',
'December',
NULL,
NULL,
'October,November,December')
--Select statement for current quarter
SELECT quarterid,
Datename(mm, @CurDate)
FROM @QuarterDefinition
WHERE Datename(mm, @CurDate)IN ( quarterfrommonth, Datename(mm, Dateadd(m, 1, '01 ' + quarterfrommonth + ' 2010')), quartertomonth )
--Select statement for previous quarter
SELECT quarterid AS [Previous Quarter],
Datename(mm, @CurDate) AS [Month]
FROM @QuarterDefinition
WHERE Datename(mm, Dateadd(m, -1, @CurDate))IN ( quarterfrommonth, Datename(mm, Dateadd(m, 1, '01 ' + quarterfrommonth + ' 2010')), quartertomonth )
Or
--Version 2 Select statement for previous quarter
SELECT quarterid AS [Previous Quarter],
Datename(mm, @CurDate) AS [Month]
FROM @QuarterDefinition
WHERE Datename(mm, Dateadd(m, -1, @CurDate))IN ( quarterfrommonth, Datename(mm, Dateadd(m, 1, '01 ' + quarterfrommonth + ' ' + CAST(YEAR(@CurDate) AS VARCHAR))), quartertomonth )
Where you've gone wrong is in the "WHERE" clause.
May 19, 2010 at 6:44 am
Thanks it works for the date '1 jan 2010', it gives me quarter 4. But whne i give 1 dec 2010, it still gives me 4, which is suppose to be 3(4-1).
May 19, 2010 at 7:33 am
There will be a much better way of doing this than how I've done it. . . but this works
SELECT CASE
WHEN CAST(MONTH(@CurDate) AS VARCHAR) BETWEEN 1 AND 3 THEN ( quarterid + 3 )
ELSE ( quarterid - 1 )
END AS [Previous Quarter],
Datename(mm, @CurDate) AS [Month]
FROM @QuarterDefinition
WHERE Datename(mm, @CurDate)IN ( quarterfrommonth, Datename(mm, Dateadd(m, 1, '01 ' + quarterfrommonth + ' 2010')), quartertomonth )
May 19, 2010 at 8:05 am
Thank you very much, it works now
Can we add a small change to this query, so that i will be able to get year also. The logic is like if the current date is 01-01-2010, the previous quarter is 4 and it make sens that the year is 2009, and the same way if the date 05-05-2010, then previous quarter is 1 and year is still same as 2010.
Sorry for posting all here, instead trying, if it is in JAVA then i might have done by this time, since SQL.
Please help. Thanks in advance
May 19, 2010 at 8:15 am
Not sure I fully understand your requirement.
Are we trying to grab the year of the previous quarter? If so: -
SELECT CASE
WHEN CAST(MONTH(@CurDate) AS VARCHAR) BETWEEN 1 AND 3 THEN ( quarterid + 3 )
ELSE ( quarterid - 1 )
END AS [Previous Quarter],
Datename(mm, @CurDate) AS [Month],
CASE
WHEN CAST(MONTH(@CurDate) AS VARCHAR) BETWEEN 1 AND 3 THEN ( Datename(yyyy, @CurDate) - 1 )
ELSE ( Datename(yyyy, @CurDate) )
END AS [Year]
FROM @QuarterDefinition
WHERE Datename(mm, @CurDate)IN ( quarterfrommonth, Datename(mm, Dateadd(m, 1, '01 ' + quarterfrommonth + ' 2010')), quartertomonth )
(I'm sure there's a better way to do it than that)
If we're trying to grab the year of the date we've input then: -
SELECT CASE
WHEN CAST(MONTH(@CurDate) AS VARCHAR) BETWEEN 1 AND 3 THEN ( quarterid + 3 )
ELSE ( quarterid - 1 )
END AS [Previous Quarter],
Datename(mm, @CurDate) AS [Month],
Datename(yyyy, @CurDate) AS [Year]
FROM @QuarterDefinition
WHERE Datename(mm, @CurDate)IN ( quarterfrommonth, Datename(mm, Dateadd(m, 1, '01 ' + quarterfrommonth + ' 2010')), quartertomonth )
May 19, 2010 at 9:13 am
I am sorry, you dint get the full issue, may be i dint explain well, this may help
if the previous quarter is 4, then the year value should be current year -1, and if it is between 1- 3 then it should be the same year.. can you help me to add in to that query you have written
Viewing 12 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply