create table bookings (
bk_id uniqueidentifier default (newid()) primary key ,
bk_date datetime not null default (getdate()) ,
bk_quantity int not null default (1),
bk_amount decimal(19,4) not null ,
bk_region nvarchar(10) not null default ('EE-RDMS') ,
bk_salesperson nvarchar(50) ,
bk_customer nvarchar(10) not null
)
;
2. Fill initial data, utilizing some of the defaults set above to make things easier.
insert into bookings( bk_amount, bk_salesperson, bk_customer )
select 1234.56, 'angeliii', 'C104'
union select 567.89, 'chapmandew', 'C567'
union select 987.65, 'mark_wills', 'C543'
union select 234.50, 'aneeshattingal', 'C123'
union select 5234.56, 'angeliii', 'C123'
union select 569.50, 'chapmandew', 'C890'
union select 98.765, 'mark_wills', 'C1011'
union select 1234.50, 'aneeshattingal', 'C1234'
union select 567.89, 'mark_wills', 'C568'
union select 987.65, 'mark_wills', 'C543'
union select 1234.75, 'aneeshattingal', 'C123'
union select 3234.15, 'aneeshattingal', 'C246'
union select 5690.50, 'chapmandew', 'C543'
union select 980.765, 'chapmandew', 'C1011'
union select 12345.00, 'angeliii', 'C1234'
union select 67.89, 'angeliii', 'C567'
union select 87.65, 'mark_wills', 'C543'
union select 34.50, 'aneeshattingal', 'C123'
union select 234.56, 'angeliii', 'C123'
union select 69.50, 'chapmandew', 'C890'
union select 8.765, 'mark_wills', 'C1011'
union select 234.50, 'aneeshattingal', 'C1234'
union select 67.89, 'mark_wills', 'C568'
union select 87.65, 'angeliii', 'C543'
union select 234.75, 'aneeshattingal', 'C123'
union select 234.15, 'mark_wills', 'C246'
union select 690.50, 'chapmandew', 'C543'
union select 80.765, 'mwvisa1', 'C1011'
union select 2345.00, 'angeliii', 'C1234'
union select 345.00, 'mwvisa1', 'C1234'
;
3. Let's add some additional data values by inserts based on manipulating existing data.
while (select count(*) from bookings) < 400
insert into bookings( bk_date, bk_amount, bk_salesperson, bk_customer)
select bk_date+16 ,
bk_amount * 2.00, bk_salesperson, bk_customer
from bookings
union
select bk_date+4 ,
bk_amount * 1.25, bk_salesperson, bk_customer
from bookings
union
select bk_date-5 ,
bk_amount * 0.80, bk_salesperson, bk_customer
from bookings
;
4. Let's spread our data chronologically and add some quantities by updating our test data.
with bks
as
(
select bk_id, bk_date, bk_quantity,
(bk_amount / 500) x, -- create factor for each row by amount
-- assign different number to each record
ntile( 12+month(getdate())-1 )
-- get random ordering to make a little more real world
over ( order by rand( checksum( newid() ) ) ) n
from bookings
)
update bks
set bk_date = dateadd(month, -n, bk_date+n) ,
bk_quantity = n * rand(x) * x + bk_quantity
;
Hopefully this is a little more realistic without getting too (or more) anal-retentive. Please note, due to the "random" nature of building the data, your own examples might yield different results to those shown here.
datediff(month, bk_date, getdate())
Now we have a nice integer representing the number of months ago a date in our data is from today.
datediff(quarter, bk_date, getdate())
datediff(year, bk_date, getdate())
And so forth for week, day, and other valid dateparts.
datename(month, bk_date) -- e.g., September
datename(quarter, bk_date) -- e.g., 3 (3rd Quarter)
Got it!
'm' + convert(varchar, datediff(month, bk_date, getdate()))
'y' + convert(varchar, datediff(year, bk_date, getdate()))
Now we can tell 0 months ago values from those 0 years ago. This also simplifies our life as we won't have to deal with bracketing number only column names (e.g., [0]) as we can simply use m0 or y0. Furthermore, the removal of this ambiguity, alleviates us from having to type out a table name or alias prefix every time we want to reference one of these pivoted columns.
select bk_salesperson as salesperson,
bk_amount as amount_m,
'm' + convert(varchar, datediff(month, bk_date, getdate())) as monthsago,
bk_amount as amount_y,
'y' + convert(varchar, datediff(year, bk_date, getdate())) as yearsago
from dbo.bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate()), 0)
and bk_date < datediff(day, 0, getdate()) + 1
;
Now we will try our first pivot, using the above data. I will use a CTE, but a derived table could also be used. Temp table and other approaches are possible, but a benefit to CTE or derived table approach is you can utilize the demonstrated queries directly in a view.
with bks(salesperson, amount_m, monthsago, amount_y, yearsago)
as
(
select bk_salesperson,
bk_amount, 'm' + convert(varchar, datediff(month, bk_date, getdate())),
bk_amount, 'y' + convert(varchar, datediff(year, bk_date, getdate()))
from dbo.bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate()), 0)
and bk_date < datediff(day, 0, getdate()) + 1
)
select Salesperson,
m0 as CurrentMonth, -- Current month: 0 months ago.
m1 as LastMonth, -- 1 month ago.
m2 as TwoMonthsAgo, -- 2 months ago.
y0 as CurrentYear -- Current year: 0 years ago.
from bks
pivot( sum(amount_m) for monthsago in (m0, m1, m2) ) pvt_m
pivot( sum(amount_y) for yearsago in (y0) ) pvt_y
;
We have now successfully pivoted on multiple columns. Have a look.
with bks(salesperson, amount_m, monthsago, amount_y, yearsago)
as
(
select bk_salesperson,
bk_amount, 'm' + convert(varchar, datediff(month, bk_date, getdate())),
bk_amount, 'y' + convert(varchar, datediff(year, bk_date, getdate()))
from dbo.bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate()), 0)
and bk_date < datediff(day, 0, getdate()) + 1
)
select Salesperson,
sum(m0) as CurrentMonth, -- Current month: 0 months ago.
sum(m1) as LastMonth, -- 1 month ago.
sum(m2) as TwoMonthsAgo, -- 2 months ago.
sum(y0) as CurrentYear -- Current year: 0 years ago.
from bks
pivot( sum(amount_m) for monthsago in (m0, m1, m2) ) pvt_m
pivot( sum(amount_y) for yearsago in (y0) ) pvt_y
group by salesperson
;
Much better ! (or is it ?)
select bk_salesperson, sum(bk_amount) as year_total
from dbo.bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate()), 0)
and bk_date < datediff(day, 0, getdate()) + 1
group by bk_salesperson
;
Notice the difference now? That "current year" in the previous example isn't really the full current year as shown above (well, using my random data population at least)... And therein lies our "trap" using pivot, it does not quite pivot the elements according to both of those pivot requirements.
with bks( salesperson, amount, monthsago, yearsago )
as
(
select bk_salesperson, bk_amount
, 'm'+convert(char(10), datediff(month, bk_date, getdate()))
, 'y'+convert(char(10), datediff(year, bk_date, getdate()))
from dbo.bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate()), 0)
and bk_date < datediff(day, 0, getdate()) + 1
)
select m.Salesperson
, m0 as CurrentMonth -- Current month: 0 months ago.
, m1 as LastMonth -- 1 month ago.
, m2 as TwoMonthsAgo -- 2 months ago.
, y0 as CurrentYear -- Current year: 0 years ago.
from (select salesperson, amount, monthsago from bks) b1
pivot( sum(amount) for monthsago in (m0, m1, m2) ) m
inner join (select salesperson, amount, yearsago from bks) b2
pivot( sum(amount) for yearsago in (y0) ) y
on y.salesperson = m.salesperson
order by salesperson
;
Now look at these results.
create view vw_comparative_bookings
as
with bks( salesperson, amount, quantity, monthsago, yearsago )
as
(
select bk_salesperson, bk_amount, bk_quantity
, 'm'+convert(char(10), datediff(month, bk_date, getdate()))
, 'y'+convert(char(10), datediff(year, bk_date, getdate()))
from dbo.bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate())-1, 0)
and bk_date < datediff(day, 0, getdate()) + 1
)
select *
from bks
;
select pvt_aM.Salesperson
, aM0 as [Current MTD $] -- Current month-to-date.
, aM12 as [PY This Month $] -- Prior year actual for same month.
, aY0 as [CY Total $$] -- Current year-to-date.
, aY1 as [PY Total $$] -- Prior year actual (entire year).
, qM0 as [Current MTD # Units]
, qM12 as [PY This Month # Units]
, qY0 as [CY # Units]
, qY1 as [PY # Units]
, cM0 as [Current MTD # Orders]
, cM12 as [PY This Month # Orders]
, cY0 as [CY # Orders]
, cY1 as [PY # Orders]
from
-- dollars
(select salesperson, amount, 'a'+monthsago as monthsago
from vw_comparative_bookings) b1
pivot( sum(amount) for monthsago in (aM0, aM12) ) pvt_aM
inner join
(select salesperson, amount, 'a'+yearsago as yearsago
from vw_comparative_bookings) b2
pivot( sum(amount) for yearsago in (aY0, aY1) ) pvt_aY
on pvt_aM.salesperson = pvt_aY.salesperson
-- units
inner join
(select salesperson, quantity, 'q'+monthsago as monthsago
from vw_comparative_bookings) b3
pivot( sum(quantity) for monthsago in (qM0, qM12) ) pvt_qM
on pvt_aM.salesperson = pvt_qM.salesperson
inner join
(select salesperson, quantity, 'q'+yearsago as yearsago
from vw_comparative_bookings) b4
pivot( sum(quantity) for yearsago in (qY0, qY1) ) pvt_qY
on pvt_qM.salesperson = pvt_qY.salesperson
-- number of orders
inner join
(select salesperson, 1 as cnt, 'c'+monthsago as monthsago
from vw_comparative_bookings) b5
pivot( count(cnt) for monthsago in (cM0, cM12) ) pvt_cM
on pvt_aM.salesperson = pvt_cM.salesperson
inner join
(select salesperson, 1 as cnt, 'c'+yearsago as yearsago
from vw_comparative_bookings) b6
pivot( count(cnt) for yearsago in (cY0, cY1) ) pvt_cY
on pvt_cM.salesperson = pvt_cY.salesperson
;
And our new results.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Author
Commented:Moreover, thank you to those who have taken the time out of their days to read this. It has come in handy for me and hope it does the same for you.
Best regards,
Kevin
Author
Commented:Thanks again to all that read my articles.
It is very much appreciated!
Best regards and happy coding,
Kevin