SQL
USE [PrintMonitor]
GO
/****** Object:  View [dbo].[PAGES PRINTED BY WEEKDAY AND HOUR]    Script Date: 10/15/2008 21:57:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PAGES PRINTED BY WEEKDAY AND HOUR]
AS
SELECT     TOP (100) PERCENT dbo.[Print Jobs].[User name], dbo.Printers.Name AS [Printer Name], DATEPART(WeekDay, dbo.[Print Jobs].Submitted) AS Day, 
                      DATEPART(Hour, dbo.[Print Jobs].Submitted) AS Hour, MAX(dbo.[Print Job Events].[Pages Printed]) AS [Total Pages Printed]
FROM         dbo.[Print Jobs] INNER JOIN
                      dbo.[Print Job Events] ON dbo.[Print Jobs].[Printer Id] = dbo.[Print Job Events].[Printer Id] AND 
                      dbo.[Print Jobs].[Job Id] = dbo.[Print Job Events].[Job Id] AND 
                      dbo.[Print Jobs].[Printer Reboot Count] = dbo.[Print Job Events].[Printer Reboot Count] INNER JOIN
                      dbo.Printers ON dbo.[Print Jobs].[Printer Id] = dbo.Printers.Id
WHERE     (dbo.[Print Job Events].Event = 2) AND (dbo.[Print Job Events].Printed = 1)
GROUP BY dbo.[Print Jobs].[User name], dbo.Printers.Name, DATEPART(WeekDay, dbo.[Print Jobs].Submitted), DATEPART(Hour, dbo.[Print Jobs].Submitted)
ORDER BY dbo.[Print Jobs].[User name], Day, [Printer Name]

Last edited Oct 15, 2008 at 8:57 PM by Merrion, version 1

Comments

No comments yet.