Shahzad writes "Hello all In following query what i am trying to do is get SUM(Qty) and sa_account from table in OPENQUERY in specific Month and Year. Now if there is no data for that Month or Year I want simple sn_account with SUM as NULL or 0 doesn't matter. But currently what this query is doing is only grabbing those accounts which are in Month and not getting other accounts at all. Thats why i used LEFT OUTERJOIN. I used RIGHT OUTERJOIN as well. But no luck. Actually i tried LEFT JOIN as well. But all in vain. Any weird functions you find in query are actually ColdFusion ones so forget abt them and help me building query. Waiting desperately for anyone's response. SELECT sa_account, SUM(sa_qty) AS Qty, OD.sn_account FROM OPENQUERY(OPERADATA, 'Select sn_account From j_sname') AS OD LEFT OUTER JOIN CURRENTDATA ON CurrentData.sa_account = OD.sn_account WHERE sa_product IN (#Evaluate("qualifiedList1")#) AND CurrentData.sa_trdate Between #LSParseDateTime(form.mystartDate)# AND #LSParseDateTime(form.myendDate)# AND Month(sa_trdate) = '#Counter#' AND Year(sa_trdate) Between '#startYear#' AND '#endYear#' GROUP BY sa_account, OD.sn_account"
TSQL 
Posted - 2001-08-10 : 08:15:48