
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-03-2016 11:41 AM
Hello,
I want to make a chart where I can see counts of status (Stock, Loan, ..) have in every country.
Example of result:
STOCK
5 Belgium
3 Germany
.
.
.
My query that I tried to make but I get counts of total from EMEA and not country (ip locations)
Select Top 1000000 tblState.Statename,
Count(tblAssets.AssetID) As Count,
Case tsysIPLocations.IPLocation When 'PAPanama' Then 'PAP'
When 'BEMechelen, overall subet' Then 'Belgium'
When 'BEMechelen, clients' Then 'Belgium' When 'BEMechelen' Then 'Belgium'
End As Country,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State,
tsysIPLocations
Group By tblState.Statename,
tsysIPLocations.IPLocation,
tblAssets.Domain,
tblAssetCustom.Manufacturer
Having tblState.Statename Like 'stock' And tblAssets.Domain = 'cochlear' And
tblAssetCustom.Manufacturer = 'lenovo'
Order By tblState.Statename
I want to make a chart where I can see counts of status (Stock, Loan, ..) have in every country.
Example of result:
STOCK
5 Belgium
3 Germany
.
.
.
My query that I tried to make but I get counts of total from EMEA and not country (ip locations)
Select Top 1000000 tblState.Statename,
Count(tblAssets.AssetID) As Count,
Case tsysIPLocations.IPLocation When 'PAPanama' Then 'PAP'
When 'BEMechelen, overall subet' Then 'Belgium'
When 'BEMechelen, clients' Then 'Belgium' When 'BEMechelen' Then 'Belgium'
End As Country,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State,
tsysIPLocations
Group By tblState.Statename,
tsysIPLocations.IPLocation,
tblAssets.Domain,
tblAssetCustom.Manufacturer
Having tblState.Statename Like 'stock' And tblAssets.Domain = 'cochlear' And
tblAssetCustom.Manufacturer = 'lenovo'
Order By tblState.Statename
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-16-2016 08:54 PM
TsysIPLocations isn't linked to any other tables. It should be linked to tblAssets as explained here.
1 REPLY 1
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-16-2016 08:54 PM
TsysIPLocations isn't linked to any other tables. It should be linked to tblAssets as explained here.
