0

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
3

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.

Updates

  • included myDate in group
  • This is giving me only the very last record of the day. – eva Sep 23 '19 at 13:25
  • Not sure I understand. Can you update your question with the desired output? – Adam Spriggs Sep 23 '19 at 15:53
  • Hi Adam, first of all, thank you for trying to help! The query you gave me is not working unfortunately, I tried both with group by myDate and without it. It doesn't give me any records at all to be honest. I didn't realize it yesterday because I didn't clear the records from my previous query so I thought it gave me only the last one, but in reality is not giving me anything at all as I said. – eva Sep 24 '19 at 6:59
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.