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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  Complex computation Reply to Topic  Printer Friendly
Author  Topic

rocknpop
Posting Yak Master

201 Posts

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 11/03/2013 :  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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 11/03/2013 :  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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

 Posted - 11/03/2013 :  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 - 11/03/2013 :  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 = 4DECLARE @PERSONS INT = 5DECLARE @CHILD INT = @PERSONS - @ADULTSDECLARE @P1 INT, @P2 INT, @P3 INT, @P4 INT, @C1 INT, @C2 INT, @C3 INT, @C4 INT, @PX INT, @CX INTSELECT @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

India
52326 Posts

 Posted - 11/03/2013 :  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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs Edited by - visakh16 on 11/03/2013 06:31:10

rocknpop
Posting Yak Master

201 Posts

 Posted - 11/03/2013 :  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 EXTRAADULTThanks--------------------Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 11/03/2013 :  09:02:18 Cool. Glad that I could sort it out.Thanks for giving an interesting problem to work with.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

 Posted - 11/11/2013 :  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

India
52326 Posts

 Posted - 11/11/2013 :  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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 11/11/2013 :  02:56:42 So you want this to be repeated for each client?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

 Posted - 11/11/2013 :  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

India
52326 Posts

 Posted - 11/11/2013 :  03:11:37 which columns correspond to total adults/persons?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

 Posted - 11/11/2013 :  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 = 2DECLARE @PERSONS INT = 3DECLARE @CHILD INT = @PERSONS - @ADULTSWe 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

India
52326 Posts

 Posted - 11/11/2013 :  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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

 Posted - 11/11/2013 :  05:12:35 great, thank you so much.Thanks--------------------Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 11/11/2013 :  06:27:24 welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

 Posted - 11/11/2013 :  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

India
52326 Posts

 Posted - 11/11/2013 :  07:44:25 quote:Originally posted by rocknpopHi 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 SQLyou can use logic in final calculation------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

rocknpop
Posting Yak Master

201 Posts

 Posted - 11/11/2013 :  07:58:04 Ok Thanks.--------------------Rock n Roll with SQL
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC