Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

View in another Language

Sponsored [Advertise Here]
Don't have a blog yet?
beyondrelational.com
Start writing a blog at beyondrelational.com, get recognized and build your brand

Izenda Reports
izenda.com
Ad Hoc reporting for ASP.NET that actually works

Syndicate your blog!
beyondrelational.com
Syndicate your blog with us to get wider reach into the technology community. Click here to get started.

TSQL Challenge 59 - Playing Chess in TSQL – Part 2

This is a continuation of the previous challenge where we generated a TSQL representation of a chess board filled with pieces based on input strings in Forsyth-Edwards Notation. This challenge invites you to perform a bit more complicated operation.

Your job is to generate a string using Forsyth-Edwards Notation which represents the final position of pieces after performing a series of moves from the original position.

Want to make SQL effortless?
With code-completion, SQL reformatting, script summaries and more, SQL Prompt 5 makes writing, editing, and exploring SQL effortless. Download a 14-day free trial now.

Sample Data

Layout Table
GameID Layout
------ ---------------------------------------------------- 
	 1 rnbqk2r/ppppbppp/5n2/4p3/2B1P3/5Q2/PPPP1PPP/RNB1K1NR

Movement Table

Seq  GameID Movement
---  ------ --------
  1		  1 Pd2d3
  2       1 pa7a6
  3       1 Bc1g5
  4       1 pb7b5

The Layout table has the same input data as the previous challenge. This input string represents a chess board as given below.


A Movement table holds a series of moves made from this position. After the movements given above, the final position of the board will be as follows.


Your job is to build a Forsyth-Edwards Notation string which represents this position of the board.

Expected Results

GameID Result
------ --------------------------------------------------------
     1 rnbqk2r/2ppbppp/p4n2/1p2p1B1/2B1P3/3P1Q2/PPP2PPP/RN2K1NR

Rules

  1. See Algebraic Chess Notation for a basic understanding of how moves are recorded.
  2. To avoid ambiguity, all movements will be recorded with complete reference of the source location and target location. For example, a White Bishop moving from “c1” to “g5” will be recorded as “Bc1g5”.
  3. All movements will be valid moves.
  4. There will be no captures, promotions or castling in this version of the challenge. The only change that will happen on the board is the change of position of various pieces. In the final position, the board will have the same number of pieces.
  5. The following notation is used to identify pieces
    Piece  White Black
    ------ ----- -----
    Rook   R     r
    Knight N     n
    Bishop B     b
    King   K     k
    Queen  Q     q
    Pawn   P     p
    
  6. The results should be ordered by GameID

Sample Script

Use the TSQL Script given below to generate the source tables and fill them with sample data.

IF OBJECT_ID('TC59_Layout','U') IS NOT NULL BEGIN
	DROP TABLE TC59_Layout
END
GO

CREATE TABLE TC59_Layout(
	GameID INT,
	Layout VARCHAR(MAX)
)
GO

INSERT INTO TC59_Layout(GameID,Layout)
SELECT 1,'rnbqk2r/ppppbppp/5n2/4p3/2B1P3/5Q2/PPPP1PPP/RNB1K1NR' 

SELECT * FROM TC59_Layout

GO
IF OBJECT_ID('TC59_Movement','U') IS NOT NULL BEGIN
	DROP TABLE TC59_Movement
END
GO

CREATE TABLE TC59_Movement(
	Seq INT IDENTITY PRIMARY KEY,
	GameID INT,
	Movement VARCHAR(MAX)
)
GO

INSERT INTO TC59_Movement(GameID,Movement)
SELECT 1,'Pd2d3' UNION ALL
SELECT 1,'pa7a6' UNION ALL
SELECT 1,'Bc1g5' UNION ALL
SELECT 1,'pb7b5'

SELECT * FROM TC59_Movement

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
SQL Source Control - now with static data support
"By allowing static data to be versioned, SQL Source Control now becomes a complete source of truth for the application's data layer. I'm in." Troy Hunt, Software Architect.
More information and a free trial.

Jacob Sebastian
1 · 100.00% · 32235
Submit a Solution
View Solutions
View All Challenges Previous Challenge Next Challenge
1
Liked
17
Comments
61
Solutions



Submit

17   Comments   

Subscribe to Notifications
  • Nice challenge.

    I integrate it with Part 1 and now I can play chess, but I cant "eat" pieces.

    Hope Part 3 complete this! :D

    commented on Jun 27 2011 6:31AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    113 · 1% · 459
  • One suggestion for an hypotethic part 3 would be:

    Given de following layout: Example: 'rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR'

    Transform the Real notation into the especific one: Real: 'e4' 'e5' 'Nf3' 'd6' 'd4'

    Especific: 'Pe2e4' 'pe7e5' 'Ng1f3' 'pd7d6' 'Pd2d4'

    Knights would be very hard :p

    commented on Jun 27 2011 6:59AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    113 · 1% · 459
  • My solution "eats" pieces - although I didn't set out to design it that way. Hopefully that's the next challenge, because I'll be all set to go.

    3 questions though:

    1. Do we have to worry about NULL layouts? I'm assuming each movement will have a non-null given that the rules say that table will only contain legal moves.
    2. Is there a maximum number of moves to deal with?
    3. I assume that we should be able to do multiple games at once?

    I am using a recursive CTE, so the answers to 2 and 3 might affect that decision.

    Thanks.

    commented on Jun 27 2011 3:43PM  .  Report Abuse This post is not formatted correctly
    jpatchak
    588 · 0% · 62
  • Sample data and my result:

    IF OBJECT_ID('TC59_Layout','U') IS NOT NULL BEGIN
        DROP TABLE TC59_Layout
    END
    GO
    
    CREATE TABLE TC59_Layout(
        GameID INT,
        Layout VARCHAR(MAX)
    )
    GO
    
    INSERT INTO TC59_Layout(GameID,Layout)
    SELECT 1,'rnbqk2r/ppppbppp/5n2/4p3/2B1P3/5Q2/PPPP1PPP/RNB1K1NR' union all
    SELECT 2,'rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR' union all
    SELECT 3,'rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR' union all
    SELECT 4,'7r/8/8/8/8/8/8/Q7'
    
    SELECT * FROM TC59_Layout
    
    GO
    IF OBJECT_ID('TC59_Movement','U') IS NOT NULL BEGIN
        DROP TABLE TC59_Movement
    END
    GO
    
    CREATE TABLE TC59_Movement(
        Seq INT IDENTITY PRIMARY KEY,
        GameID INT,
        Movement VARCHAR(MAX)
    )
    GO
    
    INSERT INTO TC59_Movement(GameID,Movement)
    SELECT 1,'Pd2d3' UNION ALL
    SELECT 1,'pa7a6' UNION ALL
    SELECT 1,'Bc1g5' UNION ALL
    SELECT 1,'pb7b5' UNION ALL
    
    SELECT 3,'Pa2a3' UNION ALL
    SELECT 3,'ph7h6' UNION ALL
    SELECT 3,'Pa3a4' UNION ALL
    SELECT 3,'ph6h5' UNION ALL
    SELECT 3,'Pa4a5' UNION ALL
    SELECT 3,'ph5h4' UNION ALL
    SELECT 3,'Ra1a2' UNION ALL
    SELECT 3,'rh8h7'
    
    go
    INSERT INTO TC59_Movement(GameID,Movement)
    SELECT 4,'Qa1a8' UNION ALL
    SELECT 4,'rh8h1' UNION ALL
    SELECT 4,'Qa8h8' UNION ALL
    SELECT 4,'rh1a1' UNION ALL
    SELECT 4,'Qh8h1' UNION ALL
    SELECT 4,'ra1a8' UNION ALL
    SELECT 4,'Qh1a1' UNION ALL
    SELECT 4,'ra8h8'
    go 50
    INSERT INTO TC59_Movement(GameID,Movement)
    SELECT 4,'Qa1a2'
    
    SELECT * FROM TC59_Movement
    
    
    
    GameID  Result
    1   rnbqk2r/2ppbppp/p4n2/1p2p1B1/2B1P3/3P1Q2/PPP2PPP/RN2K1NR
    2   rnbqkbnr/pppppppp/8/8/8/8/PPPPPPPP/RNBQKBNR
    3   rnbqkbn1/pppppppr/8/P7/7p/8/RPPPPPPP/1NBQKBNR
    4   7r/8/8/8/8/8/Q7/8
    
    commented on Jun 28 2011 3:39AM  .  Report Abuse This post is not formatted correctly
    Leszek Gniadkowski
    8 · 18% · 5729
  • Thx for posting this sample data.

    The 3 first ones goes well, but in 4th one ...maxrecursion 100 crash mine :(

    Edit: then, I found the maxrecursion hint ^^

    commented on Jun 28 2011 4:01AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    113 · 1% · 459
  • Does anyone have any performance data with Leszek's sample data? Mine (returning all games): Reads: 381091 Writes: 64 Duration: 1218

    commented on Jun 28 2011 9:17AM  .  Report Abuse This post is not formatted correctly
    jpatchak
    588 · 0% · 62
  • Reads: 5422 Writes: 0 Duration: 180ms

    commented on Jun 29 2011 1:13AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    113 · 1% · 459
  • Hello, my stats: (4 row(s) affected) Table 'Worktable'. Scan count 8, logical reads 7470, physical reads 0, read-ahead reads 0, lob logical reads 4332, lob physical reads 0, lob read-ahead reads 0. Table 'TC59Layout'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC59Movement'. Scan count 414, logical reads 1656, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 374 ms, elapsed time = 383 ms.

    commented on Jun 29 2011 2:39PM  .  Report Abuse This post is not formatted correctly
    Zibi
    25 · 6% · 2030
  • final version:

    (4 row(s) affected) Table 'Worktable'. Scan count 1, logical reads 41, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC59Movement'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC59Layout'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 31 ms, elapsed time = 24 ms.

    commented on Jun 30 2011 11:40AM  .  Report Abuse This post is not formatted correctly
    Leszek Gniadkowski
    8 · 18% · 5729
  • (4 row(s) affected) Table 'Worktable'. Scan count 2, logical reads 2498, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC59Movement'. Scan count 2, logical reads 842, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'TC59Layout'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 63 ms, elapsed time = 73 ms.

    I´m getting closer...too many reads :(

    commented on Jul 1 2011 3:20AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    113 · 1% · 459
Previous 1 | 2 Next

Your Comment


Sign Up or Login to post a comment.


Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.