Agrikk fucked around with this message at 20:50 on Apr 2, 2012

SELECT DATEADD(D, -1, DATEADD(M, 1, CONVERT(DATETIME, CAST(YearMonth AS VARCHAR(8)) + '01', 112)))

JeansW fucked around with this message at 21:23 on Apr 2, 2012

Good Will Punting fucked around with this message at 20:15 on Apr 7, 2012

Sulla Faex fucked around with this message at 09:42 on Apr 8, 2012

SELECT * 
FROM   dl_item AS c, 
       dlp_item AS ci 
       LEFT JOIN dl_battlestats AS b 
         ON b.cardid = c.id 
            AND b.cardtype = 'item' 
       LEFT JOIN dlp_battlestats AS bi 
         ON bi.cardid = ci.id 
            AND bi.cardtype = 'item' 
WHERE  c.id = 19 
       AND ci.itemid = c.id

CREATE TABLE IF NOT EXISTS `dl_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier',
  `variant` smallint(5) unsigned NOT NULL COMMENT 'links to dl_variant',
  `name` varchar(50) NOT NULL,
  `cardText` text NOT NULL,
  `itemType` enum('item','ally') NOT NULL,
  `cost` tinyint(4) NOT NULL,
  `category` varchar(10) NOT NULL COMMENT 'main category, like armor, weapon, artifact',
  `subcategory` varchar(10) NOT NULL COMMENT 'secondary catagory, like mace, instrument, belt, rune',
  `quantity` smallint(5) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='A list of itemtypes' AUTO_INCREMENT=76 ;

CREATE TABLE IF NOT EXISTS `dlp_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boardid` int(11) NOT NULL,
  `itemid` int(11) NOT NULL,
  `deckOrder` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=59 ;

CREATE TABLE IF NOT EXISTS `dl_battleStats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique ID',
  `cardType` enum('player','adventure','item') NOT NULL COMMENT 'type of card',
  `cardid` int(10) unsigned NOT NULL COMMENT 'links to the player/encounter/ally',
  `maxHealth` tinyint(3) unsigned NOT NULL COMMENT 'life',
  `maxStamina` tinyint(3) unsigned NOT NULL COMMENT 'stamina',
  `melee` tinyint(3) unsigned NOT NULL COMMENT 'melee',
  `meleeDamage` tinyint(3) unsigned NOT NULL COMMENT 'melee damage',
  `ranged` tinyint(3) unsigned NOT NULL COMMENT 'ranged',
  `rangedDamage` tinyint(3) unsigned NOT NULL COMMENT 'ranged damage',
  `magic` tinyint(3) unsigned NOT NULL COMMENT 'magic',
  `magicDamage` tinyint(3) unsigned NOT NULL COMMENT 'magic damage',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=98 ;

CREATE TABLE IF NOT EXISTS `dlp_battleStats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique ID',
  `cardType` enum('player','adventure','item') NOT NULL COMMENT 'type of card',
  `cardid` int(10) unsigned NOT NULL COMMENT 'links to the player/encounter/ally',
  `health` tinyint(3) unsigned NOT NULL COMMENT 'life',
  `stamina` tinyint(3) unsigned NOT NULL COMMENT 'stamina',
  `meleeMod` tinyint(3) unsigned NOT NULL COMMENT 'melee',
  `meleeDamageMod` tinyint(3) unsigned NOT NULL COMMENT 'melee damage',
  `rangedMod` tinyint(3) unsigned NOT NULL COMMENT 'ranged',
  `rangedDamageMod` tinyint(3) unsigned NOT NULL COMMENT 'ranged damage',
  `magicMod` tinyint(3) unsigned NOT NULL COMMENT 'magic',
  `magicDamageMod` tinyint(3) unsigned NOT NULL COMMENT 'magic damage',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

SELECT * 
FROM   dl_item AS c 
       INNER JOIN dlp_item AS ci 
         ON c.id = 19 
            AND ci.itemid = c.id
       LEFT JOIN dl_battlestats AS b 
         ON b.cardid = c.id 
            AND b.cardtype = 'item' 
       LEFT JOIN dlp_battlestats AS bi 
         ON bi.cardid = ci.id 
            AND bi.cardtype = 'item' 

Gilgamesh fucked around with this message at 15:51 on Apr 8, 2012

CREATE TABLE t1 (
Day DATETIME
)

INSERT INTO t1(Day)
SELECT 
('20110505') UNION ALL
SELECT
('20110506') UNION ALL
SELECT
('20110507') UNION ALL
SELECT
('20110508') UNION ALL
SELECT
('20110509')  UNION ALL
SELEcT
('20110510' )

CREATE TABLE t2 (
Day DATETIME 
)

INSERT INTO t2(Day)

SELECT
('20110507') UNION ALL

SELECT
('20110509')  UNION ALL
SELEcT
('20110510' )

SELECT t1.Day, t2.Day FROM t1 INNER JOIN t2 on t1.Day = 
          DATEADD(D,DATEDIFF(D,t2.Day,t1.Day), 

          (SELECT TOP 1 t2.Day FROM t1 CROSS JOIN t2 
                  
                 WHERE DATEDIFF(D,t1.Day,t2.Day) >= 0 ORDER BY DATEDIFF(D,t1.Day,t2.Day)))

                               /* I'm using Sql server in case you haven't guessed from the select top 1 statement */

 SELECT TOP 1 t2.Day FROM t1 CROSS JOIN t2 
                  
                 WHERE DATEDIFF(D,t1.Day,t2.Day) >= 0 ORDER BY DATEDIFF(D,t1.Day,t2.Day) 

zyang31 fucked around with this message at 15:17 on Apr 11, 2012

Jethro fucked around with this message at 17:21 on Apr 11, 2012

revmoo fucked around with this message at 17:38 on Apr 11, 2012