Count number of times a specific daydate occurs between two dates
0
I have prices for a contract which change on the 01st April every year. The start and end dates of the contract may cross multiple price changes and initially I want to count the changes between the contract start and contract end date.
This is the result set I'm trying to get:
01/04/2017 To 31/03/2018 = 0
31/03/2018 To 01/04/2018 = 1
01/01/2015 To 31/12/2018 = 4
Currently my query looks like this (manual horribleness):
SET DATEFORMAT DMY;
SELECT
Contract_ID,
(Period_1 + Period_2 + Period_3 + Period_4 + Period_5 + Period_6 + Period_7
+ Period_8 + Period_9
) AS Split_Count
FROM
(
SELECT
C.Contract_ID,
CASE
WHEN ('01/04/2011'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_1,
CASE
WHEN ('01/04/2012'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_2,
CASE
WHEN ('01/04/2013'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_3,
CASE
WHEN ('01/04/2014'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_4,
CASE
WHEN ('01/04/2015'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_5,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_6,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_7,
CASE
WHEN ('01/04/2017'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_8,
CASE
WHEN ('01/04/2018'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_9
FROM Contract C
What would be the best way of doing this?
sql tsql
edited Nov 28 '18 at 16:00
Flimzy
40.1k1366100
asked Nov 28 '18 at 15:59
Matt HollandsMatt Hollands
1082
add a comment |
0
I have prices for a contract which change on the 01st April every year. The start and end dates of the contract may cross multiple price changes and initially I want to count the changes between the contract start and contract end date.
This is the result set I'm trying to get:
01/04/2017 To 31/03/2018 = 0
31/03/2018 To 01/04/2018 = 1
01/01/2015 To 31/12/2018 = 4
Currently my query looks like this (manual horribleness):
SET DATEFORMAT DMY;
SELECT
Contract_ID,
(Period_1 + Period_2 + Period_3 + Period_4 + Period_5 + Period_6 + Period_7
+ Period_8 + Period_9
) AS Split_Count
FROM
(
SELECT
C.Contract_ID,
CASE
WHEN ('01/04/2011'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_1,
CASE
WHEN ('01/04/2012'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_2,
CASE
WHEN ('01/04/2013'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_3,
CASE
WHEN ('01/04/2014'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_4,
CASE
WHEN ('01/04/2015'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_5,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_6,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_7,
CASE
WHEN ('01/04/2017'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_8,
CASE
WHEN ('01/04/2018'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_9
FROM Contract C
What would be the best way of doing this?
sql tsql
edited Nov 28 '18 at 16:00
Flimzy
40.1k1366100
asked Nov 28 '18 at 15:59
Matt HollandsMatt Hollands
1082
2
Sample data and desired results would really be helpful.
– Gordon Linoff
Nov 28 '18 at 16:00
The sample data and results are in the result set above the query. It doesn't have headings but the first date is the start date, the second date is the end date and the number is the number of 01/04/xx dates crossed. I should have put it in a neat table sorry.
– Matt Hollands
Nov 28 '18 at 16:39
add a comment |
0
0
0
I have prices for a contract which change on the 01st April every year. The start and end dates of the contract may cross multiple price changes and initially I want to count the changes between the contract start and contract end date.
This is the result set I'm trying to get:
01/04/2017 To 31/03/2018 = 0
31/03/2018 To 01/04/2018 = 1
01/01/2015 To 31/12/2018 = 4
Currently my query looks like this (manual horribleness):
SET DATEFORMAT DMY;
SELECT
Contract_ID,
(Period_1 + Period_2 + Period_3 + Period_4 + Period_5 + Period_6 + Period_7
+ Period_8 + Period_9
) AS Split_Count
FROM
(
SELECT
C.Contract_ID,
CASE
WHEN ('01/04/2011'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_1,
CASE
WHEN ('01/04/2012'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_2,
CASE
WHEN ('01/04/2013'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_3,
CASE
WHEN ('01/04/2014'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_4,
CASE
WHEN ('01/04/2015'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_5,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_6,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_7,
CASE
WHEN ('01/04/2017'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_8,
CASE
WHEN ('01/04/2018'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_9
FROM Contract C
What would be the best way of doing this?
sql tsql
edited Nov 28 '18 at 16:00
Flimzy
40.1k1366100
asked Nov 28 '18 at 15:59
Matt HollandsMatt Hollands
1082
I have prices for a contract which change on the 01st April every year. The start and end dates of the contract may cross multiple price changes and initially I want to count the changes between the contract start and contract end date.
This is the result set I'm trying to get:
01/04/2017 To 31/03/2018 = 0
31/03/2018 To 01/04/2018 = 1
01/01/2015 To 31/12/2018 = 4
Currently my query looks like this (manual horribleness):
SET DATEFORMAT DMY;
SELECT
Contract_ID,
(Period_1 + Period_2 + Period_3 + Period_4 + Period_5 + Period_6 + Period_7
+ Period_8 + Period_9
) AS Split_Count
FROM
(
SELECT
C.Contract_ID,
CASE
WHEN ('01/04/2011'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_1,
CASE
WHEN ('01/04/2012'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_2,
CASE
WHEN ('01/04/2013'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_3,
CASE
WHEN ('01/04/2014'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_4,
CASE
WHEN ('01/04/2015'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_5,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_6,
CASE
WHEN ('01/04/2016'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_7,
CASE
WHEN ('01/04/2017'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_8,
CASE
WHEN ('01/04/2018'BETWEEN Contract_Date_OLD AND Contract_Date_NEW) THEN 1
ELSE 0
END AS Period_9
FROM Contract C
What would be the best way of doing this?
sql tsql
sql tsql
edited Nov 28 '18 at 16:00
Flimzy
40.1k1366100
asked Nov 28 '18 at 15:59
Matt HollandsMatt Hollands
1082
edited Nov 28 '18 at 16:00
Flimzy
40.1k1366100
asked Nov 28 '18 at 15:59
Matt HollandsMatt Hollands
1082
edited Nov 28 '18 at 16:00
Flimzy
40.1k1366100
edited Nov 28 '18 at 16:00
Flimzy
40.1k1366100
edited Nov 28 '18 at 16:00
Flimzy
40.1k1366100
40.1k1366100
asked Nov 28 '18 at 15:59
Matt HollandsMatt Hollands
1082
asked Nov 28 '18 at 15:59
Matt HollandsMatt Hollands
1082
asked Nov 28 '18 at 15:59
Matt HollandsMatt Hollands
1082
1082
2
Sample data and desired results would really be helpful.
– Gordon Linoff
Nov 28 '18 at 16:00
The sample data and results are in the result set above the query. It doesn't have headings but the first date is the start date, the second date is the end date and the number is the number of 01/04/xx dates crossed. I should have put it in a neat table sorry.
– Matt Hollands
Nov 28 '18 at 16:39
add a comment |
2
Sample data and desired results would really be helpful.
– Gordon Linoff
Nov 28 '18 at 16:00
The sample data and results are in the result set above the query. It doesn't have headings but the first date is the start date, the second date is the end date and the number is the number of 01/04/xx dates crossed. I should have put it in a neat table sorry.
– Matt Hollands
Nov 28 '18 at 16:39
2
2
Sample data and desired results would really be helpful.
– Gordon Linoff
Nov 28 '18 at 16:00
Sample data and desired results would really be helpful.
– Gordon Linoff
Nov 28 '18 at 16:00
The sample data and results are in the result set above the query. It doesn't have headings but the first date is the start date, the second date is the end date and the number is the number of 01/04/xx dates crossed. I should have put it in a neat table sorry.
– Matt Hollands
Nov 28 '18 at 16:39
The sample data and results are in the result set above the query. It doesn't have headings but the first date is the start date, the second date is the end date and the number is the number of 01/04/xx dates crossed. I should have put it in a neat table sorry.
– Matt Hollands
Nov 28 '18 at 16:39
add a comment |
1 Answer
1
active
oldest
votes
0
you could do something like this using outer apply to join to your period table
;with periods as (
select 1 as [period], '01/04/2011' as startdate
union all select 2 as [period], '01/04/2012'
union all select 3 as [period], '01/04/2013'
union all select 4 as [period], '01/04/2014'
union all select 5 as [period], '01/04/2015'
union all select 6 as [period], '01/04/2016'
union all select 7 as [period], '01/04/2017'
union all select 8 as [period], '01/04/2018'
union all select 9 as [period], '01/04/2019'
)
select *
from periods
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
you can pivot this data to see the period counts on a single row.
select c.Contract_ID,
max(case when p.[period] = 1 then c.Split_Count end) as period_1,
max(case when p.[period] = 2 then c.Split_Count end) as period_2,
max(case when p.[period] = 3 then c.Split_Count end) as period_3,
max(case when p.[period] = 4 then c.Split_Count end) as period_4,
max(case when p.[period] = 5 then c.Split_Count end) as period_5,
max(case when p.[period] = 6 then c.Split_Count end) as period_6,
max(case when p.[period] = 7 then c.Split_Count end) as period_7,
max(case when p.[period] = 8 then c.Split_Count end) as period_8,
max(case when p.[period] = 9 then c.Split_Count end) as period_9
from [periods] p
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
or just get the total sum for the contract.
select c.Contract_ID,
sum(Split_Count) as Split_Count
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
answered Nov 28 '18 at 16:12
JamieD77JamieD77
12k1920
Thanks for your response. The solution I went for in the end was to have a separate Calendar table with a column for the periods. Subtracting the start period from the end period gives the number of changes.
– Matt Hollands
Dec 5 '18 at 8:44
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53523470%2fcount-number-of-times-a-specific-day-date-occurs-between-two-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
you could do something like this using outer apply to join to your period table
;with periods as (
select 1 as [period], '01/04/2011' as startdate
union all select 2 as [period], '01/04/2012'
union all select 3 as [period], '01/04/2013'
union all select 4 as [period], '01/04/2014'
union all select 5 as [period], '01/04/2015'
union all select 6 as [period], '01/04/2016'
union all select 7 as [period], '01/04/2017'
union all select 8 as [period], '01/04/2018'
union all select 9 as [period], '01/04/2019'
)
select *
from periods
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
you can pivot this data to see the period counts on a single row.
select c.Contract_ID,
max(case when p.[period] = 1 then c.Split_Count end) as period_1,
max(case when p.[period] = 2 then c.Split_Count end) as period_2,
max(case when p.[period] = 3 then c.Split_Count end) as period_3,
max(case when p.[period] = 4 then c.Split_Count end) as period_4,
max(case when p.[period] = 5 then c.Split_Count end) as period_5,
max(case when p.[period] = 6 then c.Split_Count end) as period_6,
max(case when p.[period] = 7 then c.Split_Count end) as period_7,
max(case when p.[period] = 8 then c.Split_Count end) as period_8,
max(case when p.[period] = 9 then c.Split_Count end) as period_9
from [periods] p
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
or just get the total sum for the contract.
select c.Contract_ID,
sum(Split_Count) as Split_Count
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
answered Nov 28 '18 at 16:12
JamieD77JamieD77
12k1920
Thanks for your response. The solution I went for in the end was to have a separate Calendar table with a column for the periods. Subtracting the start period from the end period gives the number of changes.
– Matt Hollands
Dec 5 '18 at 8:44
add a comment |
0
you could do something like this using outer apply to join to your period table
;with periods as (
select 1 as [period], '01/04/2011' as startdate
union all select 2 as [period], '01/04/2012'
union all select 3 as [period], '01/04/2013'
union all select 4 as [period], '01/04/2014'
union all select 5 as [period], '01/04/2015'
union all select 6 as [period], '01/04/2016'
union all select 7 as [period], '01/04/2017'
union all select 8 as [period], '01/04/2018'
union all select 9 as [period], '01/04/2019'
)
select *
from periods
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
you can pivot this data to see the period counts on a single row.
select c.Contract_ID,
max(case when p.[period] = 1 then c.Split_Count end) as period_1,
max(case when p.[period] = 2 then c.Split_Count end) as period_2,
max(case when p.[period] = 3 then c.Split_Count end) as period_3,
max(case when p.[period] = 4 then c.Split_Count end) as period_4,
max(case when p.[period] = 5 then c.Split_Count end) as period_5,
max(case when p.[period] = 6 then c.Split_Count end) as period_6,
max(case when p.[period] = 7 then c.Split_Count end) as period_7,
max(case when p.[period] = 8 then c.Split_Count end) as period_8,
max(case when p.[period] = 9 then c.Split_Count end) as period_9
from [periods] p
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
or just get the total sum for the contract.
select c.Contract_ID,
sum(Split_Count) as Split_Count
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
answered Nov 28 '18 at 16:12
JamieD77JamieD77
12k1920
Thanks for your response. The solution I went for in the end was to have a separate Calendar table with a column for the periods. Subtracting the start period from the end period gives the number of changes.
– Matt Hollands
Dec 5 '18 at 8:44
add a comment |
0
0
0
you could do something like this using outer apply to join to your period table
;with periods as (
select 1 as [period], '01/04/2011' as startdate
union all select 2 as [period], '01/04/2012'
union all select 3 as [period], '01/04/2013'
union all select 4 as [period], '01/04/2014'
union all select 5 as [period], '01/04/2015'
union all select 6 as [period], '01/04/2016'
union all select 7 as [period], '01/04/2017'
union all select 8 as [period], '01/04/2018'
union all select 9 as [period], '01/04/2019'
)
select *
from periods
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
you can pivot this data to see the period counts on a single row.
select c.Contract_ID,
max(case when p.[period] = 1 then c.Split_Count end) as period_1,
max(case when p.[period] = 2 then c.Split_Count end) as period_2,
max(case when p.[period] = 3 then c.Split_Count end) as period_3,
max(case when p.[period] = 4 then c.Split_Count end) as period_4,
max(case when p.[period] = 5 then c.Split_Count end) as period_5,
max(case when p.[period] = 6 then c.Split_Count end) as period_6,
max(case when p.[period] = 7 then c.Split_Count end) as period_7,
max(case when p.[period] = 8 then c.Split_Count end) as period_8,
max(case when p.[period] = 9 then c.Split_Count end) as period_9
from [periods] p
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
or just get the total sum for the contract.
select c.Contract_ID,
sum(Split_Count) as Split_Count
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
answered Nov 28 '18 at 16:12
JamieD77JamieD77
12k1920
you could do something like this using outer apply to join to your period table
;with periods as (
select 1 as [period], '01/04/2011' as startdate
union all select 2 as [period], '01/04/2012'
union all select 3 as [period], '01/04/2013'
union all select 4 as [period], '01/04/2014'
union all select 5 as [period], '01/04/2015'
union all select 6 as [period], '01/04/2016'
union all select 7 as [period], '01/04/2017'
union all select 8 as [period], '01/04/2018'
union all select 9 as [period], '01/04/2019'
)
select *
from periods
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
you can pivot this data to see the period counts on a single row.
select c.Contract_ID,
max(case when p.[period] = 1 then c.Split_Count end) as period_1,
max(case when p.[period] = 2 then c.Split_Count end) as period_2,
max(case when p.[period] = 3 then c.Split_Count end) as period_3,
max(case when p.[period] = 4 then c.Split_Count end) as period_4,
max(case when p.[period] = 5 then c.Split_Count end) as period_5,
max(case when p.[period] = 6 then c.Split_Count end) as period_6,
max(case when p.[period] = 7 then c.Split_Count end) as period_7,
max(case when p.[period] = 8 then c.Split_Count end) as period_8,
max(case when p.[period] = 9 then c.Split_Count end) as period_9
from [periods] p
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
or just get the total sum for the contract.
select c.Contract_ID,
sum(Split_Count) as Split_Count
outer apply ( select Contract_ID
, count(*) as Split_Count
from Contract
where startdate between Contract_Date_OLD and Contract_Date_NEW
group by Contract_ID) c
group by Contract_ID
answered Nov 28 '18 at 16:12
JamieD77JamieD77
12k1920
edited Nov 28 '18 at 16:20
answered Nov 28 '18 at 16:12
JamieD77JamieD77
12k1920
answered Nov 28 '18 at 16:12
JamieD77JamieD77
12k1920
answered Nov 28 '18 at 16:12
JamieD77JamieD77
12k1920
12k1920
Thanks for your response. The solution I went for in the end was to have a separate Calendar table with a column for the periods. Subtracting the start period from the end period gives the number of changes.
– Matt Hollands
Dec 5 '18 at 8:44
add a comment |
Thanks for your response. The solution I went for in the end was to have a separate Calendar table with a column for the periods. Subtracting the start period from the end period gives the number of changes.
– Matt Hollands
Dec 5 '18 at 8:44
Thanks for your response. The solution I went for in the end was to have a separate Calendar table with a column for the periods. Subtracting the start period from the end period gives the number of changes.
– Matt Hollands
Dec 5 '18 at 8:44
Thanks for your response. The solution I went for in the end was to have a separate Calendar table with a column for the periods. Subtracting the start period from the end period gives the number of changes.
– Matt Hollands
Dec 5 '18 at 8:44
add a comment |
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53523470%2fcount-number-of-times-a-specific-day-date-occurs-between-two-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
2
Sample data and desired results would really be helpful.
– Gordon Linoff
Nov 28 '18 at 16:00
The sample data and results are in the result set above the query. It doesn't have headings but the first date is the start date, the second date is the end date and the number is the number of 01/04/xx dates crossed. I should have put it in a neat table sorry.
– Matt Hollands
Nov 28 '18 at 16:39