February 9, 2004 at 8:04 am
Hi,
In SS 2000, I have a small table
Example of the Table:
| Area | Message | Store_Num |
| CN | Missing Sales as of 9:59am | BK2345 |
| CN | Missing Sales as of 9:59am | BK1234 |
| CN | Missing Sales as of 9:59am | BK9876 |
| CS | Missing Sales as of 9:59am | BK4567 |
| CS | Missing Sales as of 9:59am | BK1523 |
| CS | Missing Sales as of 9:59am | BK5423 |
| WI | Missing Sales as of 9:59am | BK6660 |
| WI | Missing Sales as of 9:59am | BK7435 |
This table changes every 15 minutes and has to output to a text file for populate the message on company's portal. Example of the output format:
| Missing Sales as of 9:59am: | |
| CN | BK2345 |
| BK1234 | |
| BK9876 | |
| CS | BK4567 |
| BK1523 | |
| BK5423 | |
| WI | BK6660 |
| BK7435 | |
How can I use T-SQL to have this done or I have to write a procedure?
Thank you in advance.
February 9, 2004 at 8:16 am
Since the ouptut consists of more than one resultset, you need a proc.
SELECT DISTINCT message
FROM Table -- provides the 'Missing sales' string
SELECT area, storenum
FROM Table
ORDER BY area -- provides the list
Above assumes there is only one unique message as per the example.
Any formatting should be done by the portal. (like trimming out consecutive area codes)
It's possible to do in T-SQL, but it requires a lot of hoop-jumping to do - not recommended if possible to avoid.
In order to create the result as a file, call the proc from OSQL, and use the -o parameter
=;o)
/Kenneth
February 9, 2004 at 8:43 am
You can use a cursor and a nested loop. Dress up the output by altering the PRINT statements.
-- Cursor variables...
declare @msg as varchar(50)
declare @area as char(2)
declare @store as char(6)
-- Work variables....
declare @lastmsg as varchar(50)
declare @lastarea as char(2)
declare csrMissing cursor for
Select Message, Area, Store_num
from Table3
order by Message, Area, Store_num
open csrMissing
fetch next from csrMissing into
@msg
,@area
,@store
while @@FETCH_STATUS = 0
begin
set @lastmsg = @msg
set @lastarea = NULL
print ' '
print @Msg
print ' '
while @lastmsg = @msg and @@FETCH_STATUS = 0
begin
if @area = @lastarea
begin
print ' ' + @store
end
else
begin
print @area + ' ' + @store
set @lastarea = @area
end
fetch next from csrMissing into
@msg
,@area
,@store
end
end
close csrMissing
deallocate csrMissing
February 9, 2004 at 9:11 am
Richard,
thank you. it works perfectly.
Jennifer
February 9, 2004 at 10:23 am
Or if you want your own sp:
CREATE Table DataTable (Area varchar(2), Message varchar(100), Store_Num varchar(8))
GO
Insert into DataTable (area, Message, Store_num) Values ('CN', 'Missing Sales as of 9:59am ', 'BK2345')
Insert into DataTable (area, Message, Store_num) Values ('CN', 'Missing Sales as of 9:59am ', 'BK1234')
Insert into DataTable (area, Message, Store_num) Values ('CN', 'Missing Sales as of 9:59am ', 'BK9876')
Insert into DataTable (area, Message, Store_num) Values ('CS', 'Missing Sales as of 9:59am ', 'BK4567')
Insert into DataTable (area, Message, Store_num) Values ('CS', 'Missing Sales as of 9:59am ', 'BK1523')
Insert into DataTable (area, Message, Store_num) Values ('CS', 'Missing Sales as of 9:59am ', 'BK5423')
Insert into DataTable (area, Message, Store_num) Values ('WI', 'Missing Sales as of 9:59am ', 'BK6660')
Insert into DataTable (area, Message, Store_num) Values ('WI', 'Missing Sales as of 9:59am ', 'BK7435')
Select Case When Rank = 1 Then Area Else ' ' END As AreaCode, Store_Num
FRom
(
Select Area, Store_Num, (Select Count(*) From DataTable t2 where T1.Area = T2.Area and T2.Store_Num <= T1.Store_Num) as Rank
From DataTable T1
Group By Area, Store_Num
) Q
Order By Area,Store_Num
* Noel
February 9, 2004 at 12:03 pm
Is it possible to write to a file from a stored procedure ? For example, I created a stored procedure by using Richard's script that will read data in a table using a cursor and then i want to write these results in a text file (.txt file)
Thank you
February 9, 2004 at 12:23 pm
Depending on if you have permission for xp_cmdshell you could alter Richards print statements to call xp_cmdshell and print the line into your file.
DECLARE @ctr int
, @Cmd nvarchar(255)
SET @ctr = 1
WHILE @ctr <= 20
BEGIN
SET @Cmd = 'echo This is line:' + CONVERT(nvarchar,@ctr) + ' >> c:\foo.txt'
EXEC xp_cmdshell @Cmd
SET @ctr = @ctr + 1
END
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
February 10, 2004 at 12:24 am
It would be far better to call the procedure from OSQL or ISQL with the -o parameter.
just my .02 of course...
=;o)
/Kenneth
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply