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

    Easy trigger?

    ...of in most cases failing when I run the audit-trigger-creating SP instead of failing at data insertion time. Early and conspicuous failure is a *great* feature of systems—the alternatives are late (user-presented instead of developer-presented) failure or early and silent failure (no one...
  2. ESquared

    Stored Procedure Question

    ...mean to step on your toes. In any case, thanks to Borislav and some further research, I stand corrected! It is better to use SELECT 1 than SELECT * because SELECT * makes the query compilation just a little more expensive by making it expand * into all the columns in the table, then it...
  3. ESquared

    functions within sp_executesql

    sp_executesql only lets you execute a single batch at a time. If you have script you're running that SQL Server demands to be the first statement in a batch, then that script must be submitted on its own. The reason for this is that GO is actually a client-side instruction and is usually never...
  4. ESquared

    Stored Procedure Question

    The accepted syntax for EXISTS() is to use SELECT *, not SELECT 1.
  5. ESquared

    VBA Function-> Stored Procedure!??

    By the way, SimonSellick's function gives wrong values. Try these as input: StartDate, EndDate 2008-07-01, 2008-09-01 2008-07-01, 2010-07-01
  6. ESquared

    VBA Function-> Stored Procedure!??

    ...@StartDate datetime, @EndDate datetime ) RETURNS numeric(27, 12) AS BEGIN RETURN ( SELECT DateDiff(dd, @StartDate, FromYear2) * 1.0 / DateDiff(dd, FromYear1, FromYear2) + DateDiff(dd, EndYear1, @EndDate) * 1.0 / DateDiff(dd, EndYear1, EndYear2) +...
  7. ESquared

    Loop required?

    I missed some blue but you get the idea.
  8. ESquared

    combining tables while including NULLs

    P.P.S. If you're displaying this in a web page, don't use the PIVOT. Just return two rowsets, one with the first few columns as columns (the group by values) and one with the remaining columns as rows (the pivoted values). If they are sorted correctly (the query for both rowsets can include...
  9. ESquared

    combining tables while including NULLs

    P.S. A little more extended searching on PIVOT would have found examples like this one. This is a classic PIVOT strategy. It doesn't matter that you only want single values and aren't getting the Max of anything. The Max function is used only to throw away the NULL values that you don't want for...
  10. ESquared

    combining tables while including NULLs

    I think you'll find that this is easier to maintain and performs substantially better than joining for each column. SELECT ResponseID, ResponseDate = Response.Ended ResponseType = 'I', responseLoc = Max(CASE WHEN A.ItemID = 22839 THEN T.TextValue ELSE NULL END), responseSvcDate =...
  11. ESquared

    Loop required?

    ...= ag.agnum inner join admission adm on a.agclientnum = adm.agclientnum left join discharge dis on adm.admitnum = dis.admitnum go select m.* from manuallist m where exists ( select * from visit v1 inner join visit v2 on v1.clientid = v2.clientid and...
  12. ESquared

    Loop required?

    When you hit that bug, just call my lawyers. :)
  13. ESquared

    Loop required?

    An error fix. I got one column name swapped. And this doesn't find undischarged visits that overlap. So alter the join to add some Coalesces, and see the bold column name change. inner join visit v2 on v1.clientid = v2.clientid and v1.admitnum <> v2.admitnum and v1.admitdate <=...
  14. ESquared

    Loop required?

    ...and ag.test = 0 --Eliminates Test Agency Data group by a.clientid, ag.provider, adm.admitdate, dis.dischdate GO create view Visit as select a.*, adm.admitnum, adm.admitdate, dis.dischnum, dis.dischdate from agencyclient a inner join admission adm on a.agclientnum =...
  15. ESquared

    combining tables while including NULLs

    Idea: do a search on the word PIVOT.
  16. ESquared

    OR conditions cannot use indexes?

    I see that you did say that! Sorry about that. I am curious, too, and I wish I could play with your data.
  17. ESquared

    Loop required?

    So I'm asking you to explicitly explain how a set of admit and discharge dates can be correlated to a particular provider? Unless there is some unshown relation between tprovider and tadmit or between clientnum and provnum, the table design I see doesn't make sense. You said providers own the...
  18. ESquared

    How store &quot;Date()&quot; value

    Yes, it always helps to keep the eyes peeled for the Eye-Dee Ten Tee error.
  19. ESquared

    Trigger: Changing Value with no UniqueID on table

    You might also look into an INSTEAD OF trigger which would help well with INSERTS but still be a total pain for the UPDATES (without a unique key you'd have to join to the "Inserted" meta-table on every column, taking care to compare NULLs properly, besides). But at least you might want to be...
  20. ESquared

    SQL Joins , exclude unwanted records without using WHERE

    I should learn to use preview. Silly forum markup language differences :(

Part and Inventory Search

Back
Top