SQL Server Performance

Getting Null values from Cursor

Discussion in 'T-SQL Performance Tuning for Developers' started by T-SQLQuestion, Mar 31, 2003.

  1. T-SQLQuestion New Member

    I would like to create a stored procedure to return various items from my select statment and two calculations done in the stored procedure. What i need to do is a year to date count and a month to date count. I would assign a value of 1 if its the first infant born for that year or first infant born that month. Can anyone provide me of an example for values passed to the stored procedure. I have the following code that returns null values for my year and month columns can anyone please help.


    Declare @medhisitid int, @HisNum int, @Gravida int, @DeliveredDate datetime, @DeliveredTime datetime,@SeqNum int, @NumInfants int, @y int, @m int
    Declare @BegDate Datetime, @EndDate datetime, @NewBDate datetime, @NewEDate datetime
    Set @BegDate = '2002-12-01'
    Set @EndDate = '2003-1-31'

    /*Set @NewBDate = LTRIM(CONVERT(CHAR(4), Year(@BegDate))) + '-01-01'
    Set @NewEDate = LTRIM(CONVERT(CHAR(4), Year(@EndDate))) + '-12-31' */

    Declare Deliveries Cursor for

    SELECT DCR1.medhistid, DCR1.HisNum, DCR1.Gravida, DCR1.DeliveredDate, DCR1.DeliveredTime, DCR2.SeqNum, DCR2.NumInfants, @y, @m FROM DCR1 INNER JOIN DCR2 ON (DCR1.HISNum = DCR2.HISNum) AND (DCR1.medhistid = DCR2.medhistid) WHERE DCR1.DeliveredDate Between @BegDate And @EndDate ORDER BY DCR1.DeliveredDate, DCR1.DeliveredTime, DCR2.SeqNum


    /*declare the variables that will hold values retrieved by the cursor*/
    Declare @holdMonth int, @holdYear int

    Open Deliveries
    Fetch Next From Deliveries Into @medhisitid, @HisNum, @Gravida, @DeliveredDate, @DeliveredTime,@SeqNum, @NumInfants, @y, @m

    SET @holdMonth = Month(@DeliveredDate)
    SET @holdYear = Year(@DeliveredDate)
    While @@fetch_status = 0

    Begin
    If @holdYear <> Year(@DeliveredDate) Begin
    set @y = 0
    End

    If @holdMonth <> Month(@DeliveredDate) Begin
    set @m = 0
    End

    If @SeqNum = 1 Begin
    Set @y = @y + 1
    Set @m = @m + 1
    /*Else If @NumInfants >= 2 And @SeqNum >= 2 Begin


    End */
    End

    Set @holdYear = Year(@DeliveredDate)
    Set @holdMonth = Month(@DeliveredDate)
    fetch Next from Deliveries
    End
    Close Deliveries
    Deallocate Deliveries
  2. Jacco New Member

    It is a bit difficult to work out what you exactly want without table structure, sample data and the expected output, and your narrative isn't all clear to me either, sorry for that.

    However, I can give you one part of the solution to start with, which is for the year to date and month to date counts. You can do that approximately as follows:

    SELECT YEAR(delivered_date), Month(delivered_date), COUNT(*)
    FROM DCR1
    WHERE delivered_date BETWEEN @BegDate AND @EndDate
    WITH ROLLUP
  3. bambola New Member

    You do not need a cursor to accomplish this task. Try this and let me know if it works for you.



    DECLARE @BegDate Datetime, @EndDate datetime
    SET @BegDate = '1985-12-01'
    SET @EndDate = '2003-1-31'

    -- declaring a table and filling it with columns medhistid and HISNum of those
    -- who are first in year_month. You can put it in a table function.
    DECLARE @firsts_in_month table (medhistid int, HISNum int)

    INSERT INTO @firsts_in_month
    SELECT a.medhistid, a.HISNum
    FROM DCR1 a
    WHERE EXISTS
    (
    SELECT NULL
    FROM DCR1 b
    GROUP BY convert(varchar(7),b.DeliveredDate,120)
    HAVING a.DeliveredDate = MIN(b.DeliveredDate)
    )

    SELECT DCR1.medhistid, DCR1.HisNum, DCR1.Gravida, DCR1.DeliveredDate, DCR1.DeliveredTime,
    DCR2.SeqNum, DCR2.NumInfants, convert(varchar(7),DCR1.DeliveredDate,120)
    -- a flag to indicate if first in year_month
    , CASE WHEN f.medhistid IS NOT NULL AND f.HisNum IS NOT NULL THEN 1 ELSE 0 END
    FROM DCR1
    LEFT OUTER JOIN DCR2
    ON (DCR1.HISNum = DCR2.HISNum) AND (DCR1.medhistid = DCR2.medhistid)
    LEFT OUTER JOIN @firsts_in_month f
    ON (DCR1.HISNum = f.HISNum) AND (DCR1.medhistid = f.medhistid)
    WHERE DCR1.DeliveredDate Between @BegDate And @EndDate
    ORDER BY DCR1.DeliveredDate, DCR1.DeliveredTime, DCR2.SeqNum

    HTH,

    Bambola.

Share This Page