Turning Records by Date Range into Records by Day/Month Using SQL Server or Vertica












4















I can use either SQL Server or Vertica as the DB and Tableau as the reporting tool. A solution in any of these mediums would be helpful.



DATA RESOURCES:
I have a table (userActivity) with 100 records and a structure of: User, StartDate, EndDate



NEED:
I am interested in preparing reports by day and month that show "total active days", meaning if User1 has a range of '20180101' to '20180331', they will contribute one day for each day in Jan, Feb and Mar OR 31, 28 and 31 days if aggregated by month.



GOAL:
I will ultimately be aggregating the total active days of all users as the output to achieve a single total for each day/month.



This report will span to perpetuity, so I would prefer solutions that don't hard code CASE/IF-THEN statements by day/month.



Thanks!










share|improve this question

























  • I believe I've figured it out! :) I am using the following code to create a final table with one record per user per active day. I will then count these by day or month in Tableau. Please, see below.

    – Andrew Glenn
    Nov 16 '18 at 0:54
















4















I can use either SQL Server or Vertica as the DB and Tableau as the reporting tool. A solution in any of these mediums would be helpful.



DATA RESOURCES:
I have a table (userActivity) with 100 records and a structure of: User, StartDate, EndDate



NEED:
I am interested in preparing reports by day and month that show "total active days", meaning if User1 has a range of '20180101' to '20180331', they will contribute one day for each day in Jan, Feb and Mar OR 31, 28 and 31 days if aggregated by month.



GOAL:
I will ultimately be aggregating the total active days of all users as the output to achieve a single total for each day/month.



This report will span to perpetuity, so I would prefer solutions that don't hard code CASE/IF-THEN statements by day/month.



Thanks!










share|improve this question

























  • I believe I've figured it out! :) I am using the following code to create a final table with one record per user per active day. I will then count these by day or month in Tableau. Please, see below.

    – Andrew Glenn
    Nov 16 '18 at 0:54














4












4








4








I can use either SQL Server or Vertica as the DB and Tableau as the reporting tool. A solution in any of these mediums would be helpful.



DATA RESOURCES:
I have a table (userActivity) with 100 records and a structure of: User, StartDate, EndDate



NEED:
I am interested in preparing reports by day and month that show "total active days", meaning if User1 has a range of '20180101' to '20180331', they will contribute one day for each day in Jan, Feb and Mar OR 31, 28 and 31 days if aggregated by month.



GOAL:
I will ultimately be aggregating the total active days of all users as the output to achieve a single total for each day/month.



This report will span to perpetuity, so I would prefer solutions that don't hard code CASE/IF-THEN statements by day/month.



Thanks!










share|improve this question
















I can use either SQL Server or Vertica as the DB and Tableau as the reporting tool. A solution in any of these mediums would be helpful.



DATA RESOURCES:
I have a table (userActivity) with 100 records and a structure of: User, StartDate, EndDate



NEED:
I am interested in preparing reports by day and month that show "total active days", meaning if User1 has a range of '20180101' to '20180331', they will contribute one day for each day in Jan, Feb and Mar OR 31, 28 and 31 days if aggregated by month.



GOAL:
I will ultimately be aggregating the total active days of all users as the output to achieve a single total for each day/month.



This report will span to perpetuity, so I would prefer solutions that don't hard code CASE/IF-THEN statements by day/month.



Thanks!







sql sql-server tsql tableau vertica






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 19 '18 at 20:02







Andrew Glenn

















asked Nov 15 '18 at 19:44









Andrew GlennAndrew Glenn

289




289













  • I believe I've figured it out! :) I am using the following code to create a final table with one record per user per active day. I will then count these by day or month in Tableau. Please, see below.

    – Andrew Glenn
    Nov 16 '18 at 0:54



















  • I believe I've figured it out! :) I am using the following code to create a final table with one record per user per active day. I will then count these by day or month in Tableau. Please, see below.

    – Andrew Glenn
    Nov 16 '18 at 0:54

















I believe I've figured it out! :) I am using the following code to create a final table with one record per user per active day. I will then count these by day or month in Tableau. Please, see below.

– Andrew Glenn
Nov 16 '18 at 0:54





I believe I've figured it out! :) I am using the following code to create a final table with one record per user per active day. I will then count these by day or month in Tableau. Please, see below.

– Andrew Glenn
Nov 16 '18 at 0:54












3 Answers
3






active

oldest

votes


















2














While recursive CTEs are a good candidate for this scenario, it can be handled with tableau alone. Assuming you have this data, here are the steps required to produce the view.



enter image description here




  1. Create a reference sheet which has all the days expected. Even if you need to cover 25 years from 01/01/2018 to 01/01/2043, that is still less than 10k rows.


enter image description here



You need two columns with exact same date as Tableau does not allow multiple join conditions on same column.




  1. Create an inner join between reference calendar and data using following criteria.
    enter image description here



  2. Build the view



    enter image description here








share|improve this answer


























  • I assumed a Tableau solution was available, but for me, much of Tableau is still quite convoluted. Thanks for illuminating this functionality, Jose!

    – Andrew Glenn
    Nov 16 '18 at 17:17











  • Love the detailed description, by the way!

    – Andrew Glenn
    Nov 16 '18 at 19:38











  • @AndrewGlenn, glad it helped. It was a nice question and I enjoyed answering it.

    – Jose Cherian
    Nov 16 '18 at 20:29



















2














Use Vertica - it has the TIMESERIES clause - no recursion needed.



I would try the below - and check the intermediate results of the Common Table Expressions to see how it works..



WITH 
-- two test rows ....
input(uid,start_dt,end_dt) AS (
SELECT 1,DATE '2018-01-01', DATE '2018-03-31'
UNION ALL SELECT 2,DATE '2018-02-01', DATE '2018-04-01'
)
,
-- set the stage for Vertica's TIMESERIES clause
-- note: TIMESERIES relies on timestamps ...
limits(uid,lim_dt,qty) AS (
SELECT
uid
, start_dt::TIMESTAMP
, 1
FROM input
UNION ALL
SELECT
uid
, end_dt::TIMESTAMP
, 1
FROM input
)
,
-- apply the Vertica TIMESERIES clause
counters AS (
SELECT
uid
, act_dt
, TS_FIRST_VALUE(qty) AS qty
FROM limits
TIMESERIES act_dt AS '1 DAY' OVER(PARTITION BY uid ORDER BY lim_dt)
)
SELECT
uid
, MONTH(act_dt) AS activity_month
, SUM(qty)
FROM counters
GROUP BY 1,2;
-- out uid | activity_month | sum
-- out -----+----------------+-----
-- out 1 | 1 | 31
-- out 1 | 2 | 28
-- out 1 | 3 | 31
-- out 2 | 2 | 28
-- out 2 | 3 | 31
-- out 2 | 4 | 1
-- out (6 rows)
-- out
-- out time: first fetch (6 rows): 120.515 ms. all rows formatted: 120.627 ms





share|improve this answer
























  • Excellent Vertica based solution. I like having options depending on the back end requirements. I also prefer using Vertica in many cases. Well done!

    – Andrew Glenn
    Nov 16 '18 at 17:15



















0














Solution:



WITH base AS (
SELECT
User AS u
,StartDate AS s
,EndDate AS e
,DATEDIFF(
dd,
StartDate,
EndDate
)+1 AS d
FROM userActivity
),
recurse AS (
SELECT u, s, e, d, x=(d-1)
FROM base
UNION ALL
SELECT u, s, e, d, x-1 AS x
FROM recurse
WHERE x>0
)
SELECT u, DATEADD(dd, x, s) AS recordperday
FROM recurse
ORDER BY u, recordperday
--Extends SQL Server's recursion limit
OPTION (MAXRECURSION 500)





share|improve this answer
























  • While the first solution available to me (and the one I devised), this option clearly seems inferior to the others, assuming access to Tableau or Vertica exists. Also, my SQL Server solution could face challenges if the data set or sources are larger or more complex than mine. We have 3 workable options here so far!

    – Andrew Glenn
    Nov 16 '18 at 17:34











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%2f53326856%2fturning-records-by-date-range-into-records-by-day-month-using-sql-server-or-vert%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









2














While recursive CTEs are a good candidate for this scenario, it can be handled with tableau alone. Assuming you have this data, here are the steps required to produce the view.



enter image description here




  1. Create a reference sheet which has all the days expected. Even if you need to cover 25 years from 01/01/2018 to 01/01/2043, that is still less than 10k rows.


enter image description here



You need two columns with exact same date as Tableau does not allow multiple join conditions on same column.




  1. Create an inner join between reference calendar and data using following criteria.
    enter image description here



  2. Build the view



    enter image description here








share|improve this answer


























  • I assumed a Tableau solution was available, but for me, much of Tableau is still quite convoluted. Thanks for illuminating this functionality, Jose!

    – Andrew Glenn
    Nov 16 '18 at 17:17











  • Love the detailed description, by the way!

    – Andrew Glenn
    Nov 16 '18 at 19:38











  • @AndrewGlenn, glad it helped. It was a nice question and I enjoyed answering it.

    – Jose Cherian
    Nov 16 '18 at 20:29
















2














While recursive CTEs are a good candidate for this scenario, it can be handled with tableau alone. Assuming you have this data, here are the steps required to produce the view.



enter image description here




  1. Create a reference sheet which has all the days expected. Even if you need to cover 25 years from 01/01/2018 to 01/01/2043, that is still less than 10k rows.


enter image description here



You need two columns with exact same date as Tableau does not allow multiple join conditions on same column.




  1. Create an inner join between reference calendar and data using following criteria.
    enter image description here



  2. Build the view



    enter image description here








share|improve this answer


























  • I assumed a Tableau solution was available, but for me, much of Tableau is still quite convoluted. Thanks for illuminating this functionality, Jose!

    – Andrew Glenn
    Nov 16 '18 at 17:17











  • Love the detailed description, by the way!

    – Andrew Glenn
    Nov 16 '18 at 19:38











  • @AndrewGlenn, glad it helped. It was a nice question and I enjoyed answering it.

    – Jose Cherian
    Nov 16 '18 at 20:29














2












2








2







While recursive CTEs are a good candidate for this scenario, it can be handled with tableau alone. Assuming you have this data, here are the steps required to produce the view.



enter image description here




  1. Create a reference sheet which has all the days expected. Even if you need to cover 25 years from 01/01/2018 to 01/01/2043, that is still less than 10k rows.


enter image description here



You need two columns with exact same date as Tableau does not allow multiple join conditions on same column.




  1. Create an inner join between reference calendar and data using following criteria.
    enter image description here



  2. Build the view



    enter image description here








share|improve this answer















While recursive CTEs are a good candidate for this scenario, it can be handled with tableau alone. Assuming you have this data, here are the steps required to produce the view.



enter image description here




  1. Create a reference sheet which has all the days expected. Even if you need to cover 25 years from 01/01/2018 to 01/01/2043, that is still less than 10k rows.


enter image description here



You need two columns with exact same date as Tableau does not allow multiple join conditions on same column.




  1. Create an inner join between reference calendar and data using following criteria.
    enter image description here



  2. Build the view



    enter image description here









share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 16 '18 at 12:41

























answered Nov 16 '18 at 12:35









Jose CherianJose Cherian

3,20812327




3,20812327













  • I assumed a Tableau solution was available, but for me, much of Tableau is still quite convoluted. Thanks for illuminating this functionality, Jose!

    – Andrew Glenn
    Nov 16 '18 at 17:17











  • Love the detailed description, by the way!

    – Andrew Glenn
    Nov 16 '18 at 19:38











  • @AndrewGlenn, glad it helped. It was a nice question and I enjoyed answering it.

    – Jose Cherian
    Nov 16 '18 at 20:29



















  • I assumed a Tableau solution was available, but for me, much of Tableau is still quite convoluted. Thanks for illuminating this functionality, Jose!

    – Andrew Glenn
    Nov 16 '18 at 17:17











  • Love the detailed description, by the way!

    – Andrew Glenn
    Nov 16 '18 at 19:38











  • @AndrewGlenn, glad it helped. It was a nice question and I enjoyed answering it.

    – Jose Cherian
    Nov 16 '18 at 20:29

















I assumed a Tableau solution was available, but for me, much of Tableau is still quite convoluted. Thanks for illuminating this functionality, Jose!

– Andrew Glenn
Nov 16 '18 at 17:17





I assumed a Tableau solution was available, but for me, much of Tableau is still quite convoluted. Thanks for illuminating this functionality, Jose!

– Andrew Glenn
Nov 16 '18 at 17:17













Love the detailed description, by the way!

