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

    @str help

    Can you provide a small example of the SQL you _think_ you ought to write? Can't quite understand the problem. Jeff Prenevost BI Consultant Ann Arbor, MI
  2. dGrouse

    Update part of a string in database entries

    Whoops! Posted that a little too fast. You need to take one less than the X left most characters, where "X" is the location of the first colon: update mixed_material_weighments set recipename = [new name] where left(batchID,charindex(':',batchID) - 1) = [recipe code in question] Jeff Prenevost...
  3. dGrouse

    Update part of a string in database entries

    The function you're looking for is "charindex", which returns the location of the first instance of a specified character: update mixed_material_weighments set recipename = [new name] where left(batchID,charindex(':',batchID)) = [recipe code in question] Btw, I encourage you to decompose...
  4. dGrouse

    Count by Month

    Thanks for the clarification. The following assumes you have, or can make, a table PIVOT_HELPER which contains one column, i, with integers from 0 to, say, 1000. A table like that comes in real handy, as in this case. I take it that you have a table I'll call TT_AUTH that's something like...
  5. dGrouse

    Count by Month

    Hi. You need to clarify what it means for you to have an "open auth for a month". Does it mean that the "auth" (whatever that is) was "open" for any portion of the month? If, for instance, member A had a record that looked like this: eff_date = '5/23/2008' end_date = '11/4/2008' would you want...
  6. dGrouse

    Populating Temp Table

    I'm going to assume that this temporary table is a regular table for temporary use which you can assemble more-or-less manually. If you need a single, automated, one-fell-swoop query, it gets a little trickier (though it ain't impossible, of course). Call your question lookup table QUESTION...
  7. dGrouse

    Why sql query is fast with view then it is without view??

    The ORDER BY clause is invalid in views, or at least in that one; you can only have ORDER BY present if TOP or FOR XML are also specified. So, that can't be the code for your view... Jeff Prenevost BI Consultant Ann Arbor, MI
  8. dGrouse

    help with recursive stored procedure

    Don't use PRINT statements to "return records"; ADO certainly won't take your PRINT statements as a record set. You need to return a real record set. Consider populating a table variable, and then spitting out the contents at the end. Jeff Prenevost BI Consultant Ann Arbor, MI
  9. dGrouse

    Update or insert

    Well, given that this is a one-off, what I'd do, after making a backup copy of table1, is the following: select t1.uniqueID ,t1.makecode ,t1.modelcode ,t1.body ,t1.date ,1 as matchID ,t2.completecode into matches from table1 t1 inner join table2 t2 on t1.cc = t2.cc and...
  10. dGrouse

    Rolling Report for Last 13 months

    First, make a permanent table, which I'll call PIV, which contains integers from, say, -1000 to +1000. Such a table comes in handy in many situations. I take it that your current data looks something like this: ORDERS open_date datetime close_date datetime order_num int order_revenue money...
  11. dGrouse

    Storing complex order pipeline data in a DW, is Kimball method applic?

    With such a large and indeterminate chain of events, I might look at "state change" fact table, which is basically a transaction fact table, where each fact is the event of an entity (in your case, an order) changing state (in your case, going from one point in the pipeline to another). Put a...
  12. dGrouse

    ConfusingDates

    If there's some consistent pattern to the mismatched adate & servicedate fields -- if the one is always on or up to 2 days after the other, say -- then you could simply write that into the join condition between your two fields (going to, say, a SQL command object rather than using the native...
  13. dGrouse

    Count of records per date

    Group by your datetime field, by day, and then add a summary field on an appropriate detail field to count your records by day. Jeff Prenevost IS Administrator Emergency Physicians Medical Group, PC Ann Arbor, MI
  14. dGrouse

    Crystal Syntax problem

    When you say "only showing some of the records information", do you mean that you're only displaying your text string result for _some_ of the records, or for _all_ records, you're only displaying some of the text string you're trying to get, or both? Might try adding semicolons after this...
  15. dGrouse

    Count of Previous Codes per Group

    I assume that defect records for "tube 1" and "tube 2", etc., are all in the same table, and that there's another column we're not seeing that identifies the particular tube -- is that correct? If so, here's a solution based on a SQL command object that gives you what you need: select...
  16. dGrouse

    newbie formula question

    Well, I'm sure there are a number of ways to handle this, but one that comes to mind is to group by project, day, and activity code, and write formula to do running totals of the non-trav and trav hours (in the detail section), displaying the different values in the appropriate group footer...
  17. dGrouse

    Summary result from Sub to Main header?

    You can define a shared variable in your subreport, but given that subreports are processed in the second pass, and the report will already have finished with your page header before it gets to the subreport in your group footer, I don't think it'll be possible for you to pass the shared...
  18. dGrouse

    Configure default printer for destination

    It's certainly not "my" local Windows default printer when I'm scheduling a report to run using ePortfolio. I can schedule a report to run two weeks from now on one of the machines in a CE cluster, and, when it runs, I want it to be printed to "the default printer" as a CE...
  19. dGrouse

    Configure default printer for destination

    In ePortfolio, in CE 9, you can set a report instance to go to a "Default printer", under "Customize your options" > "Print settings". There, you can check the box to "Print a copy of the report in Crystal Reports format when scheduling". Then, you can...

Part and Inventory Search

Back
Top