Help in framing query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help in framing query

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2010-07-19 : 01:10:38

I have a requirement like this
b) Select the number of records on IMP_II_ROSTERQUEUE table for the given source id
a. If the count is equal to 1 the update roster expected date to roster current date + frequency [ITS_ROSTER_EXPECTED_DATE + ITS_FREQUENCY].
b. If the count is greater than 1 check if this is the first roster for the source for the current cycle [where IIRQ_RECEIVED_DATE >= (Roster Expected Date (RED) – Frequency) and IIRQ_RECEIVED_DATE <= (Roster Expected Date (RED) – 5)] if yes then check If Roster Received Date >= (Roster Expected Date (RED) – 5) and Roster Received Date <= (Roster Expected Date (RED) + Frequency -5) then update roster expected date to (Roster Expected Date (RED) + Frequency).
Can anyone help in framing the query for this.The columns will be fetched from 2 diff tables and i have framed it like this :

DECLARE @ROSTERCOUNT INT
SET @ROSTERCOUNT=(SELECT COUNT(*) FROM IMP_II_ROSTER_QUEUE A,IMP_ITS_SOURCE B,PWB_EXTERNAL_SOURCE_REF C
WHERE A.IIRQ_ESR_ID = B.ITS_ESR_ID AND B.ITS_ESR_ID = C.ESR_ID)

if (@rostercount = 1) and (@rostercount is not 0) then
begin
update IMP_ITS_SOURCE
set
ITS_ROSTER_EXPECTED_DATE = (ITS_ROSTER_EXPECTED_DATE + ITS_FREQUENCY)
from
FROM IMP_II_ROSTER_QUEUE A,IMP_ITS_SOURCE B,PWB_EXTERNAL_SOURCE_REF C
WHERE A.IIRQ_ESR_ID = B.ITS_ESR_ID AND B.ITS_ESR_ID = C.ESR_ID
end
else if (@rostercount >1) then
begin
UPDATE IMP_ITS_SOURCE
SET
ITS_ROSTER_EXPECTED_DATE = (ITS_ROSTER_EXPECTED_DATE + ITS_FREQUENCY)
FROM IMP_II_ROSTER_QUEUE A,IMP_ITS_SOURCE B,PWB_EXTERNAL_SOURCE_REF C
WHERE A.IIRQ_ESR_ID = B.ITS_ESR_ID AND B.ITS_ESR_ID = C.ESR_ID
AND A.IIRQ_RECEIVED_DATE between ((A.IIRQ_RECEIVED_DATE >= (B.ITS_ROSTER_EXPECTED_DATE - B.ITS_FREQUENCY)) and (A.IIRQ_RECEIVED_DATE <= (B.ITS_ROSTER_EXPECTED_DATE - 5))
AND A.IIRQ_RECEIVED_DATE >= (B.ITS_ROSTER_EXPECTED_DATE - 5) and A.IIRQ_RECEIVED_DATE <= (B.ITS_ROSTER_EXPECTED_DATE + B.ITS_FREQUENCY -5)
END

I havent executed it as i have doubt if i have framed the query for the above condition properly

susan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources