View Error on a Where Clause (1 Viewer)

[Steven Deetz]

I am trying to create a view where I am only trying to look at transaction data for the current year-to-date. I have a field in the view called TransactionDate and I am trying to apply 2 types of criteria. My first question is quite simple, do I need to create two views (one for each criteria) or one? If there can be one query instead of two how do I change the below SQL statement for SQL Server to accept the clause?

The two types of criteria in this view where transactions are being grouped includes:

WHERE (dbo.tbl_TransactionsMain.TransactionDate <= GETDATE())
HAVING (DATEPART(Year, dbo.tbl_TransactionsMain.TransactionDate) = DATEPART(Year, GETDATE()))

Below is the SQL for the View. Thanks in advance!

SELECT dbo.tbl_TransactionDetail.MediChargeSubscriberID, dbo.V_Subscribers.MediChargeSubscriber, dbo.tbl_TransactionDetail.DependantNumber,
dbo.tbl_TransactionDetail.DependantMedichargeID, COUNT(dbo.tbl_TransactionsMain.TransactionDate) AS CountedTrans, SUM(dbo.tbl_TransactionDetail.BilledAmount) AS TotBilled,
SUM(dbo.tbl_TransactionDetail.DepositAmount) AS TotDeposits, dbo.tbl_TransactionDetail.TransactionCategory
FROM dbo.tbl_TransactionsMain INNER JOIN
dbo.tbl_TransactionDetail ON dbo.tbl_TransactionsMain.TransactionID = dbo.tbl_TransactionDetail.TransactionID INNER JOIN
dbo.V_Subscribers ON dbo.tbl_TransactionDetail.MediChargeSubscriberID = dbo.V_Subscribers.MediChargeSubscriberID
WHERE (dbo.tbl_TransactionsMain.TransactionDate <= GETDATE())
GROUP BY dbo.tbl_TransactionDetail.MediChargeSubscriberID, dbo.V_Subscribers.MediChargeSubscriber, dbo.tbl_TransactionDetail.DependantNumber,
dbo.tbl_TransactionDetail.DependantMedichargeID, dbo.tbl_TransactionDetail.TransactionCategory
HAVING (DATEPART(Year, dbo.tbl_TransactionsMain.TransactionDate) = DATEPART(Year, GETDATE()))

 

[pdx_man]

If you are looking for everything in the current year, then you don't need the WHERE clause. You are already taking care of that by checking the year of the TransactionDate against the year of the current date.