SQL Waddo

[rosharavinda]

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.



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.00
3 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 tblLoyaltyDiscount

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE 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]

GO



Scripts to insert data into tblLoyaltyDiscount


insert 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.

 

[Ayeshlive]

Homework ne?

 

[rosharavinda]

Ko meka karanna kenek nadda meke?

 

[Vladimir Putin]

මෙක ගිහින් දාන්න ප්‍රෝග්‍රෑමර්ස් ක්ලබ් ත්‍රෙඩ් එකෙ.

 

[ElaRosa]

As I think this is how it should be. I don't have SQL in my PC to check it.
1. Write a function that takes one integer(number_of_credits) as a parameter.
https://www.simple-talk.com/sql/t-sql-programming/sql-server-functions-the-basics/

Inside the function
1. create two variables as LstNo - int and CCount - int.
2. Write a select query to check for that number and return the DiscCostPerCredit.
IF DiscCostPerCredit != 0 (that means CreditCount = number_of_credits)
you can calculate the cost.
Else
select the CreditCount and,
IF CreditCount < number_of_credits
LstNo = CreditCount
ELSE (that means CreditCount > number_of_credits)
CCount = LstNo.
Calculate the cost based on the CCount.