/build/static/layout/Breadcrumb_cap_w.png

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.

 


0 Comments   [ + ] Show comments

Answers (4)

Answer Summary:
Posted by: jvincent 12 years ago
Orange Belt
1

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.

Posted by: hmoore 12 years ago
Second Degree Blue Belt
1

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

Posted by: jvincent 12 years ago
Orange Belt
1

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

Posted by: nshah 12 years ago
Red Belt
0

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 12 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