I'm new to SSIS and complex sql statements. I'm trying to combine my query that returns people with a date in the last month, but then take those results and get 5 truly random rows from that.
My query before I get the 5 random rows is:
SELECT
DISTINCT
isnull(dbo.fnRemovePatternFromString(p.Last_Name, '%[,-.'']%'), '') as [Last]
,isnull(dbo.fnRemovePatternFromString(p.First_Name, '%[,-.'']%'), '') as [First]
,isnull(dbo.fnRemovePatternFromString(p.Middle_Initial, '%[,-.'']%'), '') as [Middle]
,isnull(pf.Date_on_staff, '') as [Date on Staff]
,pf.Status_from_date
FROM person p
inner JOIN person_facilities pf ON p.Person_ID = pf.Person_ID
LEFT JOIN usr_FacultyMember fm ON p.Person_ID = fm.person_id
WHERE
pf.FacCode in ('s', 'H', 'E')
and
(
pf.Status_from_date >= (getdate()-31 )
and pf.Status_from_date < getdate()
)
That returns about 300 rows, but it will change each month.
From that result, I need to get 5 random rows out of the results. It can't be the top of the results, or the 5 rows at 1/5 of the results. I'd like to avoid creating a view to store the results in like this example.
Tablesample doesn't look like what I need since it's not random.
I'm trying to apply this example, since it seems random, but I need to control the number of rows in the output. This has a similar answer. This is the part I like from those 2 examples:
(abs(cast((binary_checksum(*) * rand()) as int)) % 100) <10
I'm not sure how to control the output so I only get 5 rows from that. I was thinking of creating a variable and storing what the 10 needs to be to return 5 rows, but I'm not sure how to do that. Any thoughts?
I was looking at variable for count of rows but I'm not sure what I'd do with it or how I'd apply it in my rand line.
I guess it doesn't have to be one sql query for this, but I'm still not sure how to do this in ssis. If I have to create a view, I can, but I'd rather not clutter my db with views.
Any help solving this complicated question would be greatly appreciated.
Aucun commentaire:
Enregistrer un commentaire