Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. Emtucifor

    error when running openquery

    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...
  2. Emtucifor

    help with openquery update statement

    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...
  3. Emtucifor

    Need help with a SQL query

    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...
  4. Emtucifor

    Coalesce vs. IF

    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...
  5. Emtucifor

    *SPECIAL* query help

    You also need to redesign your database. Truly.
  6. Emtucifor

    Count of Count subquery

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

    Entities which have relationship through one table but not another.

    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...
  8. Emtucifor

    SQL code to display text

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

    Averages for different data all displayed in one row

    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...
  10. Emtucifor

    Strange Loop Behavior

    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
  11. Emtucifor

    Strange Loop Behavior

    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
  12. Emtucifor

    Combine SELECT UNION with Cursor

    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...
  13. Emtucifor

    Coalesce vs. IF

    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...
  14. Emtucifor

    SQL Union/Join

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

    SQL Union/Join

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

    SQL Union/Join

    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...
  17. Emtucifor

    Difficult Row Number Needed

    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...
  18. Emtucifor

    Get first day of previous month in Access

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

    Aggregate SQL Count Problem

    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.
  20. Emtucifor

    outputting similar values as 1 value

    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

Part and Inventory Search

Back
Top