Operand clash with date table and varchar date strings in cross join
This follows on from my previous question, but since I tried to simplify, I appear to have missed something Daily snapshot table using cte loop
I am trying to set up the below cross join between dates and an employee table. I need a daily count according to division and department, but the dates won't link easily since the dates are stored as varchar (not my choice, I can't change it).
I now have a date table that includes a style112 (yyyymmdd) key that I can link to the table, but there seems to be a failure somewhere along the joins.
I'm so close, but really am lost! I have never had to work with string dates and wouldn't wish it upon anyone.
DECLARE @DATESTART AS Date = '20180928';
DECLARE @DATEEND AS Date = '20181031';
WITH Dates AS (
SELECT @DATESTART AS Dte
UNION ALL
SELECT DTE + 1
FROM Dates
WHERE Dte <= @DATEEND )
SELECT
Dt.Dte
,CAST(DTC.Style112 AS VARCHAR)
,Emp.Division_Description
,Emp.Department_Description
,(SELECT
COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
E.[Start_Date] <= CAST(DTC.Style112 AS VARCHAR)
AND (E.Leaving_Date > CAST(DTC.Style112 AS VARCHAR)
OR E.Leaving_Date = '00000000')
) Counts
FROM Dates Dt
LEFT JOIN ASS_C_DateConversions DTC
ON DTC.[Date] = Dt.DtE
CROSS JOIN
(
SELECT DISTINCT
Division_Description
,Department_Description
FROM
ASS_D_EmpMaster_Live e
) Emp
OPTION (MAXRECURSION 1000)
Desired output:
Date
Dept1
Dept2
Dept3
20180901
25
231
154
20180902
23
232
154
sql
sql-server sqldatatypes cross-join ssms-2017 asked Nov 15 '18 at 17:03
eelyfaceeelyface
34
|
show 7 more comments
This follows on from my previous question, but since I tried to simplify, I appear to have missed something Daily snapshot table using cte loop
I am trying to set up the below cross join between dates and an employee table. I need a daily count according to division and department, but the dates won't link easily since the dates are stored as varchar (not my choice, I can't change it).
I now have a date table that includes a style112 (yyyymmdd) key that I can link to the table, but there seems to be a failure somewhere along the joins.
I'm so close, but really am lost! I have never had to work with string dates and wouldn't wish it upon anyone.
DECLARE @DATESTART AS Date = '20180928';
DECLARE @DATEEND AS Date = '20181031';
WITH Dates AS (
SELECT @DATESTART AS Dte
UNION ALL
SELECT DTE + 1
FROM Dates
WHERE Dte <= @DATEEND )
SELECT
Dt.Dte
,CAST(DTC.Style112 AS VARCHAR)
,Emp.Division_Description
,Emp.Department_Description
,(SELECT
COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
E.[Start_Date] <= CAST(DTC.Style112 AS VARCHAR)
AND (E.Leaving_Date > CAST(DTC.Style112 AS VARCHAR)
OR E.Leaving_Date = '00000000')
) Counts
FROM Dates Dt
LEFT JOIN ASS_C_DateConversions DTC
ON DTC.[Date] = Dt.DtE
CROSS JOIN
(
SELECT DISTINCT
Division_Description
,Department_Description
FROM
ASS_D_EmpMaster_Live e
) Emp
OPTION (MAXRECURSION 1000)
Desired output:
Date
Dept1
Dept2
Dept3
20180901
25
231
154
20180902
23
232
154
sql
sql-server sqldatatypes cross-join ssms-2017 asked Nov 15 '18 at 17:03
eelyfaceeelyface
34
Add some sample table data and the expected result - all as formatted text, not images.
– jarlh
Nov 15 '18 at 17:03
Which dbms are you using?
– jarlh
Nov 15 '18 at 17:04
@jarlh Sorry I went back to tag it as SSMS. And I'm working on results example, thanks.
– eelyface
Nov 15 '18 at 17:11
Can you try to simplify the problem? If I have to scroll, I just leave.
– jarlh
Nov 15 '18 at 17:13
Unfortunately simplifying is how I got here. I've taken out the unnecessary WHEREs, but I worry about any more.
– eelyface
Nov 15 '18 at 17:16
|
show 7 more comments
This follows on from my previous question, but since I tried to simplify, I appear to have missed something Daily snapshot table using cte loop
I am trying to set up the below cross join between dates and an employee table. I need a daily count according to division and department, but the dates won't link easily since the dates are stored as varchar (not my choice, I can't change it).
I now have a date table that includes a style112 (yyyymmdd) key that I can link to the table, but there seems to be a failure somewhere along the joins.
I'm so close, but really am lost! I have never had to work with string dates and wouldn't wish it upon anyone.
DECLARE @DATESTART AS Date = '20180928';
DECLARE @DATEEND AS Date = '20181031';
WITH Dates AS (
SELECT @DATESTART AS Dte
UNION ALL
SELECT DTE + 1
FROM Dates
WHERE Dte <= @DATEEND )
SELECT
Dt.Dte
,CAST(DTC.Style112 AS VARCHAR)
,Emp.Division_Description
,Emp.Department_Description
,(SELECT
COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
E.[Start_Date] <= CAST(DTC.Style112 AS VARCHAR)
AND (E.Leaving_Date > CAST(DTC.Style112 AS VARCHAR)
OR E.Leaving_Date = '00000000')
) Counts
FROM Dates Dt
LEFT JOIN ASS_C_DateConversions DTC
ON DTC.[Date] = Dt.DtE
CROSS JOIN
(
SELECT DISTINCT
Division_Description
,Department_Description
FROM
ASS_D_EmpMaster_Live e
) Emp
OPTION (MAXRECURSION 1000)
Desired output:
Date
Dept1
Dept2
Dept3
20180901
25
231
154
20180902
23
232
154
sql
sql-server sqldatatypes cross-join ssms-2017 asked Nov 15 '18 at 17:03
eelyfaceeelyface
34
This follows on from my previous question, but since I tried to simplify, I appear to have missed something Daily snapshot table using cte loop
I am trying to set up the below cross join between dates and an employee table. I need a daily count according to division and department, but the dates won't link easily since the dates are stored as varchar (not my choice, I can't change it).
I now have a date table that includes a style112 (yyyymmdd) key that I can link to the table, but there seems to be a failure somewhere along the joins.
I'm so close, but really am lost! I have never had to work with string dates and wouldn't wish it upon anyone.
DECLARE @DATESTART AS Date = '20180928';
DECLARE @DATEEND AS Date = '20181031';
WITH Dates AS (
SELECT @DATESTART AS Dte
UNION ALL
SELECT DTE + 1
FROM Dates
WHERE Dte <= @DATEEND )
SELECT
Dt.Dte
,CAST(DTC.Style112 AS VARCHAR)
,Emp.Division_Description
,Emp.Department_Description
,(SELECT
COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
E.[Start_Date] <= CAST(DTC.Style112 AS VARCHAR)
AND (E.Leaving_Date > CAST(DTC.Style112 AS VARCHAR)
OR E.Leaving_Date = '00000000')
) Counts
FROM Dates Dt
LEFT JOIN ASS_C_DateConversions DTC
ON DTC.[Date] = Dt.DtE
CROSS JOIN
(
SELECT DISTINCT
Division_Description
,Department_Description
FROM
ASS_D_EmpMaster_Live e
) Emp
OPTION (MAXRECURSION 1000)
Desired output:
Date
Dept1
Dept2
Dept3
20180901
25
231
154
20180902
23
232
154
sql
sql-server sqldatatypes cross-join ssms-2017 sql
sql-server sqldatatypes cross-join ssms-2017 asked Nov 15 '18 at 17:03
eelyfaceeelyface
34
asked Nov 15 '18 at 17:03
eelyfaceeelyface
34
edited Nov 15 '18 at 17:40
eelyface
asked Nov 15 '18 at 17:03
eelyfaceeelyface
34
asked Nov 15 '18 at 17:03
eelyfaceeelyface
34
asked Nov 15 '18 at 17:03
eelyfaceeelyface
34
34
Add some sample table data and the expected result - all as formatted text, not images.
– jarlh
Nov 15 '18 at 17:03
Which dbms are you using?
– jarlh
Nov 15 '18 at 17:04
@jarlh Sorry I went back to tag it as SSMS. And I'm working on results example, thanks.
– eelyface
Nov 15 '18 at 17:11
Can you try to simplify the problem? If I have to scroll, I just leave.
– jarlh
Nov 15 '18 at 17:13
Unfortunately simplifying is how I got here. I've taken out the unnecessary WHEREs, but I worry about any more.
– eelyface
Nov 15 '18 at 17:16
|
show 7 more comments
Add some sample table data and the expected result - all as formatted text, not images.
– jarlh
Nov 15 '18 at 17:03
Which dbms are you using?
– jarlh
Nov 15 '18 at 17:04
@jarlh Sorry I went back to tag it as SSMS. And I'm working on results example, thanks.
– eelyface
Nov 15 '18 at 17:11
Can you try to simplify the problem? If I have to scroll, I just leave.
– jarlh
Nov 15 '18 at 17:13
Unfortunately simplifying is how I got here. I've taken out the unnecessary WHEREs, but I worry about any more.
– eelyface
Nov 15 '18 at 17:16
Add some sample table data and the expected result - all as formatted text, not images.
– jarlh
Nov 15 '18 at 17:03
Add some sample table data and the expected result - all as formatted text, not images.
– jarlh
Nov 15 '18 at 17:03
Which dbms are you using?
– jarlh
Nov 15 '18 at 17:04
Which dbms are you using?
– jarlh
Nov 15 '18 at 17:04
@jarlh Sorry I went back to tag it as SSMS. And I'm working on results example, thanks.
– eelyface
Nov 15 '18 at 17:11
@jarlh Sorry I went back to tag it as SSMS. And I'm working on results example, thanks.
– eelyface
Nov 15 '18 at 17:11
Can you try to simplify the problem? If I have to scroll, I just leave.
– jarlh
Nov 15 '18 at 17:13
Can you try to simplify the problem? If I have to scroll, I just leave.
– jarlh
Nov 15 '18 at 17:13
Unfortunately simplifying is how I got here. I've taken out the unnecessary WHEREs, but I worry about any more.
– eelyface
Nov 15 '18 at 17:16
Unfortunately simplifying is how I got here. I've taken out the unnecessary WHEREs, but I worry about any more.
– eelyface
Nov 15 '18 at 17:16
|
show 7 more comments
1 Answer
1
active
oldest
votes
I don't think you need the conversion table at all and I would remove it. And I believe the subquery should look like this:
SELECT COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
CAST(E.Start_Date AS DATE) <= Dt.Dte
AND (CAST(E.Leaving_Date AS DATE) > Dt.Dte OR E.Leaving_Date = '00000000')
answered Nov 15 '18 at 17:58
shawnt00shawnt00
9,94421018
Unfortunately I get "Conversion failed when converting date and/or time from character string" when I run the above and "Operand type clash: date is incompatible with int" if I remove quotes around the "dates".
– eelyface
Nov 16 '18 at 10:03
Maybe usetry_parse()on the leaving date conversion?
– shawnt00
Nov 16 '18 at 10:36
NULL result, unfortunately!
– eelyface
Nov 16 '18 at 11:32
@eelyface What do these dates look like?
– shawnt00
Nov 16 '18 at 14:55
1
Thanks for this. This combined with excluding anything before 01/01/2017 (some sort of faulty data) has solve the issue. Thank you for your help; I would not have got there otherwise.
– eelyface
Nov 21 '18 at 17:24
|
show 4 more comments
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
});
}
});
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%2f53324519%2foperand-clash-with-date-table-and-varchar-date-strings-in-cross-join%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
I don't think you need the conversion table at all and I would remove it. And I believe the subquery should look like this:
SELECT COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
CAST(E.Start_Date AS DATE) <= Dt.Dte
AND (CAST(E.Leaving_Date AS DATE) > Dt.Dte OR E.Leaving_Date = '00000000')
answered Nov 15 '18 at 17:58
shawnt00shawnt00
9,94421018
Unfortunately I get "Conversion failed when converting date and/or time from character string" when I run the above and "Operand type clash: date is incompatible with int" if I remove quotes around the "dates".
– eelyface
Nov 16 '18 at 10:03
Maybe usetry_parse()on the leaving date conversion?
– shawnt00
Nov 16 '18 at 10:36
NULL result, unfortunately!
– eelyface
Nov 16 '18 at 11:32
@eelyface What do these dates look like?
– shawnt00
Nov 16 '18 at 14:55
1
Thanks for this. This combined with excluding anything before 01/01/2017 (some sort of faulty data) has solve the issue. Thank you for your help; I would not have got there otherwise.
– eelyface
Nov 21 '18 at 17:24
|
show 4 more comments
I don't think you need the conversion table at all and I would remove it. And I believe the subquery should look like this:
SELECT COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
CAST(E.Start_Date AS DATE) <= Dt.Dte
AND (CAST(E.Leaving_Date AS DATE) > Dt.Dte OR E.Leaving_Date = '00000000')
answered Nov 15 '18 at 17:58
shawnt00shawnt00
9,94421018
Unfortunately I get "Conversion failed when converting date and/or time from character string" when I run the above and "Operand type clash: date is incompatible with int" if I remove quotes around the "dates".
– eelyface
Nov 16 '18 at 10:03
Maybe usetry_parse()on the leaving date conversion?
– shawnt00
Nov 16 '18 at 10:36
NULL result, unfortunately!
– eelyface
Nov 16 '18 at 11:32
@eelyface What do these dates look like?
– shawnt00
Nov 16 '18 at 14:55
1
Thanks for this. This combined with excluding anything before 01/01/2017 (some sort of faulty data) has solve the issue. Thank you for your help; I would not have got there otherwise.
– eelyface
Nov 21 '18 at 17:24
|
show 4 more comments
I don't think you need the conversion table at all and I would remove it. And I believe the subquery should look like this:
SELECT COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
CAST(E.Start_Date AS DATE) <= Dt.Dte
AND (CAST(E.Leaving_Date AS DATE) > Dt.Dte OR E.Leaving_Date = '00000000')
answered Nov 15 '18 at 17:58
shawnt00shawnt00
9,94421018
I don't think you need the conversion table at all and I would remove it. And I believe the subquery should look like this:
SELECT COUNT(*)
FROM ASS_D_EmpMaster_Live E
WHERE
CAST(E.Start_Date AS DATE) <= Dt.Dte
AND (CAST(E.Leaving_Date AS DATE) > Dt.Dte OR E.Leaving_Date = '00000000')
answered Nov 15 '18 at 17:58
shawnt00shawnt00
9,94421018
answered Nov 15 '18 at 17:58
shawnt00shawnt00
9,94421018
answered Nov 15 '18 at 17:58
shawnt00shawnt00
9,94421018
answered Nov 15 '18 at 17:58
shawnt00shawnt00
9,94421018
9,94421018
Unfortunately I get "Conversion failed when converting date and/or time from character string" when I run the above and "Operand type clash: date is incompatible with int" if I remove quotes around the "dates".
– eelyface
Nov 16 '18 at 10:03
Maybe usetry_parse()on the leaving date conversion?
– shawnt00
Nov 16 '18 at 10:36
NULL result, unfortunately!
– eelyface
Nov 16 '18 at 11:32
@eelyface What do these dates look like?
– shawnt00
Nov 16 '18 at 14:55
1
Thanks for this. This combined with excluding anything before 01/01/2017 (some sort of faulty data) has solve the issue. Thank you for your help; I would not have got there otherwise.
– eelyface
Nov 21 '18 at 17:24
|
show 4 more comments
Unfortunately I get "Conversion failed when converting date and/or time from character string" when I run the above and "Operand type clash: date is incompatible with int" if I remove quotes around the "dates".
– eelyface
Nov 16 '18 at 10:03
Maybe usetry_parse()on the leaving date conversion?
– shawnt00
Nov 16 '18 at 10:36
NULL result, unfortunately!
– eelyface
Nov 16 '18 at 11:32
@eelyface What do these dates look like?
– shawnt00
Nov 16 '18 at 14:55
1
Thanks for this. This combined with excluding anything before 01/01/2017 (some sort of faulty data) has solve the issue. Thank you for your help; I would not have got there otherwise.
– eelyface
Nov 21 '18 at 17:24
Unfortunately I get "Conversion failed when converting date and/or time from character string" when I run the above and "Operand type clash: date is incompatible with int" if I remove quotes around the "dates".
– eelyface
Nov 16 '18 at 10:03
Unfortunately I get "Conversion failed when converting date and/or time from character string" when I run the above and "Operand type clash: date is incompatible with int" if I remove quotes around the "dates".
– eelyface
Nov 16 '18 at 10:03
Maybe use
try_parse() on the leaving date conversion?– shawnt00
Nov 16 '18 at 10:36
Maybe use
try_parse() on the leaving date conversion?– shawnt00
Nov 16 '18 at 10:36
NULL result, unfortunately!
– eelyface
Nov 16 '18 at 11:32
NULL result, unfortunately!
– eelyface
Nov 16 '18 at 11:32
@eelyface What do these dates look like?
– shawnt00
Nov 16 '18 at 14:55
@eelyface What do these dates look like?
– shawnt00
Nov 16 '18 at 14:55
1
1
Thanks for this. This combined with excluding anything before 01/01/2017 (some sort of faulty data) has solve the issue. Thank you for your help; I would not have got there otherwise.
– eelyface
Nov 21 '18 at 17:24
Thanks for this. This combined with excluding anything before 01/01/2017 (some sort of faulty data) has solve the issue. Thank you for your help; I would not have got there otherwise.
– eelyface
Nov 21 '18 at 17:24
|
show 4 more comments
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.
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%2f53324519%2foperand-clash-with-date-table-and-varchar-date-strings-in-cross-join%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
Add some sample table data and the expected result - all as formatted text, not images.
– jarlh
Nov 15 '18 at 17:03
Which dbms are you using?
– jarlh
Nov 15 '18 at 17:04
@jarlh Sorry I went back to tag it as SSMS. And I'm working on results example, thanks.
– eelyface
Nov 15 '18 at 17:11
Can you try to simplify the problem? If I have to scroll, I just leave.
– jarlh
Nov 15 '18 at 17:13
Unfortunately simplifying is how I got here. I've taken out the unnecessary WHEREs, but I worry about any more.
– eelyface
Nov 15 '18 at 17:16