SQL query needed - Counting 365 days backwards The 2019 Stack Overflow Developer Survey Results Are InInserting multiple rows in a single SQL query?MySQL Query GROUP BY day / month / yearCount(*) vs Count(1) - SQL ServerHow do I count occurrences by day in SQL?SQL query for finding records where count > 1SQL nested query with count and partitionSQL count number of users every 7 daysreturn values within the last 365 counting from newest date ORACLE SQLOracle SQL rank queryHow to have the rolling distinct count of each day for past three days in Oracle SQL?

Accepted by European university, rejected by all American ones I applied to? Possible reasons?

Is it ok to offer lower paid work as a trial period before negotiating for a full-time job?

Can there be female White Walkers?

What is this sharp, curved notch on my knife for?

What's the name of these plastic connectors

Match Roman Numerals

What does Linus Torvalds mean when he says that Git "never ever" tracks a file?

Button changing its text & action. Good or terrible?

What information about me do stores get via my credit card?

Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?

Are there any other methods to apply to solving simultaneous equations?

How to display lines in a file like ls displays files in a directory?

Can you cast a spell on someone in the Ethereal Plane, if you are on the Material Plane and have the True Seeing spell active?

Identify boardgame from Big movie

Is it okay to consider publishing in my first year of PhD?

Can I have a signal generator on while it's not connected?

Falsification in Math vs Science

What do hard-Brexiteers want with respect to the Irish border?

APIPA and LAN Broadcast Domain

Is Cinnamon a desktop environment or a window manager? (Or both?)

What could be the right powersource for 15 seconds lifespan disposable giant chainsaw?

How do PCB vias affect signal quality?

Why didn't the Event Horizon Telescope team mention Sagittarius A*?

Why doesn't shell automatically fix "useless use of cat"?



SQL query needed - Counting 365 days backwards



The 2019 Stack Overflow Developer Survey Results Are InInserting multiple rows in a single SQL query?MySQL Query GROUP BY day / month / yearCount(*) vs Count(1) - SQL ServerHow do I count occurrences by day in SQL?SQL query for finding records where count > 1SQL nested query with count and partitionSQL count number of users every 7 daysreturn values within the last 365 counting from newest date ORACLE SQLOracle SQL rank queryHow to have the rolling distinct count of each day for past three days in Oracle SQL?



.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;








-1















I have searched the forum many times but couldn't find a solution for my situation. I am working with an Oracle database.



I have a table with all Order Numbers and Customer Numbers by Day. It looks like this:



Day | Customer Nbr | Order Nbr 
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....


Now I need a SQL Query which gives me a table by day and Customer Nbr and counts the number of unique Order Numbers within the last 365 days starting from column 1.



For the example above the resulting table should look like:



Day | Customer Nbr | Order Cnt 
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1









share|improve this question



















  • 1





    can you please add a bigger dataset for data and output?

    – Hijesh Vl
    Mar 8 at 9:41






  • 1





    How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?

    – Joakim Danielson
    Mar 8 at 9:41











  • I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.

    – Kris0603
    Mar 8 at 10:29












  • I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03

    – Kris0603
    Mar 8 at 10:38

















-1















I have searched the forum many times but couldn't find a solution for my situation. I am working with an Oracle database.



I have a table with all Order Numbers and Customer Numbers by Day. It looks like this:



Day | Customer Nbr | Order Nbr 
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....


Now I need a SQL Query which gives me a table by day and Customer Nbr and counts the number of unique Order Numbers within the last 365 days starting from column 1.



For the example above the resulting table should look like:



Day | Customer Nbr | Order Cnt 
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1









share|improve this question



















  • 1





    can you please add a bigger dataset for data and output?

    – Hijesh Vl
    Mar 8 at 9:41






  • 1





    How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?

    – Joakim Danielson
    Mar 8 at 9:41











  • I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.

    – Kris0603
    Mar 8 at 10:29












  • I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03

    – Kris0603
    Mar 8 at 10:38













-1












-1








-1








I have searched the forum many times but couldn't find a solution for my situation. I am working with an Oracle database.



I have a table with all Order Numbers and Customer Numbers by Day. It looks like this:



Day | Customer Nbr | Order Nbr 
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....


Now I need a SQL Query which gives me a table by day and Customer Nbr and counts the number of unique Order Numbers within the last 365 days starting from column 1.



For the example above the resulting table should look like:



Day | Customer Nbr | Order Cnt 
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1









share|improve this question
















I have searched the forum many times but couldn't find a solution for my situation. I am working with an Oracle database.



I have a table with all Order Numbers and Customer Numbers by Day. It looks like this:



Day | Customer Nbr | Order Nbr 
2018-01-05 | 25687459 | 256
2018-01-09 | 36478592 | 398
2018-03-07 | 25687459 | 1547
and so on....


Now I need a SQL Query which gives me a table by day and Customer Nbr and counts the number of unique Order Numbers within the last 365 days starting from column 1.



For the example above the resulting table should look like:



Day | Customer Nbr | Order Cnt 
2019-01-01 | 25687459 | 2
2019-01-02 | 25687459 | 2
...
2019-03-01 | 25687459 | 1






sql oracle






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 10 at 16:53









halfer

14.8k759117




14.8k759117










asked Mar 8 at 9:35









Kris0603Kris0603

1




1







  • 1





    can you please add a bigger dataset for data and output?

    – Hijesh Vl
    Mar 8 at 9:41






  • 1





    How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?

    – Joakim Danielson
    Mar 8 at 9:41











  • I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.

    – Kris0603
    Mar 8 at 10:29












  • I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03

    – Kris0603
    Mar 8 at 10:38












  • 1





    can you please add a bigger dataset for data and output?

    – Hijesh Vl
    Mar 8 at 9:41






  • 1





    How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?

    – Joakim Danielson
    Mar 8 at 9:41











  • I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.

    – Kris0603
    Mar 8 at 10:29












  • I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03

    – Kris0603
    Mar 8 at 10:38







1




1





can you please add a bigger dataset for data and output?

– Hijesh Vl
Mar 8 at 9:41





can you please add a bigger dataset for data and output?

– Hijesh Vl
Mar 8 at 9:41




1




1





How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?

– Joakim Danielson
Mar 8 at 9:41





How is that output "counting backwards"? Shouldn't the first row contain todays date if descending order or today - 1 year if ascending?

– Joakim Danielson
Mar 8 at 9:41













I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.

– Kris0603
Mar 8 at 10:29






I am very sorry but I don't understand your question. I try to explain in different words. The source table has all Order Numbers and Customer Numbers per Day. In the resulting table I want to see for every day in the past how many orders every customer has made. But only in a certain timeframe, which is between the day we are looking at and (the day we are lookign at minus 364 days). So, lets say Customer A has made 5 orders in 2018.

– Kris0603
Mar 8 at 10:29














I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03

– Kris0603
Mar 8 at 10:38





I am very sorry but I don't understand your question. I try to explain in different words. So, lets say Customer A has made 5 orders in 2018. I need a table for every day in 2019 (lat's say starting with 2019-01-01). First row gives me the amount of orders the customer had made between 2018-01-02 and 2019-01-01 (which is 5) The second row is 2019-01-02 and gives me the amount of orders the customer has made between 2018-01-03 and 2019-01-02 The third row is 2019-01-03 and gives me the amount of orders the customer has made between 2018-01-04 and 2019-01-03

– Kris0603
Mar 8 at 10:38












3 Answers
3






active

oldest

votes


















0














One method is to generate values for all days of interest for each customer and then use a correlated subquery:



with dates as (
select date '2019-01-01' + rownum as dte from dual
connect by date '2019-01-01' + rownum < sysdate
)
select d.dte, t.customer_nbr,
(select count(*)
from t t2
where t2.customer_nbr = t.customer_nbr and
t2.day <= t.dte and
t2.date > t.dte - 365
) as order_cnt
from dates d cross join
(select distinct customer_nbr from t) ;





share|improve this answer






























    0














    Edit:



    I've just seen you clarify the question, which I've interpreted to mean:
    For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...



    Updated Answer:



    For each customer, we count the number of records between the order day, and 365 days before it...



    WITH yourTable AS
    (
    SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
    UNION ALL
    SELECT SYSDATE - 2, 'Alex' FROM DUAL
    UNION ALL
    SELECT SYSDATE - 366, 'Alex'FROM DUAL

    UNION ALL
    SELECT SYSDATE - 400, 'Alex'FROM DUAL
    UNION ALL
    SELECT SYSDATE - 500, 'Alex'FROM DUAL
    UNION ALL
    SELECT SYSDATE - 1, 'Joe'FROM DUAL
    UNION ALL
    SELECT SYSDATE - 300, 'Chris'FROM DUAL
    UNION ALL
    SELECT SYSDATE - 1, 'Chris'FROM DUAL
    )

    SELECT Day, CustomerNbr, OrdersLast365Days
    FROM yourTable t
    OUTER APPLY
    (
    SELECT COUNT(1) OrdersLast365Days
    FROM yourTable t2
    WHERE t.CustomerNbr = t2.CustomerNbr
    AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
    AND TRUNC(t2.Day) <= TRUNC(t.Day)
    )
    ORDER BY t.Day DESC, t.CustomerNbr;


    Last 365 Days' Orders



    If you want to report on just the days you have orders for, then a simple WHERE clause should be enough:



    SELECT Day, CustomerNbr, COUNT(1) OrderCount
    FROM <yourTable>
    WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
    GROUP BY Day, CustomerNbr
    ORDER BY Day Desc;


    If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:



    WITH last365Days AS
    (
    SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
    FROM DUAL CONNECT BY ROWNUM < 365
    )

    SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
    FROM last365Days d
    LEFT OUTER JOIN <yourTable> t
    ON d.Day = TRUNC(t.Day)
    GROUP BY d.Day, t.CustomerNbr
    ORDER BY d.Day Desc;


    Left Join CTE






    share|improve this answer

























    • Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?

      – Kris0603
      Mar 8 at 12:37












    • No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.

      – Alex
      Mar 8 at 14:38












    • I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days

      – Alex
      Mar 8 at 14:47


















    0














    I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.



    This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed



    WITH DATES AS ( 
    SELECT * FROM
    (SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
    CROSS JOIN
    (SELECT DISTINCT CUST_ID FROM MY_TABLE))
    SELECT DISTINCT
    DATES.DAY,
    DATES.CUST_ID,
    COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
    FROM
    DATES
    LEFT JOIN
    MY_TABLE
    ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
    ORDER BY DATES.CUST_ID,DATES.DAY;





    share|improve this answer























      Your Answer






      StackExchange.ifUsing("editor", function ()
      StackExchange.using("externalEditor", function ()
      StackExchange.using("snippets", function ()
      StackExchange.snippets.init();
      );
      );
      , "code-snippets");

      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "1"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55060368%2fsql-query-needed-counting-365-days-backwards%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      0














      One method is to generate values for all days of interest for each customer and then use a correlated subquery:



      with dates as (
      select date '2019-01-01' + rownum as dte from dual
      connect by date '2019-01-01' + rownum < sysdate
      )
      select d.dte, t.customer_nbr,
      (select count(*)
      from t t2
      where t2.customer_nbr = t.customer_nbr and
      t2.day <= t.dte and
      t2.date > t.dte - 365
      ) as order_cnt
      from dates d cross join
      (select distinct customer_nbr from t) ;





      share|improve this answer



























        0














        One method is to generate values for all days of interest for each customer and then use a correlated subquery:



        with dates as (
        select date '2019-01-01' + rownum as dte from dual
        connect by date '2019-01-01' + rownum < sysdate
        )
        select d.dte, t.customer_nbr,
        (select count(*)
        from t t2
        where t2.customer_nbr = t.customer_nbr and
        t2.day <= t.dte and
        t2.date > t.dte - 365
        ) as order_cnt
        from dates d cross join
        (select distinct customer_nbr from t) ;





        share|improve this answer

























          0












          0








          0







          One method is to generate values for all days of interest for each customer and then use a correlated subquery:



          with dates as (
          select date '2019-01-01' + rownum as dte from dual
          connect by date '2019-01-01' + rownum < sysdate
          )
          select d.dte, t.customer_nbr,
          (select count(*)
          from t t2
          where t2.customer_nbr = t.customer_nbr and
          t2.day <= t.dte and
          t2.date > t.dte - 365
          ) as order_cnt
          from dates d cross join
          (select distinct customer_nbr from t) ;





          share|improve this answer













          One method is to generate values for all days of interest for each customer and then use a correlated subquery:



          with dates as (
          select date '2019-01-01' + rownum as dte from dual
          connect by date '2019-01-01' + rownum < sysdate
          )
          select d.dte, t.customer_nbr,
          (select count(*)
          from t t2
          where t2.customer_nbr = t.customer_nbr and
          t2.day <= t.dte and
          t2.date > t.dte - 365
          ) as order_cnt
          from dates d cross join
          (select distinct customer_nbr from t) ;






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Mar 8 at 12:40









          Gordon LinoffGordon Linoff

          796k37318423




          796k37318423























              0














              Edit:



              I've just seen you clarify the question, which I've interpreted to mean:
              For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...



              Updated Answer:



              For each customer, we count the number of records between the order day, and 365 days before it...



              WITH yourTable AS
              (
              SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
              UNION ALL
              SELECT SYSDATE - 2, 'Alex' FROM DUAL
              UNION ALL
              SELECT SYSDATE - 366, 'Alex'FROM DUAL

              UNION ALL
              SELECT SYSDATE - 400, 'Alex'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 500, 'Alex'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 1, 'Joe'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 300, 'Chris'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 1, 'Chris'FROM DUAL
              )

              SELECT Day, CustomerNbr, OrdersLast365Days
              FROM yourTable t
              OUTER APPLY
              (
              SELECT COUNT(1) OrdersLast365Days
              FROM yourTable t2
              WHERE t.CustomerNbr = t2.CustomerNbr
              AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
              AND TRUNC(t2.Day) <= TRUNC(t.Day)
              )
              ORDER BY t.Day DESC, t.CustomerNbr;


              Last 365 Days' Orders



              If you want to report on just the days you have orders for, then a simple WHERE clause should be enough:



              SELECT Day, CustomerNbr, COUNT(1) OrderCount
              FROM <yourTable>
              WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
              GROUP BY Day, CustomerNbr
              ORDER BY Day Desc;


              If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:



              WITH last365Days AS
              (
              SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
              FROM DUAL CONNECT BY ROWNUM < 365
              )

              SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
              FROM last365Days d
              LEFT OUTER JOIN <yourTable> t
              ON d.Day = TRUNC(t.Day)
              GROUP BY d.Day, t.CustomerNbr
              ORDER BY d.Day Desc;


              Left Join CTE






              share|improve this answer

























              • Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?

                – Kris0603
                Mar 8 at 12:37












              • No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.

                – Alex
                Mar 8 at 14:38












              • I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days

                – Alex
                Mar 8 at 14:47















              0














              Edit:



              I've just seen you clarify the question, which I've interpreted to mean:
              For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...



              Updated Answer:



              For each customer, we count the number of records between the order day, and 365 days before it...



              WITH yourTable AS
              (
              SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
              UNION ALL
              SELECT SYSDATE - 2, 'Alex' FROM DUAL
              UNION ALL
              SELECT SYSDATE - 366, 'Alex'FROM DUAL

              UNION ALL
              SELECT SYSDATE - 400, 'Alex'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 500, 'Alex'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 1, 'Joe'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 300, 'Chris'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 1, 'Chris'FROM DUAL
              )

              SELECT Day, CustomerNbr, OrdersLast365Days
              FROM yourTable t
              OUTER APPLY
              (
              SELECT COUNT(1) OrdersLast365Days
              FROM yourTable t2
              WHERE t.CustomerNbr = t2.CustomerNbr
              AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
              AND TRUNC(t2.Day) <= TRUNC(t.Day)
              )
              ORDER BY t.Day DESC, t.CustomerNbr;


              Last 365 Days' Orders



              If you want to report on just the days you have orders for, then a simple WHERE clause should be enough:



              SELECT Day, CustomerNbr, COUNT(1) OrderCount
              FROM <yourTable>
              WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
              GROUP BY Day, CustomerNbr
              ORDER BY Day Desc;


              If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:



              WITH last365Days AS
              (
              SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
              FROM DUAL CONNECT BY ROWNUM < 365
              )

              SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
              FROM last365Days d
              LEFT OUTER JOIN <yourTable> t
              ON d.Day = TRUNC(t.Day)
              GROUP BY d.Day, t.CustomerNbr
              ORDER BY d.Day Desc;


              Left Join CTE






              share|improve this answer

























              • Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?

                – Kris0603
                Mar 8 at 12:37












              • No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.

                – Alex
                Mar 8 at 14:38












              • I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days

                – Alex
                Mar 8 at 14:47













              0












              0








              0







              Edit:



              I've just seen you clarify the question, which I've interpreted to mean:
              For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...



              Updated Answer:



              For each customer, we count the number of records between the order day, and 365 days before it...



              WITH yourTable AS
              (
              SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
              UNION ALL
              SELECT SYSDATE - 2, 'Alex' FROM DUAL
              UNION ALL
              SELECT SYSDATE - 366, 'Alex'FROM DUAL

              UNION ALL
              SELECT SYSDATE - 400, 'Alex'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 500, 'Alex'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 1, 'Joe'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 300, 'Chris'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 1, 'Chris'FROM DUAL
              )

              SELECT Day, CustomerNbr, OrdersLast365Days
              FROM yourTable t
              OUTER APPLY
              (
              SELECT COUNT(1) OrdersLast365Days
              FROM yourTable t2
              WHERE t.CustomerNbr = t2.CustomerNbr
              AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
              AND TRUNC(t2.Day) <= TRUNC(t.Day)
              )
              ORDER BY t.Day DESC, t.CustomerNbr;


              Last 365 Days' Orders



              If you want to report on just the days you have orders for, then a simple WHERE clause should be enough:



              SELECT Day, CustomerNbr, COUNT(1) OrderCount
              FROM <yourTable>
              WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
              GROUP BY Day, CustomerNbr
              ORDER BY Day Desc;


              If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:



              WITH last365Days AS
              (
              SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
              FROM DUAL CONNECT BY ROWNUM < 365
              )

              SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
              FROM last365Days d
              LEFT OUTER JOIN <yourTable> t
              ON d.Day = TRUNC(t.Day)
              GROUP BY d.Day, t.CustomerNbr
              ORDER BY d.Day Desc;


              Left Join CTE






              share|improve this answer















              Edit:



              I've just seen you clarify the question, which I've interpreted to mean:
              For every day in the last year, show how many orders there were for each customer between that date, and 1 year previously. Working on an answer now...



              Updated Answer:



              For each customer, we count the number of records between the order day, and 365 days before it...



              WITH yourTable AS
              (
              SELECT SYSDATE - 1 Day, 'Alex' CustomerNbr FROM DUAL
              UNION ALL
              SELECT SYSDATE - 2, 'Alex' FROM DUAL
              UNION ALL
              SELECT SYSDATE - 366, 'Alex'FROM DUAL

              UNION ALL
              SELECT SYSDATE - 400, 'Alex'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 500, 'Alex'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 1, 'Joe'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 300, 'Chris'FROM DUAL
              UNION ALL
              SELECT SYSDATE - 1, 'Chris'FROM DUAL
              )

              SELECT Day, CustomerNbr, OrdersLast365Days
              FROM yourTable t
              OUTER APPLY
              (
              SELECT COUNT(1) OrdersLast365Days
              FROM yourTable t2
              WHERE t.CustomerNbr = t2.CustomerNbr
              AND TRUNC(t2.Day) >= TRUNC(t.Day) - 364
              AND TRUNC(t2.Day) <= TRUNC(t.Day)
              )
              ORDER BY t.Day DESC, t.CustomerNbr;


              Last 365 Days' Orders



              If you want to report on just the days you have orders for, then a simple WHERE clause should be enough:



              SELECT Day, CustomerNbr, COUNT(1) OrderCount
              FROM <yourTable>
              WHERE TRUNC(DAY) >= TRUNC(SYSDATE -364)
              GROUP BY Day, CustomerNbr
              ORDER BY Day Desc;


              If you want to report on every day, you'll need to generate them first. This can be done by a recursive CTE, which you then join to your table:



              WITH last365Days AS
              (
              SELECT TRUNC (SYSDATE - ROWNUM + 1) dt
              FROM DUAL CONNECT BY ROWNUM < 365
              )

              SELECT d.Day, COALESCE(t.CustomerNbr, 'None') CustomerNbr, SUM(CASE WHEN t.CustomerNbr IS NULL THEN 0 ELSE 1 END) OrderCount
              FROM last365Days d
              LEFT OUTER JOIN <yourTable> t
              ON d.Day = TRUNC(t.Day)
              GROUP BY d.Day, t.CustomerNbr
              ORDER BY d.Day Desc;


              Left Join CTE







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Mar 8 at 14:46

























              answered Mar 8 at 10:44









              AlexAlex

              22627




              22627












              • Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?

                – Kris0603
                Mar 8 at 12:37












              • No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.

                – Alex
                Mar 8 at 14:38












              • I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days

                – Alex
                Mar 8 at 14:47

















              • Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?

                – Kris0603
                Mar 8 at 12:37












              • No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.

                – Alex
                Mar 8 at 14:38












              • I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days

                – Alex
                Mar 8 at 14:47
















              Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?

              – Kris0603
              Mar 8 at 12:37






              Thank you for your response. I don't understand all of it and just try it out. What is DUAL? I often get "unsupported column aliasing". Do you know what this could mean?

              – Kris0603
              Mar 8 at 12:37














              No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.

              – Alex
              Mar 8 at 14:38






              No problem. I think the "Unsupported column aliasing" was because I used CTEs (with x (col1, col2) AS....) which doens't work before Oracle 11.2 - I guess you're using an older version? DUAL is a placeholder table so you can select things without using a real table (SELECT DUMMY FROM DUAL) just returns 'x'. I mainly use it for applying logic to columns in CROSS/OUTER APPLY or CTEs though.

              – Alex
              Mar 8 at 14:38














              I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days

              – Alex
              Mar 8 at 14:47





              I've updated the CTEs to use the old way of column aliasing, and the older CONNECT BY statement to generate the 365 days

              – Alex
              Mar 8 at 14:47











              0














              I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.



              This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed



              WITH DATES AS ( 
              SELECT * FROM
              (SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
              CROSS JOIN
              (SELECT DISTINCT CUST_ID FROM MY_TABLE))
              SELECT DISTINCT
              DATES.DAY,
              DATES.CUST_ID,
              COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
              FROM
              DATES
              LEFT JOIN
              MY_TABLE
              ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
              ORDER BY DATES.CUST_ID,DATES.DAY;





              share|improve this answer



























                0














                I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.



                This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed



                WITH DATES AS ( 
                SELECT * FROM
                (SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
                CROSS JOIN
                (SELECT DISTINCT CUST_ID FROM MY_TABLE))
                SELECT DISTINCT
                DATES.DAY,
                DATES.CUST_ID,
                COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
                FROM
                DATES
                LEFT JOIN
                MY_TABLE
                ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
                ORDER BY DATES.CUST_ID,DATES.DAY;





                share|improve this answer

























                  0












                  0








                  0







                  I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.



                  This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed



                  WITH DATES AS ( 
                  SELECT * FROM
                  (SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
                  CROSS JOIN
                  (SELECT DISTINCT CUST_ID FROM MY_TABLE))
                  SELECT DISTINCT
                  DATES.DAY,
                  DATES.CUST_ID,
                  COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
                  FROM
                  DATES
                  LEFT JOIN
                  MY_TABLE
                  ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
                  ORDER BY DATES.CUST_ID,DATES.DAY;





                  share|improve this answer













                  I would probably have done it with and analytic function. In your windowing clause, you can specify a number of rows before, or a range. In this case I will use a range.



                  This will give you, For Each customer for each day the number of orders during one rolling year before the date displayed



                  WITH DATES AS ( 
                  SELECT * FROM
                  (SELECT TRUNC(SYSDATE)-(LEVEL-1) AS DAY FROM DUAL CONNECT BY TRUNC(SYSDATE)-(LEVEL-1) >= ( SELECT MIN(TRUNC(DAY)) FROM MY_TABLE ))
                  CROSS JOIN
                  (SELECT DISTINCT CUST_ID FROM MY_TABLE))
                  SELECT DISTINCT
                  DATES.DAY,
                  DATES.CUST_ID,
                  COUNT(ORDER_ID) OVER (PARTITION BY DATES.CUST_ID ORDER BY DATES.DAY RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' SECOND PRECEDING)
                  FROM
                  DATES
                  LEFT JOIN
                  MY_TABLE
                  ON DATES.DAY=TRUNC(MY_TABLE.DAY) AND DATES.CUST_ID=MY_TABLE.CUST_ID
                  ORDER BY DATES.CUST_ID,DATES.DAY;






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Mar 8 at 16:45









                  LauDecLauDec

                  508410




                  508410



























                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55060368%2fsql-query-needed-counting-365-days-backwards%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Save data to MySQL database using ExtJS and PHP [closed]2019 Community Moderator ElectionHow can I prevent SQL injection in PHP?Which MySQL data type to use for storing boolean valuesPHP: Delete an element from an arrayHow do I connect to a MySQL Database in Python?Should I use the datetime or timestamp data type in MySQL?How to get a list of MySQL user accountsHow Do You Parse and Process HTML/XML in PHP?Reference — What does this symbol mean in PHP?How does PHP 'foreach' actually work?Why shouldn't I use mysql_* functions in PHP?

                      Compiling GNU Global with universal-ctags support Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Data science time! April 2019 and salary with experience The Ask Question Wizard is Live!Tags for Emacs: Relationship between etags, ebrowse, cscope, GNU Global and exuberant ctagsVim and Ctags tips and trickscscope or ctags why choose one over the other?scons and ctagsctags cannot open option file “.ctags”Adding tag scopes in universal-ctagsShould I use Universal-ctags?Universal ctags on WindowsHow do I install GNU Global with universal ctags support using Homebrew?Universal ctags with emacsHow to highlight ctags generated by Universal Ctags in Vim?

                      Add ONERROR event to image from jsp tldHow to add an image to a JPanel?Saving image from PHP URLHTML img scalingCheck if an image is loaded (no errors) with jQueryHow to force an <img> to take up width, even if the image is not loadedHow do I populate hidden form field with a value set in Spring ControllerStyling Raw elements Generated from JSP tagds with Jquery MobileLimit resizing of images with explicitly set width and height attributeserror TLD use in a jsp fileJsp tld files cannot be resolved