comparing the column values in same table.

• raghava_tg

  Ten Centuries

  Points: 1014

  More actions

  June 16, 2014 at 9:10 am

  #309155

  Help needed for the below task as i am not able to get a hint how to resolve the below task

  create table #temp ( idx int identity(1,1), col1 int, col2 int )

  here i want a flag success or fail on basis of

  below conditions.

  I need to take all the col1 values and then i need to compare to each other.

  if any diffrence found, i need to check diffrence more than 30, then it should raise the flag as "Failure".

  if all the col1 values are ok , then we need to check Col2 values same as above.

  --case 1

  insert into #temp(col1,col2)

  select 16522,18522

  union all

  select 16522,18522

  union all

  select 16582,18522

  --select * from #temp

  --truncate table #temp

  because of diffrence in col1 values . the value of flag should be fail.

  --case 2

  insert into #temp(col1,col2)

  select 16522,18522

  union all

  select 16522,18522

  union all

  select 16522,17522

  here also the col1 is ok but col2 values have difference so it should be Fail.

  otherwise it should be success.

  please do let me know in case of any additional information required.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  June 16, 2014 at 9:24 am

  #1722005

  Something like this?

  if exists

  (

  select count(*)

  from #temp

  having MAX(col1) - MIN(col1) > 30

  or MAX(col2) - MIN(col2) > 30

  )

  select 'Fail'

  else

  select 'Good'

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• raghava_tg

  Ten Centuries

  Points: 1014

  More actions

  June 16, 2014 at 10:33 am

  #1722028

  It is working for now.

  Thanks for the prompt response.

  Thanks again..:-):-):-)

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  June 16, 2014 at 10:45 am

  #1722038

  raghava_tg (6/16/2014)

  ---

  It is working for now.

  Thanks for the prompt response.

  Thanks again..:-):-):-)

  Glad that worked for you. 😀

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• raghava_tg

  Ten Centuries

  Points: 1014

  More actions

  June 17, 2014 at 1:53 am

  #1722172

  can you please why did you used Having clause in the query ?

  i am just curious !!

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  June 17, 2014 at 8:40 am

  #1722350

  raghava_tg (6/17/2014)

  ---

  can you please why did you used Having clause in the query ?

  i am just curious !!

  Because I wanted to do a comparison on the aggregate values, in this case MIN and MAX. Remember you use a where clause to filter rows but you use HAVING when filtering aggregates.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• raghava_tg

  Ten Centuries

  Points: 1014

  More actions

  June 17, 2014 at 9:51 am

  #1722378

  Thanks for the explanation !!!!

  :-):-):-)