sheltermanager

Open full view…

How can I round weights in a report? Right now I am getting 33.1234

tmc210
Mon, 22 Jul 2019 13:10:08 GMT

robinrt
Mon, 22 Jul 2019 16:37:06 GMT

Use the ROUND() function in your query. So instead of SELECTing Weight, use ROUND(Weight, 2) AS Weight to get 2 decimal places.

tmc210
Wed, 24 Jul 2019 18:42:50 GMT

I can't get that to work: select a.ID as AnimalID, a.ShelterCode, a.AnimalName, (select Case when b.value = '1' then 'Application Pending' when c.value = '1' then 'Adoption Pending' when a.IsNotAvailableForAdoption = '1' then 'Not Available' when d.value = '7' then 'Yes' else 'Available' end) as Status, (select Case when d.value = '1' then 'Yes' else 'No' end) as GuardianAngel, a.BreedName, sz.Size as SizeName, sx.Sex as SexName, a.weight ROUND(Weight, 2) AS Weight, a.AnimalAge, a.TimeOnShelter, (SELECT Name FROM lksyesno WHERE ID = a.Neutered) AS NeuteredName, (SELECT Name FROM lksynun WHERE ID = a.IsGoodWithCats) AS IsGoodWithCatsName, (SELECT Name FROM lksynun WHERE ID = a.IsGoodWithDogs) AS IsGoodWithDogsName, (SELECT Name FROM lksynun WHERE ID = a.IsGoodWithChildren) AS IsGoodWithChildrenName, (SELECT Name FROM lksynun WHERE ID = a.IsHouseTrained) AS IsHouseTrainedName, a.DisplayLocation, a.AnimalComments, sp.SpeciesName, additionalflags, (SELECT af.Value FROM additional af INNER JOIN additionalfield aff ON aff.ID = af.AdditionalFieldID WHERE aff.FieldName = 'DogBreed' AND af.LinkID = a.ID) AS DogBreed, (SELECT af.Value FROM additional af INNER JOIN additionalfield aff ON aff.ID = af.AdditionalFieldID WHERE aff.FieldName = 'DogHighEnergy' AND af.LinkID = a.ID) AS DogHighEnergy, (SELECT af.Value FROM additional af INNER JOIN additionalfield aff ON aff.ID = af.AdditionalFieldID WHERE aff.FieldName = 'DogIsPuppy' AND af.LinkID = a.ID) AS DogIsPuppy, (SELECT af.Value FROM additional af INNER JOIN additionalfield aff ON aff.ID = af.AdditionalFieldID WHERE aff.FieldName = 'IfGoodWithKidsType' AND af.LinkID = a.ID) AS IfGoodWithKidsType FROM animal a left join lksize sz on a.Size = sz.ID left join lksex sx on a.Sex = sx.ID left join species sp on a.SpeciesID = sp.ID left join additional b on a.id = b.linkid left join additional c on a.id = c.linkid left join additional d on a.id = d.linkid WHERE b.additionalfieldid = 3 and c.additionalfieldid = 4 and d.additionalfieldid = 1 and a.speciesid = 1 and Archived = 0 and HasTrialAdoption = 0 order by Status, AnimalName

robinrt
Thu, 25 Jul 2019 07:26:56 GMT

--- a.weight ROUND(Weight, 2) AS Weight, --- is wrong, it's an expression and it should be just --- ROUND(Weight, 2) AS Weight, ---

KarenW
Mon, 29 Jul 2019 19:24:03 GMT

I had to use: ROUND( CAST(a.weight as numeric), 2) as weight,