– Andrew Glenn
Nov 16 '18 at 19:38





Love the detailed description, by the way!

– Andrew Glenn
Nov 16 '18 at 19:38













@AndrewGlenn, glad it helped. It was a nice question and I enjoyed answering it.

– Jose Cherian
Nov 16 '18 at 20:29





@AndrewGlenn, glad it helped. It was a nice question and I enjoyed answering it.

– Jose Cherian
Nov 16 '18 at 20:29













2














Use Vertica - it has the TIMESERIES clause - no recursion needed.



I would try the below - and check the intermediate results of the Common Table Expressions to see how it works..



WITH 
-- two test rows ....
input(uid,start_dt,end_dt) AS (
SELECT 1,DATE '2018-01-01', DATE '2018-03-31'
UNION ALL SELECT 2,DATE '2018-02-01', DATE '2018-04-01'
)
,
-- set the stage for Vertica's TIMESERIES clause
-- note: TIMESERIES relies on timestamps ...
limits(uid,lim_dt,qty) AS (
SELECT
uid
, start_dt::TIMESTAMP
, 1
FROM input
UNION ALL
SELECT
uid
, end_dt::TIMESTAMP
, 1
FROM input
)
,
-- apply the Vertica TIMESERIES clause
counters AS (
SELECT
uid
, act_dt
, TS_FIRST_VALUE(qty) AS qty
FROM limits
TIMESERIES act_dt AS '1 DAY' OVER(PARTITION BY uid ORDER BY lim_dt)
)
SELECT
uid
, MONTH(act_dt) AS activity_month
, SUM(qty)
FROM counters
GROUP BY 1,2;
-- out uid | activity_month | sum
-- out -----+----------------+-----
-- out 1 | 1 | 31
-- out 1 | 2 | 28
-- out 1 | 3 | 31
-- out 2 | 2 | 28
-- out 2 | 3 | 31
-- out 2 | 4 | 1
-- out (6 rows)
-- out
-- out time: first fetch (6 rows): 120.515 ms. all rows formatted: 120.627 ms





share|improve this answer
























  • Excellent Vertica based solution. I like having options depending on the back end requirements. I also prefer using Vertica in many cases. Well done!

    – Andrew Glenn
    Nov 16 '18 at 17:15
















2














Use Vertica - it has the TIMESERIES clause - no recursion needed.



I would try the below - and check the intermediate results of the Common Table Expressions to see how it works..



WITH 
-- two test rows ....
input(uid,start_dt,end_dt) AS (
SELECT 1,DATE '2018-01-01', DATE '2018-03-31'
UNION ALL SELECT 2,DATE '2018-02-01', DATE '2018-04-01'
)
,
-- set the stage for Vertica's TIMESERIES clause
-- note: TIMESERIES relies on timestamps ...
limits(uid,lim_dt,qty) AS (
SELECT
uid
, start_dt::TIMESTAMP
, 1
FROM input
UNION ALL
SELECT
uid
, end_dt::TIMESTAMP
, 1
FROM input
)
,
-- apply the Vertica TIMESERIES clause
counters AS (
SELECT
uid
, act_dt
, TS_FIRST_VALUE(qty) AS qty
FROM limits
TIMESERIES act_dt AS '1 DAY' OVER(PARTITION BY uid ORDER BY lim_dt)
)
SELECT
uid
, MONTH(act_dt) AS activity_month
, SUM(qty)
FROM counters
GROUP BY 1,2;
-- out uid | activity_month | sum
-- out -----+----------------+-----
-- out 1 | 1 | 31
-- out 1 | 2 | 28
-- out 1 | 3 | 31
-- out 2 | 2 | 28
-- out 2 | 3 | 31
-- out 2 | 4 | 1
-- out (6 rows)
-- out
-- out time: first fetch (6 rows): 120.515 ms. all rows formatted: 120.627 ms





share|improve this answer
























  • Excellent Vertica based solution. I like having options depending on the back end requirements. I also prefer using Vertica in many cases. Well done!

    – Andrew Glenn
    Nov 16 '18 at 17:15














2












2








2







Use Vertica - it has the TIMESERIES clause - no recursion needed.



I would try the below - and check the intermediate results of the Common Table Expressions to see how it works..



WITH 
-- two test rows ....
input(uid,start_dt,end_dt) AS (
SELECT 1,DATE '2018-01-01', DATE '2018-03-31'
UNION ALL SELECT 2,DATE '2018-02-01', DATE '2018-04-01'
)
,
-- set the stage for Vertica's TIMESERIES clause
-- note: TIMESERIES relies on timestamps ...
limits(uid,lim_dt,qty) AS (
SELECT
uid
, start_dt::TIMESTAMP
, 1
FROM input
UNION ALL
SELECT
uid
, end_dt::TIMESTAMP
, 1
FROM input
)
,
-- apply the Vertica TIMESERIES clause
counters AS (
SELECT
uid
, act_dt
, TS_FIRST_VALUE(qty) AS qty
FROM limits
TIMESERIES act_dt AS '1 DAY' OVER(PARTITION BY uid ORDER BY lim_dt)
)
SELECT
uid
, MONTH(act_dt) AS activity_month
, SUM(qty)
FROM counters
GROUP BY 1,2;
-- out uid | activity_month | sum
-- out -----+----------------+-----
-- out 1 | 1 | 31
-- out 1 | 2 | 28
-- out 1 | 3 | 31
-- out 2 | 2 | 28
-- out 2 | 3 | 31
-- out 2 | 4 | 1
-- out (6 rows)
-- out
-- out time: first fetch (6 rows): 120.515 ms. all rows formatted: 120.627 ms





share|improve this answer













Use Vertica - it has the TIMESERIES clause - no recursion needed.



I would try the below - and check the intermediate results of the Common Table Expressions to see how it works..



WITH 
-- two test rows ....
input(uid,start_dt,end_dt) AS (
SELECT 1,DATE '2018-01-01', DATE '2018-03-31'
UNION ALL SELECT 2,DATE '2018-02-01', DATE '2018-04-01'
)
,
-- set the stage for Vertica's TIMESERIES clause
-- note: TIMESERIES relies on timestamps ...
limits(uid,lim_dt,qty) AS (
SELECT
uid
, start_dt::TIMESTAMP
, 1
FROM input
UNION ALL
SELECT
uid
, end_dt::TIMESTAMP
, 1
FROM input
)
,
-- apply the Vertica TIMESERIES clause
counters AS (
SELECT
uid
, act_dt
, TS_FIRST_VALUE(qty) AS qty
FROM limits
TIMESERIES act_dt AS '1 DAY' OVER(PARTITION BY uid ORDER BY lim_dt)
)
SELECT
uid
, MONTH(act_dt) AS activity_month
, SUM(qty)
FROM counters
GROUP BY 1,2;
-- out uid | activity_month | sum
-- out -----+----------------+-----
-- out 1 | 1 | 31
-- out 1 | 2 | 28
-- out 1 | 3 | 31
-- out 2 | 2 | 28
-- out 2 | 3 | 31
-- out 2 | 4 | 1
-- out (6 rows)
-- out
-- out time: first fetch (6 rows): 120.515 ms. all rows formatted: 120.627 ms






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 16 '18 at 16:19









marcothesanemarcothesane

1,5341512




1,5341512













  • Excellent Vertica based solution. I like having options depending on the back end requirements. I also prefer using Vertica in many cases. Well done!

    – Andrew Glenn
    Nov 16 '18 at 17:15



















  • Excellent Vertica based solution. I like having options depending on the back end requirements. I also prefer using Vertica in many cases. Well done!

    – Andrew Glenn
    Nov 16 '18 at 17:15

















Excellent Vertica based solution. I like having options depending on the back end requirements. I also prefer using Vertica in many cases. Well done!

– Andrew Glenn
Nov 16 '18 at 17:15





Excellent Vertica based solution. I like having options depending on the back end requirements. I also prefer using Vertica in many cases. Well done!

– Andrew Glenn
Nov 16 '18 at 17:15











0














Solution:



WITH base AS (
SELECT
User AS u
,StartDate AS s
,EndDate AS e
,DATEDIFF(
dd,
StartDate,
EndDate
)+1 AS d
FROM userActivity
),
recurse AS (
SELECT u, s, e, d, x=(d-1)
FROM base
UNION ALL
SELECT u, s, e, d, x-1 AS x
FROM recurse
WHERE x>0
)
SELECT u, DATEADD(dd, x, s) AS recordperday
FROM recurse
ORDER BY u, recordperday
--Extends SQL Server's recursion limit
OPTION (MAXRECURSION 500)





share|improve this answer
























  • While the first solution available to me (and the one I devised), this option clearly seems inferior to the others, assuming access to Tableau or Vertica exists. Also, my SQL Server solution could face challenges if the data set or sources are larger or more complex than mine. We have 3 workable options here so far!

    – Andrew Glenn
    Nov 16 '18 at 17:34
















0














Solution:



WITH base AS (
SELECT
User AS u
,StartDate AS s
,EndDate AS e
,DATEDIFF(
dd,
StartDate,
EndDate
)+1 AS d
FROM userActivity
),
recurse AS (
SELECT u, s, e, d, x=(d-1)
FROM base
UNION ALL
SELECT u, s, e, d, x-1 AS x
FROM recurse
WHERE x>0
)
SELECT u, DATEADD(dd, x, s) AS recordperday
FROM recurse
ORDER BY u, recordperday
--Extends SQL Server's recursion limit
OPTION (MAXRECURSION 500)





share|improve this answer
























  • While the first solution available to me (and the one I devised), this option clearly seems inferior to the others, assuming access to Tableau or Vertica exists. Also, my SQL Server solution could face challenges if the data set or sources are larger or more complex than mine. We have 3 workable options here so far!

    – Andrew Glenn
    Nov 16 '18 at 17:34














0












0








0







Solution:



WITH base AS (
SELECT
User AS u
,StartDate AS s
,EndDate AS e
,DATEDIFF(
dd,
StartDate,
EndDate
)+1 AS d
FROM userActivity
),
recurse AS (
SELECT u, s, e, d, x=(d-1)
FROM base
UNION ALL
SELECT u, s, e, d, x-1 AS x
FROM recurse
WHERE x>0
)
SELECT u, DATEADD(dd, x, s) AS recordperday
FROM recurse
ORDER BY u, recordperday
--Extends SQL Server's recursion limit
OPTION (MAXRECURSION 500)





share|improve this answer













Solution:



WITH base AS (
SELECT
User AS u
,StartDate AS s
,EndDate AS e
,DATEDIFF(
dd,
StartDate,
EndDate
)+1 AS d
FROM userActivity
),
recurse AS (
SELECT u, s, e, d, x=(d-1)
FROM base
UNION ALL
SELECT u, s, e, d, x-1 AS x
FROM recurse
WHERE x>0
)
SELECT u, DATEADD(dd, x, s) AS recordperday
FROM recurse
ORDER BY u, recordperday
--Extends SQL Server's recursion limit
OPTION (MAXRECURSION 500)






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 16 '18 at 0:54









Andrew GlennAndrew Glenn

289




289













  • While the first solution available to me (and the one I devised), this option clearly seems inferior to the others, assuming access to Tableau or Vertica exists. Also, my SQL Server solution could face challenges if the data set or sources are larger or more complex than mine. We have 3 workable options here so far!

    – Andrew Glenn
    Nov 16 '18 at 17:34



















  • While the first solution available to me (and the one I devised), this option clearly seems inferior to the others, assuming access to Tableau or Vertica exists. Also, my SQL Server solution could face challenges if the data set or sources are larger or more complex than mine. We have 3 workable options here so far!

    – Andrew Glenn
    Nov 16 '18 at 17:34

















While the first solution available to me (and the one I devised), this option clearly seems inferior to the others, assuming access to Tableau or Vertica exists. Also, my SQL Server solution could face challenges if the data set or sources are larger or more complex than mine. We have 3 workable options here so far!

– Andrew Glenn
Nov 16 '18 at 17:34





While the first solution available to me (and the one I devised), this option clearly seems inferior to the others, assuming access to Tableau or Vertica exists. Also, my SQL Server solution could face challenges if the data set or sources are larger or more complex than mine. We have 3 workable options here so far!

– Andrew Glenn
Nov 16 '18 at 17:34


















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%2f53326856%2fturning-records-by-date-range-into-records-by-day-month-using-sql-server-or-vert%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







這個網誌中的熱門文章

Xamarin.form Move up view when keyboard appear

Post-Redirect-Get with Spring WebFlux and Thymeleaf

Anylogic : not able to use stopDelay()