Search results for query: *

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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