May 20, 2016 at 12:44 am
i have a query that gives me the attendance of students in a school the attendance will very in days and every day is a column just like that if i need to see attendance of last three days then then query will result these columns
1-- Roll#
2-- Name
3-- Class
4--17-may-2016
5-- 18-may-2016
6-- 19-may-2016
and the second example is that i want to see attendance of last 6 days then columns will be
1-- Roll#
2-- Name
3-- Class
4--14-may-2016
5-- 15-may-2016
6-- 16-may-2016
7-- 17-may-2016
8-- 18-may-2016
9-- 19-may-2016
Now i dont have a way to get this kind of result that will very in columns almost every time.
i want to know how i can receive query result in mvc4/mvc5 application and pass that result to a view so that i can show result to user
kindly suggest me as early as possible
May 20, 2016 at 5:46 am
I read your post and I have absolutely no idea what you are looking for. Please provide some create tables, inserts for some data and expected outputs from said data. Be sure to include any exceptional cases or scenarios.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2016 at 5:55 am
IF OBJECT_ID('tempdb..#Results',N'U') IS NOT NULL
DROP TABLE #Results
SELECT st.Name, st.FatherName, st.RollNo, at.AttendanceDate , atst.Status
INTO #Results
FROM Students st
inner JOIN SectaionJunction sec
ON sec.ID = st.ClassSectionJunctionID
inner join Attendance at on at.ClassSectionJunctionId=sec.ID
inner join [dbo].[AttendaceStatus] atst
on at.StatusID=atst.ID and sec.id=@secId and at.AttendanceDate>=@datefrom and at.AttendanceDate<=@dateto
GROUP BY at.AttendanceDate, st.Name, st.FatherName,st.RollNo, atst.Status
declare @SQL nvarchar(max), @Cols nvarchar(max)
select @Cols = stuff((select ',' + quotename(dt)
from (select DISTINCT top (31) convert(varchar(12),AttendanceDate,107)
as dt from #Results order by convert(varchar(12),AttendanceDate,107) ) x
ORDER BY dt FOR XML PATH('')),1,1,'')
@sql='SELECT * FROM #Results
PIVOT (max(Status) FOR [AttendanceDate] IN (' + @Cols
+')) pvt';
execute (@sql
)
This is the query that gets record from multiple tables and store in temporery table then convert the dates that are column values to columns itself
May 20, 2016 at 6:03 am
I don't have your tables, so those queries are not useful to me to create the data. Like I said, you need to provide create table statements and inserts for the data you need to have in them to produce your expected outputs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply