纠正这个编码输出不能给出正确的 [英] 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 from tbl_StudentAttendanceMaster. Then add the join and see that the correct data is remaining after the join and so on.

这篇关于纠正这个编码输出不能给出正确的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！