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

alex weber
Starting Member

4 Posts

Posted - 2010-08-14 : 02:04:18

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]. http://www.papalocksmith.com/
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 02:15:49

can you post table structure with data and then explain what you want? that will make it clear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-14 : 02:28:36

your query should perform okey, there is something you should change:


if (@rostercount = 1) and (@rostercount is not 0) then
begin
update IMP_ITS_SOURCE

as well as here


end
else if (@rostercount >1) then
begin
UPDATE IMP_ITS_SOURCE

otherwise send DDL with some sample data.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-08-31 : 08:58:52

(@rostercount is not 0)

Don't think T-SQL will like this. Do you mean @rostercount is not NULL?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources