CODEWITHSUNDEEP
sql-servert-sql
user2715275
user2715275

Reputation: 35

value comparison on sql server 2008

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:

  1. Get the col1 and col2 of current month(Which is in the monthName column) and do the sum.
  2. Get the col4 cells value from last month. In this case it is February of monthName column.
  3. Compare the col1+col2(Current month) to col4 (Last month in monthName).
  4. In my first example , the row number 2 is true because 4+4=8 which is getting matched with col4 value(8) of January. If col1 and col2 sum of current month match with last month's col4 data then users are good.

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

Answers (2)

Kahn
Kahn

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

jean
jean

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

Related Questions