invoiceStartTime 企业最早开票时间

SELECT
min(t2.date)
FROM (
SELECT
invoice_info_tmp.billing_date AS date,
invoice_info_tmp.state,
invoice_info_tmp.amount_tax,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.state,
t1.amount_tax
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz=1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2;

#maxInvoiceMonthNuml12M过去12个月最大连续开票月份数(有点问题)

SELECT
left(t2.billing_date,7)
FROM
(
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606307’ AND t1.bz = 1
and t1.billing_date BETWEEN ‘2018-02-01’ AND ‘2019-01-31’
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
GROUP BY left(t2.billing_date,7);

invoiceAmtL12M 过去12个月开票金额总数

SELECT
sum(t2.amount_tax)

AS validAmountTax

FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz = 1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
and t2.billing_date BETWEEN ‘2018-02-1’ AND ‘2019-01-31’;

#invoiceAmtL24M 过去24个月开票金额总数
SELECT
sum(t2.amount_tax) AS validAmountTax
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz = 1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
and t2.billing_date BETWEEN ‘2017-02-1’ AND ‘2019-01-31’;
#and t2.billing_date BETWEEN date_format(DATE_SUB(now(),INTERVAL 12 MONTH),’%Y%m’) AND ‘2019-01-31’;

invoiceAmtL6M 过去6个月开票金额总数

SELECT
sum(t2.amount_tax) AS validAmountTax
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz = 1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
and t2.billing_date BETWEEN ‘2018-08-01’ AND ‘2019-01-31’;

invoiceAmtL3M 过去3个月开票金额总数

SELECT
sum(t2.amount_tax) AS amount3months
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz = 1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
and t2.billing_date BETWEEN ‘2018-11-01’ AND ‘2019-01-31’;
– # l3MPercent 过去3个月/g过去第4,5,6个月的金额比较
SELECT format((t3.validAmountTax20196/t4.validAmountTax20186),2) from
(SELECT
sum(t2.amount_tax)AS validAmountTax20196
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’
and bz=1 and (t1.billing_date BETWEEN ‘2018-11-01’ AND ‘2019-01-31’)
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2)t3,
(SELECT
sum(t2.amount_tax) AS validAmountTax20186
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ and bz=1 and (t1.billing_date BETWEEN ‘2018-07-31’ AND ‘2018-10-31’)
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2)t4;

#l6mPercent – #过去6个月/g过去第7,8,9,10,11,12个月的金额比较
SELECT format((t3.validAmountTax20196/t4.validAmountTax20186),2) from
(SELECT
sum(t2.amount_tax)AS validAmountTax20196
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’
and bz=1 and (t1.billing_date BETWEEN ‘2018-8-01’ AND ‘2019-01-31’)
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2)t3,
(SELECT
sum(t2.amount_tax) AS validAmountTax20186
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ and bz=1 and (t1.billing_date BETWEEN ‘2018-01-31’ AND ‘2018-7-31’)
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2)t4;

L3mCom 过去3个月和去年同期三个月的开票金额进行比较

SELECT format((t3.validAmountTax20196/t4.validAmountTax20186),2) from
(SELECT
sum(t2.amount_tax)AS validAmountTax20196
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’
and bz=1 and (t1.billing_date BETWEEN ‘2018-11-01’ AND ‘2019-01-31’)
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2)t3,
(SELECT
sum(t2.amount_tax) AS validAmountTax20186
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ and bz=1 and (t1.billing_date BETWEEN ‘2017-10-31’ AND ‘2018-01-31’)
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2)t4;

#L6MPercent 过去6个月和去年同期6月的开票金额进行比较
SELECT format((t3.validAmountTax20196/t4.validAmountTax20186),2) from
(SELECT
sum(t2.amount_tax)AS validAmountTax20196
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’
and bz=1 and (t1.billing_date BETWEEN ‘2018-08-01’ AND ‘2019-01-31’)
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2)t3,
(SELECT
sum(t2.amount_tax) AS validAmountTax20186
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ and bz=1 and (t1.billing_date BETWEEN ‘2017-07-31’ AND ‘2018-01-31’)
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2)t4;

averageAmount12M 过去12个月平均开票金额

SELECT
sum(t2.amount_tax)/12 as avgAmt
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz = 1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
and t2.billing_date BETWEEN ‘2018-02-1’ AND ‘2019-01-31’;

#averageAmount24M 过去24个月平均开票金额
SELECT
sum(t2.amount_tax)/24 as avgAmt
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz = 1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
and t2.billing_date BETWEEN ‘2017-02-01’ AND ‘2019-01-31’;

#standardDeviation12M 过去12个月的开票金额，标准差的计算
SELECT
STD(t2.amount_tax) AS validAmountTax
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz = 1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
and t2.billing_date BETWEEN ‘2018-02-01’ AND ‘2019-01-31’;

standardDeviation24M 过去24个月的开票金额标志差

SELECT
STD(t2.amount_tax) AS validAmountTax
FROM (
SELECT
invoice_info_tmp.billing_date,
invoice_info_tmp.amount_tax,
invoice_info_tmp.state,
@rownum := @rownum + 1,
if(@pInvoiceCode = invoice_info_tmp.invoice_code AND
@pInvoiceNumber = invoice_info_tmp.invoice_number,
@rank := @rank + 1, @rank := 1) AS rank,
@pInvoiceCode := invoice_info_tmp.invoice_code,
@pInvoiceNumber := invoice_info_tmp.invoice_number
FROM (
SELECT
t1.invoice_code,
t1.invoice_number,
t1.billing_date,
t1.amount_tax,
t1.state
FROM inoutput t1
WHERE t1.corp_id = ‘BW0020180606320’ AND t1.bz = 1
ORDER BY t1.invoice_code, t1.invoice_number ASC, state DESC
) invoice_info_tmp, (SELECT
@rownum := 0,
@pInvoiceCode := NULL,
@pInvoiceNumber := NULL,
@rank := 0) temp
) t2
WHERE t2.rank = 1 AND t2.state !=2
and t2.billing_date BETWEEN ‘2017-02-01’ AND ‘2019-01-31’;