January 7, 2012 at 6:15 am
Hi
I have 3 tables:
GO
/****** Object: Table [dbo].[tbl_supplier_impressions] Script Date: 01/07/2012 13:04:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_supplier_impressions](
[SupplierImpressionId] [int] IDENTITY(1,1) NOT NULL,
[SupplierId] [uniqueidentifier] NOT NULL,
[ImpressionDate] [date] 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(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (186, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (188, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (189, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (190, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (191, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (192, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (193, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (194, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (195, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (196, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (197, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (198, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (199, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (200, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (201, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (202, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (203, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (204, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (205, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (206, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (207, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (208, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (209, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_impressions] ([SupplierImpressionId], [SupplierId], [ImpressionDate]) VALUES (210, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
SET IDENTITY_INSERT [dbo].[tbl_supplier_impressions] OFF
/****** Object: Table [dbo].[tbl_supplier_clicks] Script Date: 01/07/2012 13:04:37 ******/
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] [date] 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 (13, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (14, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (15, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (16, N'5a3ad70a-21e9-4dd0-9312-ebccfe5cd001', CAST(0x27350B00 AS Date))
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (17, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (18, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (19, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x26350B00 AS Date))
INSERT [dbo].[tbl_supplier_clicks] ([SupplierClickId], [SupplierId], [ClickDate]) VALUES (20, N'967fe593-776f-4ecf-ad83-1fdcde7df7a7', CAST(0x27350B00 AS Date))
SET IDENTITY_INSERT [dbo].[tbl_supplier_clicks] OFF
/****** Object: Table [dbo].[tbl_nums] Script Date: 01/07/2012 13:04:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_nums](
[n] [int] NOT NULL,
CONSTRAINT [PK_tbl_nums] PRIMARY KEY CLUSTERED
(
[n] 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
INSERT [dbo].[tbl_nums] ([n]) VALUES (1)
INSERT [dbo].[tbl_nums] ([n]) VALUES (2)
INSERT [dbo].[tbl_nums] ([n]) VALUES (3)
INSERT [dbo].[tbl_nums] ([n]) VALUES (4)
INSERT [dbo].[tbl_nums] ([n]) VALUES (5)
INSERT [dbo].[tbl_nums] ([n]) VALUES (6)
INSERT [dbo].[tbl_nums] ([n]) VALUES (7)
INSERT [dbo].[tbl_nums] ([n]) VALUES (8)
INSERT [dbo].[tbl_nums] ([n]) VALUES (9)
INSERT [dbo].[tbl_nums] ([n]) VALUES (10)
INSERT [dbo].[tbl_nums] ([n]) VALUES (11)
INSERT [dbo].[tbl_nums] ([n]) VALUES (12)
INSERT [dbo].[tbl_nums] ([n]) VALUES (13)
INSERT [dbo].[tbl_nums] ([n]) VALUES (14)
INSERT [dbo].[tbl_nums] ([n]) VALUES (15)
INSERT [dbo].[tbl_nums] ([n]) VALUES (16)
INSERT [dbo].[tbl_nums] ([n]) VALUES (17)
INSERT [dbo].[tbl_nums] ([n]) VALUES (18)
INSERT [dbo].[tbl_nums] ([n]) VALUES (19)
INSERT [dbo].[tbl_nums] ([n]) VALUES (20)
INSERT [dbo].[tbl_nums] ([n]) VALUES (21)
INSERT [dbo].[tbl_nums] ([n]) VALUES (22)
INSERT [dbo].[tbl_nums] ([n]) VALUES (23)
INSERT [dbo].[tbl_nums] ([n]) VALUES (24)
INSERT [dbo].[tbl_nums] ([n]) VALUES (25)
INSERT [dbo].[tbl_nums] ([n]) VALUES (26)
INSERT [dbo].[tbl_nums] ([n]) VALUES (27)
INSERT [dbo].[tbl_nums] ([n]) VALUES (28)
INSERT [dbo].[tbl_nums] ([n]) VALUES (29)
INSERT [dbo].[tbl_nums] ([n]) VALUES (30)
INSERT [dbo].[tbl_nums] ([n]) VALUES (31)
I need to populate 2 datatables, one will hold number of impressions and another number of clicks between selected date.
Suppliers Impressions
DECLARE @StartDate nvarchar(8)
SET @StartDate = '20120101';
DECLARE @EndDate nvarchar(8)
SET @EndDate = '20120106';
DECLARE @SupplierID uniqueidentifier
SEt @SupplierID = '5a3ad70a-21e9-4dd0-9312-ebccfe5cd001'
SELECT DATEADD(day, N.n - 1, @StartDate) AS orderdate, COUNT(I.SupplierImpressionId) AS ImpressionCount
FROM tbl_nums AS N LEFT OUTER JOIN
tbl_supplier_impressions AS I ON DATEADD(day, N.n - 1, @StartDate) = I.ImpressionDate
WHERE (N.n <= DATEDIFF(day, @StartDate, @EndDate) + 1)
GROUP BY DATEADD(day, N.n - 1, @StartDate)
ORDER BY orderdate
Suppliers Clicks
DECLARE @StartDate nvarchar(8)
SET @StartDate = '20120101';
DECLARE @EndDate nvarchar(8)
SET @EndDate = '20120106';
DECLARE @SupplierID uniqueidentifier
SEt @SupplierID = '5a3ad70a-21e9-4dd0-9312-ebccfe5cd001'
SELECT DATEADD(day, N.n - 1, @StartDate) AS orderdate, COUNT(C.SupplierClickId) AS ClickCount
FROM tbl_nums AS N LEFT OUTER JOIN
tbl_supplier_clicks AS C ON DATEADD(day, N.n - 1, @StartDate) = C.ClickDate
WHERE (N.n <= DATEDIFF(day, @StartDate, @EndDate) + 1)
GROUP BY DATEADD(day, N.n - 1, @StartDate)
ORDER BY orderdate
Everything is working perfectly fine and I am getting right output. As you already noticed I declared SupplierID. Both tables (clicks, impressions) have got SupplierID.
I need to get same output as I get now but I want to filter by supplierID. My output at the moment lists dates from 01/01/2012 to 06/01/2012 but gives me count of all impressions/clicks... I need for specific supplier (supplierID) but still between selected date. Where do i need to put WHERE clause (or HAVING) SupplierId = @SupplierID?? Or is there any other way to do it??
Thanks
January 7, 2012 at 1:15 pm
all fixed
used temp tables in stored procedure
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply