Different search criteria don't always imply a different join. Oftentimes, tables are joined by a foreign key, but other columns are tested in the WHERE clause to determine...

The short answer is "Yes", you can nest CASE expressions.

-------------------------------------------------------------------

select case when 1 = case when col2 = 'B' then 1

...

Thanks 😉

Care to post up your solution in case anyone else sees this thread and has a similar question?

Jeff's right. My company tracks turntimes for a number of different processes. Once you've identified which jobs are taking the most time, the next question...

You already have a sequence number in your table, so you can "look ahead" to see the open date for the next step using a left join as shown below....

Jeff, that rocks.

But would you please take a little time to explain WHY it works? I will confess to being mystified. I ran...

Here you go, Lynn. It works regardless of the @@datefirst setting.

ALTER FUNCTION dbo.ufNextSunday( @from datetime)

RETURNS datetime

AS

BEGIN

declare @nextSunday datetime

SELECT @nextSunday = @from+N

from tally

where datepart(dw,@from+N) = 8-@@datefirst

...

Assuming Sunday is dw=1 on your server:

ALTER FUNCTION dbo.ufNextSunday( @from datetime)

RETURNS datetime

AS

BEGIN

declare @nextSunday datetime

SELECT @nextSunday = @from+N

from tally

where datepart(dw,@from+N) =1

...

I don't know of one, so I'll bow out.

However, I don't understand your concern about insertions if you are building strings under your own control. ...

This should work for both single and multiple row inserts into the sales table.

------------------------------------------------------------------------------------

ALTER TRIGGER UpdateWarehouseQty ON SALES_ITEM AFTER INSERT

AS

BEGIN

UPDATE WAREHOUSE

SET WAREHOUSE.ItemQty = warehouse.ItemQTY-dt.qty

FROM (select inserted.itemNumberSK, sum(qty) as qty

...

You're passing a constant text string to your parm. Try adding the avg string to your dynamic SQL (@SQL) before you execute it.

Thanks, Barry.

I was just responding to Angel's question and his specific code, but of course you are correct that simply qualifying table names isn't enough with many DDL commands....

For another column, try this

SELECT DOC, PRODTYPE

,sum(CASE WHEN TERM = 1 THEN 1 ELSE 0 END) as 'YR'

...

You shouldn't need "Use Database", if you are building qualified table name strings to include the DB name. Try that when you build @strTable. Assuming...