Search results for query: *

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 =...

SELECT Table1.Item, Table1.QuantityOnHand, Nz(X.SumOfSales, 0) AS Sales FROM (Table1 LEFT JOIN ( SELECT Item, Sum(Sales) AS SumOfSales WHERE [Date] >= DateAdd("m", -6, Date()) ) AS X ON (Table1.Item = X.Item)) If that doesn't work you'll have to move the derived table query to...

There is no such thing as data in a particular order without an order by clause. It is absolutely not safe to rely on the order that stuff comes out of a table, even in MS Access. The best you can do at this point is write a function that steps through the rows of the recordset and does the...

Yeah, dateserial tolerates out-of-range values for month and day and adjusts appropriately. Quite useful.

Sorry about the missing octothorpe. The total should be equal or lower, as low as half the sum of the two, because when both conditions are true at once they get counted only once. Please show your exact query.

or use hkaing79's table idea, only instead of putting a code for each one, just define the name and then a LIKE expression that will match: Name Expr awbz awbz% then you join to this table on Contract.name LIKE NewTable.Expr and then display/group by NewTable.Name