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.
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 daysSELECT @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 > 0AND 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(*) <= 8i didn't checked it |
|
|
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 pinner JOIN aS_tblCategories as Ct ON Ct.CategoryID = P.CategoryID AND Ct.PresentationCategories = @v_CategoryName AND ct.Order_ <> 0WHERE PresentationStatus = 1 AND p.CreatedDateTime >= @v_StartDate AND p.CreatedDateTime < dateadd(day, 1, @v_EndDate) Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|