Hi

I currently have a crm system running reports over night as they can take some time to run. I am trying to convert them to sql but with no joy in some cases. I only have very limited knowledge of sql and we are using sql 2005.

Code:
On OpenSelection
global myfromdate

Put daynumberofdate("14-Aug-2009") into myfromdate

set the dateformat to "yyyy/mm/dd"

put "PRODUCTLINES.MASTERPRODLINENO <> '00000000-0000-0000-0000-000000000000'" into sqlFilter
put " AND PRODUCTLINES.QUANTITY <> 0" after sqlFilter
put " AND PRODUCTLINES.CREATIONDATE >='" & date(myfromdate) & "'" after sqlFilter

call("IDDSetViewLinkFilter" The ReportViewName,1,sqlFilter)

End OpenSelection


On SelectRecord
if {ACTIVITYHEADER.USERTYPE} = "5. Order" then
	if char 1 to 5 of {PRODUCTS.NUMBER} <> "CONT2" then
		if {ACTIVITY.ACTIVITYNO} is empty then 
			set the messageresult to true
		end if
	end if
end if
End SelectRecord
My Current sql conversion
Code:
SELECT     ACTIVITYHEADER.USERCODE, PRODUCTLINES.NUMBER, PRODUCTS.NUMBER AS Expr1, PRODUCTLINES.CREATEDBY, 
                      PRODUCTLINES.CREATIONDATE
FROM         PRODUCTLINES INNER JOIN
                      ACTIVITY ON PRODUCTLINES.PRODLINENO = ACTIVITY.PRODLINENO INNER JOIN
                      ACTIVITYHEADER ON PRODUCTLINES.ACTIVITYHEADERNO = ACTIVITYHEADER.ACTIVITYHEADERNO INNER JOIN
                      PRODUCTS ON PRODUCTLINES.MASTER_PRODUCTNO = PRODUCTS.PRODUCTNO
WHERE     (PRODUCTLINES.MASTERPRODLINENO <> '00000000-0000-0000-0000-000000000000') AND (PRODUCTLINES.QUANTITY <> 0) AND 
                      (PRODUCTLINES.CREATIONDATE >= CONVERT(DATETIME, '2007-11-01 00:00:00', 102)) AND (ACTIVITYHEADER.USERTYPE = '5. Order') AND 
                      (PRODUCTS.NUMBER NOT LIKE 'CONT2%') AND (ACTIVITY.ACTIVITYNO is null)
This does not bring back any results were I am expecting some. I believe the issue is in the part in Bold as in the crm system it does the sql query then queries it again. Is this a nested select and how would I go about doing it.

Thanks in advance for any help provided.