SQL Server Performance Forum – Threads Archive

Getting Null values from Cursor

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

---

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

---

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.

---

]]>