0

I am trying to validate an Id count. I have a table - Address_rankings with the following columns

  • Address - Nvarchar
  • Id - Int
  • Date created - DateTime

Sample data looks like the below:

Address ID Datecreated
Address1 1 2023-06-30 16:59:37.6800000
Address2 2 2023-06-30 18:10:20.3166667
Address3 3 2023-06-30 18:11:45.1700000
Address4 4 2023-06-30 18:11:45.1733333
Address5 5 2023-06-30 18:12:28.7400000

newly added/removed addresses:

Address ID Datecreated
Address6 6 2023-07-03 18:12:28.7400000 (added on 07/03)
Address10 10 2023-07-06 18:12:28.7400000 (removed on 07/06)

My problem is:

  • 06/30 - count of 100 places(ids)
  • 07/31 - 125 places (starting from 07/01 to 07/31 - the 125 place count(ids) includes new places are added and some of the places are removed and total id count is 125 on 07/31).

I am trying to find the id count of 100 places (06/30) + the new places added and minus the places that are removed from 07/01-07/31 so the final id count should be equal to 125 rows.

I wrote the below query, but count doesn't seem correct to me as it's displaying a large number:

DECLARE @TheDate Date = '2023-06-30'
DECLARE @TheDate2 Date = '2023-07-31'
SELECT count(Id)
FROM place_rankings
where datecreated >= @Date AND datecreated < @Date2

I don't understand how to write a query for the above condition. My desired result is to get the count of ids as of 07/31.

I also want to find out the new ids added during this time period by including the below subquery into the main query and declaring 2 variables:

DECLARE @DateNew Date = '2023-06-30'
DECLARE @Date2New Date = '2023-07-31'                                                                    
  and (exists (select null from Address_rankings b
    WHERE b.datecreated >= @DateNew AND b.datecreated < DATEADD(d, 1, @Date2New) 
AND b.Address IS NOT NULL)rn2 and rn1.id = rn2.id))

1 Answer 1

0

Note that '2023-07-31' is equal to '2023-07-31 00:00:00', so all the times of the last day are excluded with datecreated < @Date2, except for 00:00:00!

Solution: add one day as in

WHERE datecreated >= @Date AND datecreated < DATEADD(d, 1, @Date2)

But I am not sure what you mean by "minus the places that are removed". How do you see that places are removed? Do you have a column storing this status or are these dates stored in another table?

You can exclude the places where the address is null like this:

WHERE
    datecreated >= @Date AND datecreated < DATEADD(d, 1, @Date2) AND
    Address IS NOT NULL

If you want to exclude the NULL addresses from July only, you can do it like this

Add this declaration:

DECLARE @DateExcl Date = '2023-07-01'

condition:

SELECT count(a.Id)
FROM place_rankings a
WHERE
  a.datecreated >= @Date AND a.datecreated < DATEADD(d, 1, @Date2) AND
  (a.Address IS NOT NULL OR a.datecreated < @DateExcl) AND
  EXISTS (
    SELECT NULL FROM Address_rankings b
    WHERE
      b.datecreated >= @DateNew AND b.datecreated < DATEADD(d, 1, @Date2New) AND
      b.Address IS NOT NULL AND
      b.id = a.id
  )

Your attempt with rn1.id = rn2.id must be inside the sub-select and of course use the aliases you gave earlier, namely a and b.

2
  • Thanks! Olivier.
    – RMN
    Commented Aug 21, 2023 at 17:51
  • You are welcome. Also, since you are new to this site, I would like to inform you that you can upvote good answers and accept the answer that helped you the most by checking the tick mark next to the Answer. On this site an upvote or an accepted answer counts as a "thanks". Commented Aug 22, 2023 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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