Reputation: 35
There is a table of 5 columns :
col1,col2,col3,col4,monthName
Something like:
col1 | col2 | col3 | col4 | monthName
----------------------------------------
4 | 5 | 55 | 8 | January
4 | 4 | 33 | 6 | February
col1 and col2 sum must be same as col4 of the last month. So i can compare it like
select * from table1 where col1+col2=col4 where monthName='February'
but it will compare all the rows wheres i want it to be month specific something like
select *
from table1
where col1+col2 = (select col4 from table1 where monthName='January')
where monthName='February'
How i do this in correct way?
Well, It is something about monthly report.
Perhaps my main problem is getting col4 from the past month(such as january).
I am trying to explain it more clearly:
I can get col1 and col2 very easily:
select sum(col1+col2) as currentTotal from table1 where monthName='February'
But how i get the col4 value from last month and store it somewhere then compare to currentTotal? Every month there will be 15 rows inserted and will be compared to last month.
Not sure if this time i explained a little better!
Upvotes: 1
Views: 91
Reputation: 1660
The month naming is dubious, but with the information we have, the following should do the trick:
-- Creating a table variable as a sample for your query.
DECLARE @T TABLE (col1 INT, col2 INT, col3 INT, col4 INT, monthName VARCHAR(25))
INSERT INTO @T VALUES (4,4,55,9,'January'),(5,4,3,6,'February'), (3,3,3,6,'March')
-- Update the references to your actual table in this query.
;WITH CTE AS (SELECT *
, DATEPART(MM,monthName+' 01 2014') Mnum
FROM @T)
SELECT CA.*
FROM CTE C
CROSS APPLY
(SELECT col1, col2, col3, col4, monthName
FROM CTE B
WHERE B.Mnum-1 = C.Mnum AND (B.col1+B.col2 = C.col4)) CA
ORDER BY Mnum
What it does, is it assigns a number for the monthname, then uses that set in order of month numbers, and with CROSS APPLY selects only rows where the sum of col1 and 2 amount to the col4 of previous month number.
Upvotes: 1
Reputation: 4350
You can try something like the pseudo code below
select *
from table1 t1
join
(
select monthName, SUM(col1+col2-col4) as isZero
group by monthName
) as t2
on t2.monthName = t1.monthName
where t2.isZero != 0
Upvotes: 0