SQL Using “with” and “having” Keywords
One of the projects I was working on a while back involved working with a lot of Transact-SQL for Microsoft SQL Server. Luckily I was working together with a real SQL whizz who showed me some keywords that made data mining oh so much easier. Two keywords that stand out are with and having – with allows you to quickly create temporary tables and having allows you to filter using aggregate functions.
Here’s an example SQL script to find some statistics per user on a monthly basis.
use statistics go declare @fromDate datetime set @fromDate = '2008-05-01' ;with tmp_users as ( select us.userID ,SUM(us.income) as income ,DATEPART(year, us.statDate) as d ,DATEPART(month, us.statDate) as f from tbl_userStat us where us.gameCode not in ('B2') group by us.userID ,DATEPART(year, us.statDate) ,DATEPART(month, us.statDate) having SUM(us.income) >= 1500 ), levelIncome as ( select us.userID ,MAX(us.statDate) as dateLastAction ,SUM(us.income) as income from tbl_userStat us where us.gameCode not in ('B2') group by us.userID ) select t.userID ,count(*) numMonthsOver1500 ,u.countryCode ,l.income as levelIncome ,l.dateLastAction from tmp_users t join levelIncome l on l.userID = t.userID join tbl_user u on u.userID = t.userID where u.userID not in (select userid FROM tbl_partner) group by t.userID ,u.countryCode ,l.income ,l.dateLastAction -- Temporary tables tmp_users and levelIncome -- are no longer available here
A few important details for using with:
- with must have a leading semi-colon to end all previous SQL statements
- temporary tables will only be present during the first SQL statement after the with clause
You can create multiple temporary tables by using comma as in the example above. The first table is called tmp_users and the second is called levelIncome.