SELECT shows different amount of rows [on hold]












0














I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date



INSERT MYDB.MARKET_DATA(
MARKET_DATE,
CURRENCY_BASE,
CURRENCY_TERM,
PERIOD,
MID)
(
SELECT
DATEADD(hh, 6, GETDATE()),
MARKET_DATA.CURRENCY_BASE,
MARKET_DATA.CURRENCY_TERM,
MARKET_DATA.PERIOD,
MARKET_DATA.MID
FROM MYDB.MARKET_DATA with (nolock)
WHERE cast(MARKET_DATE as date)=cast (getDate() as date)
)


We also check that the right number of rows are copied by looking at the number of rows for the last 7 days



SELECT count(*)
,CAST(market_date AS DATE)
FROM mydb.market_data
WHERE market_date > dateadd(day, - 7, getdate())
GROUP BY CAST(market_date AS DATE)
ORDER BY CAST(market_date AS DATE) DESC


We ran this SELECT query twice today. At 10am in the morning it returned 517 rows for today, and at 1pm it returns 516 rows for today.



What could possibly account for this discrepancy?




  1. There was no user interaction with this table today (other than running the two SELECT's)

  2. There is no with (nolock) on the SELECT query

  3. Note that there is a with (nolock) on the INSERT/SELECT, because the rows are updated throughout the day with latest information, and I don't want the INSERT/SELECT to fail on any rows that are being updated at the precise moment that it is being executed

  4. There is no stored procedure or job to delete any rows










share|improve this question















put on hold as off-topic by Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White 13 hours ago


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White

If this question can be reworded to fit the rules in the help center, please edit the question.









  • 1




    Have you tried to cast getdate() as date?
    – McNets
    yesterday










  • No. But which getDate() are you referring to - the one in the INSERT/SELECT or the one in the SELECT?
    – gordon613
    yesterday










  • I suggest you list out the 516 rows with a suitable order by and inspect them and your solution may become clearer.
    – Nick.McDermaid
    yesterday
















0














I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date



INSERT MYDB.MARKET_DATA(
MARKET_DATE,
CURRENCY_BASE,
CURRENCY_TERM,
PERIOD,
MID)
(
SELECT
DATEADD(hh, 6, GETDATE()),
MARKET_DATA.CURRENCY_BASE,
MARKET_DATA.CURRENCY_TERM,
MARKET_DATA.PERIOD,
MARKET_DATA.MID
FROM MYDB.MARKET_DATA with (nolock)
WHERE cast(MARKET_DATE as date)=cast (getDate() as date)
)


We also check that the right number of rows are copied by looking at the number of rows for the last 7 days



SELECT count(*)
,CAST(market_date AS DATE)
FROM mydb.market_data
WHERE market_date > dateadd(day, - 7, getdate())
GROUP BY CAST(market_date AS DATE)
ORDER BY CAST(market_date AS DATE) DESC


We ran this SELECT query twice today. At 10am in the morning it returned 517 rows for today, and at 1pm it returns 516 rows for today.



What could possibly account for this discrepancy?




  1. There was no user interaction with this table today (other than running the two SELECT's)

  2. There is no with (nolock) on the SELECT query

  3. Note that there is a with (nolock) on the INSERT/SELECT, because the rows are updated throughout the day with latest information, and I don't want the INSERT/SELECT to fail on any rows that are being updated at the precise moment that it is being executed

  4. There is no stored procedure or job to delete any rows










share|improve this question















put on hold as off-topic by Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White 13 hours ago


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White

If this question can be reworded to fit the rules in the help center, please edit the question.









  • 1




    Have you tried to cast getdate() as date?
    – McNets
    yesterday










  • No. But which getDate() are you referring to - the one in the INSERT/SELECT or the one in the SELECT?
    – gordon613
    yesterday










  • I suggest you list out the 516 rows with a suitable order by and inspect them and your solution may become clearer.
    – Nick.McDermaid
    yesterday














0












0








0







I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date



INSERT MYDB.MARKET_DATA(
MARKET_DATE,
CURRENCY_BASE,
CURRENCY_TERM,
PERIOD,
MID)
(
SELECT
DATEADD(hh, 6, GETDATE()),
MARKET_DATA.CURRENCY_BASE,
MARKET_DATA.CURRENCY_TERM,
MARKET_DATA.PERIOD,
MARKET_DATA.MID
FROM MYDB.MARKET_DATA with (nolock)
WHERE cast(MARKET_DATE as date)=cast (getDate() as date)
)


We also check that the right number of rows are copied by looking at the number of rows for the last 7 days



SELECT count(*)
,CAST(market_date AS DATE)
FROM mydb.market_data
WHERE market_date > dateadd(day, - 7, getdate())
GROUP BY CAST(market_date AS DATE)
ORDER BY CAST(market_date AS DATE) DESC


We ran this SELECT query twice today. At 10am in the morning it returned 517 rows for today, and at 1pm it returns 516 rows for today.



What could possibly account for this discrepancy?




  1. There was no user interaction with this table today (other than running the two SELECT's)

  2. There is no with (nolock) on the SELECT query

  3. Note that there is a with (nolock) on the INSERT/SELECT, because the rows are updated throughout the day with latest information, and I don't want the INSERT/SELECT to fail on any rows that are being updated at the precise moment that it is being executed

  4. There is no stored procedure or job to delete any rows










share|improve this question















I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date



INSERT MYDB.MARKET_DATA(
MARKET_DATE,
CURRENCY_BASE,
CURRENCY_TERM,
PERIOD,
MID)
(
SELECT
DATEADD(hh, 6, GETDATE()),
MARKET_DATA.CURRENCY_BASE,
MARKET_DATA.CURRENCY_TERM,
MARKET_DATA.PERIOD,
MARKET_DATA.MID
FROM MYDB.MARKET_DATA with (nolock)
WHERE cast(MARKET_DATE as date)=cast (getDate() as date)
)


We also check that the right number of rows are copied by looking at the number of rows for the last 7 days



SELECT count(*)
,CAST(market_date AS DATE)
FROM mydb.market_data
WHERE market_date > dateadd(day, - 7, getdate())
GROUP BY CAST(market_date AS DATE)
ORDER BY CAST(market_date AS DATE) DESC


We ran this SELECT query twice today. At 10am in the morning it returned 517 rows for today, and at 1pm it returns 516 rows for today.



What could possibly account for this discrepancy?




  1. There was no user interaction with this table today (other than running the two SELECT's)

  2. There is no with (nolock) on the SELECT query

  3. Note that there is a with (nolock) on the INSERT/SELECT, because the rows are updated throughout the day with latest information, and I don't want the INSERT/SELECT to fail on any rows that are being updated at the precise moment that it is being executed

  4. There is no stored procedure or job to delete any rows







sql-server t-sql sql-server-2014






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited yesterday







gordon613

















asked yesterday









gordon613gordon613

1366




1366




put on hold as off-topic by Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White 13 hours ago


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White

If this question can be reworded to fit the rules in the help center, please edit the question.




put on hold as off-topic by Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White 13 hours ago


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, Sean Gallardy, mustaccio, Mikael Eriksson, Paul White

If this question can be reworded to fit the rules in the help center, please edit the question.








  • 1




    Have you tried to cast getdate() as date?
    – McNets
    yesterday










  • No. But which getDate() are you referring to - the one in the INSERT/SELECT or the one in the SELECT?
    – gordon613
    yesterday










  • I suggest you list out the 516 rows with a suitable order by and inspect them and your solution may become clearer.
    – Nick.McDermaid
    yesterday














  • 1




    Have you tried to cast getdate() as date?
    – McNets
    yesterday










  • No. But which getDate() are you referring to - the one in the INSERT/SELECT or the one in the SELECT?
    – gordon613
    yesterday










  • I suggest you list out the 516 rows with a suitable order by and inspect them and your solution may become clearer.
    – Nick.McDermaid
    yesterday








1




1




Have you tried to cast getdate() as date?
– McNets
yesterday




Have you tried to cast getdate() as date?
– McNets
yesterday












No. But which getDate() are you referring to - the one in the INSERT/SELECT or the one in the SELECT?
– gordon613
yesterday




No. But which getDate() are you referring to - the one in the INSERT/SELECT or the one in the SELECT?
– gordon613
yesterday












I suggest you list out the 516 rows with a suitable order by and inspect them and your solution may become clearer.
– Nick.McDermaid
yesterday




I suggest you list out the 516 rows with a suitable order by and inspect them and your solution may become clearer.
– Nick.McDermaid
yesterday










1 Answer
1






active

oldest

votes


















3














What you're running into is probably a misunderstanding with how DATEADD works.



If you run these queries, you'll see what I mean:



DECLARE @getdate DATETIME = '2019-01-06 10:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

SET @getdate = '2019-01-06 01:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;


Subtracting 7 days doesn't bring you to the start of seven days ago. It brings you to 7 days ago, but to the hour of the datetime value you passed in.



If you need to flatten a datetime to the beginning of the day, you need to do something like this.



SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), 0);



You can check out my answer here for more information:




  • How does date math work in SQL Server?






share|improve this answer





















  • Thank you @Erik Darling for your answer! May I suggest, however, that my omission to flatten the datetime of 7 days ago, would not affect the problem with the rows for today which are being grouped anyway by a (cast getDate() as Date) which would flatten the dates correctly. At worst, the query would omit some records from 7 days ago.
    – gordon613
    yesterday










  • @gordon613 The issue is with your where clause.
    – Erik Darling
    yesterday












  • I am sorry I don't follow. Even if I corrected my WHERE clause to WHERE market_date > DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), -7) the records that would be returned for the last six days would be identical to before the correction. The only change would be those records seven days ago. And my problem was with today's records
    – gordon613
    yesterday






  • 4




    @gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results.
    – Erik Darling
    yesterday










  • This kind of error only happens every couple of months or so, (although this is the first time I have seen the manifestation described in my question) and it is too late now for me to post the incorrect results, as they have been somehow corrected - as per my question - I will try and catch it next time!
    – gordon613
    yesterday


















1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









3














What you're running into is probably a misunderstanding with how DATEADD works.



If you run these queries, you'll see what I mean:



DECLARE @getdate DATETIME = '2019-01-06 10:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

SET @getdate = '2019-01-06 01:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;


Subtracting 7 days doesn't bring you to the start of seven days ago. It brings you to 7 days ago, but to the hour of the datetime value you passed in.



If you need to flatten a datetime to the beginning of the day, you need to do something like this.



SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), 0);



You can check out my answer here for more information:




  • How does date math work in SQL Server?






share|improve this answer





















  • Thank you @Erik Darling for your answer! May I suggest, however, that my omission to flatten the datetime of 7 days ago, would not affect the problem with the rows for today which are being grouped anyway by a (cast getDate() as Date) which would flatten the dates correctly. At worst, the query would omit some records from 7 days ago.
    – gordon613
    yesterday










  • @gordon613 The issue is with your where clause.
    – Erik Darling
    yesterday












  • I am sorry I don't follow. Even if I corrected my WHERE clause to WHERE market_date > DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), -7) the records that would be returned for the last six days would be identical to before the correction. The only change would be those records seven days ago. And my problem was with today's records
    – gordon613
    yesterday






  • 4




    @gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results.
    – Erik Darling
    yesterday










  • This kind of error only happens every couple of months or so, (although this is the first time I have seen the manifestation described in my question) and it is too late now for me to post the incorrect results, as they have been somehow corrected - as per my question - I will try and catch it next time!
    – gordon613
    yesterday
















3














What you're running into is probably a misunderstanding with how DATEADD works.



If you run these queries, you'll see what I mean:



DECLARE @getdate DATETIME = '2019-01-06 10:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

SET @getdate = '2019-01-06 01:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;


