Cross query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cross query

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-10-15 : 23:56:16

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.

scoo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 03:41:55

i think you need to first unpivot based on month and then join with your month table based on month name. Then group by stdid and take sum based on condition of code values as well as monthnumber > = month(enddate)

ie something like


...
SUM(CASE WHEN  c.code = 'A' and monthnumber > = MONTH(enddate)  then c.value else 0 end) AS A
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources