Partitioning by dates

Question

Partitioning by dates

Andy Soden 20

Hi,

Looking for some pointers as this seems to have me stumped.

I've a dataset of URN, Dates (From and To) related to the URN.

In attempting to partition by the URN and From date to create a Row_Num per iteration of of URN and From date combinations im seeing an incorrect result.

Current approach is below.. The date is in DateTime format, I've previously toyed with the format of the field i.e. Convert/Cast as Date to remove time element. However the output still shows URN and Start date grouping where they should be partitioned together as having multiple row numbers.

User's image

Thank you all.

Select T.*,  
    ROW_NUMBER() OVER(PARTITION BY URN, StartDate
      ORDER BY StartDate AS Iteration
from T

Accepted answer

1 additional answer

Your answer

Answer 1

LiHongMSFT-4306 31,571

Hi @Andy Soden

However the output still shows URN and Start date grouping where they should be partitioned together as having multiple row numbers.

Row_number is not an aggregate function; it just gives you the row numbers of different partitions.

I guess you need Count here.

Select URN, StartDate,COUNT(*) AS Iteration
From T
Group by URN, StartDate

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Andy Soden 20 Reputation points

2023-08-04T08:25:33.9533333+00:00

Thanks @LiHongMSFT-4306 - After the initial answer, quickly worked out the error of my ways in assuming SQL would group by the URN and Start Date combination to Row_Number. Have since had more coffee and realised the URN and Start still have multiple rows and therefore will consecutively count all rows in that partition. I've now managed to standardise by creating a distinct view of the URN and start, applying the row_num to this and then re-applying back to my main dataset as a lookup.

Answer 2

Andy 0

If the output you want is the output you show in your screenshot, then converting the StartDate to date in your partition function will give you the desired result.

Select t.urn, convert(date, startdate) as startdate, ROW_NUMBER() OVER(PARTITION BY URN, convert(date,StartDate) ORDER BY StartDate) AS Iteration
from t

User's image

you don't have to use the first convert if you want to have the datetime fully displayed.

If you're actually looking to figure out how many iterations where done for a certain URN on a certain day, then the following code will do that for you:

select t.urn, convert(date, startdate) as date, count(*) as DailyIterations from t group by convert(date, startdate), t.urn

User's image

Andy Soden 20 Reputation points

2023-08-04T08:26:47.8566667+00:00

Thanks for your help, wasn't directly what I needed, however it kicked the gears into touch to highlight where I had taken a wrong turn.

Share via

Partitioning by dates

1 additional answer

Your answer