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 2000 Forums
 SQL Server Development (2000)
 Can We have a Better Approach Than This ?

Author  Topic 

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-06-21 : 02:56:09
Can We have a Better Approach Than This ?

The Objective is to get Minimum of 8 Records For The given Date Filter and Category
if the count of Records is less than 8 ..Then the date Filter is Increased unless we get 8 Records



/*PARAMETERS:
@v_StartDate ----START DATE
@v_EndDate -----END DATE
@v_CategoryName ---CATEGORY NAME */

Declare @Row_Count int

SELECT @Row_Count=COUNT(*) FROM aS_tblPresentations p JOIN aS_tblCategories Ct ON (Ct.CategoryID = P.CategoryID AND Ct.PresentationCategories = @v_CategoryName)
WHERE PresentationStatus = 1 AND p.CategoryID NOT IN (SELECT CategoryID FROM aS_tblCategories WHERE Order_ = 0)
AND CAST(CONVERT(VARCHAR, p.CreatedDateTime, 101) AS DATETIME) between
CAST(CONVERT(VARCHAR, @v_StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @v_EndDate, 101) AS DATETIME)

/* If Count of Records is Less Than 8 , I Expand the date Filter Unless The Count Becomes 8 */

/* This Part of The Code Needs Optimization */


IF( @Row_Count < 8)
BEGIN

While (@Row_Count < 8)
Begin

SET @v_StartDate=DATEADD(D,-1,CAST(@v_StartDate AS DATETIME))

/* This is to end Loop in case we dont get Records */
if(@v_StartDate < '1/1/2007')
begin
break
end

SELECT @Row_Count=COUNT(*) FROM aS_tblPresentations p JOIN aS_tblCategories Ct ON (Ct.CategoryID = P.CategoryID AND Ct.PresentationCategories = @v_CategoryName)
WHERE PresentationStatus = 1 AND p.CategoryID NOT IN (SELECT CategoryID FROM aS_tblCategories WHERE Order_ = 0)
AND CAST(CONVERT(VARCHAR, p.CreatedDateTime, 101) AS DATETIME) between
CAST(CONVERT(VARCHAR, @v_StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @v_EndDate, 101) AS DATETIME)


End

END
/* This Part of The Code Needs Optimization --- End here */



/* This Part of the Query gets the Resut Based on above mentioned parameters*/


SELECT @@RowCount,P.PresentationID AS [ID], ISNULL(STUFF(PresentationTitle, 14, 200, '...'), PresentationTitle) AS [Title],
ISNULL(PresentationThumbNailPath,'No Path') AS ThumbnailPath, PresentationUniqueName AS UniqueName,
PresentationUniqueName + '.xml' AS XMLPath, TitleFormat as Title, PlayerType,PresentationTitle as [FullTitle],
dbo.aS_sp_GetDaysAgoString(DATEDIFF(Minute, P.CreatedDateTime, GETDATE())) AS DaysAgo, PresentationCategories,
LTRIM(RTRIM(PresentationCategories)) AS Category,
ISNULL(LEFT(CAST(R.RatingNum AS FLOAT)/ CAST(R.CntRatings AS FLOAT), 3), 0) as RatingValue,
ISNULL(CntRatings,0) as RatingCount,
CASE -- Rating Image
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 1.0 AND 1.0 THEN '0-1'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 1.1 AND 1.5 THEN '1-5'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 1.6 AND 2.0 THEN '2-0'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 2.1 AND 2.5 THEN '2-5'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 2.6 AND 3.0 THEN '3-0'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 3.1 AND 3.5 THEN '3-5'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 3.6 AND 4.0 THEN '4-0'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 4.1 AND 4.5 THEN '4-5'
WHEN ISNULL(LEFT(CAST(RatingNum AS FLOAT)/ CAST(CntRatings AS FLOAT), 3), 0) BETWEEN 4.6 AND 5.0 THEN '5-0'
ELSE '0-0'
END AS RatingImage ,
COUNT(C.PresentationID) as CommentsCount,
ISNULL(PresentationViews,0) AS ViewsCount,
DisplayName, M.MemberID as UserID
FROM aS_tblPresentations P
JOIN aS_tblMembers M ON (M.MemberID = P.MemberID)
LEFT OUTER JOIN aS_tblRatings R on(R.PresentationID=P.PresentationID)
LEFT OUTER JOIN aS_tblComments C on(C.PresentationID=P.PresentationID)
JOIN aS_tblCategories Ct ON (Ct.CategoryID = P.CategoryID AND Ct.PresentationCategories = @v_CategoryName)
WHERE P.PresentationStatus = 1 AND P.CategoryID NOT IN (SELECT CategoryID FROM aS_tblCategories WHERE Order_ = 0)
AND CAST(CONVERT(VARCHAR, P.CreatedDateTime, 101) AS DATETIME) between
CAST(CONVERT(VARCHAR, @v_StartDate, 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @v_EndDate, 101) AS DATETIME)
GROUP BY P.PresentationID, PresentationTitle, PresentationDescription, PresentationThumbNailPath,
PresentationStoragePath, PresentationUniqueName, TitleFormat, PlayerType, PresentationViews, PresentationCategories, CntRatings, RatingNum, DisplayName, M.MemberID, P.CreatedDateTime
ORDER By P.CreatedDateTime DESC

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-21 : 06:31:13
Hi try the following if u r date range is below 256 days

SELECT @Row_Count=COUNT(*)
FROM master..spt_values N, aS_tblPresentations p
JOIN aS_tblCategories Ct ON (Ct.CategoryID = P.CategoryID AND Ct.PresentationCategories = @v_CategoryName)
WHERE PresentationStatus = 1
AND p.CategoryID NOT IN (SELECT CategoryID FROM aS_tblCategories WHERE Order_ = 0)
AND n.Type = 'p'
AND n.number > 0
AND CAST(CONVERT(VARCHAR, p.CreatedDateTime, 101) AS DATETIME)
between CAST(CONVERT(VARCHAR, dateadd( d, -n.number, @v_StartDate), 101) AS DATETIME) AND CAST(CONVERT(VARCHAR, @v_EndDate, 101) AS DATETIME)
having COUNT(*) <= 8

i didn't checked it
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-21 : 14:08:49
Assuming @v_startdate and @v_enddate does not have time information...
SELECT		@Row_Count = COUNT(*)
FROM aS_tblPresentations as p
inner JOIN aS_tblCategories as Ct ON Ct.CategoryID = P.CategoryID
AND Ct.PresentationCategories = @v_CategoryName
AND ct.Order_ <> 0
WHERE PresentationStatus = 1
AND p.CreatedDateTime >= @v_StartDate
AND p.CreatedDateTime < dateadd(day, 1, @v_EndDate)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -