Skip to content

Instantly share code, notes, and snippets.

@myobie

myobie/gist:15218

Created Oct 7, 2008
Embed
What would you like to do?
-- Original
SELECT DATEADD(day, - (1 * DATEPART(dw, RegisterDateTime)), CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, RegisterDateTime)))) AS Week,
COUNT(ProductRegistrationID) AS RegistrationCount, SUM(CONVERT(TINYINT, ContentExpert)) AS ContentExpertCount
FROM ProductRegistrations
WHERE (RegisterDateTime > DATEADD(year, - 1, DATEADD(day, (1 * DATEPART(dw, GETDATE())), GETDATE())))
GROUP BY DATEADD(day, - (1 * DATEPART(dw, RegisterDateTime)), CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, RegisterDateTime))))
ORDER BY Week DESC
-- My Attempt to convert
SELECT RegisterDateTime, YEARWEEK(RegisterDateTime) AS Week, COUNT(ProductRegistrationID) AS RegistrationCount, SUM(ContentExpert) AS ContentExpertCount
FROM ProductRegistrations
WHERE RegisterDateTime > DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY Week
ORDER BY Week DESC
-- My Table Structure
DROP TABLE IF EXISTS `PRODUCTREGISTRATIONS`;
CREATE TABLE `PRODUCTREGISTRATIONS` (
`PRODUCTREGISTRATIONID` int(11) default NULL,
`PRODUCTNAME` varchar(32) default NULL,
`PRODUCTVERSION` varchar(10) default NULL,
`QBISBN` varchar(13) default NULL,
`QBTITLE` varchar(128) default NULL,
`PLATFORMOS` varchar(10) default NULL,
`PLATFORMOSVERSION` varchar(10) default NULL,
`PLATFORMSPN` varchar(10) default NULL,
`PLATFORMDOSVERSION` varchar(10) default NULL,
`PLATFORMAVAILMEMORYKB` int(11) default '0',
`PLATFORMAVAILDISKKB` int(11) default '0',
`USERTITLE` varchar(4) default NULL,
`USERNAME` varchar(128) default NULL,
`USERLASTNAME` varchar(128) default NULL,
`USEREMAIL` varchar(128) default NULL,
`USERSCHOOLNAME` varchar(128) default NULL,
`USERSCHOOLZIP` varchar(30) default NULL,
`USERCOUNTRY` varchar(64) default NULL,
`SENDPRODUCTUPDATEINFO` int(1) default '0',
`REGISTERDATETIME` date default NULL,
`REMOTE_ADDR` varchar(15) default NULL,
`UPGRADE` int(1) default '0',
`USESEDU` int(1) default NULL,
`USESBB` int(1) default NULL,
`USESWCT` int(1) default NULL,
`USESQM` int(1) default NULL,
`USESPO` int(1) default NULL,
`USESEG` int(1) default NULL,
`USESGA` int(1) default NULL,
`USESANGEL` int(1) default '0',
`USESD2L` int(1) default '0',
`USESECOLLEGE` int(1) default '0',
`USESOTHER` int(1) default '0',
`OTHERCMS` varchar(128) default NULL,
`CONTENTEXPERT` int(1) default '0',
`ROLE` varchar(64) default NULL,
`SALESID` int(10) default NULL,
`CMS` varchar(160) default NULL,
`DISCIPLINE` varchar(160) default NULL,
`LICENSE` varchar(128) default NULL,
`LICENSEISTRIAL` int(1) default '0',
`LICENSEPLATFORM` int(3) default NULL,
`LICENSEMAJORVERSION` int(3) default NULL,
`LICENSEMINORVERSION` int(5) default NULL,
`LICENSELEVEL` int(5) default NULL,
`LICENSEDATE` date default NULL,
`LICENSETYPE` int(5) default NULL,
`ISINCOMPLETE` int(1) default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.