Need Logic

Question

Need Logic

torrid.inundate

Old Hand

Points: 357
More actions
April 11, 2017 at 10:32 pm

#401934

Hi Team,
I have a monthly billing report which works absolutely fine. Now i need to add some more features to my report.
My Report
SELECT vm.VirtualMachineName,
      vbs.VblockSerialNumber,
      cf.ClusterFolderName,
      vbm.VblockModelName,
      CASE TT.PoweredOnOff
            WHEN 1 THEN 'Powered On'
       WHEN 0 THEN 'Powered Off'
         ELSE '-'
       END PoweredOnOff,
      vmc.Billable,
      mt.ExclusionName,
      vbs.VblockSystemName,
      vbs.EnvironmentType,
      dc.DataCenterName,
      CAST(Round(sum(vmc.UsageCPUAvg)/31,4) AS DECIMAL(10,4)) as UsageCPUAvg,
CAST(Round(sum(vmc.UsageMemoryAvg)/31,4) AS DECIMAL(10,4)) as UsageMemoryAvg,
      CAST(Round(max(vmc.UsageCPUPeak),4) AS DECIMAL(10,4)) AS UsageCPUPeak,
      CAST(Round(max(vmc.UsageMemoryPeak),4) AS DECIMAL(10,4)) AS UsageMemoryPeak,
CAST(Round(sum(vmc.ConfiguredMemory)/31,4 ) AS DECIMAL(10,4)) AS ConfiguredMemory,
      COUNT(DISTINCT vmc.ClientBillingPeriod) "VM Days"

     FROM DataCenters dc INNER JOIN VblockSystems vbs
      ON vbs.DataCenterID = dc.DataCenterID
     INNER JOIN VblockModels vbm
      ON vbm.VblockModelId = vbs.VblockModelId
     INNER JOIN ClusterFolders cf
      ON vbs.VblockSystemId = cf.VblockSystemId
      AND cf.ClientBillingPeriod between '01-DEC-2016' AND '31-DEC-2016'
     INNER JOIN ClusterVirtualMachines cvm
      ON cf.ClusterFolderId = cvm.ClusterFolderId
     INNER JOIN VirtualMachines vm
      ON vm.VirtualMachineId = cvm.VirtualMachineId
     INNER JOIN VirtualMachineCapacities vmc
      ON cvm.ClusterVirtualMachineId = vmc.ClusterVirtualMachineId
     INNER JOIN MetricsExclusionTypes mt
      ON vmc.MetricsExclusionTypeID = mt.MetricsExclusionTypeID
     INNER JOIN (SELECT a.VirtualMachineName, a.ClusterFolderName, a.PoweredOnOff
                FROM
                   (
                   SELECT A.VirtualMachineName VirtualMachineName, V.ClusterFolderName, T.PoweredOnOff,
                       row_number() over(PARTITION BY A.VirtualMachineName, V.ClusterFolderName order by T.ClientBillingPeriod DESC) rn
                   FROM VIRTUALMACHINES A INNER JOIN VIRTUALMACHINECAPACITIES T
                        ON A.VirtualMachineId = T.VirtualMachineId
                       INNER JOIN CLUSTERVIRTUALMACHINES U
                       ON T.ClusterVirtualMachineId = U.ClusterVirtualMachineId
                       INNER JOIN CLUSTERFOLDERS V
                       ON V.ClusterFolderId = U.ClusterFolderId
                       WHERE T.ClientBillingPeriod BETWEEN '01-DEC-2016' AND '31-DEC-2016'
                   ) a
                WHERE a.rn = 1) TT
            ON TT.VirtualMachineName = vm.VirtualMachineName
            AND TT.ClusterFolderName = cf.ClusterFolderName
      GROUP BY vm.VirtualMachineName,
         vbs.VblockSerialNumber,
         cf.ClusterFolderName,
         vbm.VblockModelName,
         vmc.Billable,
         mt.ExclusionName,
         vbs.VblockSystemName,
         dc.DataCenterName,
         vbs.EnvironmentType,
         CASE TT.PoweredonOff
             WHEN 1 THEN 'Powered On'
             WHEN 0 THEN 'Powered Off'
             ELSE '-'
             END;
Current report calculates UsageCPU on monthly Average. Now i need to show daily reading along with Monthly Avg of that metric.If no data available for UsageCPU in a particular Day then 0 need to mention on my report for that day.
Current report-    CAST(Round(sum(vmc.UsageCPUAvg)/31,4) AS DECIMAL(10,4)) as UsageCPUAvg,CAST(Round(sum(vmc.UsageMemoryAvg)/31,4) AS DECIMAL(10,4)) as UsageMemoryAvg

Expected report-    CAST(Round(sum(vmc.UsageCPUAvg)/31,4) AS DECIMAL(10,4)) as UsageCPUAvg,Day1 value, Day2 value...., Day31 value ,CAST(Round(sum(vmc.UsageMemoryAvg)/31,4) AS DECIMAL(10,4)) as UsageMemoryAvg

Thanks
Torrid.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

There's no obvious date field that would represent the date for which the retrieved values occurred on. Without that being detailed, and a good idea of exactly how all the listed tables are supposed to be related, it would be difficult to even contemplate what we can do to help. You may be running this report monthly, but as you aggregate the various values, we'd need to know at what granularity level the actual individual rows of data exist, from a date point of view. You'll need a whole new approach if you're looking to have up to 31 values displayed (all the days of the month).

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

In the long run, it would probably be cheaper and better to just buy a monitoring product if you're going to do that level of measuring and reporting. I do think it's overkill but it is nice to show progress and to justify an active "Continuous Improvement Program" where you're frequently on the lookout for the worst performing code and can actually have folks fix it. Might also be used to justify a "Continuous Improvement Program" as well. If no one intends to do anything about CPU usage, then such reporting is just an extra bit of load on the CPUs that will go to waste.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

torrid.inundate Old Hand Points: 357 More actions · Answer 3

Thanks, Steve/Jeff

Please find the attached excel. we used to get data in this format.This column to row conversion of the specific columns is required in my report. I need to apply this logic to my old code. column names in the excel are sample name.

Thanks
Torrid

sgmunson SSC Guru Points: 110640 More actions · Answer 4

torrid.inundate - Monday, May 1, 2017 11:54 AM
Thanks, Steve/Jeff
Please find the attached excel. we used to get data in this format.This column to row conversion of the specific columns is required in my report. I need to apply this logic to my old code. column names in the excel are sample name.
Thanks
Torrid

You'll have to provide a different way to look at your data. Most folks on this site work during the day and are loathe to download spreadsheets from what amounts to an untrusted source, and many are in a position of actually being UNABLE to do so. I choose not to trust a spreadsheet from a forum, and from a security perspective, it's a good choice to make.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

That being said, please see the first link under "Helpful Links" in my signature line below for how to post readily consumable data to help us help you more quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)