SQL Using “with” and “having” Keywords

by Michael

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 havingwith 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.

Advertisements