April 28, 2008 at 9:19 am
Dear All,
I am fighting with CURSOR to replace it with SET BASED Logic.
My situation is:
I have one table called PRREQ
Table Structure:
PRID Month Year PRDate MGID
AAA 2 2008 02/21/2008 A8
AAA 2 2008 02/21/2008 A9
AAA 2 2005 02/21/2004 A8
AAA 2 2008 NULL NULL
BBB 4 2008 04/20/2008 B5
BBB 4 2008 NULL NULL
My query:
-------------------------------------------------------------------------------
Create Table #NullPform
(
PRID varchar(55) NULL,
month int null,
year int null,
MonthLastDate Datetime null
)
declare @PRID varchar(10),@Year int,@Month int,@EndofMonth int,@FirstMEnd varchar(12)
declare c1 cursor for
Select distinct PRID
From PRREQ
where PRDate is null
and MGID is null
open c1
fetch PRID into @PRID
while (@@sqlstatus = 0)
BEGIN
Select @year = min(Year)
From PRREQ
where PRDate is null
and PRID = @PRID
select @Month = min(Month)
From PRREQ
where PRDate is null
and Year = @Year
and PRID = @PRID
SELECT @EndofMonth=(CASE WHEN @Month IN (1,3,5,7,8,10,12) THEN 31
ELSE CASE WHEN @Month IN (4,6,9,11) THEN 30
ELSE CASE WHEN @Year%4=0 THEN 29 ELSE 28 END END END)
SELECT @monthEnd=convert(varchar,@month)+'/'+convert(varchar,@EndOfMonth)+'/'+convert(varchar,@year)
select @FirstMEnd = @MonthEnd
select @LastMEnd =max(PDDate)
from PQREQ
where PRID = @PRID
and SNO in (select SID from MRREQ where PRID = @PRID)
select @yearEnd = datepart(yy,@LastMEnd)
select @PartEnd = datepart(mm,@LastMEnd)
select @ycur=datepart(yy,@FirstMEnd)
select @mcur=datepart(mm,@FirstMEnd)
While (@ycur <= @yearEnd)
BEGIN
if (@ycur = @yearEnd)
BEGIN
WHILE (@mcur <= @partEnd)
BEGIN
SELECT @EndofMonth=(CASE WHEN @mcur IN (1,3,5,7,8,10,12) THEN 31
ELSE CASE WHEN @mcur IN (4,6,9,11) THEN 30
ELSE CASE WHEN @ycur%4=0 THEN 29 ELSE 28 END END END)
SELECT @monthEnd = convert(varchar,@mcur)+'/'+convert(varchar,@EndofMonth)+'/'+convert(varchar,@ycur)
insert into #NullPform (PRID,month,year,monthLastDate)
values (@PRID, @mcur, @ycur, @EndOFMonth)
INSERT INTO Log (Dt, Usr, PRID, MID,Remarks)
values (getdate(), user_id(), @PRID, null, 'Testing...')
select @mcur = @mcur + 1
END
END
if (@ycur < @yearEnd)
BEGIN
WHILE (@mcur <= 12)
BEGIN
SELECT @EndofMonth=(CASE WHEN @mcur IN (1,3,5,7,8,10,12) THEN 31
ELSE CASE WHEN @cur IN (4,6,9,11) THEN 30
ELSE CASE WHEN @ycur%4=0 THEN 29 ELSE 28 END END END)
SELECT @MonthEnd =convert(varchar,@mcur)+'/'+convert(varchar,@EndofMonth)+'/'+convert(varchar,@ycur)
insert into #NullPform (PRID,month,year,monthLastDate)
values (@PRID, @mcur, @ycur, @EndOFMonth)
INSERT INTO Log (Dt, Usr, PRID, MID,Remarks)
values (getdate(), user_id(), @PRID, null, 'Testing...')
select @mcur = @mcur + 1
END
select @mcur = 1
END
select @ycur = @ycur + 1
END
fetch c1 into @PRID
END
close c1
deallocate c1
---------------------------------------------------------------------
Can anybody help me to get SET Based logic ?
Help would be highly appreciable !
karthik
April 28, 2008 at 9:28 am
Karthik,
What is it that you are trying to accomplish? I understand that you want to go "Set Based", but what is the goal of the procedure?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 29, 2008 at 4:21 am
Jason,
I want to generate the last date for each month.
Select @year = min(Year)
From PRREQ
where PRDate is null
and PRID = @PRID
select @Month = min(Month)
From PRREQ
where PRDate is null
and Year = @Year
and PRID = @PRID
SELECT @EndofMonth=(CASE WHEN @Month IN (1,3,5,7,8,10,12) THEN 31
ELSE CASE WHEN @Month IN (4,6,9,11) THEN 30
ELSE CASE WHEN @Year%4=0 THEN 29 ELSE 28 END END END)
SELECT @monthEnd=convert(varchar,@month)+'/'+convert(varchar,@EndOfMonth)+'/'+convert(varchar,@year)
Here, i am generating the minimum date for that particular PRID.(@monthend contains the minimum date)
select @LastMEnd =max(PDDate)
from PQREQ
where PRID = @PRID
and SNO in (select SID from MRREQ where PRID = @PRID)
Here , i am getting the maximum date for that particular PRID.
Now i want to generate the last date of each month for the given period.
Say for example,
Assume @MonthEnd = '01/Jan/2008' ,@LastMEnd = '01/Apr/2008'.
I have to insert the last date of each month into a seperate table like
PRID Month Year LastDate
AAA 1 2008 31/Jan/2008
AAA 2 2008 28/Feb/2008
AAA 3 2008 31/Mar/2008
AAA 4 2008 30/Apr/2008
Suppose if @MonthEnd is '01/01/2005' we have to generate the Last date of each month from 2005 January to 2008 April.
Right now i am doing it with cursor, i want to use SET BASED Logic.
Please help me.
karthik
April 29, 2008 at 7:39 am
Karthik,
Take a look at this. It does what you want. See if you understand it, and if you should have any questions, I'd be happy to answer them... 😀
DECLARE @NullPform TABLE
(PRID varchar(55) NULL
,[month] INT NULL
,[year] INT NULL
,MonthLastDate DATETIME NULL
,MGID CHAR(2))
-- I create test data
INSERT @NullPform
SELECT 'AAA',2,2008,'02/21/2008','A8' UNION
SELECT 'AAA',2,2008,'02/21/2008','A9' UNION
SELECT 'AAA',2,2005,'02/21/2005','A8' UNION
SELECT 'AAA',2,2008,NULL,NULL UNION
SELECT 'BBB',4,2008,'04/20/2008','B5' UNION
SELECT 'BBB',4,2008,NULL,NULL;
-- create a tally table if you don't have one :-) I believe you've done this before, and use that ...
-- we'll cross join it to be able to "set base" this, but first use a CTE to get the min and max dates for each prid
-- you were using month, but that won't handle ranges that cross years so we put it all together here
WITH pridLimits
AS (SELECT
prid
,MIN(CAST(CAST([month] AS VARCHAR(2)) + '/1/' + CAST([year] AS VARCHAR(4)) AS DATETIME)) AS minDate
,MAX(CAST(CAST([month] + 1 AS VARCHAR(2)) + '/1/' + CAST([year] AS VARCHAR(4)) AS DATETIME)) AS maxDate
FROM
@NullPform AS a
GROUP BY
prid)
SELECT
prid
,number
,DATEADD(DAY,-1,DATEADD(MONTH,(number),minDate)) AS x
FROM
pridLimits
CROSS JOIN (SELECT number FROM dbo.tally) AS n
WHERE
number <= DATEDIFF(MONTH,minDate,maxDate)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 29, 2008 at 8:02 am
Jason,
Thanks a lot for your prompt reply.
Sure. I am using sql2000. I think your query will work in sql2005 only.
Once again my hearty thanks to you.
karthik
April 29, 2008 at 8:07 am
then just change the CTE to a temp table. 😀
CREATE TABLE #pridLimits (prid VARCHAR(55), minDate DATETIME, maxDate DATETIME)
INSERT #pridLimits
SELECT
prid
,MIN(CAST(CAST([month] AS VARCHAR(2)) + '/1/' + CAST([year] AS VARCHAR(4)) AS DATETIME)) AS minDate
,MAX(CAST(CAST([month] + 1 AS VARCHAR(2)) + '/1/' + CAST([year] AS VARCHAR(4)) AS DATETIME)) AS maxDate
FROM
@NullPform AS a
GROUP BY
prid
SELECT
prid
,number
,DATEADD(DAY,-1,DATEADD(MONTH,(number),minDate)) AS x
FROM
#pridLimits
CROSS JOIN (SELECT number FROM dbo.tally) AS n
WHERE
number <= DATEDIFF(MONTH,minDate,maxDate)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 29, 2008 at 8:24 am
Jason,
I will check it and come back to you.:)
karthik
April 29, 2008 at 8:46 am
Jason,
Its working perfectly. Thanks a lot.:)
Really your help is very much useful for me. So once again My hearty thanks to you.:)
karthik
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply