Recent content by Emtucifor

The only reason you'd use FMTONLY off is to deal with SQL Server's propensity to try to get resultset metadata out of queries that are submitted to it (because it secretly runs the SP once with SET FMTONLY ON, which fails for example if the SP creates a temp table since the table won't be...

Can you run any kind of trace on the remote system to see what actual queries are being submitted? I've found that extremely useful in diagnosing problems with linked-server queries. Also, you should know that linked server queries do NOT operate how one might expect. You can get very strange...

Don't use datediff on the column, please. Do it like this: SELECT ParentID, LastCompletedDate = Max(StatusDate) FROM Child WHERE [Status] = 'Completed' AND StatusDate <= DateAdd(Day, -120, GetDate()) GROUP BY ParentID Of further note is that the child table should have statuses in a lookup...

John, I would like to point out that just because two execution plans are not the same does not mean the two result sets aren't identical or that the two queries don't have identical semantic meaning. I could use a LEFT JOIN RightTable with a WHERE RightColumn = NULL or a NOT EXISTS (SELECT 1...

You also need to redesign your database. Truly.

Note that you don't need the State column in the inner query (though of course you keep the GROUP BY State part).

I would remove `OR [Valid] = 0`, and put `AND [Valid] = 1` in the LEFT JOIN to Acknowledgement. It's logically more clear and might perform a little better. Also, I'd check to see if the following query performs better: SELECT m.*, soi.SaleOrder_ID, soi.SaleOrderItem_ID, s.SaleOrder_Number...

P.S. djj55, it's spelled Shepherd not Shepard

Note: some of your formulas refer to empty cells (see the (91) and (107) in red). I think you should use SSIS to create the reports. Then you can put all sorts of formulas in there to your heart's content. You would just need to pull the correct values for each section. The 1650, 1800, 1950...

And in case I misunderstood your requirements: with data as ( select *, newranking = row_number() over (partition by distdate order by rownum) from @v_table1 ) update data set ranking = newranking

If you have SQL 2005 and up: with data as ( select *, newranking = rownum - min(rownum) over (partition by distdate) + 1 from @v_table1 ) update data set ranking = newranking

Saners, I applaud you for your good attitude in your response. I would probably not have handled it as well. SQLSister's answer, though harsh, wasn't really misplaced...

quick note: dbo.tblTrackingTable.TrackingDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @Enter_Starting_Date), 0) And DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, @Enter_Ending_Date), 1)) This isn't best. Presuming the column is indeed smalldatetime then it will work, but it isn't best...

P.S. I think derived table aliases may not accept "AS", like so: ) X ON (Table1.Item = X.Item))

My apologies. I left out a FROM clause: SELECT Table1.Item, Table1.QuantityOnHand, Nz(X.SumOfSales, 0) AS Sales FROM (Table1 LEFT JOIN ( SELECT Item, Sum(Sales) AS SumOfSales FROM Table2 WHERE [Date] >= DateAdd("m", -6, Date()) ) AS X ON (Table1.Item =...