How to get the null column output

• sqlfriends@sql.com

  SSCommitted

  Points: 1504

  More actions

  August 4, 2010 at 3:03 am

  #238440

  Dear All;

  with A

  AS

  (

  SELECT CONVERT(VARCHAR,OESM.EDATETIME,103) [AExamdate],CM.COURSEID [ACOURSEID],CM.COURSENAME [ACOURSENAME],

  CASE WHEN SUBSTRING(CONVERT(VARCHAR,OESM.EDATETIME,0),13,2)='10' THEN 'Fore Noon'

  WHEN SUBSTRING(CONVERT(VARCHAR,OESM.EDATETIME,0),14,1)='2' THEN 'After Noon' end [Bsession],

  count(distinct HM.REGISTERNO) [REGULARCOUNT] FROM FeeTbl Efr

  Inner Join EFeeDet Efrd on Efr.receiptno=Efrd.receiptno

  Inner Join Eschedule Oesm on Oesm.Offlineesid=Efrd.Exsid

  Inner Join Hmas Hm on Hm.registerno=Efr.registerno

  Inner Join HDet Hd on Hd.Hallticketid=Hm.Hallticketid --and Hd.Exschid = Efrd.Exsid

  Inner Join Cmas cm on cm.courseid=Efrd.courseid

  Where 1=1

  and Oesm.Examscheduleid='05'

  and EFR.Examscheduleid='05'

  and convert(Varchar,Oesm.EdateTime,121) < '2010-06-28'

  and Oesm.PublishFlag='P'

  AND CM.coursetype not in('P' ,'f')

  and Efr.Appstatus='A'

  and Efrd.courseid='077000010'

  Group by

  CM.COURSEID,CM.COURSENAME,

  CASE WHEN SUBSTRING(CONVERT(VARCHAR,OESM.EDATETIME,0),13,2)='10' THEN 'Fore Noon'

  WHEN SUBSTRING(CONVERT(VARCHAR,OESM.EDATETIME,0),14,1)='2' THEN 'After Noon' end,

  CONVERT(VARCHAR,OESM.EDATETIME,103)

  ), B

  AS

  (

  SELECT CONVERT(VARCHAR,OESM.EDATETIME,103) [Examdate],CM.COURSEID,CM.COURSENAME,

  CASE WHEN SUBSTRING(CONVERT(VARCHAR,OESM.EDATETIME,0),13,2)='10' THEN 'Fore Noon'

  WHEN SUBSTRING(CONVERT(VARCHAR,OESM.EDATETIME,0),14,1)='2' THEN 'After Noon' end [session],

  count(distinct HM.REGISTERNO) [ARREARCOUNT] FROM FeeTbl Efr

  Inner Join EFeeDet Efrd on Efr.receiptno=Efrd.receiptno

  Inner Join Eschedule Oesm on Oesm.Offlineesid=Efrd.Exsid

  Inner Join Hmas Hm on Hm.registerno=Efr.registerno

  Inner Join HDet Hd on Hd.Hallticketid=Hm.Hallticketid --and Hd.Exschid = Efrd.Exsid

  Inner Join Cmas cm on cm.courseid=Efrd.courseid

  Where 1=1

  and Oesm.Examscheduleid='05'

  and EFR.Examscheduleid='05'

  and convert(Varchar,Oesm.EdateTime,121) < '2010-06-28'

  and Oesm.PublishFlag='P'

  AND CM.coursetype not in('P' ,'F')

  and Efr.Appstatus='A'

  and Efrd.courseid='077000010'

  Group by

  CM.COURSEID,CM.COURSENAME,

  CASE WHEN SUBSTRING(CONVERT(VARCHAR,OESM.EDATETIME,0),13,2)='10' THEN 'Fore Noon'

  WHEN SUBSTRING(CONVERT(VARCHAR,OESM.EDATETIME,0),14,1)='2' THEN 'After Noon' end,

  CONVERT(VARCHAR,OESM.EDATETIME,103)

  )

  SELECT [Examdate],COURSEID,COURSENAME,[session],isnull([ARREARCOUNT],0) [Arrear]

  ,isnull([REGULARCOUNT],0) [Regular],isnull(isnull([REGULARCOUNT],0)+Isnull([ARREARCOUNT],0),0) [Total Count] From

  A aj

  Left Join B bj on Aj.ACOURSEID=bj.COURSEID

  order By Courseid

  Output

  *****

  BExamdate BCOURSEID BCOURSENAMEBsession ARREARCOUNT REGULARCOUNT Total Count

  NULL NULL NULLNULL8 NULL NULL

  The result has been to null column has been showing ,

  I need the following output

  Examdate COURSEID COURSENAME session ARREARCOUNT REGULARCOUNT Total Count

  18/05/2010 070540033 ANALYSIS & DESIGN After Noon8 NULL NULL

  pls any one to henl to solved the problem

  Thanks;

  Rahuman.A

  coimbatore

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 4, 2010 at 6:53 am

  #1202056

  Please provide table DDL and sample data.

  Based on what you've provided so far, I guess there a re some missing rows in your B cte.

  Hard to tell without sample data...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]