# Get all records inside a given date

I need to get all the records that are inside a given date. What I have now is giving me only one of them randomly every time I run my query. Any suggestions on how to fix that?

``````SELECT table1.Number AS Number,
SUM(table2.record) AS Record
FROM table1
INNER JOIN table2
ON table1.Code = table2.Code
GROUP BY Number, table1.myDate
HAVING table1.myDate >= DATEADD(day, DATEDIFF(day, 0, MAX(table1.myDate)), 0)
AND table1.myDate < DATEADD(day, DATEDIFF(day, 0, MAX(table1.myDate)), 1)
``````

Sample Data:

``````Record | Code   | myDate
-------|--------|-------
1      | 001    | 12/31/2018
1      | 001    | 12/31/2018
2      | 001    | 12/31/2018
3      | 001    | 12/31/2018
1      | 002    | 01/28/2019
3      | 002    | 01/28/2019
``````

I would expect to get for the date 12/31/2018 this result:

1 + 1 + 2 + 3 = 7

And for the date 01/28/2019 this result:

1 + 3 = 4

But right now all I'm getting from the date 12/31/2018, for example, is only one of the Records, not the SUM of all of them. And sometimes is the Record 1, sometimes is the Record 2 and other times is the Record 3. It changes every time I run the Query. Same thing happens with any other date.

• Hi eva - just confirming the query you are trying to write. You want the Numbers and Sum(Records) for all rows, where the myDate value is between table1.myDate and the day after table1.myDate? If this is not the case, can you please explain your use case further? – Cameron Robert Sep 23 '19 at 8:14
• Hi @CameronRobert, yes I need the SUM of all the records (all of them, even if they are repeated several times, I need to know the actual total of all of them) inside a given date. In this case "myDate" is a precise day, a 24h period, that I get from table1. – eva Sep 23 '19 at 11:01
• Hello @Eva, I think that the problem is with the date. There will be no record after the MAX(table1.myDate) as its the same date as the first operand in the '>=' operator. You wrote that only one record is being retrieved, can it be that the record is the last one and fullfils the 'equals' operation? – SebOB Sep 23 '19 at 11:15
• Hi @SebOB, as I specified in the question, unfortunately, there is not a particular record that my query is giving me, it changes randomly every time I run it. And it takes only the records inside the date I want it to take, so that's good. It just doesn't SUM all the records, but presents a random one every time. Hope that clarifies your doubts. – eva Sep 23 '19 at 11:48
• Please update your question with a sample of the data in table1 and table2, along with the desired output. – Adam Spriggs Sep 23 '19 at 11:52

If it's just summing column values of the records between two dates, here's a simpler approach:

``````select
t1.myDate
, t1.code
, sum(t2.record) record
from table1 t1
inner join table2 t2 on t1.code = t2.code
where t1.mydate >= convert(date, getDate()-1)
and t1.myDate < convert(date, getDate())
group by
t1.myDate
, t1.code
``````

This just sums the `t2.record` values by `t1.date` and `t1.code` for records yesterday.

• included `myDate` in group
• Your join to `table2` makes your query dependent on matches. You haven't provided enough information about `table2` or the desired output. Hard to help without those bits. – Adam Spriggs Sep 24 '19 at 11:34