Search results for query: *

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

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

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

...@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) +...

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

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

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

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

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 :(