sql server storedprocedure =>如何从数据库表中获取3周的数据 [英] sql server storedprocedure => how to get 3 weeks data from database tables

问题描述

你好，先生，

i编写了这个程序，用于从数据库（星期一到星期六）打造一周的数据

我希望编写storedprocedure来获取数据库中的3周数据

但是我不知道怎么写

请检查我的sp并给我解决方案

提前感谢

hello sir,
i wrote this procedure for geeting one week data from database (monday to saturday)
and i want to write storedprocedure for get 3 weeks data from database
but i have no idea how to write
please check my sp and give me solution
thanks in advance

--exec [GetStudentCourseWiseBookingReport] '2010-09-08'
ALTER PROCEDURE [dbo].[GetStudentCourseWiseBookingReport]
@UserDate datetime
AS
BEGIN
DECLARE @StartDate	AS DATETIME
DECLARE @EndDate AS DATETIME
	
	set @StartDate=CONVERT(DATETIME,DATEADD(wk,DATEDIFF(wk,0,@UserDate),0))
	set @EndDate=CONVERT(DATETIME,DATEADD(wk,DATEDIFF(wk,0,@UserDate),5))
	
	select CourseName,count(CourseName)course_count,StartDate as DB_StartDate,@StartDate as Week_StartDate , @EndDate AS Week_EndDate
	from GL_BookingList bl
		inner join GL_MasterPriceDefaultSelection mpds on bl.PriceItemId=mpds.PriceItemId 
		inner join GL_Course on GL_Course.CourseID= mpds.SelectionId			  
		where GL_Course.StartDate between @StartDate and @EndDate
		group by CourseName,StartDate	
	
END	

推荐答案

怎么样：

How about:

select contentID, title, created 
from content
where created < dateadd(week,-3,getdate());

这更贴近问题。 21天很好，显然意味着相同，但我发现使用问题中使用的术语是好的。

例如......一段时间后我被要求调查平均值为1在一个网站的50个访问者。我把它描述为0.02的比例，客户不满意。我向客户指出他们是一样的，但是我吸取了教训，现在如果我改变了描述某些内容的方式，我确保对这种效果进行评论，最好不要在第一次改变它。地点。如果客户想要3周，则需要3周，而不是21天。

This sticks closer to the question. 21 days is fine, obviously means the same, but I find that it's good to use the terminology used in the question.
For example... a while back I was asked to survey an average of 1 in 50 visitors to a site. I described this as a proportion of 0.02, and the client wasn't happy. I pointed out to the client that they're the same, but I learned my lesson, and now if I change the way that something is described, I make sure I comment to that effect, and preferably don't change it in the first place. If the client wants 3 weeks, do it as 3 weeks, not 21 days.

manish350写道（评论中） ：

manish350 wrote (in comment):

亲爱的，

i想要这种格式的数据

my dear,
i want data in this format

coursename,  firstweekdata secondweekdata thirdweekdata
c language       3             5               10

这个数字是coursename的数量

this digits are count of coursename

< a href =http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx>数据透视查询 [ ^ ]可能会有所帮助。

Pivot query[^] may help.

SELECT coursename, [1], [2], [3]
FROM (
    SELECT coursename, DATEDIFF(WK, StartDate, @userdate)
    FROM ...
) AS DT
PIVOT(COUNT(coursename) FOR coursename IN ([1], [2], [3])) AS PT

这篇关于sql server storedprocedure =&gt;如何从数据库表中获取3周的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！