CODEWITHSUNDEEP
sqlsql-servertransactions
Viola Tent
Viola Tent

Reputation: 5

count value per month based on conditions in sql

I have a table of Purchases that looks like this:

Chassis_Number  Customer_Trn  Purchase_ID  date_sold  selling_price  tax_amount
-------------------------------------------------------------------------------
1234            567           1234567      2012-1-23  2345653        12343
2345            678           2345678      2013-4-05  1089255        11332

and another table Credit_Customer that looks like this:

Customer_Trn  Repayment  Monthly_Payment
----------------------------------------
567           456530     4530

I am trying to create a transaction that counts credit_customer purchases with respect to month(date_sold)

if credit_customer purchases<12/month then allow insert else rollback

Begin Transaction CC_Limit

    Alter Table Purchases
    ADD mon int

    Update Purchases
    Set mon = MONTH(date_sold)
    Where mon IS NULL

    Select Credit_Customer.Customer_Trn, Purchases.date_sold
    From Credit_Customer
    Inner Join Purchases
    On Credit_Customer.Customer_Trn=Purchases.Customer_Trn
    Order By Credit_Customer


    Declare @count int = (Select Count(*) from Credit_Customer)
    If @count<100

    Insert into Purchases (Chassis_Number,Customer_Trn,Purchase_ID,date_sold,selling_price,tax_amount)
        Values (@Chassis_Number,@Customer_Trn,@Purchase_ID,@date_sold,@selling_price,@tax_amount)


    Begin
    ROLLBACK trans

Upvotes: 0

Views: 144

Answers (2)

Robert Davidian
Robert Davidian

Reputation: 36

To check the credit worthiness, you can do something like this:

CREATE PROCEDURE GetCreditWorthiness 
    @Customer_Trn int 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE isCreditWorthy bit = 0
    DECLARE @Yr = YEAR(getdate())
    DECLARE @Mon = MONTH(getdate())

    SELECT @isCreditWorthy = CASE WHEN COUNT(Credit_Customer.Customer_Trn) < 12 THEN 1 ELSE 0 END
    FROM Credit_Customer 
        INNER JOIN Purchases On Credit_Customer.Customer_Trn=Purchases.Customer_Trn 
    WHERE Credit_Customer.Customer_Trn = @Customer_Trn 
        AND MONTH(date_sold) = @Mon and YEAR(date_sold) = @Yr    

    RETURN @isCreditWorthy
END

Upvotes: 1

Robert Davidian
Robert Davidian

Reputation: 36

I think you want to do something like this if you want to get all customers who have less than 12 transactions in a month:

SELECT Credit_Customer, COUNT(Credit_Customer.Customer_Trn) AS Cnt, YEAR(Purchases.date_sold) AS Yr, MONTH(Purchases.date_sold) AS Mon
FROM Credit_Customer 
Inner Join Purchases On Credit_Customer.Customer_Trn=Purchases.Customer_Trn 
GROUP BY Yr, Mon
HAVING COUNT(*) < 12
ORDER BY Credit_Customer

Upvotes: 1

Related Questions