Declare @ A datetime
Set @ A = '000000'
While @ A> = '20140901'
Begin
Insert into mdxs
Select pluno =. pluno, pluname = max (. pluname), qty = sum (. qty), net = sum (. net), disamt = sum (. disamt), slamt = sum (. slamt), ML = case when Max (B. prop) = 0 then sum (. net)-sum (. qty) * max (isnull (B. curcsprc, 0 ))
When Max (B. Prop) <> 0 then sum (A. Net) * (max (B. prcrat)/100) End
, '20140901', @
From
(Select pluno =. pluno, pluname =. pluname, spec =. SPEC, qty =. qty, net =. net, disamt =. disamt, slamt =. net +. disamt
From (select. pluno,. pluname,. SPEC, D. qty, D. net, D. disamt, D. pluid,. optid,. dptid,. bndid
From basplucrtc,
(
Select B. pluid, qty = sum (B. qty), net = sum (B. AMT-B .DISAMT-ISNULL. disc, 0), disamt = sum (B. disamt + isnull (B. disc, 0 ))
From possrlbf C (nolock), possrldt B (nolock)
Where C. uid = B. UID and C. sldat> = dateadd (day,-1, @ A) and C. sldat <= @ A and (C. flag = 1 or C. flag = 5) and B. isdel = 0
Group by B. pluid
) D
Where a. pluid = D. pluid) A, basplucrtc B, basdept E
Where a. pluid = B. pluid and B. dptid = E. ID
Union all
Select pluno =. pluno, pluname = max (. pluname), spec = max (. SPEC), qty = sum (B. qty), net = sum (B. slnet), disamt = sum (B. disamt), slamt = sum (B. slamt)
From doc21bf C, doc21dt B, basplucrtc A, basdept E
Where C. docno = B. docno and B. pluid = A. pluid and A. dptid = E. ID
And C. docdat> = dateadd (day,-1, @ A) and C. docdat <@
And C. ctyp = 1 and isnull (C. Respiratory, 0) = 0 and C. Valid = 1
Group by A. pluno
) A left Outer Join (select pluno, curcsprc, prcrat, prop from basplumain A, baspluprc B where a. pluid = B. pluid) B on A. pluno = B. pluno
Group by A. pluno
Set @ A = dateadd (day,-1, @)
End