I have a table like below after several joins
username trandate positionname ChannelID
-------- ---- ------------ ---------
system 01/01/2019 1
anderson 06/04/2019 chief 1
williams 07/03/2019 chief 2
julie 15/02/2019 technician 48
julie 27/05/2019 chief technician 21
I want to count total number of transactions, sum of amount grouped by month, year and channel type.
there is a condition: if channelID is equals to "1", then query should exclude (1) transactions with has null value on "positionname" or (2) transactions with "system" username.
My code is something like this below:
select DISTINCT
DATEPART(YEAR,a.TranDate) as [year],
DATEPART(MONTH,a.TranDate) as [month],
count(*) as [transaction number],
sum(a.Amount) as [Total amount],
b.Name as [branch name],
c.ChannelName as [channel]
from transactions_main as a
left join branches as b WITH (NOLOCK) ON a.TranBranch=b.BranchId
left join Channels as c WITH (NOLOCK) ON a.ChannelId=c.ChannelId
left join Staff_Info as d WITH (NOLOCK) ON a.UserName=d.UserCode
where
a.TranDate>'20181231'
and b.name not in ('HQ')
and (a.ChannelId=1
and d.positionname is not null
and a.UserName not in ('SYSTEM', 'auto') )
group by b.Name, c.ChannelName,DATEPART(YEAR,a.TranDate), DATEPART(MONTH,a.TranDate)
order by b.Name, Year,month
In the end, of course, it didnt worked. I got the sum and count of transactions of only channelID=1's.
BONUS: You want to help another topic of mine? in which i also need help about indexing:
determining the staff's title when a certain transaction is done
here it is : link
use an OR operator
where
a.TranDate>'20181231'
and b.name not in ('HQ')
and (
(a.ChannelId=1
and d.positionname is not null
and a.UserName not in ('SYSTEM', 'auto') )
)
OR
a.ChannelId<>1
)