
May 25th, 2018, 02:38 AM
#1
Thread Starter
PowerPoster
[RESOLVED] Need explanation of what this statement does?
Can anyone tell me what this statement is doing? To me, it doesn't look like it's summing anything. I've never seen this kind of statement before.
Code:
SUM(case when isnull(old.field1, '') = isnull(new.field1, '') THEN 1 ELSE 0 END)
Thanks,

May 25th, 2018, 02:49 AM
#2
Re: Need explanation of what this statement does?
It's effectively counting the number of rows in which 'old.field1' and 'new.field1' contain the same value and it is treating NULL and an empty string as being equal. For each row, it basically replaces NULL with an empty string in the 'field1' column of both 'old' and 'new'. It then compares those two columns and returns 1 if they are equal and 0 if they are not. It then sums all those numeric results.

May 25th, 2018, 02:51 AM
#3
Re: Need explanation of what this statement does?
It's a way of doing a count of records which meet a criteria.
So this bit:
Code:
case when isnull(old.field1, '') = isnull(new.field1, '') THEN 1 ELSE 0 END
…will return 1 when old.field1 = new.field1. The isnulls are probably there to make NULL = NULL (it usually doesn't, but that's a whole 'nother discussion) but they will have the side effect of allowing blank to equal null too. When those equalities are not met it will return zero.
The sum then adds all the zeros and ones together which, since zero doesn't have any effect on an addition, is the same as adding all the ones together.
The net effect is that you'll get a count of all the records where the case criteria is met.
edit>crossed with JM
You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility  Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire  Inferrd

May 25th, 2018, 03:06 AM
#4
Re: Need explanation of what this statement does?
Originally Posted by FunkyDexter
edit>crossed with JM
If you didn't spend so much time pontificating and adding your fancy code tags, maybe you'd get in first.

May 25th, 2018, 09:55 AM
#5
Re: Need explanation of what this statement does?
I use SUM(CASE...) all the time
This finds all the tasks for a given zone and counts those due in each of the upcoming 5 weeks.
Code:
Select @TaskCnt=IsNull(Sum(1),0), @TaskLatest=Max(TaskDate)
,@TaskCntW1=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 1 and 7 Then 1 Else 0 End),0)
,@TaskCntW2=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 8 and 14 Then 1 Else 0 End),0)
,@TaskCntW3=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 15 and 21 Then 1 Else 0 End),0)
,@TaskCntW4=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 22 and 28 Then 1 Else 0 End),0)
,@TaskCntW5=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 29 and 35 Then 1 Else 0 End),0)
,@TaskCntOther=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) not Between 1 and 35 Then 1 Else 0 End),0)
From Task_V Where TaskStatus not in ('Completed','Deleted') and Employee=@StaffZone=@Zone
This one gets the violation codes along with the count of FAILS, the TOTAL count and also the LATEST DATE
Code:
Select viosCode,f_text
,sum(case When f_pass='Fail' Then 1 Else 0 End) "FailCount"
,sum(1) "TotalCount"
,Convert(varchar(10),max(f_date),101) "LatestDate"
This one does TASK STATISTICS by FACILITY  note that it's not a simple Then 1 Else 0 End like the others.
Code:
Select TV.Facility "~wide~Facility"
,FT.EdLevel "~wide~Ed Level"
,FT.NonPublic "~wide~Non Public"
,ST.Category "~wide~Category"
,ST.Statistic "~wide~Statistic"
,Sum(Case When DatePart(mm,TS.SDate)=7 Then TS.SCount Else 0 End) ">Jul"
,Sum(Case When DatePart(mm,TS.SDate)=8 Then TS.SCount Else 0 End) ">Aug"
,Sum(Case When DatePart(mm,TS.SDate)=9 Then TS.SCount Else 0 End) ">Sep"
,Sum(Case When DatePart(mm,TS.SDate)=10 Then TS.SCount Else 0 End) ">Oct"
,Sum(Case When DatePart(mm,TS.SDate)=11 Then TS.SCount Else 0 End) ">Nov"
,Sum(Case When DatePart(mm,TS.SDate)=12 Then TS.SCount Else 0 End) ">Dec"
,Sum(Case When DatePart(mm,TS.SDate)=1 Then TS.SCount Else 0 End) ">Jan"
,Sum(Case When DatePart(mm,TS.SDate)=2 Then TS.SCount Else 0 End) ">Feb"
,Sum(Case When DatePart(mm,TS.SDate)=3 Then TS.SCount Else 0 End) ">Mar"
,Sum(Case When DatePart(mm,TS.SDate)=4 Then TS.SCount Else 0 End) ">Apr"
,Sum(Case When DatePart(mm,TS.SDate)=5 Then TS.SCount Else 0 End) ">May"
,Sum(Case When DatePart(mm,TS.SDate)=6 Then TS.SCount Else 0 End) ">Jun"
,IsNull(Sum(Case When DatePart(mm,TS.SDate)=7 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=8 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=9 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=10 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=11 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=12 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=1 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=2 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=3 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=4 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=5 Then TS.SCount Else 0 End),0)
+IsNull(Sum(Case When DatePart(mm,TS.SDate)=6 Then TS.SCount Else 0 End),0) ">Total"
,ST.*
,TS.*
,TV.*
From TaskStatistics_T TS
Left Join Activity_T AT on AT.ActId=TS.ActId
Left Join Task_V TV on TV.TaskId=AT.TaskId
Left Join Statistics_T ST on ST.SType=TS.SType
Left Join Facility_T FT on FT.Facility=TV.Facility
Where TaskType='Statistics' and TaskStatus<>'Deleted'
and TS.SDate between @SDate and @EDate
Group by TV.Facility,ST.Category,ST.Statistic,FT.EdLevel,FT.NonPublic

May 25th, 2018, 10:05 AM
#6
Thread Starter
PowerPoster
Re: Need explanation of what this statement does?
So essentially it's just a counter...correct?

May 25th, 2018, 10:08 AM
#7
Thread Starter
PowerPoster
Re: Need explanation of what this statement does?
Thanks for the explanation guys!

May 25th, 2018, 10:11 AM
#8
Re: Need explanation of what this statement does?
Yes  just a counter in most cases.
If you are writing financial systems you would do something like SUM(AmountPaid) to add up that columns values.
Sometimes you want to count (or summarize  or better yet the term is aggregate) something and do so conditionally  that is where the CASE statement works it wonders.

May 25th, 2018, 10:15 AM
#9
Thread Starter
PowerPoster
Re: Need explanation of what this statement does?
I understand what the SUM statement does. But n ow that I put the statement in perspective, it makes sense. I've just never used the SUM statement in this context before and it stumped me.
Thanks again!

May 29th, 2018, 02:40 AM
#10
Re: Need explanation of what this statement does?
If you didn't spend so much time pontificating and adding your fancy code tags, maybe you'd get in first.
The tortoise won in the end
(Yeah, I do tend to get a bit too verbose though)
It tends to get used as a poor mans pivot a lot:
Code:
Select Sum(Case When Col1 = 'Val1' then 1 else o end) as [VAL1],
Sum(Case When Col1 = 'Val2' then 1 else 0 end) as [VAL2],
Sum(Case When Col1 = 'Val3' then 1 else 0 end) as [VAL3]
From SomeTable
You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility  Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire  Inferrd

May 29th, 2018, 10:08 AM
#11
Thread Starter
PowerPoster
Re: [RESOLVED] Need explanation of what this statement does?
Thanks guys for your help!
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
