SQL Server Filter Data from View

Question

I have a view that currently pulls in millions of records and I want to filter it down to a single, specific account number. I have currently decided that I would like to create new custom views with a filter by account number (for ease of use).

This is how I have done it:

SELECT * FROM VIEW_NAME WHERE [Account Number] = '1000'

This gives me exactly what I expected. However, I would like to create a brand new view called VIEW_NAME_1000 with one other modification, I would like to create a month column based off of an already existing date field.

I have tried this:

CASE
    WHEN(MONTH(T1.[TRX Date]) = 1) THEN "January " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 2) THEN "February " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 3) THEN "March " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 4) THEN "April " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 5) THEN "May " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 6) THEN "June " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 7) THEN "July " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 8) THEN "August " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 9) THEN "September " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 10) THEN "October " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 11) THEN "November " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    WHEN(MONTH(T1.[TRX Date]) = 12) THEN "December " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
END as 'Trx Month'

I do not know if I needed the T1. reference, especially since I am only pulling data from my already existing view.

Here is my entire SQL query, but it isn't working as expected... I am getting an error message no matter where I place my case when.

CREATE VIEW [dbo].[VIEW_NAME_1000] AS 

SELECT     [Journal Entry], Series, [TRX Date], [Account Number], [Account Description], [Credit Amount], [Debit Amount], [Account Category Number], 
                      [Account Description from Account Master], [Account Index], [Account Type], [Account Type from Account Master], Active, [Adjust for Inflation], [Back Out JE], 
                      [Balance For Calculation], [Balance For Calculation from Account Master], [Batch Number], [Batch Source], [Closed Year], [Conversion Method], [Correcting JE], 
                      CorrespondingUnit, [Created Date], [Currency ID], [Currency Index], [DTA Control Number], [DTA GL Status], [DTA Index], [DTA TRX Type], [Decimal Places], 
                      [Decimal Places from Account Master], [Denomination Exchange Rate], Description, [Document Date], [Document Status], [Error State], [Exchange Date], 
                      [Exchange Rate], [Exchange Table ID], [Fixed Or Variable], [Fixed Or Variable from Account Master], [Historical Rate], [History TRX], [History Year], ICDists, [IC TRX], 
                      [Inflation Equity Account Index], [Inflation Revenue Account Index], [Intercompany ID], [Last Date Edited], [Last User], [Line Status], [MC Transaction State], 
                      [Main Account Segment], [Modified Date], [Note Index], [Note Index from Account Master], [Open Year], [Original JE], [Originating Company ID], 
                      [Originating Control Number], [Originating Credit Amount], [Originating DTA Series], [Originating Debit Amount], [Originating Document Number], 
                      [Originating Journal Entry], [Originating Master ID], [Originating Master Name], [Originating Posted Date], [Originating Sequence Number], [Originating Source], 
                      [Originating TRX Source], [Originating TRX Type], [Originating Type], [Period ID], [Period Posting Number], [Approval User ID], [Approval Date], [Polled Transaction], 
                      [Post Inventory In], [Post Payroll In], [Post Purchasing In], [Post Sales In], [Posting Number], [Posting Type], [Posting Type from Account Master], [Printing Status], 
                      [Quick Offset], [Rate Calculation Method], [Rate Type ID], [Recurring TRX], [Recurring TRX Sequence], Reference, [Reversing Closed Year], [Reversing Date], 
                      [Reversing History TRX], [Reversing Period ID], [Reversing TRX Source], [Reversing Year], Segment1, Segment2, Segment3, [Sequence Number], [Source Document], 
                      [TRX Source], [Tax Date], Time, [Transaction Type], [Typical Balance], [User Defined 1], [User Defined 2], [User Who Posted], Voided, Segments, 
                      [Workflow Approval Status], [Workflow Priority], [Ledger Name], [Ledger Description], [Account Index For Drillback], [Journal Entry For Drillback]    
FROM         dbo.AccountTransactions
WHERE   ([Account Number] = '1000-000-00')

    CASE
        WHEN(MONTH(T1.[TRX Date]) = 1) THEN "January " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 2) THEN "February " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 3) THEN "March " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 4) THEN "April " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 5) THEN "May " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 6) THEN "June " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 7) THEN "July " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 8) THEN "August " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 9) THEN "September " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 10) THEN "October " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 11) THEN "November " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
        WHEN(MONTH(T1.[TRX Date]) = 12) THEN "December " + CONVERT(VARCHAR(4), YEAR(T1.[TRX Date]))
    END as 'Trx Month'

GO

What steps am I missing? I have been racking my head and don't know how to proceed.

You've tacked a `case` expression on the `where` clause, but it isn't "connected". There is nothing to tie it to the first term (`([Account Number] = '1000-000-00')`), e.g. `and`, and there is nothing using the value of the `case` expression, e.g. `... and case ... end = 'March 2017'`. [This](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer may help clarify it. It looks like you intended to add the `case` to the column list, i.e. before the `from` clause. — HABO, May 25 '17 at 18:30

score 3 · Accepted Answer · answered May 25 '17 at 18:05

3

You can drastically simplify the code to:

CREATE VIEW [dbo].[VIEW_NAME_1000] AS 
    SELECT . . .,
           DATENAME(MONTH, t.[TRX Date]) + ' ' + DATENAME(YEAR, t.[TRX Date])
    FROM dbo.AccountTransactions t
    WHERE [Account Number] = '1000-000-00';

You don't need a giant case expression to generate the month name.

answered May 25 '17 at 18:05

Gordon Linoff

1,242,037
58
646
786

I really, really appreciate it! That makes sense. – Jordan May 25 '17 at 20:43

SQL Server Filter Data from View

1 Answers1