Sql-server – Create view with group by and extra fields

I think there's some misunderstanding about what you're attempting to do here.

Since your current design is to return all 24 rows from the base table, presumably all the supplementary fields are returned as well (to display in a grid, or something).

In order to fully aggregate the Value column, all the supplementary columns cannot be included in the SELECT list. Alternatively, if those columns are included in the GROUP BY clause, the view would represent only a partial aggregation, as there would be one row for each unique combination of the columns in the GROUP BY column list.

The only way I see something like this being useful is if the supplementary columns aren't included in the view, and there is some other process that requires only the daily aggregated values, without the base row data. Such a view could be defined like this:

CREATE TABLE [dbo].[BaseTbl]
(
    ColRowID bigint NOT NULL,
    AggregateID int NOT NULL,
    Epoch int NOT NULL,
    CustomerID int NOT NULL,
    TypeID tinyint NOT NULL,
    ErrorID smallint NOT NULL,
    Value int NOT NULL,

    PRIMARY KEY CLUSTERED(Epoch, CustomerId)
);
GO

CREATE VIEW [dbo].[ixvw_AggTbl]
    WITH SCHEMABINDING
AS
    SELECT
        t.Epoch / 86400 AS EpochDay,
        CustomerID,
        TypeID,
        SUM(t.Value) AS TotalValue,
        COUNT_BIG(*) AS __RowCount
        FROM [dbo].[BaseTbl] t
        GROUP BY
            t.Epoch / 86400,
            CustomerID,
            TypeID;
GO

CREATE UNIQUE CLUSTERED INDEX IX_ixvw_AggTbl
    ON [dbo].[ixvw_AggTbl](EpochDay, CustomerID, TypeID);

Unfortunately, you can't go farther and convert the EpochDay column to an actual date within the indexed view because DATEADD is non-deterministic (see Aaron's comment below for why), so you'd have to convert it in the actual SELECT query against the view. But that's not too difficult.

In any event, as I said before, I'm not sure how useful this would be for your specific application.

If you use the ROW_NUMBER function partitioning over "Code" and ordering by "Date" descending like the following query

SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
FROM   A
WHERE  CODE <> 'FOL'

you will get.

    RNO ID           CODE       FDATE
    1   customer001  INQ        09/30/2013
    2   customer001  INQ        01/01/2013
    1   customer001  REF        10/15/2013

Then if you use a left outer join using that same select statement on RNO where the code of the right table is 'REF' like below

SELECT a1.ID,
   a1.CODE AS Code1,
   a2.CODE AS Code2,
   a1.fdate AS fdate1,
   a2.fdate AS fdate2
FROM
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
  FROM A
  WHERE CODE <> 'FOL'
) AS a1
LEFT OUTER JOIN
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY DATE DESC) AS rno,
     ID,
     CODE,
     CONVERT (VARCHAR, DATE, 101) AS fdate
  FROM   A
  WHERE  CODE <> 'FOL'
  ) AS a2
  ON a1.rno = a2.rno
  AND a2.CODE = 'REF'
  WHERE  a1.CODE <> 'REF'
  ORDER BY FDATE1

and finally use a where clause filter on the left table where code is not equal to 'REF' you will get the following.

    ID          CODE1   CODE2   FDATE1      FDATE2
    customer001 INQ     (null)  01/01/2013  (null)
    customer001 INQ     REF     09/30/2013  10/15/2013

Hope this helps or is what you are looking for.