dcsimg
Results 1 to 11 of 11

Thread: [RESOLVED] Need explanation of what this statement does?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,857

    Resolved [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,
    Blake

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,048

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    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

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,048

    Re: Need explanation of what this statement does?

    Quote Originally Posted by FunkyDexter View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,764

    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=@Staff--Zone=@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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,857

    Re: Need explanation of what this statement does?

    So essentially it's just a counter...correct?
    Blake

  7. #7

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,857

    Re: Need explanation of what this statement does?

    Thanks for the explanation guys!
    Blake

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,764

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,857

    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!
    Blake

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    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

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,857

    Re: [RESOLVED] Need explanation of what this statement does?

    Thanks guys for your help!
    Blake

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width