Urgent Update statement query Required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Urgent Update statement query Required

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-03-28 : 07:04:04

Hi,
Am new to SQL In my application i have two table

1.#BonusApplicable
2.#tempCalendar

want to find out whether bonus is applicable or not for a given range of dates so tried to update the table getting incorrect values:

UPDATE
a
SET
isBonusApplicable = b.isBonusApplicable
FROM
#tempCalendar a
JOIN
#BonusApplicable b
ON
a.bonusId = b.bonusID
AND
a.date >= b.effectiveDate

Tables Used in my application:

IF OBJECT_ID('tempdb.dbo.#BonusApplicable') IS NOT NULL
DROP TABLE #BonusApplicable

CREATE TABLE #BonusApplicable
(
bonusId VARCHAR(10),
effectiveDate DATETIME,
isBonusApplicable VARCHAR(10)
)

INSERT #BonusApplicable VALUES (1,'01/01/2010','Y')
INSERT #BonusApplicable VALUES (1,'02/04/2010','N')
INSERT #BonusApplicable VALUES (1,'01/01/2011','Y')
INSERT #BonusApplicable VALUES (1,'01/18/2011','N')
INSERT #BonusApplicable VALUES (1,'03/01/2011','Y')

INSERT #BonusApplicable VALUES (2,'01/05/2010','Y')
INSERT #BonusApplicable VALUES (3,'01/01/2010','Y')
INSERT #BonusApplicable VALUES (3,'01/22/2010','N')
INSERT #BonusApplicable VALUES (4,'03/15/2010','N')

INSERT #BonusApplicable VALUES (2,'01/05/2011','N')
INSERT #BonusApplicable VALUES (3,'01/01/2011','Y')
INSERT #BonusApplicable VALUES (3,'01/22/2011','N')
INSERT #BonusApplicable VALUES (4,'03/15/2011','Y')

SELECT * FROM #BonusApplicable ORDER BY bonusId,effectiveDate

IF OBJECT_ID('tempdb.dbo.#tempCalendar') IS NOT NULL
DROP TABLE #tempCalendar

CREATE TABLE #tempCalendar
(
date DATETIME,
bonusId VARCHAR(10),
isBonusApplicable VARCHAR(10)
)

DECLARE @fromDate DATETIME
DECLARE @toDate DATETIME

SET @fromDate = '01/15/2010'
SET @toDate = '02/15/2010'

DECLARE @date DATETIME
SET @date = @fromDate

WHILE @date <= @toDate
BEGIN
INSERT INTO
#tempCalendar
SELECT
@date,
a.bonusId,
NULL isBonusApplicable
FROM
#BonusApplicable a
JOIN
(
SELECT
bonusId,
MAX(effectiveDate) effectiveDate
FROM
#BonusApplicable
GROUP BY
bonusId
)b
ON
a.bonusId = b.bonusId
AND
a.effectiveDate = b.effectiveDate

SET @date = @date + 1

END

SELECT * FROM #tempCalendar ORDER BY DATE,bonusId

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-28 : 07:51:53

You need to get the date range for each bonusId.
Something like:


;WITH OrderedBonus
AS
(
	SELECT bonusId, effectiveDate, isBonusApplicable
		,ROW_NUMBER() OVER (PARTITION BY bonusId ORDER BY effectiveDate) AS RN
	FROM #BonusApplicable
)
, BonusRange
AS
(
	SELECT B1.bonusId
		,B1.effectiveDate AS DateFrom
		,COALESCE(B2.effectiveDate, '99991231') AS DateTo
		,B1.isBonusApplicable
	FROM OrderedBonus B1
		LEFT JOIN OrderedBonus B2
			ON B1.bonusId = B2.bonusId
				AND B1.RN = B2.RN - 1
)
UPDATE C
SET isBonusApplicable = R.isBonusApplicable
FROM #tempCalendar C
	JOIN BonusRange R
		ON C.bonusId = R.bonusId
			AND C.[date] >= R.DateFrom
			AND C.[date] < R.DateTo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources