Excel Forum

Code: Select all

SELECT DISTINCT case LEFT(a.[VBELN],2) when '00' then RIGHT(a.[VBELN],8)
       else a.[VBELN] end as [Bill_No]
      ,cast(b.FKDAT as datetime2) as [Bill_Date]

	  ,case when left(a.[AUBEL],2)='00'  then RIGHT(a.[AUBEL],8)+RIGHT('00'+RIGHT(cast(a.[AUPOS] as int)/10,2),2)
	  else trim(right(a.[AUBEL],9))+RIGHT('00'+RIGHT(a.[AUPOS]/10,1),2) end as [SOOrderItem]

	  ,case b.FKART         when 'ZPA1' then CAST(a.[NETWR] as money) *-1
	                                when 'ZPA3' then CAST(a.[NETWR] as money) *-1
					when 'ZPC0' then CAST(a.[NETWR] as money) *-1
					when 'ZPC2' then CAST(a.[NETWR] as money) *-1
					when 'ZPC3' then CAST(a.[NETWR] as money) *-1
					when 'ZPC4' then CAST(a.[NETWR] as money) *-1
					when 'ZPC5' then CAST(a.[NETWR] as money) *-1
					else CAST(a.[NETWR] as money) end as [Amount THB]

  FROM [dbo].[VBRP] a
  left join [dbo].[VBRK] b
  on a.VBELN = b.VBELN

  where a.VSTEL = 'R221' and a.GSBER = 'R200' and a.VRKME <> 'ST' and a.WERKS = 'R221'
  and cast(b.FKDAT as datetime2) between '2022-08-01' and '2023-03-31'

Code: Select all

,case b.FKART when 'ZPA1' then CAST(a.[NETWR] as money) *-1
when 'ZPA3' then CAST(a.[NETWR] as money) *-1
when 'ZPC0' then CAST(a.[NETWR] as money) *-1
when 'ZPC2' then CAST(a.[NETWR] as money) *-1
when 'ZPC3' then CAST(a.[NETWR] as money) *-1
when 'ZPC4' then CAST(a.[NETWR] as money) *-1
when 'ZPC5' then CAST(a.[NETWR] as money) *-1
else CAST(a.[NETWR] as money) end as [Amount THB]

Code: Select all

,case when left(a.[AUBEL],2)='00'  then RIGHT(a.[AUBEL],8)+RIGHT('00'+RIGHT(cast(a.[AUPOS] as int)/10,2),2)
	  else trim(right(a.[AUBEL],9))+RIGHT('00'+RIGHT(a.[AUPOS]/10,1),2) end as [SOOrderItem]

Code: Select all

Sum(case b.FKART when 'ZPA1' then CAST(a.[NETWR] as money) *-1
when 'ZPA3' then CAST(a.[NETWR] as money) *-1
when 'ZPC0' then CAST(a.[NETWR] as money) *-1
when 'ZPC2' then CAST(a.[NETWR] as money) *-1
when 'ZPC3' then CAST(a.[NETWR] as money) *-1
when 'ZPC4' then CAST(a.[NETWR] as money) *-1
when 'ZPC5' then CAST(a.[NETWR] as money) *-1
else CAST(a.[NETWR] as money) end) as [Amount THB]

Code: Select all

Group By [SOOrderItem]