A simple and interesting SQL stored procedure--personal financial management

Just beginning to learn the small white storage process, the Internet after reading the grammar generally want to practice, but often do not know what to write a stored procedure to practiced hand, the following provides a simple and interesting, to query the monthly personal financial situation of the stored procedures.

I. Establishment of the table

First, a fact table is needed to record important monthly expenses, including the type of expenditure, the cost, the payment method, the monthly payment time, the first instalment repayment time, the last installment repayment time (the time is finished), the use, the expense category ... Not necessarily will be used, but also according to the actual situation to do the corresponding increase or decrease, the following only provide an example.

CREATE TABLE [dbo]. [Fact_costtype]    (      [Costtypeid] [nvarchar] () not NULL,   --type of expenditure      [cost] [numeric] (2) NULL,           --costs      [PayType] [ NVARCHAR] NULL,          --payment Method      [refundate] [nvarchar] () NULL,        --monthly payment time      [StartDate] [int] NULL,                 - -First Instalment repayment time      [EndDate] [int] NULL,                   --Last instalment repayment time (expected time)      [usetype] [nvarchar] (+) NULL,          --Use      [ Bigtype] [nvarchar] (a) NULL,          --large expenditure category (fixed expenditure or temporary expenditure)      PRIMARY KEY CLUSTERED ([Costtypeid] ASC) with        (pad_in DEX = off, Statistics_norecompute = off,               ignore_dup_key = off, Allow_row_locks = on,               allow_page_locks = ON) on [PRIMARY]    ) On  [Primary]go

Here is the output diagram of the demo

Second, write the stored procedure

CREATE PROC [dbo].    [Cost] (@YearMonth NVARCHAR (50),---required: Month input format for query, example: 201608 @Profit INT = 0,-                                                   -Optional: Last month balance amount, according to the actual situation set a value, the default is 0 @Salary INT = 3000,---optional: Monthly salary income, recommended to fill in the actual amount of deduction of five risk of one gold and after tax, demo with actual salary income 3000 for example                                                   --1, monthly income fixed, you can write dead in the stored procedure --2, monthly income is not fixed, you can execute the stored procedure, enter the actual income of the month @HousingFund INT = 1800--Optional: Provident Fund, if there is a quarterly extract, root          According to the actual situation set the value; 0;demo to Provident Fund January 600, quarterly extract for example) as CREATE TABLE #CostTable ([Costtypeid] [nvarchar] (50), [Cost] [Numeric]          (2), [paytype] [nvarchar] (a), [refundate] [nvarchar] (a), [StartDate] [int], [EndDate]            [int], [usetype] [nvarchar] (a), [bigtype] [nvarchar] ()) INSERT into #CostTable SELECT * from [on]. [dbo].            [Fact_costtype] WHERE PayTypeIn (' By Month ', ' by-order ') IF right (@YearMonth, 2) in (' "", ' "', '" ', ') ', ' "+ ') ' BEGIN INSERT into #Cos Ttable SELECT [Costtypeid], [cost], [PayType]                            , [Refundate], REPLACE ([StartDate], ' 210001 ', @YearMonth),                            REPLACE ([EndDate], ' 210001 ', @YearMonth), [Usetype],                    [Bigtype] from [the]. [dbo].                    [Fact_costtype] WHERE PayType in (' Quarterly ') SET @Salary = @Salary + @HousingFund END SELECT @Year Monthly as month, sum (cost) as total expense amount from #CostTable WHERE left (CONVERT (NVARCHAR (8), EndDate, 112), 6) >= @YearMonth and Left (CONVERT (NVARCHAR (8), StartDate, Bigtype), 6) <= @YearMonth SELECT [ ] as large class, sum (cost) as expenditure amount from #CostTable WHERE Left (CONVERT (NVARCHAR (8), EndDate, a), 6) >= @YearMonth and left (CONVERT (NVARCHAR (8), Sta            Rtdate, 6) <= @YearMonth GROUP by Bigtype SELECT [Bigtype] as large class, [Costtypeid] as project, [Cost] As expense details, [Usetype] as use from #CostTable WHERE left (CONVERT (NVARCHAR (8), EndDate, 6) >= @y   Earmonth and Left (CONVERT (NVARCHAR (8), StartDate, DECLARE), 6) <= @YearMonth @cost INT SELECT            @cost = SUM (cost) from #CostTable WHERE left (CONVERT (NVARCHAR (8), EndDate, 6) >= @YearMonth                          and left (CONVERT (NVARCHAR (8), StartDate, 6) <= @YearMonth SET @Profit = @Profit + @Salary-@cost if (@Profit < 0) BEGIN SELECT @Salary-@cost as ' silver is not enough to spend, you should think about how to make money, you loser! ！！ ' END IF (@Profit >=) BEGIN SELECT @Salary-@cost as ' good, you miser!!! ' END IF (@ProfiT > 0 and @Profit <) BEGIN SELECT @Salary-@cost as ' Another one months, you moonlight Clan!!! ' END

Third, run the stored procedure

1,ui execution

2, statement execution

DECLARE @return_value intexec @return_value = [dbo]. [Cost]     @YearMonth = N ' 201608 '   --, @Profit = 0   --, @Salary = 0   --, @HousingFund = 0GO

Iv. Results of implementation

Personal financial Situation July 2016

　　

Personal financial Situation August 2016

A simple and interesting SQL stored procedure--personal financial management