• This topic has 36 replies, 13 voices, and was last updated 2 months ago by Tom.
Currently, there are 0 users and 0 guests visiting this topic.
Viewing 17 posts - 21 through 37 (of 37 total)
  • Author
    Posts
  • #165224
    blokenamedblake
    Participant
    @blokenamedblake

    How can I find games I have on my system but have never played?

     

    I thought this would work:

    select * from Games where NumberPlays=0

    #169158
    DBut73
    Participant
    @dbut73

    How can I find games I have on my system but have never played?

    I thought this would work:

    select * from Games where NumberPlays=0

    Try this in a playlist

    SELECT * FROM Games Gm WHERE Visible=1 AND NOT EXISTS(SELECT 1 FROM GamesStats WHERE GameID=Gm.GameID) ORDER BY GameName

    Any games that have not been played should appear in the playlist. If you play a game you will have to back out of the playlist then go back in to update the list and remove any played games.

    #169730
    Stevenoj2769
    Participant
    @stevenoj2769

    Anyone know what SQL I can use to get a playlist that just shows tables that were added in the last 30 days.  I tried several things with no luck.

    Here is one of the things I tried that did not work.

    SELECT * FROM Games WHERE DateAdded >= DATEADD(day,-30, getdate()) ORDER BY DateAdded desc;

    Thanks in advance!!!

    What almost works if dates are not involved is this will get you just the last 30 tables you added.

    Select * from Games order by dateadded desc limit 30;

    #169858
    NailBuster
    Moderator
    @nailbuster
    ModeratorMember

    fyi… documentation for SQL lite is on this site:  https://www.sqlite.org/lang_datefunc.html

    1 user thanked author for this post.
    #169929
    Stevenoj2769
    Participant
    @stevenoj2769

    …after NailBuster posted the link to SQL lite documentation I got what I needed to work to show just the tables added in the last 30 days.  Thanks NailBuster!

    Here is what I came up with to get a playlist that is just the games added in the last 30 days.

    Select * from Games where dateadded >= datetime(‘now’,’-30 day’) order by dateadded desc;

    I hope this helps someone out.

    #177955
    bschaberg21
    Participant
    @bschaberg21
    Member

    I’m trying to add a recently added playlist like the recent post above. I’m getting an error message when I test the activesql query.  Any thoughts or help would be or help would be appreciated.

     

    . settingsError message

    BSchaberg21

    #177956
    John William
    Participant
    @johnwilliam

    I’m trying to add a recently added playlist like the recent post above. I’m getting an error message when I test the activesql query. Any thoughts or help would be or help would be appreciated.

    The single quotes look like they got swapped in copy/paste/encoding.. replace them with the single quote just to the left of the enter key if using a US keyboard.

    #177960
    bschaberg21
    Participant
    @bschaberg21
    Member

    That worked.  Thanks so much!

    BSchaberg21

    #184239
    gride
    Participant
    @gride

    I’m trying to create a “recently played” playlist across playlists. I’ve tried the below.

    select * from Games where GameID in (select GameID from GamesStats order by LastPlayed DESC limit 25)

    This seems to give me the order backwards, but if I order ASC it’s incorrect.

    Any thoughts on what I can change?

    #189966
    superproxy
    Participant
    @superproxy

    help, please. how do i list most played according to total play time?

    #191168
    DBut73
    Participant
    @dbut73

    help, please. how do i list most played according to total play time?

    Try this:

    SELECT * FROM Games JOIN GamesStats ON Games.GameID=GamesStats.GameID AND Visible=1 ORDER BY GamesStats.TimePlayedSecs DESC LIMIT 5

    This should display the top 5 tables played by time in seconds. Change ‘LIMIT 5’ to list a different amount of tables e.g. ‘LIMIT 10’ will display top 10.

    1 user thanked author for this post.
    #191733
    superproxy
    Participant
    @superproxy

    help, please. how do i list most played according to total play time?

    Try this:

    SELECT * FROM Games JOIN GamesStats ON Games.GameID=GamesStats.GameID AND Visible=1 ORDER BY GamesStats.TimePlayedSecs DESC LIMIT 5

    This should display the top 5 tables played by time in seconds. Change ‘LIMIT 5’ to list a different amount of tables e.g. ‘LIMIT 10’ will display top 10.

    Thanks a lot!! This worked like a charm! :)

    #194469
    superproxy
    Participant
    @superproxy

    help, please. how do i list most played according to total play time?

    Try this:

    SELECT * FROM Games JOIN GamesStats ON Games.GameID=GamesStats.GameID AND Visible=1 ORDER BY GamesStats.TimePlayedSecs DESC LIMIT 5

    This should display the top 5 tables played by time in seconds. Change ‘LIMIT 5’ to list a different amount of tables e.g. ‘LIMIT 10’ will display top 10.

    Thanks a lot!! This worked like a charm! :)

    Hi. Is there a way to take this a step further and say for example, most played according to total play time, for the last 30 days only?

    #194556
    NailBuster
    Moderator
    @nailbuster
    ModeratorMember

    Hi. Is there a way to take this a step further and say for example, most played according to total play time, for the last 30 days only?

    not possible as that would require to track playing time by date.  pinup just has a total time played field for each game… not a detail by date/time.

    1 user thanked author for this post.
    #194590
    Tom
    Participant
    @armyaviation
    MemberContributor

    I recently got into popper and started digging around.  This is a really nice front end.  I have watched a few videos on popper setup, is there a video tutorial that explains the stuff you are talking about here to us lay people or is this something that should be left to the professionals

    #194658
    Rajo Joey
    Participant
    @joey2001
    Member

    I recently got into popper and started digging around. This is a really nice front end. I have watched a few videos on popper setup, is there a video tutorial that explains the stuff you are talking about here to us lay people or is this something that should be left to the professionals

    The sql-commands are a great help. You don’t have to put tables in a playlist. It was all automatically done. Only add the table in the game manager and it’s in the playlist. If you want some, I can send you my sql-commands. It’s easy to edit them.

    https://www.nailbuster.com/wikipinup/doku.php?id=playlists_sql_examples

    #202437
    Tom
    Participant
    @armyaviation
    MemberContributor

    what would be a command for top ten played tables, thanks

Viewing 17 posts - 21 through 37 (of 37 total)
  • You must be logged in to reply to this topic.

©2020 VPinBall.com

Log in with your credentials

or    

Forgot your details?

Create Account

The Vpinball app

FREE
VIEW