Soundscan

If any of you send files to nielsen for reporting cd sales, here is the format for the file that is sent. I may be the first to post this anywhere on the internet. how very exciting.


EAN DOWNLOAD DATA FORMAT

This is the format used by vendors who download data to the SoundScan system.

RECORDS/FIELDS CHARACTERS POSITION

STORE RETAIL SALES HEADER RECORD
Record Number (92) 2 1 - 2
Chain Number 5 3 - 7
Individual Store No. 5 8 - 12
Period End Date (YYMMDD) 6 13 - 18
Filler (Blanks) 2 19 - 20

STORE RETAIL SALES RECORD
Record Number (I3) 2 1 - 2
UPC Number of Selection 13 3 - 15
Position 3 = "0" if less the 13 digits
Positions 3-15 = EAN 13
Quantity Sold 5 16 - 20

STORE RETAIL SALES TRAILER RECORD
Record Number (94) 2 1 - 2
Number of Sales Records (I3's) 5 3 - 7
Number of Units Sold 7 8 - 14
Filler (Blanks) 6 15 - 20


20 Byte Records:
1 - 92 = Header Record per store
Multiple - I3 Data sales info records ( one for each EAN sold)
1 - 94 = Trailer Record with sum of items sold



FILE FORMAT - ASCII, DOS CR/LF, OEF AT END OF FILE

920090100010061029
I30000002900049-0001
I3000002900015900001
I3012342900106900001
I3123457994450200001
I3000007994455200001
94 5 4



List series locks in seat ranges

Aha!
I have rigged this one up using a sonic screwdriver, and it just might work.
This is like the single-event-lock-list rpt in a prev post, except for series locks:


CREATE procedure moa_series_lock_count_list (@series int, @season int, @scheme int)
as
declare @l table (event int, chair int, area int, row int, col int, vertical bit, rowname varchar(5), seatname varchar(5), sec varchar(5), ticktype int )

insert into @l (event, chair, area, row, col, vertical, rowname, seatname, sec, ticktype)
select slsseries, slschair, slsarea , ahrow, ahcol, 0, ahline, ahchair, left(ahshortarname,5), slsticktype
from moa_pe..serieslockedseats serieslockedseats with(nolock)
inner join moa_pe..areachair areachair with(nolock) on slschair = ahchaircode
where slsseries > 0 and slsticktype between 2000 and 3000
and slsseries in (select srcode from moa_pe..serieses sr with(nolock)
where (srcode = @series or @series = 0)
and (srseasons = @season or @season = 0)
and (srshowtype = @scheme or @scheme = 0) )
order by slsseries, slschair, slsarea, ahrow, ahcol

update @l set vertical = 1 where area in (select arcode from moa_pe..areas with(nolock) where arname like '%boxes%' )

declare @out table (event int, list varchar(255), row varchar(5), area int, cnt int, ticktype int, sec varchar(10) )

declare @ev int, @oldev int, @ar int, @oldar int, @row int, @oldrow int, @col int, @oldcol int
declare @sec varchar(5), @oldsec varchar(5) , @rowname varchar(5), @oldrowname varchar(5), @seat varchar(5), @oldseat varchar(5)
declare @ticktype int, @oldticktype int
declare @count int, @seatstr varchar(5000)



declare ch cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 0 order by event, area, row, col

open ch
fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col - 1
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @row <> @oldrow or @ticktype <> @oldticktype or @col <> @oldcol + 1)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close ch
deallocate ch


--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec

declare cv cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 1 order by event, area, col, row

open cv
fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row - 1
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @col <> @oldcol or @ticktype <> @oldticktype or @row <> @oldrow + 1 or @oldrowname <> @rowname)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close cv
deallocate cv

--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec



update @out set list = left(list,len(list)-1) where len(list) > 1
update @out set list = right(list,len(list)-1) where list like ',%'
select o.*, clrdescr, srdescr, arname
from @out as o inner join
moa_pe..colors c on clrstatus = o.ticktype inner join
moa_pe..serieses on srcode = o.event inner join
moa_pe..areas on arcode = o.area
order by srdescr, arname, sec, row, list


GO