@echo off
setlocal EnableDelayedExpansion

:loop
rem Step 1: Loop through each store code in st.txt
for /f "usebackq delims=" %%i in ("st.txt") do (
    set "storeCode=%%i"
    echo Processing store code: !storeCode!
    pause

    rem Step 2: Transform store code to pth12345
    set "transformedCode=pth!storeCode!"
    echo Transformed store code to: !transformedCode!
    pause

    rem Step 3: Check if the pth12345 file exists
    if exist "!transformedCode!" (
        echo Found the file: !transformedCode!
        echo Skipping to next store code.
        pause
        goto :next
    )

    rem Step 4: Ping pth12345
    ping -n 1 "!transformedCode!" > nul 2>&1
    if errorlevel 1 (
        echo !transformedCode! is offline. Stopping for this store code.
        pause
        goto :next
    )

    rem Fetch yesterday's date using osql
    osql -UTRANSF -PFSNART -Sbgcpitsd-mn01 -dmaster -Q "select convert(varchar(10), getdate()-1, 120)" -h-1 -w150 > date.dat
    echo Fetched yesterday's date.
    pause

    rem Check if the date.dat file was created
    if not exist date.dat (
        echo Failed to retrieve date from the database. Exiting.
        pause
        goto :end
    )

    rem Read the date from the file and format it
    set /p date=<date.dat
    echo Date retrieved: %date%
    set day=%date:~0,10%   rem Get the full date string "YYYY-MM-DD"
    pause

    rem Extract components
    set yyyy=%day:~0,4%    rem Year
    set mm=%day:~5,2%      rem Month
    set dd=%day:~8,2%      rem Day
    echo Date components: Year=!yyyy!, Month=!mm!, Day=!dd!
    pause

    rem Construct the file names
    set "brandFile=BRAND_!mm!!dd!.!storeCode!"
    set "departmentFile=DEPARTMENT_!mm!!dd!.!storeCode!"
    set "productFile=PRODUCT_!mm!!dd!.!storeCode!"
    echo Constructed file names: !brandFile!, !departmentFile!, !productFile!
    pause

    rem Step 5: Run the osql command and check result for the specific product pattern
    set "result="  rem Initialize result variable to empty

    for /f "delims=" %%j in ('osql -UTRANSF -PFSNART -S!transformedCode! -Q "exec xp_cmdshell 'dir d:\FTPRoot\transfIN\PRODUCT_!mm!!dd!.!storeCode!'"') do (
        echo Checking for PRODUCT file: %%j
        echo %%j | findstr /c:"PRODUCT_!mm!!dd!.!storeCode!" >nul
        if not errorlevel 1 (
            set "result=PRODUCT_!mm!!dd!.!storeCode!"
        )
    )
    pause

    rem Step 6: Check result
    if defined result (
        echo Found result: !result!
        echo Result indicates true for the presence of PRODUCT pattern for store code !storeCode!.
        pause
        goto :next
    )

    echo No relevant result found for store code !storeCode!. Continuing to download.
    pause

    rem Step 7: FTP download
    (
        echo open ftp.yourserver.com
        echo username
        echo password
        echo get !brandFile! D:\map_drive\wprx\!brandFile!
        echo get !departmentFile! D:\map_drive\wprx\!departmentFile!
        echo get !productFile! D:\map_drive\wprx\!productFile!
        echo bye
    ) > ftp_commands.txt
    echo FTP commands prepared.
    pause

    ftp -n -s:ftp_commands.txt
    echo Downloaded files to D:\map_drive\wprx\
    pause

    rem Step 8: Copy files to the pth12345 folder
    (
        echo exec xp_cmdshell 'net use x: \\172.16.141.210\map_drive "user&pass002" /user:ho\2083761'
        echo exec xp_cmdshell 'copy x:\wprx\* d:\FTPRoot\TransfIN\ /y'
        echo exec xp_cmdshell 'net use x: /d /y' 
    ) > transfer2store.sql

    osql -UTRANSF -PFSNART -S!transformedCode! -i transfer2store.sql
    echo Copied files complete for store code !storeCode!
    pause

    :next
)

rem Compare st.txt and st_result.txt
fc /b st.txt st_result.txt > nul 2>&1
if errorlevel 1 (
    echo st.txt and st_result.txt are different. Restarting loop.
    timeout /t 60 /nobreak > nul
    goto loop
) else (
    echo st.txt and st_result.txt are the same. Stopping program.
)

:end
echo Script finished processing all store codes.
pause
endlocal

where is result to st_result.txt