select 
distinct
VisitDate
  , OrgName
  --, Location
  --, ClientCode
  --, ClientName
  --,  BusinessDescription
  --,  Iscash
  --, sum (case when LEN (Visitnumber) > 12 then 1 else 0 end) as RNICount
 -- , sum (case when LEN  ( VisitNumber) <= 12 then 1 else 0 end) as AttuneCount
   , sum (case when ThirdPartyReg = '1' then 1 else 0 end) as RNICount
  , sum (case when ThirdPartyReg is null then 1 else 0 end) as AttuneCount
  from 

(
SELECT distinct
cast (PV.Visitdate as date) VisitDate
  ,cast (PV.Visitdate as Time) VisitTime
  , oo.name OrgName
  ,OAD.Location
  ,CM.ClientCode
  ,CM.ClientName
  , MV.[Description] BusinessDescription
  , CM.Iscash
  ,PV.VisitNumber
  ,PV.ThirdPartyReg
  --, sum (case when LEN (PV.Visitnumber) > 12 then 1 else 0 end) as RNICOunt
  --, sum (case when LEN  ( PV.VisitNumber) > 10 then 1 else 0 end) as AttuneCount
  --, sum (case when len  (  PV.VisitNumber) <= 12 then 1 else 0 end) as AttuneCount
  --, case when len  ( PV.VisitNumber) > 12 then COUNT(DISTINCT PV.VisitNumber)  end as RNICount
  --, case when len  (  PV.VisitNumber) <= 12 then COUNT(DISTINCT PV.VisitNumber)  end as AttuneCount
  --, SUM(BD.ActualAmount) GrossValue
  --, SUM(BD.ActualAmount-BD.Amount) CollectionCharges
  --, SUM(BD.DiscountAmount) Billdiscount
  --, SUM(ISNULL(BD.Amount , 0) - (ISNULL(BD.DiscountAmount,0) + ISNULL(BD.RedeemAmount,0) )) Netvalue


  from [LIMS_LIVE].[dbo].[BillingDetails] BD (NOLOCK)
  INNER JOIN [LIMS_LIVE].[dbo].FINALBILL FB  (NOLOCK) ON BD.OrgID = FB.OrgID
  AND BD.FinalBillID = FB.FinalBillID
  LEFT JOIN [LIMS_LIVE].[dbo].[AmountRefundDetails] ARD ON
     ARD.[FinalBillID] = BD.FinalBillID
     AND ARD.[BillingDetailsID] = BD.[BillingDetailsID]
     AND ARD.[OrgID] = BD.[OrgID]
  INNER JOIN [LIMS_LIVE].[dbo].[VisitClientMapping] VCM (NOLOCK) ON
        VCM.[FinalBillID] = FB.FinalBillID
		AND VCM.VisitID = FB.VisitID
        AND VCM.[OrgID] = FB.OrgID
  INNER JOIN [LIMS_LIVE].[dbo].[PatientVisit] PV (NOLOCK) ON
        FB.[VisitID] = PV.PatientVisitID
        AND FB.[OrgID] = PV.OrganizationID
  INNER JOIN [LIMS_LIVE].[dbo].[Patient] P (NOLOCK) ON
        P.[PatientID] = PV.PatientID
        AND P.[OrganizationID] = PV.OrganizationID
  INNER JOIN [LIMS_LIVE].[dbo].ClientMaster cm (NOLOCK) on VCM.[OrgID] = cm.OrgID  AND VCM.[ClientID] = cm.ClientID
  inner  join [LIMS_LIVE].[dbo].OrganizationAddress OAD With(nolock)
     ON BD.OrgID = OAD.OrganizationID AND FB.OrgAddressID = OAD.AddressID
  INNER JOIN [LIMS_LIVE].[dbo].[View_Tests671] Tests
  ON Tests.ACRPID = BD.[FeeId]
     AND Tests.AType = BD.[FeeType]
     AND Tests.OrgID = BD.OrgID
     Inner JOin [LIMS_LIVE].[dbo].Organization oo with (nolock) on oo.OrganizationID = OAD.OrganizationID
	 Inner join [LIMS_LIVE].[dbo].MetaValue_Common MV with (nolock) on MV.OrgID = CM.OrgID
and MV.MetaValueID = Cm.CustomerType

  WHERE  ARD.[BillingDetailsID] IS NULL
  AND ClientCode <> 'DCTG'
  AND ClientCode NOT LIKE '%DUMM%'
  AND P.NAME NOT LIKE '%DUMM%'
  AND ISNULL(VCM.[IsActive] , 'Y') = 'Y'
  AND ISNULL(FB.[RefFinalBillID] , 0) = 0
    and PV.ReferOrgID is not null
AND  CAST(PV.Visitdate as Date)  >= cast ( getdate ()  as date)
AND  CAST(PV.Visitdate as Date)  <= cast ( getdate ()  as date)
--and Tests.Tcode not in ('S0558')
and CM.Clientcode not like 'SL%'
and CM.Clientcode not in ( 'NS001')
and CM.Clientcode not like 'INT%'
and CM.Clientcode not like 'IF%'
/* and OO.OrganizationID in 

(70,181,184,140,150,200,194,142,163,86,109,195,148,196,151,153,183,
114,137,88,104,89,
97,
99,
101,
69,
72,84,162,201,205,87,110,120,83,155,161,168,122,123,125,79,
116,
117,
135,
147,
149,189,192,80,136,178, 128, 78,107, 191,190, 71,126,143,144,182, 108,121,209,
118,130,131,113,171
,204, 175, 173,138, 193, 103, 102, 98,111
,177,132,127,198,203,207,172


-----------------20200618 added 96 below orgs ---

(127,
70,
181,
184,
140,
198,
150,
200,
163,
86,
109,
195,
194,
142,
148,
196,
151,
153,
183,
114,
137,
88,
104,
132,
118,
108,
89,
97,
203,
99,
101,
69,
72,
83,
84,
162,
87,
121,
209,
201,
205,
79,
116,
117,
135,
74,
147,
149,
189,
130,
110,
131,
112,
113,
120,
155,
171,
207,
161,
168,
172,
100,
192,
204,
193,
103,
173,
138,
80,
136,
178,
185,
210,
115,
119,
141,
129,
128,
122,
123,
125,
191,
190,
78,
107,
71,
126,
143,
144,
182,
111,
98,
75,
157,
176,
102,
175,
158,
67
)
*/
and BD.Feeid > 0

) Thane
	--ORDER BY AttuneCount ASC
   GROUP BY
    VisitDate
  , OrgName
  --, Location
  --, ClientCode
  --, ClientName
  --,  BusinessDescription
  --,  Iscash