return placeholder Property names and 0 for aggregate columns for YYYYMM 'group by', even if no activity happened in that time span?

Question

return placeholder Property names and 0 for aggregate columns for YYYYMM 'group by', even if no activity happened in that time span?

KoldCoffee

SSCoach

Points: 18971
More actions
October 9, 2014 at 8:49 am

#297313

Using a relational database (not datawarehouse) sql server 2008 R2.
My stored procedure returns sum of activities by property and groups them by date (as YYYYMM).
The outcome for one property, with start_date '10/02/2012', and end date '10/03/2013', is like this:
Since no entries in table for dates when no activity, right joining to calendar table brings in dates, but doesn't populate property column:
Earnst Living, 201210, 102, 8
Earnst Living, 201211, 162, 10
Earnst Living, 201212, 96, 4
NULL, 201301, NULL, NULL
NULL, 201302, NULL, NULL
NULL, 201303, NULL, NULL
NULL, 201304, NULL, NULL
NULL, 201305, NULL, NULL
NULL, 201306, NULL, NULL
NULL, 201307, NULL, NULL
NULL, 201308, NULL, NULL
NULL, 201309, NULL, NULL
NULL, 201310, NULL, NULL
but I need Property column to have Property name, and aggregate columns to have 0, instead of NULL.
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201301, 0, 0
Earnst Living, 201210, 102, 8
Earnst Living, 201211, 162, 10
Earnst Living, 201212, 96, 4
Here's some DDL to help share the problem. I've created a table to hold just the data set of the last CTE in my sproc but otherwise everything else is representative ie. it requires 3 params: property_ids = XML, start date DATETIME, end date DATETIME.
And, database has no calendar table so I've created a table function that uses the @start_date and @end_date sproc parameters to return a column of YYYYMM that I join to at run time.
First, here's the data for the table that you run the sproc against (all made up data). I generated create script using SSMS Tasks to generate data and schema for table object, which is why the date is CAST(hexadecimal)
CREATE TABLE [dbo].[LastCTE](
[Property] [varchar](20) NULL,
[activity_date] [date] NULL,
[SentLeads] [int] NULL,
[Referrals] [int] NULL,
[Property_id] [int] NULL
)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x2E350B00 AS Date), 13, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x34350B00 AS Date), 12, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x95350B00 AS Date), 11, 2, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0xDD350B00 AS Date), 20, 3, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x4A350B00 AS Date), 10, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x58350B00 AS Date), 12, 0, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x67350B00 AS Date), 23, 2, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x76350B00 AS Date), 18, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x77350B00 AS Date), 18, 0, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x2E350B00 AS Date), 17, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x34350B00 AS Date), 21, 1, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x59350B00 AS Date), 13, 2, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x63350B00 AS Date), 25, 3, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x67350B00 AS Date), 26, 2, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x94350B00 AS Date), 30, 0, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0xA4350B00 AS Date), 27, 1, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0xD2350B00 AS Date), 21, 1, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0xF1350B00 AS Date), 17, 0, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x9C360B00 AS Date), 15, 2, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0xC1360B00 AS Date), 14, 1, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0xE3360B00 AS Date), 13, 3, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0xEF360B00 AS Date), 21, 4, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x11370B00 AS Date), 12, 2, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x3E370B00 AS Date), 15, 1, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x4E370B00 AS Date), 25, 3, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x7C370B00 AS Date), 20, 2, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x9C370B00 AS Date), 19, 1, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Builders Bunk', CAST(0x9C360B00 AS Date), 20, 2, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xA2360B00 AS Date), 23, 1, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xA8360B00 AS Date), 19, 3, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xB4360B00 AS Date), 30, 5, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xB8360B00 AS Date), 31, 3, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xE2360B00 AS Date), 32, 1, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xD4360B00 AS Date), 29, 2, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0x3F370B00 AS Date), 23, 2, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xF7370B00 AS Date), 20, 1, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x2E350B00 AS Date), 13, 1, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x8F350B00 AS Date), 12, 1, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x95350B00 AS Date), 11, 2, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0xA0350B00 AS Date), 20, 3, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x5C360B00 AS Date), 10, 1, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x4B360B00 AS Date), 12, 0, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x1F360B00 AS Date), 23, 2, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x3A350B00 AS Date), 18, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x5A350B00 AS Date), 18, 0, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x6A350B00 AS Date), 17, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x70350B00 AS Date), 21, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x95350B00 AS Date), 13, 2, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x82350B00 AS Date), 25, 3, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xA4350B00 AS Date), 26, 2, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xB2350B00 AS Date), 30, 0, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xC3350B00 AS Date), 27, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xD2350B00 AS Date), 21, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xE4350B00 AS Date), 13, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xEA350B00 AS Date), 12, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x0F360B00 AS Date), 11, 2, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xFC350B00 AS Date), 20, 3, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x1F360B00 AS Date), 10, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x2D360B00 AS Date), 12, 0, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x3D360B00 AS Date), 23, 2, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x6B360B00 AS Date), 18, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x8A360B00 AS Date), 18, 0, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0xE4350B00 AS Date), 17, 1, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0xEA350B00 AS Date), 21, 1, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0xF0350B00 AS Date), 13, 2, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0xFC350B00 AS Date), 25, 3, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0x00360B00 AS Date), 26, 2, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0x0E360B00 AS Date), 30, 0, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0x1F360B00 AS Date), 27, 1, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0x2E360B00 AS Date), 21, 1, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'East', CAST(0x2F360B00 AS Date), 17, 0, 56852)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'East', CAST(0x2F360B00 AS Date), 17, 0, 56852)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x40360B00 AS Date), 13, 1, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x46360B00 AS Date), 12, 1, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x4C360B00 AS Date), 11, 2, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x58360B00 AS Date), 20, 3, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x5C360B00 AS Date), 10, 1, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x6A360B00 AS Date), 12, 0, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x7A360B00 AS Date), 23, 2, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x89360B00 AS Date), 18, 1, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x8A360B00 AS Date), 18, 0, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x40360B00 AS Date), 17, 1, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x46360B00 AS Date), 21, 1, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x4C360B00 AS Date), 13, 2, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x58360B00 AS Date), 25, 3, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x5C360B00 AS Date), 26, 2, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x6A360B00 AS Date), 30, 0, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x7A360B00 AS Date), 27, 1, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x89360B00 AS Date), 21, 1, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'East', CAST(0x8A360B00 AS Date), 17, 0, 56852)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'East', CAST(0x8A360B00 AS Date), 17, 0, 56852)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x2E350B00 AS Date), 13, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x34350B00 AS Date), 12, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x95350B00 AS Date), 11, 2, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0xDD350B00 AS Date), 20, 3, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x4A350B00 AS Date), 10, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x58350B00 AS Date), 12, 0, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x67350B00 AS Date), 23, 2, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x76350B00 AS Date), 18, 1, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x77350B00 AS Date), 18, 0, 92583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Regency Corner', CAST(0x2E350B00 AS Date), 17, 1, 92583)
GO
print 'Processed 100 total records'
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x34350B00 AS Date), 21, 1, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x59350B00 AS Date), 13, 2, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x63350B00 AS Date), 25, 3, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x67350B00 AS Date), 26, 2, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0x94350B00 AS Date), 30, 0, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0xA4350B00 AS Date), 27, 1, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0xD2350B00 AS Date), 21, 1, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Cantebury Hills', CAST(0xF1350B00 AS Date), 17, 0, 66283)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x9C360B00 AS Date), 15, 2, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0xC1360B00 AS Date), 14, 1, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0xE3360B00 AS Date), 13, 3, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0xEF360B00 AS Date), 21, 4, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x11370B00 AS Date), 12, 2, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x3E370B00 AS Date), 15, 1, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x4E370B00 AS Date), 25, 3, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x7C370B00 AS Date), 20, 2, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Lined Living', CAST(0x9C370B00 AS Date), 19, 1, 23583)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Builders Bunk', CAST(0x9C360B00 AS Date), 20, 2, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xA2360B00 AS Date), 23, 1, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xA8360B00 AS Date), 19, 3, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xB4360B00 AS Date), 30, 5, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xB8360B00 AS Date), 31, 3, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xE2360B00 AS Date), 32, 1, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xD4360B00 AS Date), 29, 2, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0x3F370B00 AS Date), 23, 2, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Thetis Lake', CAST(0xF7370B00 AS Date), 20, 1, 36587)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x2E350B00 AS Date), 13, 1, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x8F350B00 AS Date), 12, 1, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x95350B00 AS Date), 11, 2, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0xA0350B00 AS Date), 20, 3, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x5C360B00 AS Date), 10, 1, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x4B360B00 AS Date), 12, 0, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Sycamore Reach', CAST(0x1F360B00 AS Date), 23, 2, 36574)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x3A350B00 AS Date), 18, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x5A350B00 AS Date), 18, 0, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x6A350B00 AS Date), 17, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x70350B00 AS Date), 21, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x95350B00 AS Date), 13, 2, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x82350B00 AS Date), 25, 3, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xA4350B00 AS Date), 26, 2, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xB2350B00 AS Date), 30, 0, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xC3350B00 AS Date), 27, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xD2350B00 AS Date), 21, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xE4350B00 AS Date), 13, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xEA350B00 AS Date), 12, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x0F360B00 AS Date), 11, 2, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0xFC350B00 AS Date), 20, 3, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x1F360B00 AS Date), 10, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x2D360B00 AS Date), 12, 0, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x3D360B00 AS Date), 23, 2, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x6B360B00 AS Date), 18, 1, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'GoGo Stop', CAST(0x8A360B00 AS Date), 18, 0, 45987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0xE4350B00 AS Date), 17, 1, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0xEA350B00 AS Date), 21, 1, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0xF0350B00 AS Date), 13, 2, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0xFC350B00 AS Date), 25, 3, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0x00360B00 AS Date), 26, 2, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0x0E360B00 AS Date), 30, 0, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0x1F360B00 AS Date), 27, 1, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Faust House', CAST(0x2E360B00 AS Date), 21, 1, 78987)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'East', CAST(0x2F360B00 AS Date), 17, 0, 56852)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'East', CAST(0x2F360B00 AS Date), 17, 0, 56852)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x40360B00 AS Date), 13, 1, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x46360B00 AS Date), 12, 1, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x4C360B00 AS Date), 11, 2, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x58360B00 AS Date), 20, 3, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x5C360B00 AS Date), 10, 1, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x6A360B00 AS Date), 12, 0, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x7A360B00 AS Date), 23, 2, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x89360B00 AS Date), 18, 1, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'HK House', CAST(0x8A360B00 AS Date), 18, 0, 52639)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x40360B00 AS Date), 17, 1, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x46360B00 AS Date), 21, 1, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x4C360B00 AS Date), 13, 2, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x58360B00 AS Date), 25, 3, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x5C360B00 AS Date), 26, 2, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x6A360B00 AS Date), 30, 0, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x7A360B00 AS Date), 27, 1, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'Earnst Living', CAST(0x89360B00 AS Date), 21, 1, 96325)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'East', CAST(0x8A360B00 AS Date), 17, 0, 56852)
INSERT [dbo].[LastCTE] ([Property], [activity_date], [SentLeads], [Referrals], [Property_id]) VALUES (N'East', CAST(0x8A360B00 AS Date), 17, 0, 56852)
here's the query I use in my sproc with parameter values supplied:
DECLARE
@property_ids XML ='<properties><id>96325</id><id>92583</id><id>78987</id>
<id>66283</id><id>56852</id><id>52639</id><id>45987</id>
<id>36587</id><id>36574</id><id>23583</id></properties>',
@from_date DATETIME = '10/02/2012',
@to_date DATETIME = '10/03/2013'
;WITH LEADS as
(
SELECT [Property]
, DATEADD(MONTH, DATEDIFF(MONTH, 0, activity_date), 0) as referral_date
,[SentLeads]
,[Referrals]
,property_id
FROM [Sandbox].[dbo].[LastCTE]
WHERE [activity_date] between @from_date and @to_date
AND property_id in
(
SELECT ParamValues.property_id.value('.','INT') property_id
FROM @property_ids.nodes('/properties/id') as ParamValues(property_id))
)
SELECT [Property]
, CONVERT(VARCHAR(6), IndividualDate,112) YYYYMM
,sum([SentLeads]) SumSentLeads
,sum([Referrals]) SumReferrals
FROM LEADS l
RIGHT JOIN fn_DateRange('m', @from_date, @to_date) AS a
ON a.IndividualDate = l.referral_date
GROUP BY [Property], CONVERT(VARCHAR(6), IndividualDate,112)
ORDER BY [Property], CONVERT(VARCHAR(6), IndividualDate,112)
Before running the above query you need to create the below function first.
Here's the code for the table function fn_DateRange I RIGHT JOIN to (it returns first_day_of_month for every month between start and end dates). Courtesy of
http://www.mssqltips.com/sqlservertip/2800/sql-server-function-to-return-a-range-of-dates/
http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
CREATE FUNCTION [dbo].[fn_DateRange]
(
@Increment CHAR(1),
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS
@SelectedRange TABLE
(IndividualDate DATETIME)
AS
BEGIN
set @StartDate = case
WHEN @Increment = 'm' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0)
END
;WITH cteRange (DateRange) AS (
SELECT @StartDate
UNION ALL
SELECT
CASE
WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
END
FROM cteRange
WHERE DateRange <=
CASE
WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
END)
INSERT INTO @SelectedRange (IndividualDate)
SELECT DateRange
FROM cteRange
OPTION (MAXRECURSION 3660);
RETURN
END
GO
Can someone here please tell me the trick so that for each YYYYMM I can get a row, having property name, YYYYMM, SumSentLeads, SumReferrals column?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63519 More actions · Answer 1

If you did a cross join between your dates table and your Properties table, it would return all possible combinations of (Property, Date) and that would solve your problem.

INSERT INTO @SelectedRange (IndividualDate, PropertyID)

SELECT DateRange, PropertyID

FROM cteRange CROSS JOIN Property

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

CROSS JOIN is a possible way of doing it. I tried that and it was a performance KILLER when I had lots of properties.

Here's what I ended up doing:

I created a #property (temp) table above the main query of the sproc. In addition to 'property' column the

#property table had to have a YYYYMM date associated with every property supplied by property_id variable. I did this with a JOIN to the fn_daterange table function ON 1=1 (rendering Cartesian product). That gave me 201310, 201311, 201312, etc. through 201410 for each property ie. 13 rows per property.

I then added another column to the #property table that concatenated CONVERT(VARCHAR(6), IndividualDate,112) + property calling it YYYYMMProperty which rendered values like Living01310, Lined Living201311, Lined Living201312, et. through Lined Living201410.

Finally, in the leads cte I also created a new column, whose values were the result of concatenating CONVERT(VARCHAR(6), IndividualDate,112) + property. I called it YYYYMMProperty as well and right joined it to the #property.YYYYMM column

Voila! Now everywhere that leads.YYYYMM = #property.YYYYMM I could pull over the property name. This guaranteed that I would populate the NULL fields with the correct property name and date.

pietlinden SSC Guru Points: 63519 More actions · Answer 3

Yes, I admit a CROSS JOIN would kill performance, but I would definitely filter first and then create the smallest cross join possible.

Glad you got it sorted.

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

KoldCoffee

SSCoach

Points: 18971

October 9, 2014 at 7:49 pm

#1751481

Thanks very much for engaging with me.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Okay, using your sample data, query, and function as a basis to work from here is what I put together.

First, a rewrite of your multi-statement table valued function as an inline table valued function:

CREATE FUNCTION [dbo].[ufn_DateRange]

(

@Increment CHAR(1),

@StartDate DATETIME,

@EndDate DATETIME

)

RETURNS TABLE

AS RETURN(

with

DateRangeAndIncrement as (

select

@Increment Increment,

case @Increment when 'm' then DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0) else @StartDate end StartDate,

@EndDate EndDate,

case @Increment

when 'd' then datediff(dd,@StartDate,@EndDate)

when 'w' then datediff(ww,@StartDate,@EndDate)

when 'm' then datediff(mm,DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0),@EndDate)

end + 1 DateRange

),

e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),

e2(n) as (select 1 from e1 a cross join e1 b),

e4(n) as (select 1 from e2 a cross join e2 b),

eTally(n) as (select top((select DateRange from DateRangeAndIncrement)) row_number() over (order by (select null)) from e4 a cross join e4 b)

select

case @Increment

when 'd' then DATEADD(dd,n - 1,@StartDate)

when 'w' then dateadd(ww,n - 1,@StartDate)

when 'm' then dateadd(mm,n - 1,case @Increment when 'm' then DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0) else @StartDate end)

end IndividualDate

from eTally

where

case @Increment

when 'd' then DATEADD(dd,n - 1,@StartDate)

when 'w' then dateadd(ww,n - 1,@StartDate)

when 'm' then dateadd(mm,n - 1,case @Increment when 'm' then DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0) else @StartDate end)

end < CASE

WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)

WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)

WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)

END

)

Then your query, slightly modified:

DECLARE

@property_ids XML ='<properties><id>96325</id><id>92583</id><id>78987</id>

<id>66283</id><id>56852</id><id>52639</id><id>45987</id>

<id>36587</id><id>36574</id><id>23583</id></properties>',

@from_date DATETIME = '10/02/2012',

@to_date DATETIME = '10/03/2013';

WITH

PropertyInfo as (

select distinct Property, Property_id from [dbo].[LastCTE]

), Dates as (

select IndividualDate, Property, Property_id from dbo.ufn_DateRange('m', @from_date, @to_date) cross apply PropertyInfo

)

--select * from Dates order by Property, IndividualDate

select

convert(varchar(6),d.IndividualDate,112) YYYYMM,

d.Property,

d.Property_id,

isnull(sum([SentLeads]),0) SumSentLeads,

isnull(sum([Referrals]),0) SumReferrals

from

Dates d

left outer join dbo.LastCTE lc

on d.Property = lc.Property

and convert(varchar(6),d.IndividualDate,112) = convert(varchar(6),lc.activity_date,112)

group by

d.Property,

d.Property_id,

d.IndividualDate

order by

d.Property,

d.IndividualDate;

All of this was run in my Sandbox database which is why the database name is left out of your query for the table dbo.LastCTE.

KoldCoffee SSCoach Points: 18971 More actions · Answer 6

Dead of night and must cook up an 11 yr old's birthday party...hours from now will assess and report back to you, Lynn. Appreciate it, very much.