PG&E Rebate report for K1000
Our company qualifies for a $15 per computer rebate from our electricity provider, PG&E, for using K1000 power management. In order to apply for the rebate we must submit a report which lists each PG&E account number and the number of computers that are in each account. We have created labels for each site with the naming convention of PGE_<site_name>_<PGE-account-number> (example: PGE_PA_56676543). There are around 20 labels.
We need to create a report that will list all of the labels that start with PGE and then list the total number of computers that are in those labels to the right of each label name. Any suggestions on how to create a SQL report to do this task would be greatly appreciated.
Answers (4)
Thank you for your response nshah. I’m afraid that a listing of all of the computers that qualify would be too much as it is over 5000 machines. At this point PG&E is requiring simply a total computer count for each of their accounts with us.
TRy this..
SELECT GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,MACHINE.NAME AS SYSTEM_NAME FROM MACHINE LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden') WHERE (LABEL.NAME like 'PGE%') GROUP BY MACHINE.ID ORDER BY LABEL_NAME_GROUPED,SYSTEM_NAME
Worked with Mark G. and Philip C. from KACE support. Philip nailed the SQL query. Awesome job you guys. Thanks!!
The SQL script is below:
Select
DISTINCT LABEL.NAME AS 'Label Name', COUNT(*) AS 'Machine Count'
From
LABEL Inner Join
MACHINE_LABEL_JT On MACHINE_LABEL_JT.LABEL_ID = LABEL.ID Inner Join
MACHINE On MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
Where
LABEL.NAME like 'PGE%'
Group By
LABEL.NAME
You could do this with the Reporting Wizard if you just need a count. The only thing is the wizard will list all the machines under the label as well as give you the count.
SELECT GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,MACHINE.NAME AS SYSTEM_NAME FROM MACHINE LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID AND LABEL.TYPE <> 'hidden') WHERE (LABEL.NAME like 'PGE%') GROUP BY MACHINE.ID ORDER BY LABEL_NAME_GROUPED
Comments:
-
This is just bringing back the name of the machine and the label name. - nshah 11 years ago