Subtracting 7 days doesn't bring you to the start of seven days ago. It brings you to 7 days ago, but to the hour of the datetime value you passed in.



If you need to flatten a datetime to the beginning of the day, you need to do something like this.



SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), 0);



You can check out my answer here for more information:




  • How does date math work in SQL Server?






share|improve this answer





















  • Thank you @Erik Darling for your answer! May I suggest, however, that my omission to flatten the datetime of 7 days ago, would not affect the problem with the rows for today which are being grouped anyway by a (cast getDate() as Date) which would flatten the dates correctly. At worst, the query would omit some records from 7 days ago.
    – gordon613
    yesterday










  • @gordon613 The issue is with your where clause.
    – Erik Darling
    yesterday












  • I am sorry I don't follow. Even if I corrected my WHERE clause to WHERE market_date > DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), -7) the records that would be returned for the last six days would be identical to before the correction. The only change would be those records seven days ago. And my problem was with today's records
    – gordon613
    yesterday






  • 4




    @gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results.
    – Erik Darling
    yesterday










  • This kind of error only happens every couple of months or so, (although this is the first time I have seen the manifestation described in my question) and it is too late now for me to post the incorrect results, as they have been somehow corrected - as per my question - I will try and catch it next time!
    – gordon613
    yesterday














3












3








3






What you're running into is probably a misunderstanding with how DATEADD works.



If you run these queries, you'll see what I mean:



DECLARE @getdate DATETIME = '2019-01-06 10:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

SET @getdate = '2019-01-06 01:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;


Subtracting 7 days doesn't bring you to the start of seven days ago. It brings you to 7 days ago, but to the hour of the datetime value you passed in.



If you need to flatten a datetime to the beginning of the day, you need to do something like this.



SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), 0);



You can check out my answer here for more information:




  • How does date math work in SQL Server?






share|improve this answer












What you're running into is probably a misunderstanding with how DATEADD works.



If you run these queries, you'll see what I mean:



DECLARE @getdate DATETIME = '2019-01-06 10:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;

SET @getdate = '2019-01-06 01:00:00.000';

SELECT @getdate AS getdatevariable, DATEADD(DAY, -7, @getdate) AS getdateminus7;


Subtracting 7 days doesn't bring you to the start of seven days ago. It brings you to 7 days ago, but to the hour of the datetime value you passed in.



If you need to flatten a datetime to the beginning of the day, you need to do something like this.



SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), 0);



You can check out my answer here for more information:




  • How does date math work in SQL Server?







share|improve this answer












share|improve this answer



share|improve this answer










answered yesterday









Erik DarlingErik Darling

21.1k1263103




21.1k1263103












  • Thank you @Erik Darling for your answer! May I suggest, however, that my omission to flatten the datetime of 7 days ago, would not affect the problem with the rows for today which are being grouped anyway by a (cast getDate() as Date) which would flatten the dates correctly. At worst, the query would omit some records from 7 days ago.
    – gordon613
    yesterday










  • @gordon613 The issue is with your where clause.
    – Erik Darling
    yesterday












  • I am sorry I don't follow. Even if I corrected my WHERE clause to WHERE market_date > DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), -7) the records that would be returned for the last six days would be identical to before the correction. The only change would be those records seven days ago. And my problem was with today's records
    – gordon613
    yesterday






  • 4




    @gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results.
    – Erik Darling
    yesterday










  • This kind of error only happens every couple of months or so, (although this is the first time I have seen the manifestation described in my question) and it is too late now for me to post the incorrect results, as they have been somehow corrected - as per my question - I will try and catch it next time!
    – gordon613
    yesterday


















  • Thank you @Erik Darling for your answer! May I suggest, however, that my omission to flatten the datetime of 7 days ago, would not affect the problem with the rows for today which are being grouped anyway by a (cast getDate() as Date) which would flatten the dates correctly. At worst, the query would omit some records from 7 days ago.
    – gordon613
    yesterday










  • @gordon613 The issue is with your where clause.
    – Erik Darling
    yesterday












  • I am sorry I don't follow. Even if I corrected my WHERE clause to WHERE market_date > DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), -7) the records that would be returned for the last six days would be identical to before the correction. The only change would be those records seven days ago. And my problem was with today's records
    – gordon613
    yesterday






  • 4




    @gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results.
    – Erik Darling
    yesterday










  • This kind of error only happens every couple of months or so, (although this is the first time I have seen the manifestation described in my question) and it is too late now for me to post the incorrect results, as they have been somehow corrected - as per my question - I will try and catch it next time!
    – gordon613
    yesterday
















Thank you @Erik Darling for your answer! May I suggest, however, that my omission to flatten the datetime of 7 days ago, would not affect the problem with the rows for today which are being grouped anyway by a (cast getDate() as Date) which would flatten the dates correctly. At worst, the query would omit some records from 7 days ago.
– gordon613
yesterday




Thank you @Erik Darling for your answer! May I suggest, however, that my omission to flatten the datetime of 7 days ago, would not affect the problem with the rows for today which are being grouped anyway by a (cast getDate() as Date) which would flatten the dates correctly. At worst, the query would omit some records from 7 days ago.
– gordon613
yesterday












@gordon613 The issue is with your where clause.
– Erik Darling
yesterday






@gordon613 The issue is with your where clause.
– Erik Darling
yesterday














I am sorry I don't follow. Even if I corrected my WHERE clause to WHERE market_date > DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), -7) the records that would be returned for the last six days would be identical to before the correction. The only change would be those records seven days ago. And my problem was with today's records
– gordon613
yesterday




I am sorry I don't follow. Even if I corrected my WHERE clause to WHERE market_date > DATEADD(DAY, DATEDIFF(DAY, 0, @getdate), -7) the records that would be returned for the last six days would be identical to before the correction. The only change would be those records seven days ago. And my problem was with today's records
– gordon613
yesterday




4




4




@gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results.
– Erik Darling
yesterday




@gordon613 okay, cool, post the sample data that's giving you incorrect results, along with expected results.
– Erik Darling
yesterday












This kind of error only happens every couple of months or so, (although this is the first time I have seen the manifestation described in my question) and it is too late now for me to post the incorrect results, as they have been somehow corrected - as per my question - I will try and catch it next time!
– gordon613
yesterday




This kind of error only happens every couple of months or so, (although this is the first time I have seen the manifestation described in my question) and it is too late now for me to post the incorrect results, as they have been somehow corrected - as per my question - I will try and catch it next time!
– gordon613
yesterday



Popular posts from this blog

1300-talet

1300-talet

Display a custom attribute below product name in the front-end Magento 1.9.3.8