Reply to thread

Message: <blockquote data-quote="rosharavinda" data-source="post: 16716257" data-attributes="member: 68151">Please SQL waddo mata mekata help ekak denna.We have given a set of records in a table. You need to write an SQL function without using CURSORs to return cost per a given number of credits. Credit cost information is given in a table called “tblLoyaltyDiscount” and “tblLoyaltyDiscount” is given below. If you are interested in creating the table in your own database and try, scripts are given at the bottom of this document. You can give an approximate logic in TSQL.[ATTACH]51655[/ATTACH]Cost is calculated by multiplying DiscCostPerCredit with number of credits. DiscCostPerCredit is taken from the closest minimum credit record. Some examples are given below.1 credit = £195.003 credits = £500.00 (can be taken directly from the table)4 credits = £668.00 (167.00 * 4,  167.00 taken from the record for 3 credits)5 credits = £800.00 (can be taken directly from the table)6 credits = £960.00 (160.00 * 6,  160.00 taken from the record for 5 credits)12 credits = £1800.00 (150.00 * 12,  150.00 taken from the record for 10 credits)720 credits = £72000.00 (100.00 * 720,  100.00 taken from the record for 500 credits)Script to create tblLoyaltyDiscountSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblLoyaltyDiscount](    [LoyaltyDiscountID] [int] IDENTITY(1,1) NOT NULL,    [Sequence] [int] NULL,    [CreditCount] [int] NULL,    [DiscountedTotal] [decimal](18, 2) NULL,    [DiscCostPerCredit] [decimal](18, 2) NULL,    [Active] [bit] NULL) ON [PRIMARY]GOScripts to insert data into tblLoyaltyDiscountinsert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active) VALUES (1,1,195,195,'True')insert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active)VALUES (2,3,500,167,'True')insert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active)VALUES (3,5,800,160,'True')insert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active)VALUES (4,10,1500,150,'True')insert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active)VALUES (5,25,3500,140,'True')insert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active)VALUES (6,50,6500,130,'True')insert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active)VALUES (7,100,12000,120,'True')insert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active)VALUES (8,250,27500,110,'True')insert INTO [dbo].[tblLoyaltyDiscount] (Sequence,CreditCount,DiscountedTotal,DiscCostPerCredit,Active)VALUES (9,500,50000,100,'True')Please help me Guys.<img src="/styles/default/xenforo/smilies/default/sad.gif"  class="smilie" loading="lazy" alt=":(" title="Sad    :(" data-shortname=":(" /><img src="/styles/default/xenforo/smilies/default/sad.gif"  class="smilie" loading="lazy" alt=":(" title="Sad    :(" data-shortname=":(" /><img src="/styles/default/xenforo/smilies/default/sad.gif"  class="smilie" loading="lazy" alt=":(" title="Sad    :(" data-shortname=":(" /><img src="/styles/default/xenforo/smilies/default/sad.gif"  class="smilie" loading="lazy" alt=":(" title="Sad    :(" data-shortname=":(" /></blockquote>

Verification: Dawasata paya keeyak thibeda?

Top Bottom