►
From YouTube: TTEOT Training Video - S1E8 - SQL: Dates and Subqueries
Description
Continuing on with the SQL saga, George freaks Christopher out with his geeky date voodoo and Subquery magic.
Referenced code: https://wiki.koha-community.org/wiki/SQL_Reports_Library
Music: https://www.bensound.com
A
Hello,
I'm
george
williams
from
the
northeast
kansas
library
system,
I'm
christopher
brown,
with
the
coeur
d'alene
public
library
and
the
cooperative
information
network,
and
this
is
another
one
of
our
videos
about
how
to
do
crazy,
stuff
with
coho
and
once
again
we're
talking
about
sql
how
to
write
better
reports
and
I'm
on
the
cohe
on
the
screen.
Now
you'll
see
the
koha
us
home
page
and
you
can
find
all
of
these
videos
under
learn
from
koha
us.
A
I
don't
know
I
just
want
to
mention.
I
was
looking
at
the
the
videos
the
other
day
and
I
was
realizing
how
many
people
have
been
viewing
these
these
videos,
and
I
just
want
to
say
thank
you
to
everybody-
that's
been
keeping
up
with
them.
We
appreciate
the
viewership
and-
and
we
hope
we
hope
that
we're
providing
information
that
is
helpful
and
also
you
know.
I
want
to
throw
out
the
suggestion
if
there's
something
that
you
would
like
to
learn
about,
that
we
can
do
a
video
on.
A
Please
shoot
an
email
our
way
and
we'll
see
what
we
can
do
and
even
better
if
there's
something
that
you
wanted
us
to
do
a
video
about
that.
You
want
to
help
us.
Do
a
video
about
we're
happy
to
have
guests
absolutely
we'd,
be
we'd
love
it.
If
somebody
said
hey,
I
want
to
come
on
and
show
you
guys
how
to
do
something
that
would
be
awesome.
A
A
They
have
absolutely
no
interest
in
how
this
stuff
works.
Well,
maybe
you
can
mention
them
in
the
videos.
Oh,
that
would
be
even
worse.
I
can't
believe
you,
you
said
my
name
on
youtube.
Oh
my
god,
knock
it
off
dad
and
that'd,
be
my
21
year
old,
all
right!
So
george,
what
do
we
have
for
today
today?
I'm
going
to
talk
about
sub
queries
is
kind
of
the
focus,
but
there's
a
couple
of
other
things
that
go
into
this.
That
I
think,
could
be
useful.
A
A
couple
of
ways
that
I
play
with
dates
and
I've
got
a
report
that
you
can
download
from
mana
that
can
give
you
some
cool
date
formulas
to
work
with.
That
can
be
really
useful
cool.
Looking
forward
to
seeing
that-
and
you
know
stuff
in
sql,
you
know
if
you
go
to
the
coho
wiki,
there's
a
library
full
of
reports.
A
And
it
starts
off
with
a
big
section
on
tips:
how
to
avoid
errors,
notes
for
single
branch,
libraries,
how
to
embed
links?
How
to
query
mark
all
these,
this
good
stuff
and
we're
going
to
do
some
of
that
today,
because
part
of
what
I'm
going
to
talk
about
will
be
run
time.
Parameters,
okay,
because
there
are
ways
to
get
dates
that
are
easy
and
there
are
ways
to
get
dates
that
are
hard
and
there
are
a
couple
of
important
tricks
with
dates.
A
So
let
me
flip
over
and
I
I
wrote
a
whole
bunch
of
reports
and
I
stuck
them
on
my
system-
I'm
doing
this
on
my
system
today,
because
there
there
isn't
enough
data
in
the
coho
us
demo
site
to
do
a
really
good
report
like
this.
This
report
will
just
give
us
a
lot
of
stuff.
So,
okay,
so
first
I'm
going
to
open
this
in
a
new
window
and
let
it
run.
A
A
A
So
if
I
don't
put
the
if
I
don't
put
statistics
type
in
the
two,
these
two
in
parentheses
it'll
give
me
the
statistics
where,
where
the
date
time
is
between
this
and
this
and
statistic
type
equals
issues.
So
it's
going
to
give
me
the
checkouts
between
these
two
dates
or
it's
going
to
give
me
all
of
the
renews
back
as
far
as
the
table
goes.
A
A
A
Yeah
you
get
zero
results;
it
has
to
be
in
that
right
format.
So
I
don't
like
doing
this
when
you
you,
what
I
find
is,
if
you
tell
if,
if
I
write
a
report
like
this,
I
have
to
put
a
note
somewhere
on
the
screen
that
reminds
people
that
you
have
to
do:
2020
dash,
10-01
and
2020-11-0,
so
that
those
date
inputs
will
get
you
all
of
the
days
between
all
of
the
data
between
the
first
and
the
31st.
The
other
reason
I
don't
like
the
other
reason,
I
don't
like
inputting
the
dates.
A
It
only
goes
to
the
beginning
of
the
day,
so
you
lose
a
whole
day's
worth
of
data
yep,
and
so
there
also
had
would
have
to
be
a
note
here
explaining
to
to
my
staff
that
this
is
going
to
chop
off
the
last
day
of
the
month.
So
that's
a
that's
a
way
of
inputting
this
that
you
don't
have
to
hard
code,
the
dates
in
there
every
time,
but
it
if
you're
dealing
with
staff
if
you're
building
these
reports
for
other
people
to
run.
A
A
A
A
The
issue
with
this
again,
though,
is
you
have
to
remember
that
if
you
choose
like
october
1st
october
31st
you're
still
going
to
lose
that
whole
day's
worth
of
data,
you
still
have
to
choose
now.
Is
it
you
or
is
it
the
date
picker
that
reformatted
the
date
it's
the
date
picker
that
reformats
the
date
that's
kind
of
funny
too,
because
you
know
you
get
used
to
entering
the
date
with
the
year
first
and
with
hyphens
and
the
date
picker
formats
it
a
completely
different
way.
A
A
What
I'm
doing
here
is
I'm
saying
statistics,
it's
the
same
report,
except
when
we
get
to
the
year,
I'm
using
some
sql
to
say
just
give
me
the
month
from
the
date
time
on
this
line
and
give
me
the
year
from
the
date
time
on
this
line,
and
then
I've
hard
coded
in
a
10
for
the
month
and
a
20
20
for
the
year
so
by
going
months
statistics
day
time,
all
it's
getting
out
of
that
date.
Time
is
the
number
10.
A
A
But
that's
that's
got
the
same
problem
as
that
early
one
is.
If
I
want
to
change
it,
I've
got
to
go
in
there
and
hard
code,
it
again
right
and
so
here's
the
same
report
where
now
all
I've
got
to
do
is
type
a
10
and
a
20
20.,
except
you
have
to
know
that
you're
entering
digits
for
the
month
and
you
have
to
know
that
you're
running
the
full
four
digits
for
the
year.
That's
true!
A
So
if
you
don't
enter
those
quite
right,
you'll
still
have
problems
with
your
report.
One
of
my
predecessors
here
I
don't
have.
I
don't
have
an
example
of
this,
but
they
actually
what
they
did
is
they
created
a
list
of
authorized
values
for
month
and
another
list
for
year,
so
you
get,
you
can
create
a
drop
down
nice
for
month
and
year.
A
A
A
So
let
me
show
the
results
here
here.
I
just
run
the
report
and
I
get
a
result
and
that's
because,
and
so
what
I've
got
this
report
said
to
do
is
it's
doing
the
same
thing.
It's
looking
for
the
month
from
the
statistics
date
time
field,
but
I'm
looking
for
the
month
and
I'm
using
a
formula
to
get
last
month
and
then
I'm
doing
the
same
thing
with
the
year.
A
That
way,
if
I
run
this
in
january,
it's
not
looking
it's
not
it's
getting
the
year
from
last
month
cool.
So
that
way.
Now
the
problem
with
the
the
now
statement,
though,
is
whenever
you
use
the
now
statement,
and
you
ask
for
what
time
is
it
now?
It's
not
telling
you
what
time
it
is
now
it's
telling
you
what
time
it
was
when
you
asked.
A
A
A
A
A
So
if
I
run
this
report-
and
you
know,
I'm
I'm
not
going
to
get
a
list
of
all
54
different
branches-
I'm
just
going
to
get
the
ones
where
there's
a
where
there
was
a
circulation
in
the
last
month,
so
I've
got
54
branches,
but
I've
only
got
52
results
and
that's
ultimately,
one
of
the
one
of
the
issues
that
I'm
trying
to
solve
is
I
want
to
make
this
that
I've
got
each
branch
lined
up
there.
So
when
I
go
to
plug
that
data
into
the
spreadsheet,
I
don't
have
any.
A
A
I'm
getting
this
report
set
up
so
that
because
next
year
I
want
to
start
using
the
branch
names
instead
of
the
codes
and
that
data
that
I
output
every
month,
and
so
what
I've
got
here
is
instead
of
getting
the
branch
code
from
the
statistics
table,
I've
joined
the
branches
table
to
this
table,
so
I'm
getting
the
branch
name
and
then
I'm
I'm
showing
you
the
branch
name.
Every
time.
The
branch
in
the
statistics
table
matches
the
the
branch
name
of
the
branch
table
nice.
A
So
the
next
step
actually
adding
the
sub
queries.
That's
step
number
eight
and
that's
going
to
get
me
the
the
results
where
there's
a
zero
check
out
in
this
table,
and
that's
where
we
have
digital
content
shows
up
here
with
the
blank
space
and
I've
got
54
results.
So
I've
got
a
result
for
each
each
library
in
my
system,
and
so
let
me
show
you
how
that
works,
and
I
usually
don't
format
the
sql
this
way.
But
I've
got
it
broken
up.
So
we
can
talk
about
each
individual
sub,
query,
okay.
A
That
is
this
table
that
I've
created
in
this
select
here.
It's
the
sub
query!
It's
the
first
sub
query
here,
so
I'm
getting
the
branch
name.
Every
time
I
find
I'm
saying,
give
me
branches.
Dot
branch
name
from
this
table
that
I
create
in
the
table
is
select
branch's
branch
code
from
branch
name
or
select
branches,
branch
code
and
branches
branch
name
from
branches,
and
it's
calling
that
a
new
table
it's
giving
that
a
new
name
which
is
branches
with
two
s's
in
all
caps.
A
So
that's
where
the
branch
name
is
coming
from.
That's
in
this
in
this
final
result
interesting.
I
didn't
know
that
that
sub
query
that
you
didn't
have
to
use
the
word
as
in
front
of
the
name
of
the
the
sub
query.
I've
never
used
as
I
always
have.
It
works
either
way,
but
it's
in
the
same
stuff,
I
didn't
know
you
could
use
ass.
A
So
I'm
getting
the
branch
name
from
this
sub
query
here
and
then
I've
got
another
sub
query
here
and
I'm
joining
these
branches
and
my
other
one
called
checkouts
and
renewals
I'm
joining
those
here.
So
essentially
I've
got
that
first
query,
that
first
sequel,
that
I
did
where
I've
got.
Statistics
branch
and
I'm
counting
the
the
rows
in
that
table
and
I'm
limiting
it
to
the
month
to
last
month,
where
the
statistic
type
is
issue
or
renew,
and
I'm
calling
that
a
separate
table
called
checkout
renewals
and
because
it's
a
left
join.
A
A
A
So,
okay,
I
like
that.
It's,
including
everything
that
you
want
in
there
I'm
one
of
the
the
ocd
people
that
says
I
can't
stand
when
a
lion
is
left
blank.
Do
you
do
you
ever
address
something
like
that?
That's
actually
something
that
we're
going
to
talk
about
in
the
future.
It's
called
there's
a
function
that
we
can
use
to
do
that
called
coalesce,
and
I
can
plug
it
in
here
and
show
you
what
it
looks
like
real,
real
quickly
yeah.
So
I've
got
this
data
here.
A
And
this
report
takes
a
second
or
two
to
run
because
it's
a
more
complicated
report
there
you'll
see
digital
content
now
has
zero
nice.
I've
always
used
the
the.
If
statement
for
that,
but
I
yeah
that's
that
works
great
too
yeah.
If
then,
would
work
also,
you
could
say
if
this
is
blank
or
null
then
then
put
in
a
zero,
otherwise
just
leaving
the
data
yeah.
So
there's
two
ways
there's
more
than
it
seems
like
there's
another
way
to
do
it
too.
A
We
could
do
another
sub
query
that
just
looks
for
one
item
type
or
maybe
a
group
of
item
types
or
we
could
do
one
collection
code
or
a
group
of
collection
codes
in
a
separate
sub
query,
and
then
we
get
something
like
this,
where
we've
got
a
column
for
checks
out
check
out
some
renewals,
total
and
another
column
here
for
checkout
renewals,
where
it's
just
the
book
item
type
and
let
me
show
this
code
and
show
you
what
that
looks
like.
So
here
I'm
doing
the
same
thing,
I'm
getting
branches
I
didn't
get.
A
I
didn't
do
this
one
in
all
caps,
but
I've
got
the
branches
coming
from
this
sub
query
and
I'm
getting
checkouts
and
renewals
from
the
other
sub
query
that
we
talked
about
last
time.
But
now
I've
got
an
additional
sub
query
here:
called
books
where
I've
just
it's
essentially
the
same
as
this
checkouts
and
renewals
sub
query:
it's
just
that
here.
I've
added
another
parameter
in
the
where
statement
where
I'm
saying
issues
and
renews
in
last
month,
where
the
item
tag
equals
book.
A
A
So
you
know
in
all
these
previous
demos,
we
were
talking
about
circ
data,
but
just
because
the
data
you
know,
if
you're
collecting
different
data
at
the
end
of
the
month
or
whenever,
where
you
need
that
that
column
to
be
something
else,
you
know
we
can
do
something
like
what
I've
done
here,
which
maybe
one
of
the
data
points.
You're
collecting
every
month
is
going
to
be
borrower
data.
So
here
I've
got
borrowers
added,
and
so
you
know
I've
got
the
branch
name.
A
A
A
This
sub
query
here
is
the
new
one
where
I'm
not
even
looking
at
the
statistics
table,
I'm
going
right
into
borrowers
and
I'm
getting
a
borrower's
branch
code
and
counting
borrowers
added
where
the
date
enrolled
the
month
of
the
date
enrolled
equals
last
month
and
the
year
of
the
date
enrolled
equals
the
this
month
this
year
last
month,
so
so
it
doesn't
just
have
to
be.
You
know
you
can
get
anything
you
can
get
from
the
sub
query
you
can
put.
You
can
find
a
way
to
put
it
in
there.
A
And
it's
it
too,
like
the
others,
is
going
to
be
left.
It's
left
joined
to
the
branches
information
that
we've
got
there
and
it
takes
a
little
longer
to
run
when
you,
the
more
sub
queries
you
put
in
there,
the
longer
it
will
take
to
run.
I
was
just
going
to
say
that,
yes,
so
that's
one
thing
we
got
to
be
careful
of
because
it's
easy
to.
A
So
I
don't.
I
try
not
to
do
more
than
five
when
I'm
doing
one
of
these
kinds
of
reports,
but
you
can
see
you
know.
Corning
had
40
checkouts
36
rules,
but
they
didn't
have
zero
patron.
They
added
zero
patrons
last
month.
So
there's
a
blank
spot
there
in
highland
community
college.
They
only
had
patrons
really
beginning
the
so
it'll
have
the
blank
spots
there
for
the
spaces.
A
Excellent
well
george.
Thank
you
very
much
for
enlightening
us.
I
really
appreciate
all
that
you
have
shown
us
and
can't
wait
for
the
the
next
video
to
come
out
so
you've.
Certainly
given
me
some
ideas
for
my
report,
writing
for
the
future.
So
thank
you
very
much.
Okay!
So
thanks
everybody
and
we'll
see
you.