SQL Queries for Dise Complete

From DISE KnowledgeBase

Jump to: navigation, search

You are welcome to create your own interfaces that use the Dise Complete database.

Here are a number of example SQL queries to help you get started:

Contents

Content

Get all the content for the whole Dise Complete database

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany 
FROM Content 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany 
FROM Content 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
LEFT JOIN People ON Providers.PeopleID=People.IndexID 
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID;

Get the content for a specific project

Change "<ProjectID>" to the number of the project

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.ContentCount, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany 
FROM 
(
  SELECT Content.IndexID AS ContentIndexID, 
  COUNT(*) AS ContentCount 
  FROM Content 
  LEFT JOIN (PlayerContent) ON (Content.IndexID=PlayerContent.ContentID) 
  LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
  LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
  LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
  LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
  LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
  LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
  WHERE (Projects.IndexID=<ProjectID>)
  GROUP BY Content.IndexID
) AS Totals 
LEFT JOIN (Content) ON (Content.IndexID = Totals.ContentIndexID) 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.ContentCount, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  COUNT(*) AS ContentCount 
  FROM Content 
  LEFT JOIN PlayerContent ON Content.IndexID=PlayerContent.ContentID 
  LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
  LEFT JOIN People ON Providers.PeopleID=People.IndexID 
  LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
  LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
  LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
  LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
  WHERE (Projects.IndexID=<ProjectID>) 
  GROUP BY Content.IndexID
) AS Totals 
LEFT JOIN Content ON Content.IndexID = Totals.ContentIndexID 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
LEFT JOIN People ON Providers.PeopleID=People.IndexID 
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID;

Get the content for a specific site

Change "<SiteID>" to the number of the site

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.ContentCount, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany 
FROM 
(
  SELECT Content.IndexID AS ContentIndexID, 
  COUNT(*) AS ContentCount 
  FROM Content 
  LEFT JOIN (PlayerContent) ON (Content.IndexID=PlayerContent.ContentID) 
  LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
  LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
  LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
  LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
  LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
  LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
  WHERE (Sites.IndexID=<SiteID>) 
  GROUP BY Content.IndexID
) AS Totals 
LEFT JOIN (Content) ON (Content.IndexID = Totals.ContentIndexID) 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID);

Example.png MSSQL:

SELECT
Content.ContentName, 
Content.Type, 
Totals.ContentCount, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  COUNT(*) AS ContentCount 
  FROM Content 
  LEFT JOIN PlayerContent ON Content.IndexID=PlayerContent.ContentID 
  LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
  LEFT JOIN People ON Providers.PeopleID=People.IndexID 
  LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
  LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
  LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
  LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
  WHERE (Sites.IndexID=<SiteID>) 
  GROUP BY Content.IndexID 
) AS Totals 
LEFT JOIN Content ON Content.IndexID = Totals.ContentIndexID 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
LEFT JOIN People ON Providers.PeopleID=People.IndexID 
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID;

Get the content for a specific player

Change "<PlayerID>" to the number of the player

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.ContentCount, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  COUNT(*) AS ContentCount 
  FROM Content 
  LEFT JOIN (PlayerContent) ON (Content.IndexID=PlayerContent.ContentID) 
  LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
  LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
  LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
  LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
  LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
  LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
  WHERE (Players.IndexID=<PlayerID>) 
  GROUP BY Content.IndexID
) AS Totals 
LEFT JOIN (Content) ON (Content.IndexID = Totals.ContentIndexID) 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID)

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.ContentCount, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  COUNT(*) AS ContentCount 
  FROM Content 
  LEFT JOIN PlayerContent ON Content.IndexID=PlayerContent.ContentID 
  LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
  LEFT JOIN People ON Providers.PeopleID=People.IndexID 
  LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
  LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
  LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
  LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
  WHERE (Players.IndexID=<PlayerID>) 
  GROUP BY Content.IndexID 
) AS Totals 
LEFT JOIN Content ON Content.IndexID = Totals.ContentIndexID 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
LEFT JOIN People ON Providers.PeopleID=People.IndexID 
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID;

Versioned content

List all the Dise Movie version information

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany, 
Content.IndexID 
FROM Content 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
WHERE (Content.Versioned = 1);

Example.png MSSQL:

SELECT
Content.ContentName, 
Content.Type, 
People.Name AS Provider, 
Companies.Name AS ProviderCompany, 
Content.IndexID 
FROM Content 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID
LEFT JOIN People ON Providers.PeopleID=People.IndexID
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID
WHERE (Content.Versioned = 1);

List all the versiones of a certain Dise Movie

Change "<DiseMovieContentID>" to the content ID of the Dise Movie

Example.png MySQL:

SELECT 
ContentFiles.FileName AS Name, 
ContentFiles.Version AS MajorVersion, 
ContentFiles.SubVersion AS MinorVersion, 
ContentFiles.FileDate AS LastChanged, 
ApprovedLevel.Name AS ApprovedLevel,
ApprovedLevel.ColorRed, 
ApprovedLevel.ColorGreen,
ApprovedLevel.ColorBlue FROM ContentFiles 
LEFT JOIN (ApprovedLevel) ON (ContentFiles.ALevel=ApprovedLevel.ALevel) 
WHERE (ContentID=<DiseMovieContentID>) 
ORDER BY ContentFiles.Version, ContentFiles.SubVersion;

Example.png MSSQL:

SELECT
ContentFiles.FileName AS Name, 
ContentFiles.Version AS MajorVersion, 
ContentFiles.SubVersion AS MinorVersion, 
ContentFiles.FileDate AS LastChanged, 
ApprovedLevel.Name AS ApprovedLevel,
ApprovedLevel.ColorRed, 
ApprovedLevel.ColorGreen,
ApprovedLevel.ColorBlue FROM ContentFiles 
LEFT JOIN ApprovedLevel ON ContentFiles.ALevel=ApprovedLevel.ALevel
WHERE (ContentID=<DiseMovieContentID>) 
ORDER BY ContentFiles.Version, ContentFiles.SubVersion;

Statistics

Get Playback date and time for everything in the databasse

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Statistics.Start, 
Statistics.Stop 
FROM Statistics 
LEFT JOIN (PlayerContent) ON (PlayerContent.IndexID = Statistics.ContentID) 
LEFT JOIN (Content) ON (PlayerContent.ContentID = Content.IndexID) 
LEFT JOIN (Players) ON (Players.IndexID=PlayerContent.PlayerID) 
LEFT JOIN (Sites) ON (Sites.IndexID=Players.SiteID) 
LEFT JOIN (Projects) ON (Projects.IndexID=Sites.ProjectID) 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
ORDER BY 3;

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
"Statistics".Start, 
"Statistics".Stop 
FROM "Statistics" 
LEFT JOIN PlayerContent ON PlayerContent.IndexID = "Statistics".ContentID 
LEFT JOIN Content ON PlayerContent.ContentID = Content.IndexID 
LEFT JOIN Players ON Players.IndexID=PlayerContent.PlayerID 
LEFT JOIN Sites ON Sites.IndexID=Players.SiteID 
LEFT JOIN Projects ON Projects.IndexID=Sites.ProjectID 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
LEFT JOIN People ON Providers.PeopleID=People.IndexID 
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
ORDER BY 3 DESC;

Get Playback date and time for a specific project

Change "<ProjectID>" to the number of the project

Example.png MySQL:

SELECT
Content.ContentName, 
Content.Type, 
Statistics.Start, 
Statistics.Stop 
FROM Statistics 
LEFT JOIN (PlayerContent) ON (PlayerContent.IndexID = Statistics.ContentID) 
LEFT JOIN (Content) ON (PlayerContent.ContentID=Content.IndexID) 
LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
WHERE (Projects.IndexID=<ProjectID>);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
"Statistics".Start, 
"Statistics".Stop 
FROM "Statistics" 
LEFT JOIN PlayerContent ON PlayerContent.IndexID = "Statistics".ContentID 
LEFT JOIN Content ON PlayerContent.ContentID=Content.IndexID 
LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
LEFT JOIN People ON Providers.PeopleID=People.IndexID 
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
WHERE (Projects.IndexID=<ProjectID>);

Get Playback date and time for a specific site

Change "<SiteID>" to the number of the site

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Statistics.Start, 
Statistics.Stop 
FROM Statistics 
LEFT JOIN (PlayerContent) ON (PlayerContent.IndexID = Statistics.ContentID) 
LEFT JOIN (Content) ON (PlayerContent.ContentID=Content.IndexID) 
LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
WHERE (Sites.IndexID=<SiteID>);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
"Statistics".Start, 
"Statistics".Stop 
FROM "Statistics" 
LEFT JOIN PlayerContent ON PlayerContent.IndexID = "Statistics".ContentID 
LEFT JOIN Content ON PlayerContent.ContentID=Content.IndexID 
LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
LEFT JOIN People ON Providers.PeopleID=People.IndexID 
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
WHERE (Sites.IndexID=<SiteID>);

Get Playback date and time for a specific player

Change "<PlayerID>" to the number of the player

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Statistics.Start, 
Statistics.Stop 
FROM Statistics 
LEFT JOIN (PlayerContent) ON (PlayerContent.IndexID = Statistics.ContentID) 
LEFT JOIN (Content) ON (PlayerContent.ContentID=Content.IndexID) 
LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
WHERE (Players.IndexID=<PlayerID>);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
"Statistics".Start, 
"Statistics".Stop 
FROM "Statistics" 
LEFT JOIN PlayerContent ON PlayerContent.IndexID = "Statistics".ContentID 
LEFT JOIN Content ON PlayerContent.ContentID=Content.IndexID 
LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
LEFT JOIN People ON Providers.PeopleID=People.IndexID 
LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
WHERE (Players.IndexID=<PlayerID>);

Get Playback count and total time for everything in the database

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.PlayedCount, 
TIME_FORMAT(SEC_TO_TIME(Totals.SumTime), '%H:%i:%s') AS TotalTime 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  Count(*) AS PlayedCount, 
  SUM(TIME_TO_SEC(TIMEDIFF(Statistics.Stop, Statistics.Start))) AS SumTime 
  FROM Statistics 
  LEFT JOIN (PlayerContent) ON (Statistics.ContentID=PlayerContent.IndexID) 
  LEFT JOIN (Content) ON (PlayerContent.ContentID=Content.IndexID) 
  LEFT JOIN (Players) ON (Players.IndexID=PlayerContent.PlayerID) 
  LEFT JOIN (Sites) ON (Sites.IndexID=Players.SiteID) 
  LEFT JOIN (Projects) ON (Projects.IndexID=Sites.ProjectID) 
  LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
  LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
  LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
  GROUP BY Content.IndexID
) AS Totals 
LEFT JOIN (Content) ON (Content.IndexID=Totals.ContentIndexID);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.PlayedCount, 
CONVERT(varchar(6), Totals.SumTime/3600) + ':' +
  RIGHT('0' + CONVERT(varchar(2), (Totals.SumTime%3600) / 60), 2) + ':' + 
  RIGHT('0' + CONVERT(varchar(2), (Totals.SumTime%60)), 2) AS TotalTime 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  Count(*) AS PlayedCount, 
  SUM(DATEDIFF(second, CASE WHEN Start < '2000-01-01 00:00:00' THEN NULL ELSE Start END, Stop)) AS SumTime 
  FROM "Statistics" 
  LEFT JOIN PlayerContent ON "Statistics".ContentID=PlayerContent.IndexID 
  LEFT JOIN Content ON PlayerContent.ContentID=Content.IndexID 
  LEFT JOIN Players ON Players.IndexID=PlayerContent.PlayerID 
  LEFT JOIN Sites ON Sites.IndexID=Players.SiteID 
  LEFT JOIN Projects ON Projects.IndexID=Sites.ProjectID 
  LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
  LEFT JOIN People ON Providers.PeopleID=People.IndexID 
  LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
  GROUP BY Content.IndexID 
) AS Totals 
LEFT JOIN Content ON Content.IndexID=Totals.ContentIndexID;

Get Playback count and total time for a specific project

Change "<ProjectID>" to the number of the project

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.PlayedCount, 
TIME_FORMAT(SEC_TO_TIME(Totals.SumTime), '%H:%i:%s') AS TotalTime 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  Count(*) AS PlayedCount, 
  SUM(TIME_TO_SEC(TIMEDIFF(Statistics.Stop, Statistics.Start))) AS SumTime 
  FROM Statistics 
  LEFT JOIN (PlayerContent) ON (Statistics.ContentID=PlayerContent.IndexID) 
  LEFT JOIN (Content) ON (PlayerContent.ContentID=Content.IndexID) 
  LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
  LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
  LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
  LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
  LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
  LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
  WHERE (Projects.IndexID=<ProjectID>) 
  GROUP BY Content.IndexID
) AS Totals 
LEFT JOIN (Content) ON (Content.IndexID=Totals.ContentIndexID);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.PlayedCount, 
CONVERT(varchar(6), Totals.SumTime/3600) + ':' +
  RIGHT('0' + CONVERT(varchar(2), (Totals.SumTime%3600) / 60), 2) + ':' + 
  RIGHT('0' + CONVERT(varchar(2), (Totals.SumTime%60)), 2) AS TotalTime 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  Count(*) AS PlayedCount, 
  SUM(DATEDIFF(second, CASE WHEN Start < '2000-01-01 00:00:00' THEN NULL ELSE Start END, Stop)) AS SumTime 
  FROM "Statistics" 
  LEFT JOIN PlayerContent ON "Statistics".ContentID=PlayerContent.IndexID 
  LEFT JOIN Content ON PlayerContent.ContentID=Content.IndexID 
  LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
  LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
  LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
  LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
  LEFT JOIN People ON Providers.PeopleID=People.IndexID 
  LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
  WHERE (Projects.IndexID=<ProjectID>) 
  GROUP BY Content.IndexID 
) AS Totals 
LEFT JOIN Content ON Content.IndexID=Totals.ContentIndexID;

Get Playback count and total time for a specific site

Change "<SiteID>" to the number of the site

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.PlayedCount, 
TIME_FORMAT(SEC_TO_TIME(Totals.SumTime), '%H:%i:%s') AS TotalTime 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  Count(*) AS PlayedCount, 
  SUM(TIME_TO_SEC(TIMEDIFF(Statistics.Stop, Statistics.Start))) AS SumTime 
  FROM Statistics 
  LEFT JOIN (PlayerContent) ON (Statistics.ContentID=PlayerContent.IndexID) 
  LEFT JOIN (Content) ON (PlayerContent.ContentID=Content.IndexID) 
  LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
  LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
  LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
  LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
  LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
  LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
  WHERE (Sites.IndexID=<SiteID>) 
  GROUP BY Content.IndexID 
) AS Totals 
LEFT JOIN (Content) ON (Content.IndexID=Totals.ContentIndexID);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.PlayedCount, 
CONVERT(varchar(6), Totals.SumTime/3600) + ':' +
  RIGHT('0' + CONVERT(varchar(2), (Totals.SumTime%3600) / 60), 2) + ':' + 
  RIGHT('0' + CONVERT(varchar(2), (Totals.SumTime%60)), 2) AS TotalTime 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  Count(*) AS PlayedCount, 
  SUM(DATEDIFF(second, CASE WHEN Start < '2000-01-01 00:00:00' THEN NULL ELSE Start END, Stop)) AS SumTime 
  FROM "Statistics" LEFT JOIN PlayerContent ON "Statistics".ContentID=PlayerContent.IndexID 
  LEFT JOIN Content ON PlayerContent.ContentID=Content.IndexID 
  LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
  LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
  LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
  LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
  LEFT JOIN People ON Providers.PeopleID=People.IndexID 
  LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
  WHERE (Sites.IndexID=1) 
  GROUP BY Content.IndexID 
) AS Totals 
LEFT JOIN Content ON Content.IndexID=Totals.ContentIndexID;

Get Playback count and total time for a specific player

Change "<PlayerID>" to the number of the player

Example.png MySQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.PlayedCount, 
TIME_FORMAT(SEC_TO_TIME(Totals.SumTime), '%H:%i:%s') AS TotalTime 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  Count(*) AS PlayedCount, 
  SUM(TIME_TO_SEC(TIMEDIFF(Statistics.Stop, Statistics.Start))) AS SumTime 
  FROM Statistics 
  LEFT JOIN (PlayerContent) ON (Statistics.ContentID=PlayerContent.IndexID) 
  LEFT JOIN (Content) ON (PlayerContent.ContentID=Content.IndexID) 
  LEFT JOIN (Players) ON (PlayerContent.PlayerID=Players.IndexID) 
  LEFT JOIN (Sites) ON (Players.SiteID = Sites.IndexID) 
  LEFT JOIN (Projects) ON (Sites.ProjectID = Projects.IndexID) 
  LEFT JOIN (Providers) ON (Content.ProviderID=Providers.IndexID) 
  LEFT JOIN (People) ON (Providers.PeopleID=People.IndexID) 
  LEFT JOIN (Companies) ON (Providers.CompanyID=Companies.IndexID) 
  WHERE (Players.IndexID=<PlayerID>) 
  GROUP BY Content.IndexID
) AS Totals 
LEFT JOIN (Content) ON (Content.IndexID=Totals.ContentIndexID);

Example.png MSSQL:

SELECT 
Content.ContentName, 
Content.Type, 
Totals.PlayedCount, 
CONVERT(varchar(6), Totals.SumTime/3600) + ':' +
  RIGHT('0' + CONVERT(varchar(2), (Totals.SumTime%3600) / 60), 2) + ':' + 
  RIGHT('0' + CONVERT(varchar(2), (Totals.SumTime%60)), 2) AS TotalTime 
FROM 
(
  SELECT 
  Content.IndexID AS ContentIndexID, 
  Count(*) AS PlayedCount, 
  SUM(DATEDIFF(second, CASE WHEN Start < '2000-01-01 00:00:00' THEN NULL ELSE Start END, Stop)) AS SumTime 
  FROM "Statistics" 
  LEFT JOIN PlayerContent ON "Statistics".ContentID=PlayerContent.IndexID 
  LEFT JOIN Content ON PlayerContent.ContentID=Content.IndexID 
  LEFT JOIN Players ON PlayerContent.PlayerID=Players.IndexID 
  LEFT JOIN Sites ON Players.SiteID = Sites.IndexID 
  LEFT JOIN Projects ON Sites.ProjectID = Projects.IndexID 
  LEFT JOIN Providers ON Content.ProviderID=Providers.IndexID 
  LEFT JOIN People ON Providers.PeopleID=People.IndexID 
  LEFT JOIN Companies ON Providers.CompanyID=Companies.IndexID 
  WHERE (Players.IndexID=<PlayerID>) 
  GROUP BY Content.IndexID 
) AS Totals 
LEFT JOIN Content ON Content.IndexID=Totals.ContentIndexID;

Status

List all the Players and their details

Example.png MySQL:

SELECT 
Players.Name,
Players.Identity,
Players.MacAddress,
Players.IPNumber,
Players.Status, 
Sites.Name AS SiteName,
Projects.Name AS ProjectName 
FROM Players 
LEFT JOIN (Sites) ON (Players.SiteID=Sites.IndexID) 
LEFT JOIN (Projects) ON (Sites.ProjectID=Projects.IndexID);

Example.png MSSQL:

SELECT 
Players.Name,
Players."Identity",
Players.MacAddress,
Players.IPNumber,
Players.Status, 
Sites.Name AS SiteName,
Projects.Name AS ProjectName 
FROM Players 
LEFT JOIN Sites ON Players.SiteID=Sites.IndexID 
LEFT JOIN Projects ON Sites.ProjectID=Projects.IndexID;

Show the details for one inventory item (software)

Change "<InventoryID>" to the id of the inventory

Example.png MySQL:

SELECT 
Inventory.Name, 
Inventory.InventoryGUID, 
Inventory.Description, 
Inventory.Version, 
InventoryLibrary.Name AS InvName, 
InventoryLibrary.Type AS InvType, 
InventoryLibrary.Description AS InvDesc, 
ControlSites.Name AS CSName, 
Players.Name AS PName, 
Sites.Name AS CSiteName, 
Projects.Name AS CProjectName, 
Sites2.Name AS PSiteName, 
Projects2.Name AS PProjectName, 
Status.LastContact, 
Status.TTL, 
CASE 
  WHEN Status.TTL IS NULL THEN 
    NULL
  ELSE 
    TIME_TO_SEC(TIMEDIFF(Status.LastContact, SUBTIME(CURRENT_TIMESTAMP, SEC_TO_TIME(Status.TTL)))) 
END AS SecondsToLive, 
Status.LastContactLocalTime, 
Status.Screenshot 
FROM Inventory 
LEFT JOIN (InventoryLibrary) ON (Inventory.LibraryID=InventoryLibrary.IndexID) 
LEFT JOIN (ControlSites) ON (Inventory.ControlSiteID=ControlSites.IndexID) 
LEFT JOIN (Players) ON (Inventory.PlayerID=Players.IndexID) 
LEFT JOIN (Sites) ON (ControlSites.SiteID=Sites.IndexID) 
LEFT JOIN (Projects) ON (Sites.ProjectID=Projects.IndexID) 
LEFT JOIN (Sites AS Sites2) ON (Players.SiteID=Sites2.IndexID) 
LEFT JOIN (Projects AS Projects2) ON (Sites2.ProjectID=Projects2.IndexID) 
LEFT JOIN (Status) ON (Status.InventoryID=Inventory.IndexID) 
WHERE (Inventory.IndexID=<InventoryID>);

Example.png MSSQL:

SELECT 
Inventory.Name, 
Inventory.InventoryGUID, 
Inventory.Description, 
Inventory.Version, 
InventoryLibrary.Name AS InvName, 
InventoryLibrary.Type AS InvType, 
InventoryLibrary.Description AS InvDesc, 
ControlSites.Name AS CSName, 
Players.Name AS PName, 
Sites.Name AS CSiteName, 
Projects.Name AS CProjectName, 
Sites2.Name AS PSiteName, 
Projects2.Name AS PProjectName, 
Status.LastContact, Status.TTL, 
CASE 
  WHEN Status.TTL IS NULL THEN 
    NULL
  ELSE
    DATEDIFF(second, DATEADD(second,  -Status.TTL, CURRENT_TIMESTAMP), Status.LastContact) 
END AS SecondsToLive, 
Status.LastContactLocalTime, 
Status.Screenshot 
FROM Inventory 
LEFT JOIN InventoryLibrary ON Inventory.LibraryID=InventoryLibrary.IndexID 
LEFT JOIN ControlSites ON Inventory.ControlSiteID=ControlSites.IndexID 
LEFT JOIN Players ON Inventory.PlayerID=Players.IndexID 
LEFT JOIN Sites ON ControlSites.SiteID=Sites.IndexID 
LEFT JOIN Projects ON Sites.ProjectID=Projects.IndexID 
LEFT JOIN Sites AS Sites2 ON Players.SiteID=Sites2.IndexID 
LEFT JOIN Projects AS Projects2 ON Sites2.ProjectID=Projects2.IndexID 
LEFT JOIN Status ON Status.InventoryID=Inventory.IndexID 
WHERE (Inventory.IndexID=<InventoryID>);

List messages posted by a certain player

Change "<PlayerID>" to the id of the player

Example.png MySQL:

SELECT 
CASE 
  WHEN Players.Name IS NULL THEN 
    Players.Identity 
  WHEN Players.Name = '' THEN 
    Players.Identity 
  ELSE 
    Players.Name 
END AS PCName, 
Inventory.Name, 
MessageTypes.Text, 
MessageTypes.Level, 
Messages.ExtraText, 
Messages.TimeStamp, 
TIME_FORMAT(TIMEDIFF(CURRENT_TIMESTAMP, Messages.TimeStamp), '%H:%i:%s') AS Elapsed 
FROM Messages 
LEFT JOIN (MessageTypes) ON (Messages.MessageTypeID = MessageTypes.IndexID) 
LEFT JOIN (Inventory) ON (Inventory.IndexID = Messages.InventoryID) 
LEFT JOIN (Players) ON (Players.IndexID = Inventory.PlayerID)
WHERE (Inventory.PlayerID=<PlayerID>);

Example.png MSSQL:

SELECT 
CASE 
  WHEN Inventory.ControlSiteID IS NULL THEN
  (
    CASE 
      WHEN Players.Name IS NULL THEN 
        Players."Identity"      
      WHEN Players.Name = '' THEN 
        Players."Identity"      
      ELSE 
        Players.Name   
    END
  ) 
  ELSE
  (
    CASE 
      WHEN ControlSites.Name IS NULL THEN 
        ControlSites."Identity"      
      WHEN ControlSites.Name = '' THEN 
        ControlSites."Identity"      
      ELSE ControlSites.Name   
    END
  ) 
END AS PCName, 
Inventory.Name, 
MessageTypes.Text, 
MessageTypes.Level, 
Messages.ExtraText, 
Messages.TimeStamp, 
CONVERT(varchar(6), 
  DATEDIFF(second, Messages.TimeStamp, CURRENT_TIMESTAMP)/3600) + ':' +
  RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, Messages.TimeStamp, CURRENT_TIMESTAMP)%3600) / 60), 2) + ':' + 
  RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, Messages.TimeStamp, CURRENT_TIMESTAMP)%60)), 2) AS Elapsed 
FROM Messages 
LEFT JOIN MessageTypes ON Messages.MessageTypeID = MessageTypes.IndexID 
LEFT JOIN Inventory ON Inventory.IndexID = Messages.InventoryID 
LEFT JOIN ControlSites ON Inventory.ControlSiteID = ControlSites.IndexID 
LEFT JOIN Players ON Inventory.PlayerID = Players.IndexID 
WHERE (Inventory.PlayerID=<PlayerID>);

List events posted by a certain inventory item (Software)

Change "<InventoryID>" to the id of the inventory

Example.png MySQL:

SELECT 
Events.TimeStamp, 
Events.Description,
Events.ObjectName 
FROM Events 
LEFT JOIN (Inventory) ON (InventoryID = Inventory.IndexID) 
LEFT JOIN (ControlSites) ON (Inventory.ControlSiteID = ControlSites.IndexID) 
LEFT JOIN (Players) ON (Inventory.PlayerID = Players.IndexID) 
LEFT JOIN (Sites AS CSites) ON (ControlSites.SiteID = CSites.IndexID) 
LEFT JOIN (Sites AS PSites) ON (Players.SiteID = PSites.IndexID) 
LEFT JOIN (Projects AS CProj) ON (CSites.ProjectID = CProj.IndexID) 
LEFT JOIN (Projects AS PProj) ON (PSites.ProjectID = PProj.IndexID) 
WHERE (Inventory.IndexID=<InventoryID>);

Example.png MSSQL:

SELECT 
Events.TimeStamp, 
Events.Description,
Events.ObjectName 
FROM Events 
LEFT JOIN Inventory ON InventoryID = Inventory.IndexID 
LEFT JOIN ControlSites ON Inventory.ControlSiteID = ControlSites.IndexID 
LEFT JOIN Players ON Inventory.PlayerID = Players.IndexID 
LEFT JOIN Sites AS CSites ON ControlSites.SiteID = CSites.IndexID 
LEFT JOIN Sites AS PSites ON Players.SiteID = PSites.IndexID 
LEFT JOIN Projects AS CProj ON CSites.ProjectID = CProj.IndexID 
LEFT JOIN Projects AS PProj ON PSites.ProjectID = PProj.IndexID 
WHERE (Inventory.IndexID=<InventoryID>);

Get Screen shot for a specific inventory item (Software)

Change "<InventoryID>" to the id of the inventory

Example.png MySQL:

SELECT 
Status.Screenshot 
FROM Inventory 
LEFT JOIN (Status) ON (Status.InventoryID=Inventory.IndexID) 
WHERE (Inventory.IndexID=<InventoryID>);

Example.png MSSQL:

SELECT 
Status.Screenshot 
FROM Inventory 
LEFT JOIN Status ON Status.InventoryID=Inventory.IndexID 
WHERE (Inventory.IndexID=<InventoryID>);

List all players with errors

Change "<MaxLevel>" to the maximum error level

Example.png MySQL:

SELECT 
Inventory.Name, 
Status.LastContact, 
Status.TTL, 
CASE 
  WHEN Status.TTL IS NULL THEN 
    NULL
  ELSE 
    TIME_TO_SEC(TIMEDIFF(Status.LastContact, SUBTIME(CURRENT_TIMESTAMP, SEC_TO_TIME(Status.TTL)))) 
END AS SecondsToLive 
FROM Inventory 
LEFT JOIN (InventoryLibrary) ON (Inventory.LibraryID=InventoryLibrary.IndexID) 
LEFT JOIN (ControlSites) ON (Inventory.ControlSiteID=ControlSites.IndexID) 
LEFT JOIN (Players) ON (Inventory.PlayerID=Players.IndexID) 
LEFT JOIN (Sites) ON (ControlSites.SiteID=Sites.IndexID) 
LEFT JOIN (Projects) ON (Sites.ProjectID=Projects.IndexID) 
LEFT JOIN (Sites AS Sites2) ON (Players.SiteID=Sites2.IndexID) 
LEFT JOIN (Projects AS Projects2) ON (Sites2.ProjectID=Projects2.IndexID) 
LEFT JOIN (Status) ON (Status.InventoryID=Inventory.IndexID)
LEFT JOIN (Messages) ON (Messages.InventoryID=Inventory.IndexID)
LEFT JOIN (MessageTypes) ON (MessageTypes.IndexID=Messages.MessageTypeID)
WHERE (MessageTypes.Level) > <MaxLevel> 
GROUP BY (Players.IndexID)
HAVING (SecondsToLive < 0);

Retrieved from "http://kb.displayevolution.com/index.php/SQL_Queries_for_Dise_Complete"



All items on this website are copyright Klocktornet AB 2012, all rights reserved.
All trademarks are property of their respective owners.