Selecting values by month/year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting values by month/year

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-05-27 : 03:25:11

I am trying to put together a table that gives a breakdown of the number of users that have subscribed/unsubscribed from an email list, and displays the data month by month. A very basic version of my database would look like this:


CREATE TABLE subscribers (
	[ID] int PRIMARY KEY,
	dateAdded smalldatetime,
	dateDeleted smalldatetime,
	active bit	-- 0 for inactive, 1 for active
)
GO

INSERT INTO subscribers (dateAdded, dateDeleted) VALUES ('2000-01-14', NULL, 1)
GO
INSERT INTO subscribers (dateAdded, dateDeleted) VALUES ('2000-02-15', NULL, 1)
GO
INSERT INTO subscribers (dateAdded, dateDeleted) VALUES ('2000-01-17', '2000-02-21', 0)
GO
INSERT INTO subscribers (dateAdded, dateDeleted) VALUES ('2000-02-16', '2000-03-10', 0)
GO

Basically, I want a breakdown of the results like this:


|  Month/Year   |  Subscribers  | Unsubscribers |
|---------------|---------------|---------------|
|    1/2000     |       2       |        0      |
|    2/2000     |       2       |        1      |
|    3/2000     |       0       |        1      |

It would also be great to have a running total of the number of users that are subscribed up to that month, but I can just add that through code when I am looping through the records.

Can anyone help me?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-05-27 : 04:20:28

The best way to do this is already have a table with all the months / years in it.

Here is an example where I have made that table on the fly with some derived tables and unions.

(It's not real pretty)

Select TheMonth, TheYear,
SUM(
CASE WHEN Month(dateAdded) = TheMonth AND Year(dateAdded) = TheYear THEN 1 ELSE 0 END) as Added,
SUM(
CASE WHEN Month(dateDeleted) = TheMonth AND Year(dateDeleted) = TheYear THEN 1 ELSE 0 END) as Deleted

FROM

(
SELECT *
FROM
(
SELECT 1 as TheMonth
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
UNION SELECT 11
UNION SELECT 12
) months
CROSS JOIN (
SELECT 2000 as TheYear
UNION SELECT 2001
UNION SELECT 2002
UNION SELECT 2003
UNION SELECT 2004
) Years
) DateTable

INNER JOIN Subscribers
ON
(Month(dateAdded) = TheMonth AND Year(dateAdded) = TheYear)
OR
(Month(dateDeleted) = TheMonth AND Year(dateDeleted) = TheYear)

GROUP BY TheMonth, TheYear
ORDER BY TheYear, TheMonth

Do a search for running totals on the site for a good article on them.

Damian

Edited by - merkin on 05/27/2003 04:20:55

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-05-27 : 19:40:23

Thanks Merkin, that worked like a charm!! Checking out the running totals article now (http://www.sqlteam.com/item.asp?ItemID=3856).

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-27 : 22:30:05

I wouldn't use running total techniques for this; I'd attack it from a different angle.

You could generate the year/months like Merkin demonstrates, but you might calculate the running totals and the daily activity all at once something like this:

select Years.Y as Year, Months.M as Month,
SUM(CASE WHEN DeletedMonth <> Years.Y * 100 + Months.M THEN 1 ELSE 0 END) as ActiveSubscribers,
SUM(CASE WHEN AddedMonth = Years.Y * 100 + Months.M THEN 1 ELSE 0 END) as Added,
SUM(CASE WHEN DeletedMonth = Years.Y * 100 + Months.M THEN 1 ELSE 0 END) as Deleted
from
(select 2000 as Y union select 2001 union select 2002 union select 2003) Years
CROSS JOIN
(select 1 as M union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12) Months
INNER JOIN
(
Select ID,
Year(dateAdded)*100 + Month(dateAdded) as AddedMonth,
ISNULL(Year(DateDeleted) * 100 + Month(DateDeleted), 999999) as DeletedMonth
FROM
subscribers
) s
ON
Years.Y * 100 + Months.M BETWEEN s.AddedMonth and s.DeletedMonth
GROUP BY Years.Y, Months.M

This assumes that if you delete on ANY DAY a month, you are not active for that entire month. By the same token, if you are added ANY DAY in a month, you are active for that entire month.

It also uses an "old fashioned" technique to do "date math" with only years and months (the old y*100 + m formula) that some of you may enjoy.

It seemed to be the easiest way to solve this problem. Of course, it completely negates the existance of any possible indexes on the date fields but you get the idea.

The actual joining of the data can be done differently or more efficiently, but the technique should be pretty sound.

- Jeff

Edited by - jsmith8858 on 05/27/2003 22:32:14

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-05-28 : 03:14:00

Thanks Jeff, I have already coded this so that the total get calculated programmatically as they are displayed, but I will take a good look at your example so I have an understanding of that you have done (any maybe change my code).

Thanks again.

peddi_praveen
Starting Member

48 Posts

Posted - 2003-05-28 : 07:37:22

my approach is like this:
----- this is ur table structure
CREATE TABLE subscribers (
[ID] int PRIMARY KEY,
dateAdded smalldatetime,
dateDeleted smalldatetime,
active bit -- 0 for inactive, 1 for active
)
GO

INSERT INTO subscribers (dateAdded, dateDeleted, active) VALUES ('2003-01-14', NULL, 1)
GO
INSERT INTO subscribers (dateAdded, dateDeleted,active) VALUES ('2003-02-15', NULL, 1)
GO
INSERT INTO subscribers (dateAdded, dateDeleted,active) VALUES ('2003-01-17', '2000-02-21', 0)
GO
INSERT INTO subscribers (dateAdded, dateDeleted, active) VALUES ('2003-02-16', '2000-03-10', 0)
GO
----- i had modified data for more clarifications

solution:
create a lookup table l_date_month like this:
create table l_date_month
(year int,
month smallint
)

create index in the order , in which ur reports needs.
in this case in the order of month,year
u cud manually insert the possible records

insert into l_date_month values(2003,1)
insert into l_date_month values(2003,2)
insert into l_date_month values(2003,3)
insert into l_date_month values(2003,4)
insert into l_date_month values(2003,5)
insert into l_date_month values(2003,6)
........

select lookup.yr,lookup.mnth,
SUM(CASE WHEN Month(added.dateAdded) = lookup.mnth AND Year(added.dateAdded) = lookup.yr THEN 1 ELSE 0 END) as Added1 ,
SUM(CASE WHEN Month(deleted.datedeleted) = lookup.mnth AND Year(deleted.datedeleted) = lookup.yr THEN 1 ELSE 0 END) as deleted1

from l_date_month lookup
left outer join subscribers added
on datepart(mm,added.dateadded) =mnth
and datepart(yyyy,added.dateadded) =yr
left outer join subscribers deleted
on datepart(mm,deleted.datedeleted) =mnth
and datepart(yyyy,deleted.datedeleted) =yr
group by mnth, yr

----
the above stmt will return u 0 n 0 for those records, whre year n month not exist in ur subscribers table.
-------
--------
to eliminate the records from result,
assuming u vl b populating lookup table for future months
also in one shot---
following stmt vl retrive the results till system date
-------

select outset.yr,
outset.mnth,
ISNULL(subset.added1,0) as added,
ISNULL(subset.deleted1,0) as deleted
from l_date_month outset
join
(
select lookup.yr,lookup.mnth,
SUM(CASE WHEN Month(added.dateAdded) = lookup.mnth AND Year(added.dateAdded) = lookup.yr THEN 1 ELSE 0 END) as Added1 ,
SUM(CASE WHEN Month(deleted.datedeleted) = lookup.mnth AND Year(deleted.datedeleted) = lookup.yr THEN 1 ELSE 0 END) as deleted1

from l_date_month lookup
left outer join subscribers added
on datepart(mm,added.dateadded) =mnth
and datepart(yyyy,added.dateadded) =yr
left outer join subscribers deleted
on datepart(mm,deleted.datedeleted) =mnth
and datepart(yyyy,deleted.datedeleted) =yr
group by yr, mnth
) subset
on outset.mnth = subset.mnth
and outset.yr = subset.yr
where outset.yr <= datepart(yyyy, getdate())
and outset.mnth <= datepart(mm,getdate())

hope this vl b helpful
praveen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources