Query - SQL Server Forums

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)

Query

Author

Topic

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-04-27 : 06:26:48

I have a Table with This format and Start up with Data as Shown

TBL_UserSessions

Session_ID Student_ID Session_Date
845 100 4/12/2007 0:00
846 100 4/15/2007 2:09
847 100 4/19/2007 5:32
848 100 4/16/2007 5:56
849 101 4/12/2007 0:00
850 103 4/12/2007 0:00
851 103 4/15/2007 0:00
852 103 4/13/2007 0:00
861 104 4/11/2007 0:00
862 104 4/12/2007 0:00
863 100 4/12/2007 0:00
864 100 4/15/2007 2:09

I want to Check out number of Sessions Each student has attended in the two input dates

Suppose for StartDate= '4/12/2007' and EndDate= '4/15/2007'

Is it Possible to get an Output of following format

Student_ID 4/12/2007 4/13/2007 4/14/2007 4/15/2007
100 2 2
101 1
103 1 1 1
104 1

Thanks In advance

Learner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 06:41:08

Yes, with CROSSTAB/PIVOT query.

Peter Larsson
Helsingborg, Sweden

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-04-27 : 06:46:07

Thanks

I havent Heard about CrossTab/Pivot query

Please help !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 06:52:28

SELECT Student_ID, COUNT(*) AS Total
FROM TBL_UserSessions
WHERE Session_Date >= @StartDate AND Session_Date < DATEADD(DAY, 1, @EndDate)
GROUP BY Student_ID
ORDER BY Student_ID

Peter Larsson
Helsingborg, Sweden

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-04-27 : 07:01:46

This will Just give me the count of Sessions each student has attended, i want it datewise for each date between the start date and enddate

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-27 : 07:04:28

Can you do this in your front end reporting ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 07:06:19

What about the dates NOT in the table?

Example: You have Jan 1 and Jan 3 in the table.
Do you want Jan 2 in the report?

Peter Larsson
Helsingborg, Sweden

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-04-27 : 07:07:26

No That is My problem, it has to be exclusively done thru SQL PRocedure

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-27 : 07:08:47

quote:
Originally posted by zubairmasoodi

No That is My problem, it has to be exclusively done thru SQL PRocedure

Thanks

seen this http://www.sqlteam.com/item.asp?ItemID=2955 ?

zubairmasoodi
Starting Member

35 Posts

Posted - 2007-04-27 : 07:09:24

Yes Peso !!

I want all the Dates that span between the Range. A value of Null for the field is desired for the date which is not in the Table data

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources