report with 2 output

[sryfro]

Hi, excuse me for my english , i would like in my report this result ( for exemple) in Output =


Install date Total
2014.11.17 12
2014.11.18 21
2014.11.19 21
2014.11.20 16
2014.11.21 15
2014.11.22 2
2014.11.24 21
2014.11.25 14
2014.11.26 10

I would like the number of PCs that have installed the KB Microsoft , installed by WSUS to check.
And for the Install date, i would like have 1 month " dynamically", not fixed



Today, my report but just for check PC who have KB

Select Top 1000000 tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblQuickFixEngineeringUni.HotFixID,
tblQuickFixEngineeringUni.Description,
tblOperatingsystem.*,
tblOperatingsystem.AssetID As AssetID2,
tblOperatingsystem.AssetID As AssetID1,
tblOperatingsystem.InstallDate As InstallDate1
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblQuickFixEngineering On tblAssets.AssetID =
tblQuickFixEngineering.AssetID
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblQuickFixEngineeringUni.HotFixID = 'KBxxxxxxx' And
tblAssetCustom.State = 1



Can someone help me please ?

TY

[Daniel_B · Accepted solution]

The report below will give you the required result. You need to adapt the filter, depending on which hotfix installation you are interested in. Note: We read the install date from the WMI as Windows reports them to us. Sometimes there is no value or a value not in a date format given. Therefore some conversion is necessary in this report.

Select Top 1000000 Case
    When CharIndex('/', tblQuickFixEngineering.InstalledOn) > 0 Then
    Convert(datetime,tblQuickFixEngineering.InstalledOn,101)
    Else Convert(Datetime,'1/1/1900',101) End As [Install date],
  Count(tblAssets.AssetID) As Number
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblQuickFixEngineering On tblAssets.AssetID =
    tblQuickFixEngineering.AssetID
  Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
    tblQuickFixEngineering.QFEID
Where tblQuickFixEngineeringUni.HotFixID = 'KB3003057' And
  tblAssetCustom.State = 1 And GetDate() - 30 < Case
    When CharIndex('/', tblQuickFixEngineering.InstalledOn) > 0 Then
    Convert(datetime,tblQuickFixEngineering.InstalledOn,101)
    Else Convert(Datetime,'1/1/1900',101) End
Group By tblQuickFixEngineering.InstalledOn
Order By tblQuickFixEngineering.InstalledOn