Re: Nested Iif in View / Stored Procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Kevin3NF (KHill_at_NopeIDontNeedNoSPAM3NF-inc.com)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 13:49:48 -0600

See below. I added "as whatever" after each Case When statement. All that
does is name the field...same as "Quarter: some experession" in Access.

Please let me know if that works and was what you were looking for 

-- 
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Candace" <anonymous@discussions.microsoft.com> wrote in message
news:1411801c41368$a67ddf10$a501280a@phx.gbl...
>
> I got that far:  Here's what I have and I'm not sure what
> to do with respect to the expression names (Quarter,
> Positive_Infection, Short_CVAD, Long_CVAD) I am figuring I
> need to declare them but where do I do this?
>
> Thanks
> Candace
>
> Alter PROCEDURE Sub_Annual_Histogram  @Start_Date
> datetime, @End_Date datetime, @Nursing_Unit nvarchar(25),
> @Team nvarchar(50), @Program nvarchar(50), @Type_of_Vein
> nvarchar(20)
>
> AS SELECT CASE WHEN ((Month(dbo.Day_CVAD_Date) = 4 OR Month
> (dbo.Day_CVAD_Date) = 5 OR Month(dbo.Day_CVAD_Date) = 6))
> THEN Quarter =  1 as Quarter
>     WHEN ((Month(dbo.Day_CVAD_Date) = 7 OR Month
> (dbo.Day_CVAD_Date) = 8 OR Month(dbo.Day_CVAD_Date) = 9)
> THEN Quarter = 2 as Quarter
>     WHEN ((Month(dbo.Day_CVAD_Date) = 10 OR Month
> (dbo.Day_CVAD_Date) = 11 OR Month(dbo.Day_CVAD_Date) = 12)
> THEN Quarter = 3 as Quarter
>     WHEN ((Month(dbo.Day_CVAD_Date) = 1 OR Month
> (dbo.Day_CVAD_Date) = 2 OR Month(dbo.Day_CVAD_Date) = 3))
> THEN Quarter = 4 as Quarter
>     CASE WHEN (dbo.Infection_Nosocomial = 'Yes' AND
> (dbo.Infection_Type = 'Blood' OR dbo.Infection_Type
> = 'Both')) THEN Positive_Infection = 1
>     ELSE Positive_Infection = 0 as Positive_Infection
>     CASE WHEN (dbo.Device_CVAD_Length = 'Short ') THEN
> Short_CVAD = 1
>     ELSE Short_CVAD = 0 as Short_CVAD
>     CASE WHEN (dbo.Device_CVAD_Length = 'Long') THEN
> Long_CVAD = 1
>     ELSE Long_CVAD = 0 as Long_CVAD
> FROM dbo.Device INNER JOIN
>     dbo.Day ON
>     dbo.Device.Device_New_CVAD_ID = dbo.Day.Day_New_CVAD_ID
>      INNER JOIN
>     dbo.Patient_Care_Area ON
>     dbo.Day.Day_Patient_Care_Area_ID =
> dbo.Patient_Care_Area.Patient_Care_Area_ID
>      INNER JOIN
>     dbo.Infection ON
>     dbo.Day.Day_ID = dbo.Infection.Infection_Day_ID
> WHERE (dbo.Patient_Care_Area.Patient_Care_Area_Program
> LIKE @Program) AND
>     (dbo.Patient_Care_Area.Patient_Care_Area_Team_Grouping
> LIKE @Team) AND
>     (dbo.Patient_Care_Area.Patient_Care_Area_Nursing_Unit
> LIKE @Nursing_Unit) AND
>     (dbo.Device.Device_Vein_Line LIKE @Type_of_Vein) AND
>     (dbo.Day.Day_CVAD_Date BETWEEN @Start_Date AND
> @End_Date))
>
> >-----Original Message-----
> >IIF statements become CASE statements in SQL Server
> stored procedures
> >
> >-- 
> >Kevin Hill
> >President
> >3NF Consulting
> >
> >www.3nf-inc.com/NewsGroups.htm
> >
> >"Candace" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:145ad01c41360$f6303880$a101280a@phx.gbl...
> >>
> >> How do you write a nested Iif statement in a view or a
> >> stored procedure?
> >>
> >> Listed below is what I have in a query in Access, I
> >> realize [Day_CVAD_Date] will become dbo.Day_CVAD_Date
> and
> >> I'm assuming the word Quarter is in my Alias column.  So
> >> specifically my question is what syntax do I use in
> >> my 'Column' column?
> >>
> >> Quarter: IIf(Month([Day_CVAD_Date])=4 Or Month
> >> ([Day_CVAD_Date])=5 Or Month([Day_CVAD_Date])=6,1,IIf
> (Month
> >> ([Day_CVAD_Date])=7 Or Month([Day_CVAD_Date])=8 Or Month
> >> ([Day_CVAD_Date])=9,2,IIf(Month([Day_CVAD_Date])=10 Or
> >> Month([Day_CVAD_Date])=11 Or Month([Day_CVAD_Date])
> >> =12,3,IIf(Month([Day_CVAD_Date])=1 Or Month
> >> ([Day_CVAD_Date])=2 Or Month([Day_CVAD_Date])=3,4,0))))
> >>
> >> Candace
> >
> >
> >.
> >

Relevant Pages

  • Re: Nested Iif in View / Stored Procedure
    ... datetime, @End_Date datetime, @Nursing_Unit nvarchar, ... FROM dbo.Device INNER JOIN ... >IIF statements become CASE statements in SQL Server ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Nested Iif in View / Stored Procedure
    ... IIF statements become CASE statements in SQL Server stored procedures ... 3NF Consulting ... "Candace" wrote in message ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Re: List parameters for Stored Procedures
    ... INNER JOIN sys.systypes st ON st.xtype = p.system_type_id ... OP said he wants "to get a list of stored procedures (not system ... I think this is possible by querying the sysobjects table and the ... syscolumns has parameters of procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: Search/ Stored Procedure.. Not Working
    ... I'm sort of new to Stored Procedures; ... > FROM ((tblAccounts INNER JOIN tblManagerList ON ... > Not sure what is missing, but below is what the Query Analyzer comes ... > PRINT @PrnLine ...
    (microsoft.public.dotnet.general)
  • RE: Stored Procedure with Multiple conditions
    ... > stored procedures and then using them for the reports. ... > C.CostCenterID INNER JOIN ... > entries, and then there may only be 1 entry. ...
    (microsoft.public.sqlserver.programming)