October 15, 2010 at 11:04 am
I have two tables:
one with name tblmonth and another with name tbldate as follows:
tblmonth:
stdidcodejanfebmarchaprilmay
100A1012502130
100B12119645
101A1265453423
101B3432125634
tbldate:
stdidsdateeddate
10001/02/201004/05/2010
10102/03/201004/02/2010
Now, i want my result as following :
stdid eddate A B
100 04/05/2010 if(jan)then feb+mar+april+mayfeb + mar + april+may
if(feb) then mar+april+may mar+april+may
I have tried pivot for first table
select * from tblmonth
pivot(sum(jan) for jan in([jan],[feb])as t
but i m geting data only for jan and not for feb, march .. etc.
And even after getting all data how can i get my above desired output.
October 15, 2010 at 12:04 pm
Would you mind telling us your expected result based on your sample data?
It's probably a lot easier to understand than your current description...
October 15, 2010 at 1:27 pm
Do you mean something like this?
declare @tblmonth table(stdid int, code char(1), jan int, feb int, march int, april int, may int)
declare @tbldate table(stdid int, sdate date, eddate date)
insert into @tblmonth
values
(100, 'A', 10, 12, 50, 21, 30),
(100, 'B', 12, 11, 9, 6, 45),
(101, 'A', 12, 65, 45, 34, 23),
(101, 'B', 34, 32, 12, 56, 34)
insert into @tbldate
values
(100, '20100102', '20100505'),
(101, '20100203', '20100302')
select * from @tblmonth
select * from @tbldate
;with cte as
(
-- Unpivot @tblmonth
select
t.stdid, t.code, m.monthno, m.value
from
@tblmonth t
cross apply
(
values
(1, jan),
(2, feb),
(3, march),
(4, april),
(5, may)
) m(monthno, value)
)
select
d.stdid, d.eddate,
coalesce(sum(case when c.code = 'A' then c.value end), 0) A,
coalesce(sum(case when c.code = 'B' then c.value end), 0) B
from
@tbldate d
left join
cte c on c.stdid = d.stdid and month(eddate) < monthno
group by
d.stdid, d.eddate
order by
d.stdid
October 15, 2010 at 9:34 pm
yes, this is what i want, but what i wanted is to the column A and B the values in @tblmonth be added.
i.e row of a like 10+20+40.. to be added for column A.
I mean Row to be added for column .
here how 0 0 came in the column A and B i m not getting since values in A and B were not 0.
October 15, 2010 at 9:48 pm
Yes i got it . I have removed the condition in join for month :
cte c on c.stdid = d.stdid --and month(eddate) < monthno
now thw the sum is coming but the actual problem is still there.
I want if the date of month for example march then the sum should be for march + april + may
and if date is april then sum should be for april+ may and so on.
I m not getting where to put this logic.
October 16, 2010 at 2:30 am
scottichrosaviakosmos (10/15/2010)
yes, this is what i want, but what i wanted is to the column A and B the values in @tblmonth be added.i.e row of a like 10+20+40.. to be added for column A.
I mean Row to be added for column .
here how 0 0 came in the column A and B i m not getting since values in A and B were not 0.
I changed your sample data a little just to test the left join. The eddate of stdid 100 is 05/05/2010 so there is nothing to sum up after may.
Yes i got it . I have removed the condition in join for month :
cte c on c.stdid = d.stdid --and month(eddate) < monthno
now thw the sum is coming but the actual problem is still there.
I want if the date of month for example march then the sum should be for march + april + may
and if date is april then sum should be for april+ may and so on.
I m not getting where to put this logic.
The condition is required if you only want to sum up months after the month of eddate. Seems you changed your requirements a little. In your first post, you said 'if(feb) then mar+april+may mar+april+may'. Now you say 'if(feb) then feb+mar+april+may feb+mar+april+may'. If this is what you want just change 'and month(eddate) < monthno' to 'and month(eddate) <= monthno'
Peter
October 16, 2010 at 2:33 am
BTW, why did you started a new thread on the answer I gave you?
October 16, 2010 at 11:13 pm
I m not an old member of this group and plus i m a fresher to database. Anyway , your query has made my 60 % work done but still the major problem is there . I tried Rank function by storing month with rank in one temporary table but dont know how to make this logic work for above condition.
October 16, 2010 at 11:26 pm
Yes , your are right what your r saying . but my requirement is same and not changed, sorry by mistake i wrote that. I want if(feb) then sum(after feb ) and yes i want this condition in your same query since i found your script very short and nice, but i am badly stuck in the logic of where to put my logic in your logic.
And if you don't mind may i know why you have not used unpivot instead of cross apply, because i was trying unpivot but it was not working .
By that time you give your suggestion i am trying my logic of ranking the month.
October 17, 2010 at 12:28 am
oops i missed the condition of month.. yes i got it. Thanks
But actually i missed something. I missed one condition for month and year.
If month is feb then it ll sum values after feb but wt if that is feb 2009 and my condition is upto march 2010, then it will add all upto march. but if year is feb 2010 then it ll add only march 2010 ie. for only one month.
again i m stuck. let me try this.
datepart(year,edate) so if year is 2010 then ...
but wt if i get feb 2010 because then i have sum only mar 2010 and not all from march to dec.
My month column will remain same for all year but values for month will differ.
Now my condition starts from march 2009 to march 2010.
Any suggestions how to get this done?.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply