3 views becoming unweildy

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
GOHi
"riyaz.mansoor@.gmail.com" wrote:

> 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
>
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
useful sample data.
You don't give which version of SQL Server you are using. With SQL 2005
there is a pivot operator. The method you show will work on SQL 2000. Usuall
y
pivoting is best left to the client. You should be able to do this with a
single view, which may be better if you don't need the other views. You make
want to make the view indexed which may improve performance depending how
many changes will be made.
If you want to use a single view the following should work assuming that you
are only testing the presence of the values indicating an entry and when
present it indicates a single occassion. It is not tested as there is no ddl
.
CREATE VIEW MoveStatus AS
SELECT
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyTrips IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyTrips,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyMoves IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyMoves,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT)
AND VR.DailyDistance IS NOT NULL THEN 1 ELSE 0 END) AS TodaysDailyDistance,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1
AND VR.DailyTrips IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyTrips,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1
AND VR.DailyMoves IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyMoves,
SUM(CASE WHEN CAST(VR.MoveDate AS INT) = CAST(GETDATE() AS INT) -1 AND
VR.DailyDistance IS NOT NULL THEN 1 ELSE 0 END) AS TodayPlus1DailyDistance
FROM T_VesselRoute AS VR
WHERE VR.MoveDate >= CAST(GETDATE() AS INT)
John