So I'm not quite sure why this one is giving me so much trouble this morning. I'm going to chalk it up to being Friday morning. :) The goal is to count, by frequency, the number of real estate properties in that period that have zero photos, three photos and six or more photos. I need a result set that looks like this (NOTE: the "three column or more should NOT include ones that have exactly six -- there can be a max of six photos):
Period Start Period End Zero ThreeOrMore ExactlySix
------------------------------------------------------------------------
1/1/2011 1/7/2011 15 132 512
1/8/2011 1/14/2011 44 123 402
I made the dates up, the SQL I have actually produces the correct week start/end dates.
My "Property" table looks like this:
PropertyRecId Other fields
-----------------------------
12345 <blah>
56789 <blah>
My "PropertyPhoto" table looks like this (only the necessary fields included):
PropertyPhotoId PropertyRecId CreatedOn
---------------------------------------------
1 12345 3/1/2011
2 12345 3/1/2011
etc...
I'm using the query below but I'm not beholden to it. I just need to accomplish the goal. Keep in mind that with my existing query, I don't have access to the "Propery" record in my top-level select. I tried dropping the queries down into the secondary select where PeriodStart and PeriodEnd are created, but that just caused different issues.
DECLARE @DateStart datetime
DECLARE @DateEnd datetime
SET @DateStart = '1/1/2011'
SET @DateEnd = '12/31/2011'
DECLARE @Frequency varchar(50)
SET @Frequency = 'month'
SELECT
PeriodStart,
PeriodEnd,
??? As Zero,
??? As ThreeOrMore,
??? As ExactlySix
FROM (
SELECT
PeriodStart = CASE @Frequency
WHEN 'day' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, CreatedOn))
WHEN 'week' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, CreatedOn), CreatedOn)))
WHEN 'month' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedOn), 0)))
WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0)))
WHEN 'year' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, CreatedOn), 0)))
END,
PeriodEnd = CASE @Frequency
WHEN 'day' THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, CreatedOn)))
WHEN 'week' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CreatedOn), CreatedOn))))
WHEN 'month' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedOn) + 1, 0)))))
WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn) + 1, 0)))))
WHEN 'year' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, CreatedOn) + 1, 0)))))
END
FROM Property P
WHERE CreatedOn BETWEEN @DateStart AND @DateEnd
) s
GROUP BY
PeriodStart,
PeriodEnd
ORDER BY PeriodStart
You can use the over function in your subquery to get this, and case statements in your outer select to add 'em up:
DECLARE @Frequency varchar(50)
SET @Frequency = 'month'
SELECT
PeriodStart,
PeriodEnd,
SUM(case when s.PhotoCount = 0 then 1 else 0 end) As Zero,
SUM(case when s.PhotoCount between 3 and 5 then 1 else 0 end) As ThreeOrMore,
SUM(case when s.PhotoCount = 6 then 1 else 0 end) As ExactlySix
FROM (
SELECT
PeriodStart =
CASE @Frequency
WHEN 'day' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, CreatedOn))
WHEN 'week' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, CreatedOn), CreatedOn)))
WHEN 'month' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedOn), 0)))
WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0)))
WHEN 'year' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, CreatedOn), 0)))
END,
PeriodEnd =
CASE @Frequency
WHEN 'day' THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, CreatedOn)))
WHEN 'week' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CreatedOn), CreatedOn))))
WHEN 'month' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedOn) + 1, 0)))))
WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn) + 1, 0)))))
WHEN 'year' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, CreatedOn) + 1, 0)))))
END,
p.PropertyRecId,
COUNT(ph.PropertyPhotoId) over (partition by p.PropertyRecId) as PhotoCount
FROM
Property P
left join PropertyPhoto ph on
p.PropertyRecId = ph.PropertyRecId
WHERE
CreatedOn BETWEEN @DateStart AND @DateEnd
) s
GROUP BY
PeriodStart,
PeriodEnd
ORDER BY
PeriodStart
You can also make your inner query with a traditional group by, but I love me some over and wanted to bring it to your attention:
SELECT
PeriodStart =
CASE @Frequency
WHEN 'day' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, CreatedOn))
WHEN 'week' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(DAY, 1 - DATEPART(WEEKDAY, CreatedOn), CreatedOn)))
WHEN 'month' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedOn), 0)))
WHEN 'quarter' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0)))
WHEN 'year' THEN DATEADD(hour, 0, DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, CreatedOn), 0)))
END,
PeriodEnd =
CASE @Frequency
WHEN 'day' THEN DATEADD(s, -1, DATEADD(day, 1, DATEDIFF(DAY, 0, CreatedOn)))
WHEN 'week' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CreatedOn), CreatedOn))))
WHEN 'month' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CreatedOn) + 1, 0)))))
WHEN 'quarter' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn) + 1, 0)))))
WHEN 'year' THEN DATEADD(s, -1, DATEADD(hour, 0, DATEDIFF(DAY, -1, DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, CreatedOn) + 1, 0)))))
END,
p.PropertyRecId,
COUNT(ph.PropertyPhotoId) over (partition by p.PropertyRecId) as PhotoCount
FROM
Property P
left join PropertyPhoto ph on
p.PropertyRecId = ph.PropertyRecId
WHERE
CreatedOn BETWEEN @DateStart AND @DateEnd
GROUP BY
p.PropertyRecId,
p.CreatedOn
Does this work?:
--1 : Make a lookup-up table of period start/end
CREATE TABLE #PERIODS (
[Period Start] datetime
, [Period End] datetime
);
DECLARE @firstDay datetime
DECLARE @rowid int
SET @firstDay = '2011-01-01'
WHILE @firstDay < '2012-01-01'
BEGIN
INSERT INTO #PERIODS VALUES ( @firstDay, DATEADD(DAY,6,@firstDay));
SET @firstDay = @firstDay + 7
END
GO
-- 2 Count the photos per property per period
CREATE TABLE #PHOTOCOUNT (
[Period Start] datetime
, [Period End] datetime
, PropertyRecId int
, PhotoCount int
);
GO
INSERT INTO #PHOTOCOUNT
SELECT
PD.[Period Start]
, PD.[Period End]
, Property.PropertyRecId
, COUNT(PropertyPhoto.PropertyPhotoId) AS PhotoCount
FROM
#PERIODS AS PD,
Property
LEFT JOIN PropertyPhoto
ON Property.PropertyRecId = PropertyPhoto.PropertyRecId
WHERE
PropertyPhoto.PropertyRecId Is Null
OR PropertyPhoto.CreatedOn BETWEEN PD.[Period Start] AND PD.[Period End]
GROUP BY
PD.[Period Start]
, PD.[Period End]
, Property.PropertyRecId;
GO
-- 3 Categorize by 0, 3 to 5, 6
SELECT
[Period Start]
, [Period End]
, SUM(RANGES.Zero) AS Zero
, SUM(RANGES.ThreeToFive) AS ThreeToFive
, SUM(RANGES.Six) AS Six
FROM (
SELECT
[Period Start]
, [Period End]
, Zero = CASE WHEN PhotoCount = 0 THEN 1 ELSE 0 END
, ThreeToFive = CASE WHEN PhotoCount BETWEEN 3 AND 5 THEN 1 ELSE 0 END
, Six = CASE WHEN PhotoCount = 6 THEN 1 ELSE 0 END
FROM #PHOTOCOUNT
) RANGES
GROUP BY
[Period Start]
, [Period End]
;
DROP TABLE #PERIODS;
DROP TABLE #PHOTOCOUNT;
