Great god genuflect is begged for advice, how to use the SQL statistics on the number of effective c-CodePudding

Effective customer is defined as: nearly a year into the shop customers, statistics a fixed time I go back to the statistics, but at once if you want to put every month this year are displayed, just don't understand how to write the SQL?

For example:

Effective customer is in March 2020, in March 2019 to May 2020 in shop customer
Effective customer is in April 2020, in April 2019 to May 2020 in shop customer
Effective customer is in May 2020, in May 2019 to May 2020 in shop customer
.

Now at the same time show

Effective number of clients in
In January,
In February,
In march,
In April,
In may,

Thank you for the great god give directions!

CodePudding user response:

You have customers into the store in the table of consumption time
First time for years, according to the monthly group had sum amount

 SELECT YearMonth, COUNT (client) AS 'customers' FROM Table GROUP BY YearMonth

If you want to repeat it

 SELECT YearMonth, COUNT (Distinct customer) AS 'customers' FROM Table GROUP BY YearMonth

CodePudding user response:

reference 1st floor kaijie_wu1209 response:

do you have customers into the store in the table of time
First time for years, according to the monthly group had sum amount
 SELECT YearMonth, COUNT (client) AS 'customers' FROM Table GROUP BY YearMonth 
If you want to repeat it
 SELECT YearMonth, COUNT (Distinct customer) AS 'customer number FROM the Table GROUP BY YearMonth 

Thank you very much for your reply, into the shop, the time consumption is some, but this calculation is wrong, because the starting time and ending time of each month is different, so directly by month after grouping statistics as a result of the "monthly" into the shop customers, rather than "nearly a year" consumer customers into the store

CodePudding user response:

refer to the second floor wend6608 response:

Quote: refer to 1st floor kaijie_wu1209 response:

You have customers into the store in the table of consumption time
First time for years, according to the monthly group had sum amount
SELECT YearMonth, COUNT (client) AS 'customers' FROM Table GROUP BY YearMonth

If you want to repeat it
SELECT YearMonth, COUNT (Distinct customer) AS 'customer number FROM the Table GROUP BY YearMonth

Thank you very much for your reply, into the shop, the time consumption is some, but this calculation is wrong, because the starting time and ending time of each month is different, so directly by month after grouping statistics as a result of the "monthly" into the shop customers, rather than "nearly a year into the shop customers"

Filter conditions added a year, you see what type is your time, such as the following is the current 2020-1=2019 can get a year of

 where Left (time, 4)=cast (replace (convert (varchar (4), dateadd (month, 0, getdate ()), 120), '-', ') as int) - 1

CodePudding user response:

 grown ettleDate BETWEEN DATEADD (yy, 1, CONVERT (datetime, GETDATE () + '00:00:00. 00', 121)) AND the CONVERT (datetime, GETDATE () + '23:59:59. 99', 121)

Added, "I'm almost a year" filter conditions, but this is calculated in the current date "nearly a year of customers each month, and are actually is:

Such as it is in May 2020, after the query results should be

Customer number: in March in March 2019 to March 2020 consumption
Customer number: in April between April 2019 and April 2020 consumption
In may the customer number: in May 2019 to May 2020 consumption

CodePudding user response:

Think about this question, your time is two parts, one is the actual business time, one is you want corresponding to each month in the past one year, every month we need to take out the corresponding time over the past year alone for assembly line,

 
The create table # TIMES (
Monthdef varchar (10), 
Monthcut varchar (10) 
) 
INSERT INTO # TIMES 
VALUES (' 2020-05 ', '2020-05), (' 2020 -' 05, 'the 2020-04), (' 2020-05', '2020-03), (' 2020-05', '2020-02), (' 2020-05', '2020-01), 
(' 2020-05 ', '2019-12), (' 2020-05', '2019-11), (' 2020 -' 05, 'the 2019-10), (' 2020 -' 05, 'the 2019-09), (' 2020 -' 05, 'the 2019-08), 
(' 2020 - '05,' the 2019-07), (' 2020-05 ', '2019-06), (' 2020-05', '2019-05)

Of course I will be inserted directly, you want to write cycle of data on actual work, the idea is that the data warehouse solution, the hope can help you,

CodePudding user response:

 
WITH CTE 
AS 
(SELECT '2020' + RIGHT (' 00 '+ CAST (NUMBER + 1 AS VARCHAR), 2) the AS MON, 
DATEADD (MONTH, NUMBER, CAST (' 2020-01-01 'AS the DATE) AS BEGIN_DATE, 
DATEADD (NUMBER + 1 MONTH, CAST (' 2020-01-01 'AS the DATE) AS END_DATE 
The FROM (SELECT MONTH (GETDATE ()) AS MON) AS A 
JOIN the MASTER. The DBO. SPT_VALUES B ON arjun ON> B.N UMBER 
WHERE TYPE='P') 


The SELECT MON, COUNT (DISTINCT customer) 
The FROM CTE AS A 
JOIN TABLE B ON b. sales date & gt;=a. EGIN_DATE AND b. sales date & lt; A.E ND_DATE 
GROUP BY MON