Need help with statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with statement

Author

Topic

rsvore
Starting Member

15 Posts

Posted - 2012-02-10 : 09:30:06

I'm teaching myself SQL and also trying to convert FoxPro statements to SQL. I have this statement that gives me an eroor , Incorrect syntax near = Can anyone help me resolve this. Thanks,

SELECT sum(IIF (tp.sex='F' AND tt_dtl.prog_type='I',1,0)) as female,count(*) as all
from tp,tt_dtl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:34:42

quote:
Originally posted by rsvore

I'm teaching myself SQL and also trying to convert FoxPro statements to SQL. I have this statement that gives me an eroor , Incorrect syntax near = Can anyone help me resolve this. Thanks,

SELECT sum(IIF (tp.sex='F' AND tt_dtl.prog_type='I',1,0)) as female,count(*) as all
from tp,tt_dtl

IIF is not available in T-sql atleast until SQL 2012

so you should be using CASE WHEN instead


SELECT SUM(CASE WHEN tp.sex='F' AND tt_dtl.prog_type='I' THEN 1 ELSE 0 END) as female,
count(*) as all
from tp,tt_dtl
some condition missing

Not sure if you realised but above code is doing a cross join (cartesian product) as you've not specified condition based on which tables need to be joined. so count(*) will return rowcount of tp * rowcount of tt_dtl. si this what you're really after?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsvore
Starting Member

15 Posts

Posted - 2012-02-10 : 10:12:15

Thanks,

Here is the rest of the statement which has the problem of Convert to Date (CTOD). Maybe it should be something like:
AND last_dt >= ('10/1"' & @fy-3 )

SELECT SUM(CASE WHEN tp.sex='F' AND tt_dtl.prog_type='I' THEN 1 ELSE 0 END) as female,
count(*) as [all]
from tp,tt_dtl
WHERE tp.cc=@cc and tp.case_id_ds+tp.case_mstr+tp.trk_seq=LEFT(tt_dtl.trk_link,20)
AND last_dt between CTOD('10/1/" & @fy-3 & "') AND CTOD('12/31/" & @fy-2 & "')
GROUP BY tp.cc

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-10 : 10:15:23

Look up the CONVERT function in Books Online (SQL Server help).

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 10:24:32

quote:
Originally posted by rsvore

Thanks,

Here is the rest of the statement which has the problem of Convert to Date (CTOD). Maybe it should be something like:
AND last_dt >= ('10/1"' & @fy-3 )

SELECT SUM(CASE WHEN tp.sex='F' AND tt_dtl.prog_type='I' THEN 1 ELSE 0 END) as female,
count(*) as [all]
from tp,tt_dtl
WHERE tp.cc=@cc and tp.case_id_ds+tp.case_mstr+tp.trk_seq=LEFT(tt_dtl.trk_link,20)
AND last_dt between CTOD('10/1/" & @fy-3 & "') AND CTOD('12/31/" & @fy-2 & "')
GROUP BY tp.cc

is this t-sql
i dont think CTOD is sql system function?
or is it udf?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsvore
Starting Member

15 Posts

Posted - 2012-02-10 : 10:33:30

It's TSQL

I tried the CAST like this:
AND last_dt >= '10/1/' + CAST(@fy-3 as char(4)) AND last_dt <='12/31/' + CAST(@fy-2 as char(4))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 10:37:06

why you're doing like this? assuming @fy is integer you can just do


AND last_dt >= DATEADD(mm,9,DATEADD(yy,(@fy-3)-1900,0)) AND last_dt <=DATEADD(yy,(@fy-1)-1900,0)-1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsvore
Starting Member

15 Posts

Posted - 2012-02-10 : 11:53:05

Yes @fy is an integer 2011.
Thanks it works, thanks a bunch. I didn't know a better way to do it but appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 11:54:42

ok...go through below link for tips on dates

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources