May 19, 2010 at 2:55 am
QuarterId QuarterFromMonth QuarterToMonth StartDate EndDate Months
----------- -------------------- -------------------- ----------------------- ----------------------- -------------------
1 JANUARY MARCH NULL NULL JANUARY,FEBRUARY,MARCH
2 APRIL JUNE NULL NULL APRIL,MAY,JUNE
3 JULY SEPTEMBER NULL NULL JULY,AUGUST,SEPTEMBER
4 OCTOBER DECEMBER NULL NULL OCTOBER,NOVEMBER,DECEMBER
Here is my table, and the values in it.
Now i need to write a query, which will give me quarterid on the basis of month we are in. I got an logic, i have to write a query to check whether the current month(may) is in Months column and i have to get corresponding Quarter id, may look simple. But the next query i have to get the previous quarter value, some thing like current quarter id from current month may is "2", and i need to get the previous quarter id "1", i can minus one, but if the current quarter is 1, then it has to gimee "4". Please help me to do all these in SQL query.
I need 2 query basically
1. to get current quarter id from the table using current month
2. to get the previous quarter id, from the current month
Thanks in advance
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply