Help for applying logic on selected data

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

Transact-SQL (2005)

Help for applying logic on selected data

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-06-29 : 07:23:44

Hi

My select query returns data in the below given way. I need to apply some logic on the data and insert it into onther table

----------------------------------------------------------------------

Department_ID | Subject_ID | Student_ID | Marks | Modified_Date
----------------------------------------------------------------------
1 | S1 | 1 | 10 | 2009-06-27
----------------------------------------------------------------------
1 | S1 | 1B | 10 | 2009-06-27
----------------------------------------------------------------------
1 | S2 | 2 | 10 | 2009-06-26
----------------------------------------------------------------------
1 | S2 | 3 | 10 | 2009-06-26
----------------------------------------------------------------------
1 | S2 | 4 | 10 | 2009-06-26
----------------------------------------------------------------------
2 | S1 | 1 | 10 | 2009-06-26
----------------------------------------------------------------------
2 | S1 | 1B | 10 | 2009-06-26
----------------------------------------------------------------------
2 | S1 | 2 | 10 | 2009-06-26
----------------------------------------------------------------------
2 | S2 | 3 | 10 | 2009-06-27
----------------------------------------------------------------------
2 | S2 | 4 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S2 | 2 | 10 | 2009-06-26
----------------------------------------------------------------------
3 | S1 | 3 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S1 | 4 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S1 | 5 | 10 | 2009-06-27
----------------------------------------------------------------------
4 | S1 | 1B | 10 | 2009-06-28
----------------------------------------------------------------------

1) I need to select only the records of one Subject_ID per one Department_ID by last modified_Date

2) Then if the subject_ID is 1 then I need to combine 1 and 1B’s Marks together and displays it as one row.

3) Two Subject_ID s will not have the same date time and I am considering modified date with milliseconds

4) Multiple entries of a Subject_ID for a department will always have the exact datetime.

My desired end result should be like below:

----------------------------------------------------------------------
Department_ID | Subject_ID | Student_ID | Marks | Modified_Date
----------------------------------------------------------------------
1 | S1 | 1B | 20 | 2009-06-27
----------------------------------------------------------------------
2 | S2 | 3 | 10 | 2009-06-27
----------------------------------------------------------------------
2 | S2 | 4 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S1 | 3 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S1 | 4 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S1 | 5 | 10 | 2009-06-27
----------------------------------------------------------------------
4 | S1 | 1B | 10 | 2009-06-28
----------------------------------------------------------------------

Can any body help me achieve this data in temp table so that I could insert it into onther table? Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 07:57:13

See http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

N 56°04'39.26"
E 12°55'05.63"

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-06-30 : 03:19:58

Hi

I was able to do the 1,3,4 part with the following query

" Select Department_ID,Subject_ID,Student_ID,Marks,Modified_Date from #TempO A
Where Modified_Date = (Select MAX(Modified_Date) From #TempO Where Department_ID= A.Department_ID Group By Department_ID)
Order By A.Department_ID "

I am getting the following result :

----------------------------------------------------------------------
Department_ID | Subject_ID | Student_ID | Marks | Modified_Date
----------------------------------------------------------------------
1 | S1 | 1 | 10 | 2009-06-27
----------------------------------------------------------------------
1 | S1 | 1B | 10 | 2009-06-27
----------------------------------------------------------------------
2 | S2 | 3 | 10 | 2009-06-27
----------------------------------------------------------------------
2 | S2 | 4 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S1 | 3 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S1 | 4 | 10 | 2009-06-27
----------------------------------------------------------------------
3 | S1 | 5 | 10 | 2009-06-27
----------------------------------------------------------------------
4 | S1 | 1B | 10 | 2009-06-28
----------------------------------------------------------------------

Only thing i need to do is point 2) where i need to merge both rows and sum the Marks of one Department_ID if it contains STUDENT_IDs 1 and 1B

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-30 : 03:24:35

select department_id, sum(marks) as marks from
(Select Department_ID,Subject_ID,Student_ID,Marks,Modified_Date from #TempO A
Where Modified_Date = (Select MAX(Modified_Date) From #TempO Where Department_ID= A.Department_ID Group By Department_ID))s
where cond........
group by department_id

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-06-30 : 03:27:49

I tried to set a row number and reset it to each Subject_ID with the below query

" select row_number() over(partition by Subject_ID order by Subject_ID ,Student_ID) as row_number, * from #TempO B "

----------------------------------------------------------------------
ROWID | Department_ID | Subject_ID | Student_ID | Marks | Modified_Date
----------------------------------------------------------------------
1 |1 | S1 | 1 | 10 | 2009-06-27
----------------------------------------------------------------------
2 |1 | S1 | 1B | 10 | 2009-06-27
----------------------------------------------------------------------
1 |2 | S2 | 3 | 10 | 2009-06-27
----------------------------------------------------------------------
2 |2 | S2 | 4 | 10 | 2009-06-27
----------------------------------------------------------------------
1 |3 | S1 | 3 | 10 | 2009-06-27
----------------------------------------------------------------------
2 |3 | S1 | 4 | 10 | 2009-06-27
----------------------------------------------------------------------
3 |3 | S1 | 5 | 10 | 2009-06-27
----------------------------------------------------------------------
1 |4 | S1 | 1B | 10 | 2009-06-28
----------------------------------------------------------------------

But not able to figure out how i can merger two rows based on a row set for studentIDs 1 and IB and delete the row for 1 for each department.

----------------------------------------------------------------------
ROWID | Department_ID | Subject_ID | Student_ID | Marks | Modified_Date
----------------------------------------------------------------------
1 |1 | S1 | 1 | 10 | 2009-06-27
----------------------------------------------------------------------
2 |1 | S1 | 1B | 10 | 2009-06-27
----------------------------------------------------------------------

quote:
Originally posted by Peso

See http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:59:57

[code]
SELECT Department_ID , Subject_ID , REPLACE(Student_ID,'1B','1') AS Student_ID, SUM(Marks) AS Marks ,Modified_Date
FROM
(
SELECT Department_ID , Subject_ID , Student_ID , Marks ,Modified_Date,DENSE_RANK() OVER (PARTITION BY Department_ID ORDER BY Modified_Date) AS RowNo
FROM YourTable
)t
WHERE RowNo=1
GROUP BY Department_ID ,Subject_ID ,REPLACE(Student_ID,'1B','1'),Modified_Date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 14:49:24

You can also try to do the REPLACE in the derived table

SELECT		Department_ID,
		Subject_ID,
		Student_ID,
		SUM(Marks) AS Marks,
		Modified_Date
FROM		(
			SELECT	Department_ID,
				Subject_ID,
				REPLACE(Student_ID, '1B', '1') AS Student_ID,
				Marks,
				Modified_Date,
				DENSE_RANK() OVER (PARTITION BY Department_ID ORDER BY Modified_Date) AS RowNo
			FROM	YourTable
		) AS t
WHERE		RowNo = 1
GROUP BY	Department_ID,
		Subject_ID,
		Student_ID,
		Modified_Date

N 56°04'39.26"
E 12°55'05.63"

kneekill
Yak Posting Veteran

76 Posts

Posted - 2009-07-01 : 06:35:00

Thanks a lot visakh and Peso

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:35:05

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources