SQL Query

An SQL Query may extract almost anything from the database. 

 This process ignores the security restrictions in the system, so you will have to implement them yourself (see special keywords below). 

 This widget supports different rendering modes, for a deep dive on the diagrams,  click here . 

 Sample: Limited status distribution 

 The following example displays quite well in a pie chart. 

 SELECT s.Status, count(*)

 FROM data_leads1 as d

 JOIN formstatus as s ON s.StatusID = d.StatusID 

 WHERE d.StatusID IN (11344,11343,11345)

 GROUP BY 1 

 Special keywords / variables 

 

 %UserID% 

 %ExclusiveGroupID% 

 %GroupListSql% 

 %ExclusiveGroupListSql% 

 %AndRecordFilter% 

 

 SQL query examples 

 

 

 

 

 

 Grouping 

 The following queies are suitable for use with Dashboard SQL tables. With minor modification they can also be used with the Field type "SQL: Table query" 

 Status related data 

 SELECT s.Status, COUNT(k.DataID)

FROM data_kuglespil as k

JOIN formstatus as s ON k.StatusID = s.StatusID

GROUP BY 1 

 Lookup related data 

 SELECT l.`Value`, COUNT(k.DataID)

FROM data_kuglespil as k

JOIN formfieldlookup as l ON k.STRRELSE = l.LookupID

GROUP BY 1

ORDER BY l.SortOrder 

 Grouped variables 

 SELECT 'Under 5' as Text, COUNT(k.DataID)

FROM data_kuglespil as k

WHERE k.RESULTAT <= 5

UNION

SELECT 'Middel (6-16)' as Text, COUNT(k.DataID)

FROM data_kuglespil as k 

WHERE k.RESULTAT <= 5 AND k.RESULTAT < 17

UNION

SELECT 'Top performer (17)' as Text, COUNT(k.DataID)

FROM data_kuglespil as k

WHERE k.RESULTAT = 17 

 Population percentages 

 SELECT

 AVG(IF(StatusID=35,0,100)) as `% completed`

FROM

 data_tpdocument 

 Corner cases and weird stuff 

 List of birthdays 

 SELECT NAVN as Navn, FDSELSDAG as Fødselsdag, ROUND(DATEDIFF(NOW(),FDSELSDAG) / 365 ) as Alder

FROM data_medarbejder

WHERE DATEDIFF( MAKEDATE(YEAR(NOW()),DAYOFYEAR(FDSELSDAG)), NOW() ) BETWEEN 0 AND 7