result set - 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

SQL Server 2005 Forums

Transact-SQL (2005)

result set

Author

Topic

abbikhan
Starting Member

26 Posts

Posted - 2011-05-03 : 06:40:57

I want to retrieve data in this manner but how can and what statements should I use, I am struck
Setp1: Having 4 variables
@StartDate=’9/9/2010’, @EndDate=’9/9/2011’, @StartingStage=1, @EndingStage=5
And another which have values
@count =2, 3, 4, 5 (if @ StartingStage=1 and @EndingStage=5)
Step2:
(SELECT id FROM Table1
WHERE Stage=@StartingStage--(=1)
AND Date BETWEEN @StartDate AND @EndDate) A_Result_set
Step3:
(SELECT id FROM Table1 inner join with A_Result_set
WHERE Stage=@count--(=2)
AND Date BETWEEN @StartDate AND @EndDate) new_resultset1

Step 3:
(SELECT id FROM Table1 inner join with new_resultset1
WHERE Stage=@count--(=3)
AND Date BETWEEN @StartDate AND @EndDate) new_resultset2

Step4:
(SELECT id FROM Table1 inner join with new_resultset2
WHERE Stage=@count(--=4)
AND Date BETWEEN @StartDate AND @EndDate) new_resultset3

Step5:
(SELECT id FROM Table1 inner join with new_resultset3
WHERE Stage=@count(--=5)
AND Date BETWEEN @StartDate AND @EndDate) new_resultset4

Result = new_resultset4

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-03 : 06:48:46

What result you want? Post some output.
And send your full query

In Love... With Me!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 06:54:11

Do you have rows for each id with a stage from 1 to 5 and you want to get the IDs with entries for each stage up to the count entered? I doubt it but...

select id
from Table1
where Date BETWEEN @StartDate AND @EndDate
and stage betwen @StartingStage and @EndingStage
group by id
having count(*) = @EndingStage-@StartingStage+1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

abbikhan
Starting Member

26 Posts

Posted - 2011-05-03 : 07:57:22

quote:
Originally posted by nigelrivett

Do you have rows for each id with a stage from 1 to 5 and you want to get the IDs with entries for each stage up to the count entered? I doubt it but...

select id
from Table1
where Date BETWEEN @StartDate AND @EndDate
and stage betwen @StartingStage and @EndingStage
group by id
having count(*) = @EndingStage-@StartingStage+1

Yes i have rows in table 2 agianst these ids
and
that should not be between two stages, that should complete these stages one by one then may be some ids removed

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 08:39:40

Not sure what you want to do with this.
Try explaining in english rather than code.

Sounds like you have a table with an entry for each stage that is completed by an id and some entries can be removed later.
You pass in a start and end date and a start stage and end stage.
What is the resultset you want from this?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources