September 17, 2010 at 1:14 pm
Hi,
I have to create a report for billing to clients: Here are some details:
-- create sample table of users
CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[LoginId] [varchar](100) NOT NULL,
[FullName] [varchar](50) NULL,
[AddedDateTime] [datetime] NULL,
[UpdatedDateTime] [datetime] NULL,
[IsDeleted] [bit] NULL,
CONSTRAINT [PK_TLUser_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- Insert data in users table for sampling
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test01@login.com', 'Test User01', '12-Dec-2009', '15-Jan-2010', 0)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test02@login.com', 'Test User02', '15-Dec-2009', '17-Jan-2010', 1)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test03@login.com', 'Test User03', '23-Jan-2010', '23-Apr-2010', 1)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test04@login.com', 'Test User04', '27-Jan-2010', '28-Jan-2010', 0)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test05@login.com', 'Test User05', '10-Feb-2010', '10-Feb-2010', 0)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test06@login.com', 'Test User06', '14-Feb-2010', '25-Apr-2010', 0)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test07@login.com', 'Test User07', '26-Feb-2010', '22-Mar-2010', 1)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test08@login.com', 'Test User08', '29-Mar-2010', '24-Apr-2010', 0)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test09@login.com', 'Test User09', '10-Mar-2010', '10-Mar-2010', 0)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test10@login.com', 'Test User10', '21-Mar-2010', '21-Apr-2010', 1)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test11@login.com', 'Test User11', '11-Mar-2010', '11-Mar-2010', 0)
INSERT INTO Users (LoginId, FullName, AddedDateTime, UpdatedDateTime, IsDeleted) VALUES ('test12@login.com', 'Test User12', '26-Apr-2010', '26-Apr-2010', 0)
select LoginId, CONVERT(varchar, addeddatetime, 107), CONVERT(varchar, UpdatedDatetime, 107) upd, IsDeleted from users
The desired output is as follows:
MonthNew UsersDeletedTotal Billing
Dec-09--2--0--2
Jan-10--2--1--4
Feb-10--3--0--6
Mar-10--4--1--10
Apr-10--1--2--10
May-10--0--0--8
I need the output for only Month and Total Billing rest of the two middle columns are just for explaination.
Formula for total billing would be: (Sum of total users up till this month - Sum of total users deleted before this month)
Please let me know how do i do this from users table with sample data.
Shamshad Ali.
September 17, 2010 at 2:14 pm
Would something along the following lines do the job?
Side note: the basic concept is to use a calendar based on month and cross join the users table to that calendar. The calendar CTE is hard coded to cover the time span as per your example so you might need to change it if required.
;
WITH calendar AS -- build a claendar table on the fly
(
SELECT DATEADD(mm,n,'20091201') START,DATEADD(mm,n+1,'20091201') finish
FROM
(
SELECT number n FROM master..spt_values WHERE TYPE ='P' AND number < 6
)x
), user_status AS -- cross join calendar on user, setting deleted flag and added flag for the related month
(
SELECT
CASE WHEN isdeleted =1 AND updateddatetime < START THEN 1 ELSE 0 END AS deleted,
CASE WHEN addeddatetime < finish THEN 1 ELSE 0 END AS added,
c.START,
c.finish
FROM users u
CROSS APPLY calendar c
WHERE c.finish > u.addeddatetime
)
-- final result by aggregation of status flags per month
SELECT
CONVERT(VARCHAR, start, 107) as Report_Month,
SUM(added) as RunningTotal_added ,
SUM(deleted) as RunningTotal_deleted,
SUM(added)-SUM(deleted) as Total_Billing
FROM user_status
GROUP BY START
ORDER BY START
/* result set:
Report_Month RunningTotal_added RunningTotal_deleted Total_Billing
Dec 01, 2009 2 0 2
Jan 01, 2010 4 0 4
Feb 01, 2010 7 1 6
Mar 01, 2010 11 1 10
Apr 01, 2010 12 2 10
May 01, 2010 12 4 8
*/
September 20, 2010 at 7:30 am
Thanks, this worked - Great.
With continuation, I have one another table "UserTracking" in which there is duration of users something like below:
Table: UserTracking
Columns: startDateTime, endDateTime, Duration, UserId
The duration col. contains data in seconds like computed col. but not exaction as below - just for sampling, you may go with following for your understanding:
DateDiff(ss,StartDateTime, EndDateTime)
I would like to add the duration in Monthly output as well.
Could you plz. let me know how do i do this. I tried following but its giving wrong output:
Declare @BillingFrom datetime, @BillingTo datetime
set @BillingFrom = '12-dec-2009 3:45 PM'
set @BillingTo = '25-Feb-2011 4:45 PM'
-- EXEC uspMonthlyBilling @BillingFrom='12-dec-2009 3:45 PM', @BillingTo= '25-Feb-2011 4:45 PM'
set @BillingFrom = left(convert (varchar, @BillingFrom, 112), 6 ) + '01';
WITH calendar AS -- build a claendar table on the fly
(
SELECT DATEADD(mm,n,@BillingFrom) START,DATEADD(mm,n+1,@BillingFrom) finish
FROM
(
SELECT number n FROM master..spt_values WHERE TYPE ='P' AND number < DATEDIFF(mm, @BillingFrom, @BillingTo)
)x
), user_status AS -- cross join calendar on user, setting deleted flag and added flag for the related month
(
SELECT
CASE WHEN isdeleted =1 AND updateddatetime < START THEN 1 ELSE 0 END AS deleted,
CASE WHEN addeddatetime < finish THEN 1 ELSE 0 END AS added,
c.START,
c.finish
FROM users u
CROSS APPLY calendar c
WHERE c.finish > u.addeddatetime
), user_viewing AS
(
select TotalViewingTime*60 as ViewingTime, c.START, c.finish from UserTracking ut CROSS APPLY calendar c
where ut.StartDateTimeOnClient between c.start and c.finish
)
--select replace (CONVERT(VARCHAR, start, 107), ' 01','') as [Billing Month], sum(ViewingTime) from user_viewing
--GROUP BY START
--ORDER BY START
SELECT
replace (CONVERT(VARCHAR, us.start, 107), ' 01','') as [Billing Month], sum(ViewingTime),
-- SUM(added) as RunningTotal_added ,
-- SUM(deleted) as RunningTotal_deleted,
SUM(added)-SUM(deleted) as [Users]
FROM user_status us inner join user_viewing uv on us.start = uv.start
GROUP BY us.START
ORDER BY us.START
The required output would be something like below:
Billing Month------Viewing Hours------Users
Dec, 2009------11 hours 23 mins------2
Jan, 2010------23 mins------4
Feb, 2010------1 hour 3 mins------6
Mar, 2010------3 hours 1 min------10
Apr, 2010------20 hours 5 mins------10
May, 2010------12 hours 44 mins------8
Jun, 2010------5 hours------8
Jul, 2010------55 mins------8
Aug, 2010------50 mins------8
Sep, 2010------1 min------8
September 20, 2010 at 11:19 am
Please provide table def and sample data for your UserTracking table together with some sample data and expected result based on your sample. (you did an excellent job posting the users data already, so why go below that level? 😉 )
September 20, 2010 at 12:19 pm
I'm sorry as I was so busy, so I made post like this: Here is the script
-- create sample table of users
CREATE TABLE [dbo].[UserTracking](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[LoginId] [varchar](100) NOT NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
[Duration] [int] NULL,
CONSTRAINT [PK_UserTracking_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- Insert data in UserTracking table for sampling
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test01@login.com', '12-Dec-2009 3:45 PM', '12-Dec-2009 4:12 PM', 1620)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test02@login.com', '15-Dec-2009 2:22 AM', '15-Dec-2009 2:23 AM', 60)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test03@login.com', '23-Jan-2010 5:09 PM', '23-Apr-2010 7:30 PM', 7784460)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test01@login.com', '27-Jan-2010 4:55 PM', '28-Jan-2010 7:12 PM', 51420)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test02@login.com', '10-Feb-2010 7:15 AM', '10-Feb-2010 7:20 AM', 300)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test03@login.com', '14-Feb-2010 9:13 PM', '14-Feb-2010 1:21 AM', 14880)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test01@login.com', '26-Feb-2010 4:45 AM', '26-Feb-2010 5:10 AM', 1500)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test02@login.com', '29-Mar-2010 6:15 PM', '24-Apr-2010 6:21 PM', 2246760)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test03@login.com', '10-Mar-2010 1:20 PM', '10-Mar-2010 2:01 PM', 2460)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test01@login.com', '21-Mar-2010 8:55 PM', '22-Mar-2010 2:05 AM', 18600)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test02@login.com', '11-Mar-2010 6:10 AM', '11-Mar-2010 8:21 AM', 7860)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime) VALUES ('test03@login.com', '26-Apr-2010 2:44 PM', '26-Apr-2010 3:21 PM', 2220)
The output would be something like i posted earlier, but the data would be different. Hope you will understand what I meant. Thanks in advance.
Shamshad Ali
September 20, 2010 at 1:34 pm
Please provide your expected results based on your sample data.
After changing some column names and the calculation inside the user_viewing CTE I got the following results:
Billing Month(No column name)Users
Dec, 20090hrs 56min4
Jan, 20108706hrs 32min8
Feb, 201032hrs 26min18
Mar, 20106953hrs 28min40
Apr, 20107hrs 24min10
And here's the modified section:
, user_viewing AS
(
select [Duration]/60 as ViewingTime, c.START, c.finish from UserTracking ut CROSS APPLY calendar c
where ut.[StartDateTime] between c.start and c.finish
)
--select replace (CONVERT(VARCHAR, start, 107), ' 01','') as [Billing Month], sum(ViewingTime) from user_viewing
--GROUP BY START
--ORDER BY START
SELECT
replace (CONVERT(VARCHAR, us.start, 107), ' 01','') as [Billing Month],
cast(sum(ViewingTime)/60 as varchar(5)) +'hrs ' + cast(sum(ViewingTime)%60 as varchar(5)) + 'min',
-- SUM(added) as RunningTotal_added ,
-- SUM(deleted) as RunningTotal_deleted,
SUM(added)-SUM(deleted) as [Users]
FROM user_status us inner join user_viewing uv on us.start = uv.start
September 21, 2010 at 1:14 am
The fix is giving wrong output as it is same as i had worked on it and sent you the query. Also there was some data issue in Duration Col in my last post in which i was inserting incorrect duration in UserTracking Table.
The output should be as follows:
Billing_Month---------ViewingTime---------BillingUsers
Dec, 2009---------0 hrs 28 min----------------2 (Existing output is giving 0 hrs 56 mins and 4 users - which is cross apply the actual data)
Jan, 2010---------26 hrs 31 min----------------4
Feb, 2010---------1 hrs 38 min----------------6
Mar, 2010---------10 hrs 8 min----------------10
Apr, 2010---------0 hrs 37 min----------------10
May, 2010---------0 hrs 0 min----------------8
Jun, 2010---------0 hrs 0 min----------------8
Jul, 2010---------0 hrs 0 min----------------8
Aug, 2010---------0 hrs 0 min----------------8
Sep, 2010---------0 hrs 0 min----------------8
Oct, 2010---------0 hrs 0 min----------------8
Nov, 2010---------0 hrs 0 min----------------8
Dec, 2010---------0 hrs 0 min----------------8
Jan, 2011---------0 hrs 0 min----------------8
Please truncate the previous data:
-- Truncate previous data
Truncate Table dbo.UserTracking
GO
-- Insert correct data for which the desired result is placed above
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test01@login.com', '12-Dec-2009 3:45 PM', '12-Dec-2009 4:12 PM', 1620)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test02@login.com', '15-Dec-2009 2:22 AM', '15-Dec-2009 2:23 AM', 60)
--Total 0 hrs 28 min
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test03@login.com', '23-Jan-2010 5:09 PM', '24-Jan-2010 7:30 PM', 94860)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test01@login.com', '31-Jan-2010 11:55 PM', '1-Feb-2010 12:05 AM', 600)
-- Total 26 hrs 31 min
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test02@login.com', '10-Feb-2010 7:15 AM', '10-Feb-2010 7:20 AM', 300)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test03@login.com', '14-Feb-2010 9:13 PM', '14-Feb-2010 10:21 PM', 4080)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test01@login.com', '26-Feb-2010 4:45 AM', '26-Feb-2010 5:10 AM', 1500)
-- Total 1 hrs 38 min
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test02@login.com', '31-Mar-2010 11:15 PM', '1-Apr-2010 1:21 AM', 7560)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test03@login.com', '10-Mar-2010 1:20 PM', '10-Mar-2010 2:01 PM', 2460)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test01@login.com', '21-Mar-2010 8:55 PM', '22-Mar-2010 2:05 AM', 18600)
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test02@login.com', '11-Mar-2010 6:10 AM', '11-Mar-2010 8:21 AM', 7860)
-- Total 10 hrs 8 min
INSERT INTO UserTracking (LoginId, StartDateTime, EndDateTime, Duration) VALUES ('test03@login.com', '26-Apr-2010 2:44 PM', '26-Apr-2010 3:21 PM', 2220)
-- Total 0 hrs 37 min
Our existing query which has following results which is actually multiplying the data
Billing Month (No column name) Users
Dec, 2009 0hrs 56min 4
Jan, 2010 8706hrs 32min 8
Feb, 2010 32hrs 26min 18
Mar, 2010 6953hrs 28min 40
Apr, 2010 7hrs 24min 10
Also if i have given the date range up till 25-Feb- it should calculate the output up till Jan end. Like i have mentioned desired out above.
Declare @BillingFrom datetime, @BillingTo datetime
set @BillingFrom = '12-dec-2009 3:45 PM'
set @BillingTo = '25-Feb-2011 4:45 PM'
Shamshad Ali.
September 21, 2010 at 1:29 am
If I separately query for each results it is something like below:
Declare @BillingFrom datetime, @BillingTo datetime
set @BillingFrom = '12-dec-2009 3:45 PM'
set @BillingTo = '25-Feb-2011 4:45 PM'
-- EXEC uspMonthlyBilling @BillingFrom='12-dec-2009 3:45 PM', @BillingTo= '25-Feb-2011 4:45 PM'
set @BillingFrom = left(convert (varchar, @BillingFrom, 112), 6 ) + '01';
WITH calendar AS -- build a claendar table on the fly
(
SELECT DATEADD(mm,n,@BillingFrom) START,DATEADD(mm,n+1,@BillingFrom) finish
FROM
(
SELECT number n FROM master..spt_values WHERE TYPE ='P' AND number < DATEDIFF(mm, @BillingFrom, @BillingTo)
)x
), user_viewing AS
(
select duration/60 as ViewingTime, c.START, c.finish from UserTracking ut CROSS APPLY calendar c
where ut.StartDateTime between c.start and c.finish
)
SELECT
replace (CONVERT(VARCHAR, uv.start, 107), ' 01','') as [Billing Month], -- , sum(ViewingTime),
cast(sum(ViewingTime)/60 as varchar(5)) +' hrs ' + cast(sum(ViewingTime)%60 as varchar(5)) + ' min'
FROM user_viewing uv
GROUP BY uv.START
ORDER BY uv.START
and OUTPUT as :
Billing Month(No column name)
Dec, 20090 hrs 28 min
Jan, 201026 hrs 31 min
Feb, 20101 hrs 38 min
Mar, 201010 hrs 8 min
Apr, 20100 hrs 37 min
But i want to merge the previous results of Billing Users to produce the following results:
Billing_Month---------ViewingTime---------BillingUsers
Dec, 2009---------0 hrs 28 min----------------2
Jan, 2010---------26 hrs 31 min----------------4
Feb, 2010---------1 hrs 38 min----------------6
Mar, 2010---------10 hrs 8 min----------------10
Apr, 2010---------0 hrs 37 min----------------10
May, 2010---------0 hrs 0 min----------------8
Jun, 2010---------0 hrs 0 min----------------8
Jul, 2010 ---------0 hrs 0 min----------------8
Aug, 2010---------0 hrs 0 min----------------8
Sep, 2010---------0 hrs 0 min----------------8
Oct, 2010---------0 hrs 0 min----------------8
Nov, 2010---------0 hrs 0 min----------------8
Dec, 2010---------0 hrs 0 min----------------8
Jan, 2011---------0 hrs 0 min----------------8
with the following code i am able to get the desired output:
Declare @BillingFrom datetime, @BillingTo datetime
set @BillingFrom = '12-dec-2009 3:45 PM'
set @BillingTo = '25-Feb-2011 4:45 PM'
DECLARE @UserTable TABLE (
[BillingMonth] varchar (9), Users int
);
DECLARE @ViewingTime TABLE (
[BillingMonth] varchar (9), TotalViewingTime varchar(20)
);
set @BillingFrom = left(convert (varchar, @BillingFrom, 112), 6 ) + '01';
WITH calendar AS -- build a claendar table on the fly
(
SELECT DATEADD(mm,n,@BillingFrom) START,DATEADD(mm,n+1,@BillingFrom) finish
FROM
(
SELECT number n FROM master..spt_values WHERE TYPE ='P' AND number < DATEDIFF(mm, @BillingFrom, @BillingTo)
)x
), user_status AS -- cross join calendar on user, setting deleted flag and added flag for the related month
(
SELECT
CASE WHEN isdeleted =1 AND updateddatetime < START THEN 1 ELSE 0 END AS deleted,
CASE WHEN addeddatetime < finish THEN 1 ELSE 0 END AS added,
c.START,
c.finish
FROM users u
CROSS APPLY calendar c
WHERE c.finish > u.addeddatetime
)
insert into @UserTable
SELECT
replace (CONVERT(VARCHAR, start, 107), ' 01','') as [Billing Month],
SUM(added)-SUM(deleted) as [Users]
FROM user_status
GROUP BY START
ORDER BY START
;
WITH Calendar AS -- build a claendar table on the fly
(
SELECT DATEADD(mm,n,@BillingFrom) START,DATEADD(mm,n+1,@BillingFrom) finish
FROM
(
SELECT number n FROM master..spt_values WHERE TYPE ='P' AND number < DATEDIFF(mm, @BillingFrom, @BillingTo)
)x
), user_viewing AS
(
select duration/60 as TotalViewingTime, c.START, c.finish from UserTracking ut CROSS APPLY Calendar c
where ut.StartDateTime between c.start and c.finish
)
insert into @ViewingTime
SELECT
replace (CONVERT(VARCHAR, uv.start, 107), ' 01','') as [Billing Month], -- , sum(ViewingTime),
cast(sum(TotalViewingTime)/60 as varchar(5)) +' hrs ' + cast(sum(TotalViewingTime)%60 as varchar(5)) + ' mins'
FROM user_viewing uv
GROUP BY uv.START
ORDER BY uv.START
select u.BillingMonth, isnull(TotalViewingTime, '0 hrs 0 mins') as TotalViewingTime, Users from @UserTable u left join @ViewingTime v on u.BillingMonth = v.BillingMonth
FYI, In my report there are 6 more columns that I have to include here further, but as i learn how to merge these results with best possible way to minimize the code and get performance, I will try to do that myself. I have other way that i have mentioned above to add results of these in different memory Tables and then merge them but I want to have all in one query without using memory tables logic if possible. Just for learning and achieving performance.
Shamshad Ali.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply