August 30, 2012 at 1:12 pm
I store if a user visits my website via my mobile app or regular desktop browser.
I want to generate a report over the last 2 years on this (for various reasons I cant use Google Analytics, so dont suggest I should use that).
In channel I store either "mobile" or "website".
Here's the table definition:
/****** Object: Table [dbo].[location_views] Script Date: 08/30/2012 21:03:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[location_views](
[id] [int] IDENTITY(1,1) NOT NULL,
[objectid] [int] NOT NULL,
[cookieid] [nvarchar](50) NULL,
[username] [nvarchar](50) NULL,
[IPAddress] [nvarchar](50) NOT NULL,
[createdate] [datetime] NOT NULL,
[channel] [nvarchar](50) NULL,
CONSTRAINT [PK_location_views_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]
GO
ALTER TABLE [dbo].[location_views] ADD CONSTRAINT [DF_location_views_createdate_1] DEFAULT (getdate()) FOR [createdate]
GO
Here's the data:
USE [tt]
GO
/****** Object: Table [dbo].[location_views] Script Date: 08/30/2012 21:09:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[location_views](
[id] [int] IDENTITY(1,1) NOT NULL,
[objectid] [int] NOT NULL,
[cookieid] [nvarchar](50) NULL,
[username] [nvarchar](50) NULL,
[IPAddress] [nvarchar](50) NOT NULL,
[createdate] [datetime] NOT NULL,
[channel] [nvarchar](50) NULL,
CONSTRAINT [PK_location_views_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]
GO
SET IDENTITY_INSERT [dbo].[location_views] ON
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1401, 50, N'', N'', N'66.249.71.27', CAST(0x00009F01012B99C1 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1402, 48, N'', N'', N'66.249.71.27', CAST(0x00009F010130210B AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1403, 51, N'', N'', N'66.249.71.27', CAST(0x00009F010130C854 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1404, 32, N'', N'', N'82.176.192.11', CAST(0x00009F0101601338 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1405, 49, N'', N'', N'66.249.71.27', CAST(0x00009F0101761CF8 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1406, 22, N'', N'', N'66.249.71.20', CAST(0x00009F020019F498 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1407, 62, N'', N'', N'66.249.71.27', CAST(0x00009F020078789D AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1408, 53, N'', N'', N'66.249.71.27', CAST(0x00009F020079318A AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1409, 37, N'', N'', N'66.249.71.20', CAST(0x00009F02007C201B AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1410, 66, N'', N'', N'95.97.44.158', CAST(0x00009F02008F0D41 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1411, 48, N'', N'', N'66.249.71.27', CAST(0x00009F02009E60C3 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1412, 36, N'', N'', N'213.144.230.50', CAST(0x00009F0200B11434 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1413, 36, N'', N'', N'213.144.230.50', CAST(0x00009F0200B12192 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1414, 36, N'', N'', N'213.144.230.50', CAST(0x00009F0200B165F0 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1415, 51, N'', N'', N'82.161.39.87', CAST(0x00009F0200C57D1A AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1416, 51, N'', N'', N'82.161.39.87', CAST(0x00009F0200C57E93 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1417, 34, N'', N'', N'66.249.71.27', CAST(0x00009F0200D57FF2 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1418, 67, N'', N'', N'77.171.130.38', CAST(0x00009F0200E94307 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1419, 21, N'', N'', N'91.182.197.23', CAST(0x00009F020112702F AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1420, 48, N'', N'', N'82.210.71.18', CAST(0x00009F02011F7E34 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1421, 48, N'', N'', N'82.210.71.18', CAST(0x00009F02011F83C5 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1422, 49, N'', N'', N'93.125.231.41', CAST(0x00009F02016753E9 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1423, 33, N'', N'', N'66.249.71.230', CAST(0x00009F02018B11D4 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1424, 33, N'', N'', N'66.249.71.230', CAST(0x00009F0300237B9C AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1425, 20, N'', N'', N'80.246.196.145', CAST(0x00009F030093D3E6 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1426, 34, N'', N'', N'67.195.112.124', CAST(0x00009F03009E8F36 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1427, 43, N'', N'', N'86.81.19.176', CAST(0x00009F0300B9C9D7 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1428, 50, N'', N'', N'82.73.215.214', CAST(0x00009F0300BB0886 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1429, 50, N'', N'', N'82.73.215.214', CAST(0x00009F0300C0B2B5 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1430, 49, N'', N'', N'84.87.227.71', CAST(0x00009F0300CFAABF AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1431, 24, N'', N'', N'84.87.227.71', CAST(0x00009F0300CFFCC3 AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1432, 37, N'', N'', N'80.252.86.72', CAST(0x00009F0300DAAFAE AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1433, 59, N'', N'', N'83.119.240.200', CAST(0x00009F0300E4AF8A AS DateTime), N'mobile')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1434, 9, N'', N'', N'83.119.240.200', CAST(0x00009F0300E5602D AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1435, 51, N'', N'', N'83.119.240.200', CAST(0x00009F0300E5A2B6 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1436, 7, N'', N'', N'66.249.71.27', CAST(0x00009F0300E94363 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1437, 21, N'', N'', N'66.249.71.27', CAST(0x00009F0300ED4B3F AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1438, 37, N'', N'', N'80.252.86.72', CAST(0x00009F0300EEFC8B AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1439, 24, N'', N'', N'66.249.71.20', CAST(0x00009F0300F3F236 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1440, 9, N'', N'', N'66.249.71.27', CAST(0x00009F0300F664F1 AS DateTime), N'website')
INSERT [dbo].[location_views] ([id], [objectid], [cookieid], [username], [IPAddress], [createdate], [channel]) VALUES (1441, 51, N'', N'', N'66.249.71.27', CAST(0x00009F030105564A AS DateTime), N'website')
SET IDENTITY_INSERT [dbo].[location_views] OFF
/****** Object: Default [DF_location_views_createdate_1] Script Date: 08/30/2012 21:09:21 ******/
ALTER TABLE [dbo].[location_views] ADD CONSTRAINT [DF_location_views_createdate_1] DEFAULT (getdate()) FOR [createdate]
GO
I currently have this statement:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount
FROM location_views
WHERE createdate> DATEADD(dd, -730, GETDATE())
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) ORDER BY createdate ASC
which gives me:
createdatetotalcount
2011-06-13 00:00:00.0005
2011-06-14 00:00:00.00018
2011-06-15 00:00:00.00018
DESIRED OUTPUT (numbers may not match scripted data, but you get the point)
createdatemobileviewswebsiteviews
2011-06-13 00:00:00.00050
2011-06-14 00:00:00.000117
2011-06-15 00:00:00.000153
August 30, 2012 at 1:17 pm
Your query returns nothing from the sample data provided.
August 30, 2012 at 1:21 pm
I updated my question to return not last 30 days, but last 2 years. Now the data is correct again 🙂 Thanks!
August 30, 2012 at 1:24 pm
Modify as needed for your site:
SELECT
DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,
COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount,
sum(case channel when 'website' then 1 else 0 end) as WebsiteViews,
sum(case channel when 'mobile' then 1 else 0 end) as MobileViews
FROM
location_views
WHERE
createdate> DATEADD(dd, -30, '2011-07-01')
GROUP BY
DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))
ORDER BY
createdate ASC;
August 30, 2012 at 1:27 pm
Thanks! 🙂
August 31, 2012 at 2:33 am
Hi Lynn,
1 additional question: what if I want to combine these results with another similar table?
Besides location_views I also have table artist_views (which has the same column definition).
I know I should perhaps combine these tables physically in the future but for now I want to sum the values in both tables:
So here's my statement:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,
COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount,
sum(case channel when 'website' then 1 else 0 end) as WebsiteViews,
sum(case channel when 'mobile' then 1 else 0 end) as MobileViews
FROM location_views
WHERE createdate> DATEADD(dd, -3, GETDATE())
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))
UNION
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,
COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount,
sum(case channel when 'website' then 1 else 0 end) as WebsiteViews,
sum(case channel when 'mobile' then 1 else 0 end) as MobileViews
FROM artist_views
WHERE createdate> DATEADD(dd, -3, GETDATE())
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))
ORDER BY createdate ASC
But that gives me this output:
createdatetotalcountWebsiteViewsMobileViews
2012-08-29 00:00:00.0001711710
2012-08-29 00:00:00.0003803800
2012-08-30 00:00:00.0001401400
2012-08-30 00:00:00.0004104100
2012-08-31 00:00:00.00051492
2012-08-31 00:00:00.00099990
Where I want the summed values per date:
createdatetotalcountWebsiteViewsMobileViews
2012-08-29 00:00:00.0005515510
2012-08-30 00:00:00.0005505500
2012-08-31 00:00:00.0001501482
How can I change your initial statement to do that?
Thanks again! 🙂
August 31, 2012 at 3:12 am
Try unioning the data first, then aggregating it all. Look up CTE's and/or derived tables. I would do more but I need to hit the road as I have a 12+ hour drive ahead of me for our (USA) Labor Day Weekend.
August 31, 2012 at 3:20 am
Ok, first of all safe driving and happy labor day 🙂
As you can see I'm already unioning the data, so I guess so far so good? So I would now have to look in aggregating these results somehow?
Thanks!
August 31, 2012 at 4:00 am
Peter,
Lynn is saying after you've done the UNION you can encapsulate that query within a CTE (Common table expression) http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx.
I think this is what you want
WITH AllViews AS
(
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,
COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount,
sum(case channel when 'website' then 1 else 0 end) as WebsiteViews,
sum(case channel when 'mobile' then 1 else 0 end) as MobileViews
FROM location_views
WHERE createdate> DATEADD(dd, -3, GETDATE())
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))
UNION
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,
COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount,
sum(case channel when 'website' then 1 else 0 end) as WebsiteViews,
sum(case channel when 'mobile' then 1 else 0 end) as MobileViews
FROM artist_views
WHERE createdate> DATEADD(dd, -3, GETDATE())
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))
)
SELECT createdate,
SUM(totalcount) AS totalcount,
SUM(WebsiteViews) AS WebsiteViews,
SUM(MobileViews) AS MobileViews
FROM AllViews
GROUP BY createdate
ORDER BY createdate ASC
Dave
August 31, 2012 at 4:10 am
Here's another way:
;WITH Locations AS (
SELECT
DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,
COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount,
sum(case channel when 'website' then 1 else 0 end) as WebsiteViews,
sum(case channel when 'mobile' then 1 else 0 end) as MobileViews
FROM location_views
WHERE createdate> DATEADD(dd, -3, GETDATE())
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))
),
Artists AS (
SELECT
DATEADD(dd, 0, DATEDIFF(dd, 0, createdate)) AS createdate,
COUNT(DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))) AS totalcount,
sum(case channel when 'website' then 1 else 0 end) as WebsiteViews,
sum(case channel when 'mobile' then 1 else 0 end) as MobileViews
FROM artist_views
WHERE createdate> DATEADD(dd, -3, GETDATE())
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, createdate))
)
SELECT
createdate= ISNULL(l.createdate,a.createdate),
totalcount= ISNULL(l.totalcount,0) + ISNULL(a.totalcount,0),
WebsiteViews= ISNULL(l.WebsiteViews,0) + ISNULL(a.WebsiteViews,0),
MobileViews= ISNULL(l.MobileViews,0) + ISNULL(a.MobileViews,0)
FROM Locations l
FULL OUTER JOIN Artists a
ON a.createdate = l.createdate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply