April 10, 2003 at 9:37 pm
Hi all,
I have the Following Query
It is working fine if the first month is 01 and Second month is 12
but i want like this if the user selects 200103 to 200203 i want the
value of composite should be added from 200103 to 200203 in one row
and other row from 200203 to 200303.
like this if the user selects from 200004 to 200304 i have to get 3 rows..
that is Yearly data..so how can i modify the following query or any new query.
select CONVERT(CHAR(4),period,112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between '200101' and '200312'
group by CONVERT(CHAR(4),period,112)
order by CONVERT(CHAR(4),period,112)
Thanks
April 11, 2003 at 1:20 am
Try the following
DECLARE @p1 INT
DECLARE @p2 INT
DECLARE @m_start_month INT
SET @p1 = '200103'
SET @p2 = '200303'
SET @m_start_month = CAST(RIGHT(@p1,2) AS INT) -1
select CONVERT(CHAR(4),DATEADD(MM,- @m_start_month,period),112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between @p1 and @p2
group by CONVERT(CHAR(4),DATEADD(MM,- @m_start_month,period),112)
order by CONVERT(CHAR(4),DATEADD(MM,- @m_start_month,period),112)
April 11, 2003 at 3:17 am
If I read you right then from your example of 200103 to 200303 you want
2002 sum value 200103 to 200203 inclusive
2003 sum value 200203 to 200303 inclusive
then I would use a while loop
declare @s-2 varchar(6),@e varchar(6),@w varchar(6)
set @s-2 = '200101'
set @e = '200312'
set @w = CONVERT(varchar(4),CONVERT(int,LEFT(@s,4)) + 1) + RIGHT(@e,2)
while @w <= @e
begin
select LEFT(CONVERT(varchar,@w),4),SUM(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between @s-2 and @w
set @s-2 = CONVERT(varchar(4),CONVERT(int,LEFT(@s,4)) + 1) + RIGHT(@s,2)
set @w = CONVERT(varchar(4),CONVERT(int,LEFT(@w,4)) + 1) + RIGHT(@w,2)
end
Far away is close at hand in the images of elsewhere.
Anon.
April 11, 2003 at 3:50 am
DavidBurrows thanks for u reply it is working if i give the range from 200203 to 200303
but i give 200101 to 200312 then
it has to show be 3 years of data.
because in the selected range we have 3 three years...like from 200101 to 20012 and
200201 to 200212
200301 to 200312.....
this is just example range i am giving ..user may select the range from 199806 to 200206
means in that we have
199806 to 199905
199906 to 200005
200006 to 200105
200106 to 200205
...like that i want the data sorry in my first post i told the i want the data to sum up from
200003 to 200103....it is from 200003 to 200012
i am new to this so let me know...
thanks
April 11, 2003 at 4:16 am
Try sliding the dates based on the first starting dates month. This will make each period look like the year of period start.
DECLARE @d1 AS char(6)
DECLARE @d2 as char(6)
SET @d1 = '200003'
SET @d2 = '200302'
SELECT
CONVERT(char(4),DATEADD(m, -(CAST(RIGHT(@d1,2) AS INT) - 1), period), 112) as Period
SUM(composite) AS Composite_Total
FROM
cdh_price_gap
WHERE
CONVERT(CHAR(6),period,112) between @d1 and @d2
GROUP BY
CONVERT(char(4),DATEADD(m, -(CAST(RIGHT(@d1,2) AS INT) - 1), period), 112)
ORDER BY
CONVERT(char(4),DATEADD(m, -(CAST(RIGHT(@d1,2) AS INT) - 1), period), 112)
April 11, 2003 at 4:25 am
Nice one Antares.
Far away is close at hand in the images of elsewhere.
Anon.
April 11, 2003 at 10:55 pm
thanks Antares it is working....
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply