New to SQL

[royalcheese]

Hi all

I am trying to convert an access JetSQL query into a shiny SQL one.

I am having trouble with if statements

This is the access version (to compare two days)

IIf(Left(doc,2)=Left(djs,2),True,False) AS sD

I want to compare @dCalc_date = dDJS and output on the end of the grid so far as column 'SD'

USE 		BENSTAT06						-- USES BENSTAT DB

DECLARE 	@CALC_DATE VARCHAR(30),
		@DCALC_DATE VARCHAR(2)

SET 		@CALC_DATE = CONVERT(DATETIME,'31/8/2006',103)		-- THIS IS CALC DATE
SET		@DCALC_DATE = DAY(@CALC_DATE)				-- DAY OF CALC

SELECT     	 BPD.MEMBER_KEY						-- UNIQUE REFERENCE NUMBER FOR EACH MEMBER
		,BPD.NI_NUMBER 						-- NATIONAL INSURANCE NUMBER
		,BPD.TITLE 						-- MR / MRS ETC.
		,BPD.INITIALS 						-- MEMBERS INITIALS
		,BPD.SURNAME 						-- MEMBERS SURNAME
		,CONVERT(VARCHAR(30),BPD.DOB,103) AS DOB 		-- DATE OF BIRTH
                ,CONVERT(VARCHAR(30),BPD.DJS,103) AS DJS 		-- DATE JOINED SCHEME
                ,CONVERT(VARCHAR(30),BPD.NRD,103) AS NRD 		-- NORMAL DATE OF RETIREMENT
		,BPD.CURRENT_STATUS 					-- STATUS OF MEMBER
		,CONVERT(VARCHAR(30),Latest_SH.DSH,103) AS DATE_OF_SALARY-- DATE OF LATEST SALARY HELD
		,Latest_SH.BASESAL  					-- LATEST BASE SALARY			
		,YEAR (@CALC_DATE) - YEAR (DJS) AS YEAR_CALCvsDJS	-- YEARS BETWEEN CALCDATE AND DJS
		,MONTH (@CALC_DATE) - MONTH (DJS) AS MONTH_CALCvsDJS	-- MONTHS BETWEEN CALCDATE AND DJS
		,YEAR (NRD) - YEAR (@CALC_DATE)  AS YEAR_CALCvsNRD	-- YEARS BETWEEN CALCDATE AND DJS
		,MONTH (NRD) - MONTH (@CALC_DATE)  AS MONTH_CALCvsNRD	-- MONTHS BETWEEN CALCDATE AND DJS
		,DAY (DJS) AS dDJS		
		,DAY (NRD) AS dNRD
		
      -- WANT IF IN HERE !!
      -- compairing @dCalc_date = dDJS


FROM         	BPD LEFT OUTER JOIN Latest_SH 				-- OUTER JOIN ON BPD AND SALARY
                ON BPD.MEMBER_KEY = Latest_SH.MEMBER_KEY	
		

WHERE     	(BPD.CURRENT_STATUS = N'Active')			-- FILTER MEMBERS ON STATUS = ACTIVE

ORDER BY 	BPD.MEMBER_KEY

GO

 

[bborissov]

CASE WHEN @dCalc_date = dDJS THEN Value When True
     ELSE Values when false END AS Sd

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[royalcheese]

Thanks for help but the error comes on

Server:Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Line 29

 

[bborissov]

What you wrote?
Change "Value when true" and "Values when false" with actial values you want.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[royalcheese]

This is what i have still with error

	.......	,DAY (DJS) AS dDJS		
		,DAY (NRD) AS dNRD
	,CASE WHEN @dCalc_date = dDJS THEN '1' When True
         ELSE '0' when false END AS sD

FROM         	BPD LEFT OUTER JOIN Latest_SH 				-- OUTER JOIN ON BPD AND SALARY
                ON BPD.MEMBER_KEY = Latest_SH.MEMBER_KEY ..........	

[\code]

 

[royalcheese]

ahh ok im thick , sorted it now

Cheers old boy