sql server storedprocedure => how to get 3 weeks data from database tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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

C#

--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

Posted 17-Oct-14 0:36am

Manish Dalwadi

Add a Solution

2 solutions

Solution 2

manish350 wrote (in comment):
my dear,
i want data in this format
coursename,  firstweekdata secondweekdata thirdweekdata
c language       3             5               10
this digits are count of coursename

Pivot query[^] may help.

SQL

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

Posted 17-Oct-14 1:46am

Maciej Los

Updated 17-Oct-14 2:10am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

vJay Yadav · Accepted Answer · 2014-10-17T00:39:00

How about:

SQL

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

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.