1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

-- db params

declare @currentDb varchar(32)
declare @currentID int

set @currentDb = DB_NAME()
set @currentID = DB_ID()

-- set datetime

DECLARE @selDate DATETIME
Declare @inpDate varchar(8)

Set @inpDate = 20100101 -- ##MyDate## for parametrised
set @selDate = Convert(datetime, @inpDate)

-- define arrays

declare @tagArray table (Tag varchar(32))
insert into @tagArray values ('hello'),('world');

declare @valArray table (Val int)
insert into @valArray values (1),(3),(4);

declare @recArray table (Ids int, Tag varchar(16))
insert into @recArray values (1, 'goody');

-- flatten arrays

declare @list varchar(32)
set @list = (Select STRING_AGG(Tag,',') from @tagArray)

-- build xml string

declare @XMLVar varchar(32)
set @XMLVar = (SELECT CAST((SELECT Tag /*as 'data()'*/ FROM @tagArray FOR XML PATH('')) AS VARCHAR(MAX)));

-- query zone

-- Select @list,@XMLVar


with sample as (
  select top 10000000 id,CreationDate,Tags,Body from posts p
  WHERE (0.20 >= CAST(CHECKSUM(NEWID(), id) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
     and 0.03 >= CAST(CHECKSUM(NEWID(), parentId) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
     -- and ViewCount % 3 = 1 and AnswerCount > 0 and LEN(body) % 7 = 1
     ) and Tags is not null

),

-- post processing

randomPartition AS (
select *,ROW_NUMBER() 
  over ( PARTITION by datepart(yyyy, [CreationDate])
  order BY (SELECT null)) -- or 2147483647+1 
        AS n from [sample])

-- grouping

/*select t.TagName, r.id
from Tags t inner join
     randomPartition r
     on r.Tags like '%' + t.TagName + '%'*/


select * from randomPartition

/*select -- datepart(yyyy, [CreationDate]) as [year],
       -- datepart(mm, [CreationDate]) as [month],
       cast(FORMAT( [CreationDate], 'yyyy-01-01' ) as date) AS 'Date',
       --round(
       count(*)
        --/1.0,2,1) 
         as freq
     
  from randomPartition 
-- where n % 10 = 1

group by FORMAT( [CreationDate], 'yyyy-01-01' ) --datepart(yyyy, [CreationDate]),datepart(mm, [CreationDate])
order by [date]

fork query

edited mar 2 23 jon.doe39273

created aug 10 22 random.sample