Search results for query: *

I just have to weigh in... Please note that I am talking here about things like audits and parent-child relationships, not about implementing super-complex conditional business logic. I created a stored procedure that adds audit triggers to any tables I wish. If a data type is changed or a...

RiverGuy, I didn't 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...

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

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

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

I believe this function will perform significantly faster: CREATE FUNCTION dbo.Unearned ( @StartDate datetime, @EndDate datetime ) RETURNS numeric(27, 12) AS BEGIN RETURN ( SELECT DateDiff(dd, @StartDate, FromYear2) * 1.0 / DateDiff(dd, FromYear1, FromYear2) +...

I missed some blue but you get the idea.

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

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

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

LT, I'm sorry that I was so harsh. I will try to be more careful in my requests in the future so that it's clear to everyone what I'm asking for. Sad as it may sound, I really was trying to help, and not just with SQL. I guess I left out a couple of my final tweaks when I posted the code...

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

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

The reason we've been having trouble helping you is that you provided incorrect information. Your original query joined from the Client table. Now you're using a join table between Client and Agency, which you're calling AgencyClient. (Though perhaps it would be better called Visit, eh?)...

Idea: do a search on the word PIVOT.

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

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

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

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

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