Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Return more than one set of data from a subquery

Posted on 2007-10-15
7
Medium Priority
?
285 Views
Last Modified: 2010-03-19
Hello,

I am using the query below to pull information from the database.  The database design is driven by the periodid.  The issue that I am having is the query below was once a view because it only had one period in the database.  As the database grew, the view needed to be changed into a stored procedure.  The issue that I am having is when you remove the @periodid and place where periodid=1 it works with the correct month and date.  But I need all of the periods that are stored in the database to return back with the periodid and the start and end date to match.  Could you assist me in rewriting this to make sense and complete that task?  Thanks a million!


create procedure dbo.dt_getperiodid
as

Declare @periodid int,
             @availableperiodcount int

set nocount on
select @periodid =1
select @availableperiodcount=count(periodid) from period

While (@periodid<= @availableperiodcount)
Begin
      
SELECT TOP 100 PERCENT CONVERT(varchar(10),p.StartDate,101) + ' - ' + CONVERT(varchar(10),p.EndDate,101) AS Period, ea.EvaluationArea, sa.SupportArea, m.months AS [Month], aa.AvgScore AS Average,  e.LastName + ', ' + e.FirstName AS Evaluator
FROM AssessmentAverage as aa

INNER JOIN Submission AS s ON aa.SupportAreaId = s.SupportAreaId AND aa.EvaluationMonth = s.EvaluationMonth
INNER JOIN EvaluationArea AS ea ON aa.EvaluationAreaId = ea.EvaluationAreaId
INNER JOIN SupportArea AS sa ON sa.SupportAreaId = aa.SupportAreaId
LEFT JOIN Employee AS e ON e.UserName = aa.created_user
RIGHT JOIN Months AS m ON (SELECT DATEPART(month, (SELECT StartDate FROM Period where periodid= @periodid)) + aa.EvaluationMonth - 1) = m.MonthId
INNER JOIN Period AS p ON p.PeriodId = aa.PeriodId
ORDER BY p.StartDate, ea.SortOrder, aa.EvaluationMonth, sa.SortOrder

select @periodid=@periodid+1


End
0
Comment
Question by:trsc6350
  • 4
  • 2
7 Comments
 
LVL 3

Expert Comment

by:ibrusett
ID: 20080917
Try to declare another variable and use this as the argument for datepart:


create procedure dbo.dt_getperiodid
as


set nocount on
select @periodid =1
select @availableperiodcount=count(periodid) from period
declare @Start_Date as datetime

While (@periodid<= @availableperiodcount)
Begin

SELECT @Start_date=StartDate FROM Period where periodid= @periodid

SELECT TOP 100 PERCENT CONVERT(varchar(10),p.StartDate,101) + ' - ' + CONVERT(varchar(10),p.EndDate,101) AS Period, ea.EvaluationArea, sa.SupportArea, m.months AS [Month], aa.AvgScore AS Average,  e.LastName + ', ' + e.FirstName AS Evaluator
FROM AssessmentAverage as aa

INNER JOIN Submission AS s ON aa.SupportAreaId = s.SupportAreaId AND aa.EvaluationMonth = s.EvaluationMonth
INNER JOIN EvaluationArea AS ea ON aa.EvaluationAreaId = ea.EvaluationAreaId
INNER JOIN SupportArea AS sa ON sa.SupportAreaId = aa.SupportAreaId
LEFT JOIN Employee AS e ON e.UserName = aa.created_user
RIGHT JOIN Months AS m ON (SELECT DATEPART(month,@Start_date) + aa.EvaluationMonth - 1) = m.MonthId
INNER JOIN Period AS p ON p.PeriodId = aa.PeriodId
ORDER BY p.StartDate, ea.SortOrder, aa.EvaluationMonth, sa.SortOrder

select @periodid=@periodid+1

End



0
 
LVL 5

Accepted Solution

by:
Crag earned 2000 total points
ID: 20080931
What result do you get with it as-is?
You should get multiple result sets, one for each period.
If you want one result set containing all periods then the following might do the trick



create procedure dbo.dt_getperiodid
as

SELECT
          CONVERT(varchar(10),p.StartDate,101) + ' - ' + CONVERT(varchar(10)
      , p.EndDate,101) AS Period
      , ea.EvaluationArea
      , sa.SupportArea
      , m.months AS [Month]
      , aa.AvgScore AS Average
      , e.LastName + ', ' + e.FirstName AS Evaluator
