Database

UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN whereclause1 THEN VacationHours + 40
         WHEN whereclause2 THEN VacationHours + 10
         WHEN whereclause3 THEN VacationHours + 19
         WHEN whereclause4 THEN VacationHours + 11
       END
    )

Mycroft Holmes wrote:

The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible

naunt wrote:

Any article or site you want to recommend?

naunt wrote:

Thanks again and have a nice weekend.

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H20,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_H45,0)=0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 Or isNull(TDR_D40,0)>0 Or isNull(TDR_H40,0)>0 Or isNull(TDR_D45,0)>0 Or isNull(TDR_H45,0)>0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND  isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 And isNull(TDR_D40,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H45,0)=0)   AND ((isNull(FL_D20,0)>0 OR isNull(FL_D40,0) >0 OR isNull(FL_D45,0) >0 OR isNull(FL_H20,0) >0 OR isNull(FL_H40,0) >0 OR isNull(FL_H45,0) >0)  OR (isNull(CLL_D20,0) > 0 OR isNull(CLL_D40,0)  > 0 OR isNull(CLL_D45,0) > 0 OR isNull(CLL_H20,0) > 0 OR isNull(CLL_H40,0) > 0 OR isNull(CLL_H45,0)>0))

select @result = count(*) from table where column1 < 100
if @result > 10 then color = blue
else
  select @result = count(*) from table where column1 > 100
  if @result = 20 then color = yellow

cursor = select count(*) from table where column1 < 100
         union all
         select count(*) from table where column1 > 100
if first_row_in_cursor > 10 then color = blue
else if second_row_in_cursor = 20 then  color = yellow

select
      @result1 = (select count(*) from table where column1 < 100)
      @ersult2 = (select count(*) from table where column1 > 100);

if @result1 > 10 then color = blue
else if @result2 = 20 then  color = yellow

SELECT 
	@result1 = COUNT(CASE WHEN (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 ...)	
		THEN 1 ELSE NULL END),
	@result2 = COUNT(CASE WHEN (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 ...)
		THEN 1 ELSE NULL END),
	@result3 = COUNT(CASE WHEN (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 ...))
		THEN 1 ELSE NULL END)
FROM table
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL 

IF @result1 = 20    SET @Color='Red Color'
Else IF @result2=20 SET @Color='Blue Color'
Else IF @result3>0  SET @Color='Blue Color'
Else IF @result4>0  SET @Color='Blue Color'
Else IF @result5=20 SET @Color='Green Color'
Else IF @result6>0  SET @Color='Yellow Color'
Else                    @Color='Orange Color'

Select * from Table1 Where (ISNULL(A1,'')=ISNULL(B1,'') and ISNULL(B1,'')=ISNULL(C1,'')) And (ISNULL(A2,'')=ISNULL(B2,'') and ISNULL(B2,'')=ISNULL(C2,'')) And (ISNULL(A3,'')=ISNULL(B3,'') and ISNULL(B3,'')=ISNULL(C3,''))

DECLARE @TableVar1 table(
    Col1 int NOT NULL,
    Col2 varchar(100),
    Col3 datetime
)

INSERT INTO @TableVar1 (Col1, Col2, Col3) VALUES (10, 'Hello', GETDATE())