Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Caluclate COUNT with grouping and dynamic date range

Hi, guys!

I hope you can help me with following problem:

 

I have a table Sales with next structure:

 

[Column] - Description

-----------------------
[SaleID] - ID of sale
[SaleDate] - Date of sale
[ContractID] - ID of contract. On one contract may be sereval sales
[ContractDate] - Date when contract was signed
[ClientName] - Cleint Name. One cleint can have several contracrats
[Maneger] - Manger Name. One manager can have several clients. Eeach cleint correspond only to one manager
[HasNewContractInPeriod] - The flag that informs of the fact of concluding the new contract for this client, under which took place at least one sale in a period of 90 days before the date of current sale.

 

The problem is to calulate the [HasNewContractInPeriod] column.

 

I suppose it should be something like (that just a prototype of query):

SELECT ContractID, COUNT(SaleID) .... FROM Sales WHERE ContractDate BETWEEN ((SaleDate - 90, SaleDate) AND GroupBY ClientName HAVING COUNT(SaleID) > 1

  • Anonymous's avatar
    Anonymous
    9 years ago

    Anonymous, thanks for reply!

     

    I got your idea, but it's not my way.

     

    Finaly I decide to solve this task with SQL. Here is my code:

     

    UPDATE "Sales" AS s1 
SET "maxClientContractDate" = (SELECT "mDate"
	FROM (SELECT "ContractID", MAX("ContractDate") AS "mDate"
		FROM "sales" 
		WHERE "SaleDate" < s1."SaleDate"
		AND "ClientName" =  s1."ClientName" 
		GROUP BY "ContractID" 
		HAVING COUNT(*) > 0) AS cs
	WHERE
        "mDate" < p1."SaleDate",
        ORDER BY "mDate" DESC LIMIT 1;

UPDATE "Sales" AS s1
SET "hasNewContractInPeriod" = 'TRUE' 
     WHERE "SaleDate" - "maxClientContractDate" > 90;

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    You can use below formula to achieve your goal.

     

    Calculate column:

     

    HasNewContractInPeriod = if(COUNTAX(FILTER(ALL(Sheet1),Sheet1[ContractID]=EARLIER(Sheet1[ContractID])&&Sheet1[ContractDate]>=DATEADD(Sheet1[SaleDate],-90,DAY)&&Sheet1[ContractDate]<=Sheet1[SaleDate]),[SaleID])>0,TRUE(),FALSE())

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Anonymous, thanks for reply!

       

      I got your idea, but it's not my way.

       

      Finaly I decide to solve this task with SQL. Here is my code:

       

      UPDATE "Sales" AS s1 
SET "maxClientContractDate" = (SELECT "mDate"
	FROM (SELECT "ContractID", MAX("ContractDate") AS "mDate"
		FROM "sales" 
		WHERE "SaleDate" < s1."SaleDate"
		AND "ClientName" =  s1."ClientName" 
		GROUP BY "ContractID" 
		HAVING COUNT(*) > 0) AS cs
	WHERE
        "mDate" < p1."SaleDate",
        ORDER BY "mDate" DESC LIMIT 1;

UPDATE "Sales" AS s1
SET "hasNewContractInPeriod" = 'TRUE' 
     WHERE "SaleDate" - "maxClientContractDate" > 90;