Viewing 15 posts - 3,961 through 3,975 (of 4,080 total)
The short answer is "Yes", you can nest CASE expressions.
-------------------------------------------------------------------
select case when 1 = case when col2 = 'B' then 1
...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 26, 2008 at 9:28 am
Thanks 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 25, 2008 at 8:26 am
Care to post up your solution in case anyone else sees this thread and has a similar question?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 24, 2008 at 10:08 am
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...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 24, 2008 at 8:27 am
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....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2008 at 10:05 am
Jeff, that rocks.
But would you please take a little time to explain WHY it works? I will confess to being mystified. I ran...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2008 at 9:27 am
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
...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 22, 2008 at 12:34 pm
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
...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 21, 2008 at 3:26 pm
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. ...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 19, 2008 at 5:29 pm
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
...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 17, 2008 at 5:37 pm
You're passing a constant text string to your parm. Try adding the avg string to your dynamic SQL (@SQL) before you execute it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 17, 2008 at 4:47 pm
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....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 16, 2008 at 12:40 pm
For another column, try this
SELECT DOC, PRODTYPE
,sum(CASE WHEN TERM = 1 THEN 1 ELSE 0 END) as 'YR'
...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 16, 2008 at 8:11 am
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...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 16, 2008 at 7:27 am
Hey Paul
As a general reminder, remember that CASE is an expression, much like a function. It returns a value. Your error is that you can't mix an IF...
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 14, 2008 at 4:23 pm
Viewing 15 posts - 3,961 through 3,975 (of 4,080 total)