纠正这个编码输出不能给出正确的 [英] correct this coding out put can not given proper
问题描述
声明 @ Id varchar ( 20 )
set @ Id = 2'
声明 @Pro_Id varchar ( 20 )
set @Pro_Id = ' 54'
声明 @ Charge_Id varchar ( 20 )
set @ Charge_Id = ' 0'
声明 @ Type varchar ( 20 )
set @ Type = ' 1'
声明 @ day int
设置 @ day = 2
声明 @ days varchar ( 8000 )
set @ days = ' [1]'
- 声明Pivot的最终列名
声明 @ columnsda y NVARCHAR (MAX)
set @columnsday = ' isnull(cast([1] as varchar(10)),'' '')as [1]'
WHILE ( @ day < = 31 )
BEGIN
设置 @ days = @ days + ' ,' + quotename( convert ( varchar , @ day ))
set @columnsday = @ columnsday + ' ,ISNULL(cast(' + quotename( convert ( varchar , @ day ))+ ' as varchar(10)) ,''')'' + quotename( convert ( varchar , @ day ))
set @ day = @ day + 1
- print @days
END
- print @days
- print @columnsday
DECLARE @ qu ery varchar ( 8000 )
set @ query = ' SELECT StudentCurrId ,(GenStudentCode)作为StudentCode,(Stuname)作为StudentName,
sum(现在)AS Present,SUM(Absent)AS Absent,SUM(Holiday)AS Holiday,SUM(Leave)AS Leave,
( SUM(当前)+ SUM(缺席)+ SUM(假日)+总和(离开))AS总计
(SELECT DISTINCT tbl_StudentAttendanceDetails.StudentCurrId,
GenStudentCode,upper(First_Name +''''+ Middle_Name +''''+ Last_Name)作为Stuname,
day(StudentAttendanceDate)as day,Stuattend,
CASE WHEN Stuattend =''A''然后1 ELSE 0 END AS''Absent'',
情况当Stuattend =''P''那么1 ELSE 0 END AS''Present'',
情况当Stuattend =''H''那么1 0结束'结束''假日'',
案例当Stuattend =''L'时那么1 ELSE 0结束''离开''
来自tbl_StudentAttendanceDetails
内部加入
tbl_StudentAttendanceMaster
on tbl_StudentAttendanceDetails.StuAttendId = tbl_StudentAttendanceMaster.StuAttendId
inner join tbl_Student_Current
on tbl_StudentAttendanceDetails.StudentCurrId = tbl_Student_Current.StuCurrentId
内部联接StudentAdmission on tbl_Student_Current.StudentId = StudentAdmission.StudentId
where convert(varchar,tbl_StudentAttendanceMaster.SessionId)=' + @ Id + ' 和
convert(varchar,tbl_StudentAttendanceMaster.ClassId)=' + @Pro_Id + '
和convert(varchar,tbl_StudentAttendanceMaster.StreamId)=' + @ Charge_Id + '
和convert(varchar,tbl_StudentAttendanceMaster.CurrentMonth)=' + @ Type + ' 跨度>
和(tbl_Student_Current.StudentStatus为null或tbl_Student_Current.StudentStatus =''Promotion'')
)AS SourceTable
pivot(
MAX(Stuattend)
for day(' + @ days + ' )
)p group by GenStudentCode,Stuname,Present,Absent,Holiday,Leave,StudentCurrId'
print (' pivoted report')
exec ( @ query )
out put不能给出正确的
============================== ========
SID SName Scode缺席假日假总计
1 kkp 001 1 0 0 0 1
Corrent Out这一个
========================= ===
SID SName Scode目前缺席假期总计
1 kkp 001 2 0 0 0 2
< blockquote>如果没有您的数据等,则无法更正查询。
相反,请尝试将查询分解为较小的部分。从'inside'开始并在验证该部分正常工作后展开查询。
例如,首先检查是否只从<$ c获取相关行$ C> tbl_StudentAttendanceMaster 。然后添加连接并查看连接后剩余的正确数据,依此类推。
declare @Id varchar(20)
set @Id='2'
declare @Pro_Id varchar(20)
set @Pro_Id='54'
declare @Charge_Id varchar(20)
set @Charge_Id='0'
declare @Type varchar(20)
set @Type='1'
declare @day int
set @day = 2
declare @days varchar(8000)
set @days = '[1]'
-- Declare final column names for Pivot
Declare @columnsday NVARCHAR(MAX)
set @columnsday = 'isnull(cast([1] as varchar(10)),'''') as [1]'
WHILE (@day <= 31)
BEGIN
set @days = @days + ', ' + quotename(convert(varchar, @day))
set @columnsday = @columnsday + ',ISNULL(cast(' + quotename(convert(varchar, @day)) + ' as varchar(10)) ,'''') as '+ quotename(convert(varchar, @day))
set @day = @day + 1
--print @days
END
--print @days
--print @columnsday
DECLARE @query varchar(8000)
set @query = 'SELECT StudentCurrId,(GenStudentCode)as StudentCode,(Stuname) as StudentName,
sum(Present) AS Present,SUM(Absent) AS Absent,SUM(Holiday) AS Holiday,SUM(Leave) AS Leave,
(SUM(Present)+SUM(Absent)+SUM(Holiday)+sum(Leave)) AS Total from
(SELECT DISTINCT tbl_StudentAttendanceDetails.StudentCurrId,
GenStudentCode,upper(First_Name + '' '' + Middle_Name + '' '' + Last_Name) as Stuname,
day(StudentAttendanceDate) as day,Stuattend,
CASE WHEN Stuattend=''A'' then 1 ELSE 0 END AS ''Absent'',
CASE WHEN Stuattend=''P'' THEN 1 ELSE 0 END AS ''Present'',
CASE WHEN Stuattend=''H'' THEN 1 ELSE 0 END AS ''Holiday'',
CASE WHEN Stuattend=''L'' THEN 1 ELSE 0 END AS ''Leave''
FROM tbl_StudentAttendanceDetails
inner join
tbl_StudentAttendanceMaster
on tbl_StudentAttendanceDetails.StuAttendId=tbl_StudentAttendanceMaster.StuAttendId
inner join tbl_Student_Current
on tbl_StudentAttendanceDetails.StudentCurrId=tbl_Student_Current.StuCurrentId
inner join StudentAdmission on tbl_Student_Current.StudentId=StudentAdmission.StudentId
where convert(varchar,tbl_StudentAttendanceMaster.SessionId)='+@Id+' and
convert(varchar,tbl_StudentAttendanceMaster.ClassId)='+@Pro_Id+'
and convert(varchar,tbl_StudentAttendanceMaster.StreamId)='+@Charge_Id+'
and convert(varchar,tbl_StudentAttendanceMaster.CurrentMonth)='+@Type+'
and (tbl_Student_Current.StudentStatus is null or tbl_Student_Current.StudentStatus=''Promotion'')
) AS SourceTable
pivot (
MAX(Stuattend)
for day in (' + @days +')
)p group by GenStudentCode,Stuname,Present,Absent,Holiday,Leave,StudentCurrId'
print('pivoted report')
exec(@query)
out put can not given proper
======================================
SID SName Scode Present Absent Holiday Leave Total
1 kkp 001 1 0 0 0 1
Corrent Out put this one
============================
SID SName Scode Present Absent Holiday Leave Total
1 kkp 001 2 0 0 0 2
Without having your data etc it's quite impossible to correct the query.
Instead, try breaking the query to smaller pieces. Start from the 'inside' and expand the query when you have verified that the portion works correctly.
For example check first that only relevant rows are fetched fromtbl_StudentAttendanceMaster
. Then add the join and see that the correct data is remaining after the join and so on.
这篇关于纠正这个编码输出不能给出正确的的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!