3 views becoming unweildy

[riyaz.mansoor]

Hi

Table T_VesselRoute { VR_VesselTrip, VR_DateTime, VR_Distance }
FOR EACH { TODAY, TODAY+1, ..., TODAY+6, REST }
Need UNIQUE COUNT(VR_VesselTrip), COUNT(VR_DateTime),
SUM(VR_Distance)

I have devised the following 3 views to achieve this, but it is
becoming too big - especially the 3rd view (given incomplete, but u
get the idea). I also need to count UNIQUE VR_VesselTrips - 2nd view

I'm quite a newbie to SQL. Is there a better way to do this?

Riyaz


CREATE VIEW CleanMoveStatus AS
SELECT VR.*, CAST(VR_DateTime AS INT) AS MoveDate,
FROM T_VesselRoute AS VR
WHERE MoveDate >= CAST(GETDATE() AS INT)
GO

CREATE VIEW DailyMoveStatus AS
SELECT CMS.MoveDate AS MoveDate,
COUNT(CMS.VR_VesselTrip) AS DailyTrips, -- UNIQUE COUNT ???
COUNT(CMS.MoveDate) AS DailyMoves,
SUM(CMS.VR_Distance) AS DailyDistance
FROM CleanMoveStatus AS CMS
GROUP BY MoveDate
GO

CREATE VIEW MoveStatus AS
SELECT
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyTrips ELSE 0 END) AS TodaysDailyTrips,
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyMoves ELSE 0 END) AS TodaysDailyMoves,
SUM(CASE WHEN DMS.MoveDate = CAST(GETDATE() AS INT) THEN
DMS.DailyDistance ELSE 0 END) AS TodaysDailyDistance,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyTrips ELSE 0 END) AS TodayPlus1DailyTrips,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyMoves ELSE 0 END) AS TodayPlus1DailyMoves,
SUM(CASE WHEN (DMS.MoveDate + 1) = CAST(GETDATE() AS INT) THEN
DMS.DailyDistance ELSE 0 END) AS TodayPlus1DailyDistance
FROM DailyMoveStatus AS DMS
GO