FROM AssessmentAverage as aa
      INNER JOIN Submission AS s ON aa.SupportAreaId = s.SupportAreaId AND aa.EvaluationMonth = s.EvaluationMonth
      INNER JOIN EvaluationArea AS ea ON aa.EvaluationAreaId = ea.EvaluationAreaId
      INNER JOIN SupportArea AS sa ON sa.SupportAreaId = aa.SupportAreaId
      LEFT JOIN Employee AS e ON e.UserName = aa.created_user
      INNER JOIN Period AS p ON p.PeriodId = aa.PeriodId
      RIGHT JOIN Months AS m ON (SELECT DATEPART(month, p.StartDate)) + aa.EvaluationMonth - 1) = m.MonthId
ORDER BY
        p.StartDate
      , ea.SortOrder
      , aa.EvaluationMonth
      , sa.SortOrder

end
0
 

Author Comment

by:trsc6350
ID: 20081600
ibrusett:

You solution is almost there, but it is giving me a combination of the correct answer.  Is it anyway to get the two answers in one table?  The first table gives me the correct months column for all except the ones that fall under 10/1/07-03-31-08 and table two results give me the correct months and period for all except those that fall under 4/1/07-9/30/07.  How can we combine the rs to get the solution of the right months from the first table for the first period and the correct months for the second period from the second table?  thanks!

This is the resultset that I get:
table1:
Period                        EvaluationArea      SupportArea      Month      Average      Evaluator
04/01/2007 - 09/30/2007      Cost Control      Contracts      May      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      June      1      Kowal, Betsy
04/01/2007 - 09/30/2007      Cost Control      Contracts      July      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      August      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      September      1      Steele, Jane
10/01/2007 - 03/31/2008      Quality of Products
                                           and Services BTRP      April      0.36      Wright, Craig
10/01/2007 - 03/31/2008      Management of
                                           Personnel       BTRP      April      NULL      Wright, Craig
10/01/2007 - 03/31/2008      Schedule      BTRP      April      0.17      Wright, Craig
-----------------------------------------------------------------------------------------------------------------------
Table 2:
Period                        EvaluationArea      SupportArea      Month      Average      Evaluator
04/01/2007 - 09/30/2007      Schedule      Human Resources/Personnel      December      1      Thompson, Temeka
04/01/2007 - 09/30/2007      Schedule      Contracts      December      1      Kowal, Betsy
04/01/2007 - 09/30/2007      Cost Control      Contracts      October      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      November      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      December      1      Kowal, Betsy
10/01/2007 - 03/31/2008      Quality of Products
                                           and Services   BTRP      October      0.36      Wright, Craig
10/01/2007 - 03/31/2008      Management of
                                           Personnel         BTRP      October      NULL      Wright, Craig
10/01/2007 - 03/31/2008      Schedule         BTRP      October      0.17      Wright, Craig

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:trsc6350
ID: 20081657
Crag:

As is the rs looks like this:

Table1:
Period      EvaluationArea      SupportArea      Month      Average      Evaluator
04/01/2007 - 09/30/2007      Cost Control      Contracts      May      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      June      1      Kowal, Betsy
04/01/2007 - 09/30/2007      Cost Control      Contracts      July      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      August      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      September      1      Steele, Jane
10/01/2007 - 03/31/2008      Quality of Products and Services      BTRP      April      0.36      Wright, Craig
10/01/2007 - 03/31/2008      Management of Personnel      BTRP      April      NULL      Wright, Craig
10/01/2007 - 03/31/2008      Schedule      BTRP      April      0.17      Wright, Craig

--------------------------------------------------------------------------------
Table2:
Period      EvaluationArea      SupportArea      Month      Average      Evaluator
04/01/2007 - 09/30/2007      Schedule      Contracts      December      1      Kowal, Betsy
04/01/2007 - 09/30/2007      Cost Control      Contracts      October      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      November      1      Steele, Jane
04/01/2007 - 09/30/2007      Cost Control      Contracts      December      1      Kowal, Betsy
10/01/2007 - 03/31/2008      Quality of Products and Services      BTRP      October      0.36      Wright, Craig
10/01/2007 - 03/31/2008      Management of Personnel      BTRP      October      NULL      Wright, Craig
10/01/2007 - 03/31/2008      Schedule      BTRP      October      0.17      Wright, Craig
0
 
LVL 5

Expert Comment

by:Crag
ID: 20086646
How about when you ran the queries supplied?
0
 

Author Comment

by:trsc6350
ID: 20087643
It gives an error message:

Msg 4145, Level 15, State 1, Procedure dt_getperiodid, Line 18
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
0
 

Author Comment

by:trsc6350
ID: 20087774
I was missing a paren.  Thanks Crag!  It worked :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question