March 20, 2018 at 10:21 am
Guys,
have select statement
Select salesid, salesname, salesamount from salestable
ssalesid salesname salesamount
1 cust1 101.50
want to be able to split it tow 4 records
ssalesid salesname salesamount rank1
1 cust1 25.37 1
1 cust1 25.37 2
1 cust1 25.37 3
1 cust1 25.39 4
round the last one. I can do it by creating temp tables but was thinking if there was any more cleaner way of accomplishing this task?
March 20, 2018 at 11:02 am
Angela4eva - Tuesday, March 20, 2018 10:21 AMGuys,
have select statement
Select salesid, salesname, salesamount from salestablessalesid salesname salesamount
1 cust1 101.50want to be able to split it tow 4 records
ssalesid salesname salesamount rank1
1 cust1 25.37 1
1 cust1 25.37 2
1 cust1 25.37 3
1 cust1 25.39 4
round the last one. I can do it by creating temp tables but was thinking if there was any more cleaner way of accomplishing this task?
Not very pretty, but here is one way:DROP TABLE IF EXISTS #cust;
CREATE TABLE #cust
(
SalesId INT
, SalesName VARCHAR(10)
, SalesAmount DECIMAL(18, 6)
);
INSERT #cust
(
SalesId
, SalesName
, SalesAmount
)
VALUES
(
1, 'cust1', 101.50
);
SELECT
c.SalesId
, c.SalesName
, div2.Divided
, div2.rnk
FROM
#cust c
CROSS APPLY
(
SELECT Divided = ROUND(salesamount / 4, 2)
) div
CROSS APPLY
(
SELECT
div.Divided
, rnk = 1
UNION ALL
SELECT
div.Divided
, rnk = 2
UNION ALL
SELECT
div.Divided
, rnk = 3
UNION ALL
SELECT
SalesAmount - (div.Divided * 3)
, rnk = 4
) div2
ORDER BY
c.SalesId
, div2.rnk;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 20, 2018 at 11:04 am
Prob not "the best way", but I think it works:
select ssalesid, salesname, salesamount_split, rank
from (
select 1 as ssalesid, 'cust1' as salesname, cast(101.50 as decimal(9, 2)) as salesamount
) as test_data
cross apply (
select 1 as rank, round(salesamount / 4, 2, -1) as salesamount_split
union all
select 2, round(salesamount / 4, 2, -1)
union all
select 3, round(salesamount / 4, 2, -1)
union all
select 4, round(salesamount / 4, 2, -1) + (salesamount - (round(salesamount / 4, 2, -1) * 4))
) as ca1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 21, 2018 at 8:58 am
this is awesome thank you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply