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.
| Author |
Topic |
|
usafelix
Posting Yak Master
165 Posts |
 Posted - 2015-01-26 : 01:52:11
|
| Dear Sir,I write this query but the total is wrong on 1 to 3 rec, only last record total 4 is right, please help to edit this query ?case when salesman_code is null then 0 else 1 end +case when salesman_code2 is null then 0 else 1 end +case when salesman_code3 is null then 0 else 1 end +case when salesman_code4 is null then 0 else 1 end as total---------------------------------------------------Data Samplesalesman_code,salesman_code2,salesman_code3,salesman_code4, totalstaff-01 2blank space blank space blank space blank space 2 staff-01 staff-03 3 staff-02 staff-01 staff-6 staff-04 4----------------------------------------------------- |
|
|
pascal_jimi
Posting Yak Master
167 Posts |
Posted - 2015-01-26 : 03:42:37
|
| hiwhere hier error sum case when ...?your script sum case when .. uses succefulydeclare @test table(id int not null identity(1,1),salesman_code4 nchar(20) null, salesman_code5 nchar(20) null, salesman_code6 nchar(20) null)insert into @test(salesman_code4,salesman_code5,salesman_code6)select null,'1','2'unionselect '1','2',null select*from @test select sum(case when salesman_code4 is null then 0 else 1 end + case when salesman_code5 is null then 0 else 1 end +case when salesman_code6 is null then 0 else 1 end ) from @test(????? ??????????: 2)id salesman_code4 salesman_code5 salesman_code6----------- -------------------- -------------------- --------------------1 NULL 1 2 2 1 2 NULL(????? ??????????: 2)-----------4(????? ??????????: 1)http://sql-az.tr.gg/ |
 |
|
|
usafelix
Posting Yak Master
165 Posts |
Posted - 2015-01-26 : 05:31:28
|
| the problem is fixed. |
|
|
|
|
|
|
|
|
total is wrong, why ?