Adding results of two cursors
-
Hi,
I have a requirement such that I run a query to get the records for individual months. How can I get the Union of two cursors (i.e. if the user selects to generate report for february and march 2005 then I execute two queries to get the records for February and March) how to get one cursor joining the results of two months so that I can return that cursor to my coldfusion page and display the results month wise.
Please mail me the solution at moulivedula@yahoo.com
Thanks,
Chandra.
-
Not gonna mail this to you.
Simply do a single select :
Select columns from dbo.YourTable where DateCol between @StartDate and @EndDate.
Then simply present the data monthly if requested that way.
-
Hi,
Our query is written such a way that it works for individual months. It fails if I execute that for more than one month. Can you please suggest me the solution.
Thanks,
Chandra.
-
Can you post the query??
If you can't create a new query or change this one, then I suggest that you might be screwed.
-
Hi,
Following is the query:
USE CyberScholar
DECLARE @VendorID INT
DECLARE @QuizTypeID INT
DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime
SET @VendorID = 43
SET @QuizTypeID = 23
SET @StartDate = '01/01/2005 12:00:00 AM'
SET @EndDate = '05/31/2005 11:59:59 PM'
SELECT QN.QuizID, QN.QuizName, QN.Module,
IsNull(Pass.PassCount, 0) AS PassCount,
IsNull(Total.TotalCount, 0) AS TotalCount,
IsNull(UniqueUsers.UniqueCount, 0) AS UniqueCount
FROM ccsnew.dbo.Quiz_Name QN INNER JOIN
(
SELECT COUNT(DISTINCT Q.QuizInstance) AS PassCount, QN.QuizID
FROM ccsnew.dbo.Quiz_Name QN INNER JOIN
dbo.Quizzes Q ON QN.QuizID = Q.QuizID
WHERE QN.QuizTypeID = @QuizTypeID
AND QN.VendorID = @VendorID
AND Q.Status = 'T'
AND Q.[Date] between @StartDate AND @EndDate
GROUP BY QN.QuizID
 
PassON QN.QuizID = Pass.QuizID INNER JOIN
(
SELECT COUNT(DISTINCT Q.QuizInstance) AS TotalCount, QN.QuizID
FROM ccsnew.dbo.Quiz_Name QN INNER JOIN
dbo.Quizzes Q ON QN.QuizID = Q.QuizID
WHERE QN.QuizTypeID = @QuizTypeID
AND QN.VendorID = @VendorID
AND Q.Status IS NOT NULL
AND Q.[Date] between @StartDate AND @EndDate
GROUP BY QN.QuizID
 
TotalON QN.QuizID = Total.QuizID INNER JOIN
(
SELECT COUNT(DISTINCT Q.IDNumber) AS UniqueCount, QN.QuizID
FROM ccsnew.dbo.Quiz_Name QN INNER JOIN
dbo.Quizzes Q ON QN.QuizID = Q.QuizID
WHERE QN.QuizTypeID = @QuizTypeID
AND QN.VendorID = @VendorID
AND Q.Status IS NOT NULL
AND Q.[Date] between @StartDate AND @EndDate
GROUP BY QN.QuizID
 
UniqueUsers ON QN.QuizID = UniqueUsers.QuizIDWHERE QN.QuizTypeID = @QuizTypeID AND
QN.VendorID = @VendorID
It works fine if I execute this for one (individual) month.
The following is the info of record count for individual months
Record Count Month
------------- -------
75 Jan
79 Feb
82 Mar
85 Apr
10 May
Thanks,
Chandra.
-
Just use a bigger range of dates and add a group by for the months and you're golden.
-
If I add group by months I am getting 4534 records from Jan to May 2005, but I should get only 331 records (if I add the record count of individual months then it should be 331 only).
-
What did you put in for the group by?
-
The following is the query and it is returning 1184 records and it should return 331 only.
Note: replace the Satisfied icon with ")". Where ever I use that icon is coming and I do not know how to take that of and put ")".
DECLARE @VendorID INT
DECLARE @QuizTypeID INT
DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime
SET @VendorID = 43
SET @QuizTypeID = 23
SET @StartDate = '01/01/2005 12:00:00 AM'
SET @EndDate = '05/31/2005 11:59:59 PM'
Select distinct Results.Month,
Results.PassCount,
Results.QuizID,
Results.QuizName,
Results.Module,
Results.TotalCount from
(
SELECT Pass.month, QN.QuizID, QN.QuizName, QN.Module,
IsNull(Pass.PassCount, 0) AS PassCount,
IsNull(Total.TotalCount, 0) AS TotalCount,
IsNull(UniqueUsers.UniqueCount, 0) AS UniqueCount
FROM ccsnew.dbo.Quiz_Name QN INNER JOIN
(
SELECT COUNT(DISTINCT Q.QuizInstance) AS PassCount, QN.QuizID, datepart(month,Q.Date) Month
FROM ccsnew.dbo.Quiz_Name QN INNER JOIN
dbo.Quizzes Q ON QN.QuizID = Q.QuizID
WHERE QN.QuizTypeID = @QuizTypeID
AND QN.VendorID = @VendorID
AND Q.Status = 'T'
AND Q.[Date] > @StartDate AND Q.Date < @EndDate
GROUP BY datepart(month,Q.Date), QN.QuizID
 
PassON QN.QuizID = Pass.QuizID INNER JOIN
( SELECT COUNT(DISTINCT Q.QuizInstance) AS TotalCount, QN.QuizID, datepart(month,Q.Date) Month
FROM ccsnew.dbo.Quiz_Name QN INNER JOIN
dbo.Quizzes Q ON QN.QuizID = Q.QuizID
WHERE QN.QuizTypeID = @QuizTypeID
AND QN.VendorID = @VendorID
AND Q.Status IS NOT NULL
AND Q.[Date] > @StartDate AND Q.Date < @EndDate
GROUP BY datepart(month,Q.Date), QN.QuizID
 
TotalON Pass.QuizID = Total.QuizID INNER JOIN
(
SELECT COUNT(DISTINCT Q.IDNumber) AS UniqueCount, QN.QuizID, datepart(month,Q.Date) Month
FROM ccsnew.dbo.Quiz_Name QN INNER JOIN
dbo.Quizzes Q ON QN.QuizID = Q.QuizID
WHERE QN.QuizTypeID = @QuizTypeID
AND QN.VendorID = @VendorID
AND Q.Status IS NOT NULL
AND Q.[Date] > @StartDate AND Q.Date < @EndDate
GROUP BY datepart(month,Q.Date), QN.QuizID
 
UniqueUsers ON Total.QuizID = UniqueUsers.QuizID)Results
Group By Results.Month, Results.PassCount, Results.QuizID,
Results.QuizName, Results.Module, Results.TotalCount
Order By Results.Month
-
Let's start over, send me :
- tables dll
- sample data (insert statements)
- sample results
- current results.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply