January 2, 2012 at 2:29 pm
Hi
I have 2 tables
tbl_impressions
ImpressionID
SupplierID
ImpressionDate
tbl_clicks
ClickID
SupplierID
ClickDate
I need to write query where I can get number of clicks and impressions for specific supplier (supplierid from dropdownlist) for each month and year.
for example
12/2011 impressions 7 clicks 2
11/2011 impressions 5 clicks 1
.
.
.
where supplier ID = @SupplierID
through whole table
Thanks for help
January 2, 2012 at 4:03 pm
Please provide DDL and sample data as well as expected output and what you've tried so far.
For a detailed description on how to provide data in a ready to use format please see the first link in my signature.
January 2, 2012 at 4:13 pm
Hi
I am trying something like this http://demos.telerik.com/aspnet-ajax/chart/examples/databinding/database/defaultcs.aspx
top chart.
I havent got anything written yet, quite new to sql
table_impressions
impressionid | SupplierID | ImpressionDate
1 | 1 | 2012-01-01
2 | 1 | 2011-12-31
3 | 1 | 2011-11-31
table_clicks
clickid | SupplierID | ClickDate
1 | 1 | 2012-01-01
2 | 1 | 2011-12-31
3 | 1 | 2011-11-31
supplierID will come from ddl and instead of years in x-values i need last 12 months
clicks and impressions will be displayed as 2 data-series so customer can visually see how many impressions/clicks they have each month
January 2, 2012 at 4:19 pm
As mentioned earlier: please read and follow the advice given in the first article in my signature.
We're all volunteers here trying to help. So it would be great if you cold help us help you by providing ready to use sample data.
January 2, 2012 at 4:31 pm
sorry, jusr red article
table_impressions
CREATE TABLE [dbo].[tbl_supplier_impressions](
[SupplierImpressionId] [int] IDENTITY(1,1) NOT NULL,
[SupplierId] [uniqueidentifier] NOT NULL,
[ImpressionDate] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_supplier_impressions] PRIMARY KEY CLUSTERED
(
[SupplierImpressionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
table_clicks
CREATE TABLE [dbo].[tbl_supplier_clicks](
[SupplierClickId] [int] IDENTITY(1,1) NOT NULL,
[SupplierId] [uniqueidentifier] NOT NULL,
[ClickDate] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_supplier_clicks] PRIMARY KEY CLUSTERED
(
[SupplierClickId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
output i need:
supplier id is my parameter coming from ddl
date | impressions | clicks | supplier id
12/2011 | 10 | 2 | guid
11/2011 | 8 | 5 | guid
10/2011 | 20 | 4 | guid
09/2011 | 100 | 25 | guid
.
.
.
last 12 months
impressions and clicks are counts for each month
sorry i dont know how to explain it properly
January 2, 2012 at 4:48 pm
In that article is a section describing how to insert sample data (INSERT INTO ... SELECT) 😉
January 2, 2012 at 4:56 pm
CREATE TABLE [dbo].[tbl_supplier_impressions](
[SupplierImpressionId] [int] IDENTITY(1,1) NOT NULL,
[SupplierId] [uniqueidentifier] NOT NULL,
[ImpressionDate] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_supplier_impressions] PRIMARY KEY CLUSTERED
(
[SupplierImpressionId] 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].[tbl_supplier_impressions] ON
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (18, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x00009FC8013F4D4D AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (19, N'1fb6323d-0c28-41ca-9d44-e31e8370c002', CAST(0x00009FC8013F4D4E AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (20, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FC900DC720C AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (21, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FC900DC720D AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (22, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FC900DC8A07 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (23, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FC900DC8A08 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (24, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FC900DF6B85 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (25, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FC900DF6B86 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (26, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FC900E03389 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (27, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FC900E0338D AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (28, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FC9014B583C AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (29, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FC9014B583F AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (30, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FC901578E24 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (31, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FC901578E26 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (32, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FCB00EF7F24 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (33, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FCB00EF7F25 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (34, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FCC000A4397 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (35, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FCC000A4397 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (36, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FCC00FADBFC AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (37, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FCC00FADBFF AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (38, N'95eda975-546a-40aa-9fcd-51bd98d9c9ab', CAST(0x00009FCC00FAFFB2 AS DateTime))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (39, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FCC00FAFFB6 AS DateTime))
SET IDENTITY_INSERT [dbo].[tbl_supplier_impressions] OFF
/****** Object: Table [dbo].[tbl_supplier_clicks] Script Date: 01/02/2012 23:55:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_supplier_clicks](
[SupplierClickId] [int] IDENTITY(1,1) NOT NULL,
[SupplierId] [uniqueidentifier] NOT NULL,
[ClickDate] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_supplier_clicks] PRIMARY KEY CLUSTERED
(
[SupplierClickId] 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].[tbl_supplier_clicks] ON
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (11, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FC900E02CEA AS DateTime))
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (12, N'789135ee-2921-43a5-912c-2bb91e436a68', CAST(0x00009FC900E03870 AS DateTime))
SET IDENTITY_INSERT [dbo].[tbl_supplier_clicks] OFF
January 2, 2012 at 5:17 pm
Based on your sampe data, what would be your expected result?
It's partially unclear since your expected result is grouped by month but also showing supplierid as part of the result set. Since there are more than one supplierid per month, what output are you looking for?
January 2, 2012 at 5:25 pm
I need count of impressions and clicks for supplier(only one supplier at the time) grouped by Month/Year (fromSupplierImpressionDate/SupplierClickDate)
SupplierId will not be in output, it is parameter (where clause i think)
January 2, 2012 at 5:30 pm
Based on your expected output requested eariler:
date | impressions | clicks | supplier id
12/2011 | 10 | 2 | guid
11/2011 | 8 | 5 | guid
10/2011 | 20 | 4 | guid
09/2011 | 100 | 25 | guid
What is [supplier id] = "guid" referring to? :unsure:
January 2, 2012 at 5:33 pm
dont need supplier id guid sorry, ignore it
January 2, 2012 at 5:34 pm
i dont need supplier id sorry ignore it pls
January 2, 2012 at 5:41 pm
Are you looking for something along those lines?
;
WITH cte AS
(
SELECT CAST(impressionDate AS DATE) AS supplier_date, count(*) AS impression_cnt
FROM [tbl_supplier_impressions]
GROUP BY CAST(impressionDate AS DATE)
),
cte2 AS
(
SELECT CAST (clickdate AS DATE) AS clickdate, COUNT([SupplierClickId]) SupplierClickCnt
FROM [tbl_supplier_clicks]
GROUP BY CAST (clickdate AS DATE)
)
SELECT cte.*, cte2.SupplierClickCnt
FROM cte
LEFT OUTER JOIN cte2 ON cte.supplier_date=cte2.clickdate
January 3, 2012 at 6:40 am
Hi
Thank you for your reply
It is nearly exactly how I wanted.
Where should I put WHERE clause to be able to add SupplierID parameter? I need to get exactly same output but need to be able to filter it by supplierID
Thanks
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply