Complex computation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complex computation

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-03 : 00:01:33

Hi, I need to calculate an amount based on few parameters as below:

DECLARE @ADULTS INT = 2
DECLARE @PERSONS INT = 3
DECLARE @CHILD INT = @PERSONS - @ADULTS
DECLARE @P1 INT, @P2 INT, @P3 INT, @P4 INT, @C1 INT, @C2 INT, @C3 INT, @C4 INT, @PX INT, @CX INT
SELECT @P1=100, @P2 = 200, @P3= NULL, @P4= NULL, @PX = 50, @C1=10, @C2=20, @C3=30, @C4=40, @CX=25

In the above P1-P4 are person rates (1 persons to 4 persons) and C1-C4 are child rates(1 child to 4 children), PX is additonal person rate and CX is additonal child rate. If @adults/@persons are provided as parameters then the final output needs to be a single column, say TotalRate. The logic will work as follows:

1. Assumption: P1 rate will always be available, all other rates can be 0.

2. All individuals are treated as persons as long as they exhaust rates up until P4. Ex: 1 adult/2 children should pick P3 rate (3 persons), 1 adult/1child should pick P2 rate(2 person) and so on...

3. Extending point 1 above further, in case a Pn rate is not available, check P(n-1) rate. Ex. If 2 adult/2 children are searched, then P4 should be picked, if P4 is 0 check P3 for availability. If P3 is 0 check P2, if P2 is not available get P1 rate. This applies only to Pn and not to Cn's.

4. Further, this is the way an child rates/additional adult/child rates are picked:

Ex: 2 adult/2child are searched but P4 is 0 and P3 is available.
Total persons=2+2=4, P4 not available but P3 is available and since P3=3 person rate, we are left with 1 child and this should pick C1 rate.

Another example: 4 adult/5 child searched - should pick P4 rate as total=3+5=8 and we have only till P4(4 person rate).Suppose P4 is 0, pick P3 (Assuming P3>0). Now we are left with 1 adult and 5 children (as 3 adults got absorbed by P3 rate).This 1 adult is now an additional adult i.e. PX rate should be picked and for children, C4 rate will be picked (absorbs 4 children) and one child will be left which is now an additional child, i.e. CX rate should be picked.
Thus rate will be calculated as: P3 + PX + C4 + CX

Further examples:
3 adult/3 child: P4 available, C1 available, C2 not available
Rate= P4 + C1 + CX
1 adult/2 child: P3 not available, P2 not available, P1 available,C1-C4 not available, CX available
Rate=P1 + 2*CX

Please let me know if you have any questions or if I have not explained clearly about the requirement.

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 04:11:57

See this illustration


-- 1 ADULT 9 CHILDREN
-- As per your rule 2 persons covered by P2 (best available), 
--then out of rest 8 children, 4 absorbed by C4 and next 4 * CX applied 
-- so 200 + 40 +(25 * 4) = 340
DECLARE @ADULTS INT = 1
DECLARE @PERSONS INT = 10
DECLARE @CHILD INT = @PERSONS - @ADULTS
DECLARE @P1 INT, @P2 INT, @P3 INT, @P4 INT, @C1 INT, @C2 INT, @C3 INT, @C4 INT, @PX INT, @CX INT
SELECT @P1=100, @P2 = 200, @P3= NULL, @P4= NULL, @PX = 50, @C1=10, @C2=20, @C3=30, @C4=40, @CX=25


SELECT M.LVL AS ADULTSCOVERED,
P as ADULTRATE,
CASE WHEN @ADULTS > M.LVL THEN (@ADULTS - M.LVL) ELSE 0 END AS EXTRAADULTCNT,
N.LVL AS CHILDCOVERED,
C AS CHILDRATE,
CASE WHEN (@PERSONS-M.LVL-CASE WHEN @ADULTS > M.LVL THEN(@ADULTS - M.LVL)ELSE 0 END-N.LVL)>0 THEN (@PERSONS-M.LVL-CASE WHEN @ADULTS > M.LVL THEN(@ADULTS - M.LVL)ELSE 0 END-N.LVL) ELSE 0 END AS EXTRACHILDCOUNT,
P + @PX * CASE WHEN @ADULTS > M.LVL THEN (@ADULTS - M.LVL) ELSE 0 END + C + @CX * CASE WHEN (@PERSONS-M.LVL-CASE WHEN @ADULTS > M.LVL THEN(@ADULTS - M.LVL)ELSE 0 END-N.LVL)>0 THEN (@PERSONS-M.LVL-CASE WHEN @ADULTS > M.LVL THEN(@ADULTS - M.LVL)ELSE 0 END-N.LVL) ELSE 0 END AS TOTAL
FROM (SELECT TOP 1 P,LVL
      FROM (VALUES(@P1,1),(@P2,2),(@P3,3),(@P4,4),(@PX,-1))T(P,LVL)
      WHERE LVL <= @PERSONS
      AND P IS NOT NULL
      ORDER BY LVL DESC)M
OUTER APPLY (SELECT TOP 1 C,LVL
             FROM (VALUES(@C1,1),(@C2,2),(@C3,3),(@C4,4))T(C,LVL)
             WHERE LVL <=  @PERSONS - CASE WHEN M.LVL > @ADULTS THEN  M.LVL ELSE @ADULTS END
             AND C IS NOT NULL
             ORDER BY LVL DESC
             )N


output
---------------------------------------------------------------------------------------------------------
ADULTSCOVERED	ADULTRATE	EXTRAADULTCNT	CHILDCOVERED	CHILDRATE	EXTRACHILDCOUNT	  TOTAL
---------------------------------------------------------------------------------------------------------
2	        200	        0	        4	        40	        4	          340

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 04:17:40

Now case of more adults


-- 8 ADULT 2 CHILDREN
-- As per your rule 2 persons covered by P2 (best available), 
--then out of rest 8 children, 6 are Adults so PX * 6, rest 2 children absored by C2
-- so 200 + (50 * 6) + 20 = 520
DECLARE @ADULTS INT = 8
DECLARE @PERSONS INT = 10
DECLARE @CHILD INT = @PERSONS - @ADULTS
DECLARE @P1 INT, @P2 INT, @P3 INT, @P4 INT, @C1 INT, @C2 INT, @C3 INT, @C4 INT, @PX INT, @CX INT
SELECT @P1=100, @P2 = 200, @P3= NULL, @P4= NULL, @PX = 50, @C1=10, @C2=20, @C3=30, @C4=40, @CX=25


SELECT M.LVL AS ADULTSCOVERED,
P as ADULTRATE,
CASE WHEN @ADULTS > M.LVL THEN (@ADULTS - M.LVL) ELSE 0 END AS EXTRAADULTCNT,
N.LVL AS CHILDCOVERED,
C AS CHILDRATE,
CASE WHEN (@PERSONS-M.LVL-CASE WHEN @ADULTS > M.LVL THEN(@ADULTS - M.LVL)ELSE 0 END-N.LVL)>0 THEN (@PERSONS-M.LVL-CASE WHEN @ADULTS > M.LVL THEN(@ADULTS - M.LVL)ELSE 0 END-N.LVL) ELSE 0 END AS EXTRACHILDCOUNT,
P + @PX * CASE WHEN @ADULTS > M.LVL THEN (@ADULTS - M.LVL) ELSE 0 END + C + @CX * CASE WHEN (@PERSONS-M.LVL-CASE WHEN @ADULTS > M.LVL THEN(@ADULTS - M.LVL)ELSE 0 END-N.LVL)>0 THEN (@PERSONS-M.LVL-CASE WHEN @ADULTS > M.LVL THEN(@ADULTS - M.LVL)ELSE 0 END-N.LVL) ELSE 0 END AS TOTAL
FROM (SELECT TOP 1 P,LVL
      FROM (VALUES(@P1,1),(@P2,2),(@P3,3),(@P4,4),(@PX,-1))T(P,LVL)
      WHERE LVL <= @PERSONS
      AND P IS NOT NULL
      ORDER BY LVL DESC)M
OUTER APPLY (SELECT TOP 1 C,LVL
             FROM (VALUES(@C1,1),(@C2,2),(@C3,3),(@C4,4))T(C,LVL)
             WHERE LVL <=  @PERSONS - CASE WHEN M.LVL > @ADULTS THEN  M.LVL ELSE @ADULTS END
             AND C IS NOT NULL
             ORDER BY LVL DESC
             )N

output
-----------------------------------------------------------------------------------------------------------------
ADULTSCOVERED	ADULTRATE	EXTRAADULTCNT	CHILDCOVERED	CHILDRATE	EXTRACHILDCOUNT	       TOTAL
-----------------------------------------------------------------------------------------------------------------
2	        200	        6	        2	        20	        0	               520

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-03 : 04:59:46

Thanks a lot visakh.
I am checking your query for all possible combinations, will update in case we missed anything.

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-03 : 05:31:11

Hi Visakh, there is one case which is not handled yet. If all C1-C4 are not available then we need to pick CX (additional child rate).
Consider this:

DECLARE @ADULTS INT = 4
DECLARE @PERSONS INT = 5
DECLARE @CHILD INT = @PERSONS - @ADULTS
DECLARE @P1 INT, @P2 INT, @P3 INT, @P4 INT, @C1 INT, @C2 INT, @C3 INT, @C4 INT, @PX INT, @CX INT
SELECT @P1=100, @P2 = 200, @P3= 300, @P4= 400, @PX = 50, @C1=NULL, @C2=NULL, @C3=NULL, @C4=NULL, @CX=25

This should return 400 + 25 = 425 (P4 + one additional child rate). Can you please modify your logic to handle this, at present it returns 0 for ExtraChildCount.

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 06:23:50

Amended. See if it fits all your other conditions


DECLARE @ADULTS INT = 11
DECLARE @PERSONS INT = 12
DECLARE @CHILD INT = @PERSONS - @ADULTS
DECLARE @P1 INT, @P2 INT, @P3 INT, @P4 INT, @C1 INT, @C2 INT, @C3 INT, @C4 INT, @PX INT, @CX INT
SELECT @P1=NULL, @P2 = NULL, @P3= 300, @P4= 450, @PX = 50, @C1=NULL, @C2=NULL, @C3=NULL, @C4=NULL, @CX=25



SELECT M.LVL AS ADULT,M.P AS ADULTRATE,
@ADULTS-COALESCE(M.LVL,0) as EXTRAADULT,
N.LVL as CHILD,N.C AS CHILDRATE,
@CHILD - case when ( COALESCE(M.LVL,0) - @ADULTS)>0  THEN ( COALESCE(M.LVL,0) - @ADULTS) else 0 END - coalesce(N.LVL,0) as EXTRACHILD,
COALESCE(M.P,0) + CASE WHEN @ADULTS-COALESCE(M.LVL,0)> 0 THEN @ADULTS-COALESCE(M.LVL,0) ELSE 0 END* @PX + COALESCE(N.C,0) + @CX *CASE WHEN @CHILD - case when ( COALESCE(M.LVL,0) - @ADULTS)>0  THEN ( COALESCE(M.LVL,0) - @ADULTS) else 0 END - coalesce(N.LVL,0)>0 THEN @CHILD - case when ( COALESCE(M.LVL,0) - @ADULTS)>0  THEN ( COALESCE(M.LVL,0) - @ADULTS) else 0 END - coalesce(N.LVL,0) ELSE 0 END AS TOTAL
FROM (SELECT @PERSONS AS N)T
OUTER APPLY (SELECT TOP 1 P,LVL
      FROM (VALUES(@P1,1),(@P2,2),(@P3,3),(@P4,4))T(P,LVL)
      WHERE LVL <= @PERSONS
      AND P IS NOT NULL
      ORDER BY LVL DESC)M
OUTER APPLY (SELECT TOP 1 C,LVL
             FROM (VALUES(@C1,1),(@C2,2),(@C3,3),(@C4,4))T(C,LVL)
             WHERE LVL <=  @CHILD - case when ( COALESCE(M.LVL,0) - @ADULTS)>0  THEN ( COALESCE(M.LVL,0) - @ADULTS) else 0 END
             AND C IS NOT NULL
             ORDER BY LVL DESC
             )N


output
--------------------------------------------------------------------------------------------
ADULT	ADULTRATE	EXTRAADULT	CHILD	CHILDRATE	EXTRACHILD	TOTAL
--------------------------------------------------------------------------------------------
4	450	        7	         NULL	 NULL	              1	         825

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-03 : 07:23:57

Thanks a lot Visakh, working flawlessly.

Just made one minor change to the second column ExtraAdult, picked the same from your logic, though it did not matter as you handled it in the Total column. Thanks for outputting each calculative field value.
Have updated to:
CASE WHEN @ADULTS-COALESCE(M.LVL,0)> 0 THEN @ADULTS-COALESCE(M.LVL,0) ELSE 0 END as EXTRAADULT

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 09:02:18

Cool. Glad that I could sort it out.
Thanks for giving an interesting problem to work with.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-11 : 01:57:35

Hi Visakh, a question. If the source is a table instead of variables @p1-@p4, @c1-@c4,@pc,@cx; these are in a table. Can you please help me out in re-writing the From clause?

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 02:41:13

do you mean multiple rows or are they are all in same row?
Show me the table structure with how the data is.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-11 : 02:47:46

Multiple rows, there would be a join on this table and then amounts calculated.
The table structure is like this:

ID clientID Adult1 Adult2 Adult3 Adult4 AdditionalAdult Child1 Child2 Child3 Child4 AdditionalChild

So we want the values to be fetched from these tables, as against parameters we had earlier. Mapping these columns to parameters: Adultn=@Pn, AdditionalAdult = @PX, AdditionalChild = @CX

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 02:56:42

So you want this to be repeated for each client?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-11 : 03:00:49

Yes for each client/row. This table will be part of a join.

thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 03:11:37

which columns correspond to total adults/persons?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-11 : 03:42:46

@adults and @persons stay the same, these will be passed as parameters to a procedure which will output the amount column.
So we can assume we have these:

DECLARE @ADULTS INT = 2
DECLARE @PERSONS INT = 3
DECLARE @CHILD INT = @PERSONS - @ADULTS

We need to calculate the total adult amount (total person rates + additional adult rates) and total child amount (total child rates + additional child rates). I can always sum these to get the final total amount. Please let me know if you need any more info.

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 04:46:17

try like this


DECLARE @ADULTS INT = 11
DECLARE @PERSONS INT = 12
DECLARE @CHILD INT = @PERSONS - @ADULTS



SELECT M.LVL AS ADULT,M.P AS ADULTRATE,
@ADULTS-COALESCE(M.LVL,0) as EXTRAADULT,
N.LVL as CHILD,N.C AS CHILDRATE,
@CHILD - case when ( COALESCE(M.LVL,0) - @ADULTS)>0  THEN ( COALESCE(M.LVL,0) - @ADULTS) else 0 END - coalesce(N.LVL,0) as EXTRACHILD,
COALESCE(M.P,0) + CASE WHEN @ADULTS-COALESCE(M.LVL,0)> 0 THEN @ADULTS-COALESCE(M.LVL,0) ELSE 0 END* AdditionalAdult + COALESCE(N.C,0) + AdditionalChild *CASE WHEN @CHILD - case when ( COALESCE(M.LVL,0) - @ADULTS)>0  THEN ( COALESCE(M.LVL,0) - @ADULTS) else 0 END - coalesce(N.LVL,0)>0 THEN @CHILD - case when ( COALESCE(M.LVL,0) - @ADULTS)>0  THEN ( COALESCE(M.LVL,0) - @ADULTS) else 0 END - coalesce(N.LVL,0) ELSE 0 END AS TOTAL
FROM (SELECT @PERSONS AS N,AdditionalAdult,AdditionalChild FROM YourTable)T
OUTER APPLY (SELECT TOP 1 P,LVL
      FROM YourTable
      CROSS APPLY (VALUES(Adult1,1),(Adult2,2),(Adult3,3),(Adult4,4))T(P,LVL)
      WHERE LVL <= @PERSONS
      AND P IS NOT NULL
      AND ID = T.ID
      AND ClientID = T.ClientID
      ORDER BY LVL DESC)M
OUTER APPLY (SELECT TOP 1 C,LVL
             FROM YourTable
             CROSS APPLY (VALUES(Child1,1),(Child2,2),(Child3,3),(Child4,4))T(C,LVL)
             WHERE LVL <=  @CHILD - case when ( COALESCE(M.LVL,0) - @ADULTS)>0  THEN ( COALESCE(M.LVL,0) - @ADULTS) else 0 END
             AND C IS NOT NULL
             AND ID = T.ID
             AND ClientID = T.ClientID
             ORDER BY LVL DESC
             )N

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-11 : 05:12:35

great, thank you so much.

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 06:27:24

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-11 : 07:40:49

Hi Visakh, one question. The EXTRACHILD column used in the final calculation is a bit different than the actual EXTRACHILD column (4th column in the output).
Can I use the logic in the EXTRACHILD in the final calculation? If you check the code of the final column, following "AdditionalChild *" you will understand what I am trying to say, that piece of logic is slightly different from the 4th column EXTRACHILD logic. Which one should I finally use?

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 07:44:25

quote:
Originally posted by rocknpop

Hi Visakh, one question. The EXTRACHILD column used in the final calculation is a bit different than the actual EXTRACHILD column (4th column in the output).
Can I use the logic in the EXTRACHILD in the final calculation? If you check the code of the final column, following "AdditionalChild *" you will understand what I am trying to say, that piece of logic is slightly different from the 4th column EXTRACHILD logic. Which one should I finally use?

Thanks

--------------------
Rock n Roll with SQL

you can use logic in final calculation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-11 : 07:58:04

Ok Thanks.

--------------------
Rock n Roll with SQL

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources