October 15, 2010 at 9:57 pm
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
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
group by
d.stdid, d.eddate
order by
d.stdid
--------------
Now i want to put a condition on my final result column of A and B that if edate is of month march then sum will be March+ april +may or
if edate is of month april then April + may.
Right now the column A and B in final result is direct sum of all columns of first table.How to get that.
October 16, 2010 at 4:51 am
scottichrosaviakosmos (10/15/2010)
declare @tbldate table(stdid int, sdate date, eddate date)Now i want to put a condition on my final result column of A and B that if edate is of month march then sum will be March+ april +may or
if edate is of month april then April + may.
Right now the column A and B in final result is direct sum of all columns of first table.How to get that.
In your comments, you say edate... do you mean @tbldate.eddate? (edate does not exist in the supplied code anywhere)
Also... it looks like you pasted your query in here twice, the second time in the middle of the first time. You might want to clean it up. It also improves readability if you put the code in sql code tags... just highlight your code, and then click the "[/ code]" IFCode shortcut to the left of the edit window. This will maintain your formatting, and make it somewhat easier to cut/paste from your post. (Quote this post to see how I've done it below.)
In your code, you have remarked out "and month(eddate) < monthno" from your join condition. This is almost correct - make it:
and month(eddate) <= monthno (added the "=")
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply