October 24, 2012 at 8:15 am
So the guys in charge of this place made some interesting requests and pegged me as the person to do this, but I'm not an SQL guy, a game developer, so this is going to be interesting to say the least. But they are the kind of individuals that would ask their Proctologist to perform open heart surgery, so go figure, right?
OK, to the issue at hand. I am trying to aggregate a report that returns the number of requested promotional codes per week from Sunday to Saturday. This works fine no problem as a general statement. Where I found some issues was when they wanted me to start filtering out specific partner categories. When a situation occurs where the count for that week had no requested promos, there is no output in my result. However, this data is going out across the wire to a client via JSON displaying some charts, so in my series, I can't have missing weeks, I need the week in the result set with a value of 0.
Also, the remaining bulk of results end up in my ELSE clause and I've been trimming this result off in the client, but it would be nice to have clean, neat data to make my JSON objects with. So any suggestions on how to 'IGNORE COUNT RESULTS BEYOND WEEKS IN SEARCH' would be AWESOME!!!!!
DECLARE @WEEKS INT
SET @WEEKS = 12
-- Insert statements for procedure here
DECLARE @MostRecentSunday DATETIME
DECLARE @i INT, @day1 int, @day2 int
DECLARE @sql1 nvarchar(max), @sql2 nvarchar(max), @sql3 nvarchar(max), @cases nvarchar(max), @query nvarchar(max)
SET @MostRecentSunday = dateadd(day,1-datepart(dw, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)), DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
SET @i = 0
IF (COALESCE(@WEEKS, '') = '')
BEGIN
SET @WEEKS = 52
END
ELSE
BEGIN
SET @WEEKS = @WEEKS - 1
END
SET @cases = ' WHEN DELIVERY_DATE BETWEEN @MostRecentSunday AND GETDATE() THEN @MostRecentSunday '
SET @sql1 = 'DECLARE @MostRecentSunday DATETIME '
SET @sql1 = @sql1 + 'SET @MostRecentSunday = dateadd(day,1-datepart(dw, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)), DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) '
SET @sql1 = @sql1 + ' SELECT COUNT(WebTV_Promo_Codes.ID) AS total, CASE '
SET @sql2 = ' END AS DateRange FROM [SACD].[dbo].[WebTV_Promo_Customer] '
-- setup the where clauses
SET @sql2 = @sql2 + ' INNER JOIN WebTV_Promo_Codes ON WebTV_Promo_Customer.ID_CODE = WebTV_Promo_Codes.ID '
SET @sql2 = @sql2 + ' WHERE (WebTV_Promo_Codes.ACTIVE = 0) AND (WebTV_Promo_Codes.ID_CAT <> 2) '
SET @sql2 = @sql2 + ' GROUP BY CASE '
SET @sql3 = ' END ORDER BY DateRange DESC'
while (@i < @WEEKS)
begin
set @day1 = (@i * -7) -7
set @day2 = @i * -7
set @cases = @cases + ' WHEN DELIVERY_DATE BETWEEN DATEADD(dd, ' + CAST(@day1 AS nvarchar(4)) + ', @MostRecentSunday) AND DATEADD(dd, ' + CAST(@day2 AS nvarchar(4)) + ', @MostRecentSunday) THEN ' + 'DATEADD(dd, ' + CAST(@day1 AS nvarchar(4)) + ', @MostRecentSunday) ' -- CAST(@i + 2 AS NVARCHAR(4)) + ' '
set @i = @i +1
end
SET @cases = @cases + ' ELSE 0'
SET @query = @sql1 + @cases + @sql2 + @cases + @sql3
--print @query
EXECUTE sp_executesql @query
This above statement for 12 weeks returns 7 results (missing the 0 count weeks) See below:
Total DateRange
4 2012-10-21 00:00:00.000
16 2012-10-14 00:00:00.000
11 2012-10-07 00:00:00.000
3 2012-09-30 00:00:00.000
1 2012-09-23 00:00:00.000
1 2012-09-02 00:00:00.000
19 1900-01-01 00:00:00.000
So if someone who actually knows what they are doing with SQL (lord knows I don't) could give me a bit of feedback I'd be most appreciative.... And definitely have a beer on me at your next happy hour. 😀
Thanks in advance for you help!
October 24, 2012 at 8:21 am
Hey and welcome to SSC!
I'd love to help, but I'm afraid that you haven't quite provided enough information. What I could do with from you is readily consumable sample data and DDL. If you check out this article (http://www.sqlservercentral.com/articles/Best+Practices/61537/)[/url], it explains how best to provide readily consumable sample data and DDL.
The actual issue that you're having is something that I suspect everyone comes up against at least once, so I'm certain that once you provide the requested information your issue will be solved reasonably quickly.
Thanks!
October 24, 2012 at 9:13 am
Thanks for your response and I have read through the thread for creating sample data. However after following the directions for creating my first table, it throws an error at keyword UNION. Again, me not being a sql guy, can you give me some insight on how to fix?
IF OBJECT_ID('TempDB..#WebTV_Promo_Codes', 'U') IS NOT NULL
DROP TABLE #WebTV_Promo_Codes
CREATE TABLE #WebTV_Promo_Codes
(
[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ID_CAT] [int] NOT NULL,
[ID_DEPT] [int] NOT NULL,
[varchar](50) NOT NULL,
[INSERTDATE] [datetime] NOT NULL,
[ADDED_BY] [varchar](32) NOT NULL,
[ACTIVE] [int] NOT NULL
)
/*
IF OBJECT_ID('TempDB..#WebTV_Promo_Codes', 'U') IS NOT NULL
DROP TABLE #WebTV_Promo_Customer
CREATE TABLE #WebTV_Promo_Customer
(
[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED, NOT NULL,
[ID_CODE] [int] NOT NULL,
[TITLE] [varchar](5) NOT NULL,
[FIRST_NAME] [varchar](24) NOT NULL,
[LAST_NAME] [varchar](50) NOT NULL,
[varchar](255) NOT NULL,
[COUNTRY] [varchar](32) NOT NULL,
[DELIVERY_DATE] [datetime] NOT NULL,
)
*/
/*
-- create select statement result set.
SELECT TOP 100 'SELECT '
+ QUOTENAME(ID,'''')+','
+ QUOTENAME(ID_CAT,'''')+','
+ QUOTENAME(ID_DEPT,'''')+','
+ QUOTENAME(CODE,'''')+','
+ QUOTENAME(INSERTDATE,'''')+','
+ QUOTENAME(ADDED_BY,'''')+','
+ QUOTENAME(ACTIVE,'''')+','
+ ' UNION ALL'
FROM [SACD].[dbo].[WebTV_Promo_Codes]
WHERE ID_CAT <> 2
*/
SET DATEFORMAT ymd;
SET IDENTITY_INSERT #WebTV_Promo_Codes ON
INSERT INTO #WebTV_Promo_Codes
(ID, ID_CAT, ID_DEPT, CODE, INSERTDATE, ADDED_BY, ACTIVE)
SELECT '106','28','2','DMRT3B9BBETU8F','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2239','12','3','DMRT9V7A6JVYVV','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2240','12','3','DMRT9V7X4WYRYT','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2242','12','3','DMRT9VF674X9VD','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2243','12','3','DMRT9VK6Y37F3P','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2244','14','3','DMRT9VNRGYGFGJ','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2245','14','3','DMRT9VRA9GR9PN','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2246','14','3','DMRT9VWBCKRFF8','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2248','14','3','DMRT9W3CCHFWBB','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2250','12','3','DMRT9W64XMCNWB','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2251','12','3','DMRT9W6F9UYY47','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2252','12','3','DMRT9W8ARH6A3T','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2253','12','3','DMRT9W8J7WY3XN','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2254','12','3','DMRT9WB693UBHC','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2255','12','3','DMRT9WBATDPNRM','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2256','12','3','DMRT9WBBKTYDXP','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2257','12','3','DMRT9WD47EEWXD','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2259','12','3','DMRT9WETA4MAEK','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2260','48','3','DMRT9WGRH3WPYJ','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2261','12','3','DMRT9WGVFMVCEM','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2262','12','3','DMRT9WHHMU3GRJ','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2263','14','3','DMRT9WN6WJDV4A','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2264','27','3','DMRT9WNP9Y9PFE','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2265','12','3','DMRT9WNXXAGT7C','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2266','12','3','DMRT9WP4MTR7Y6','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2267','14','3','DMRT9WP84M364H','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2268','27','3','DMRT9WPDRUJ64R','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2269','12','3','DMRT9WUWUBFW9R','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2271','12','3','DMRT9WWYCW7UCB','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2273','28','3','DMRT9X4XFVMUU4','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2274','12','3','DMRT9X4XR6YH7W','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2275','12','3','DMRT9X7DP97RPU','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2276','27','3','DMRT9X97PDK4NJ','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2279','12','3','DMRT9XBU9B8UNC','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2280','12','3','DMRT9XBV9E7V7X','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2283','12','3','DMRT9XGV7FW67J','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2285','12','3','DMRT9XNE8BYPY9','Nov 4 2010 1:52PM','felicia','0', UNION ALL
SELECT '2286','12','3','DMRT9XR3FYBXWM','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2287','12','3','DMRT9XR6BNH8Y4','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2288','12','3','DMRT9Y3CGK9WPJ','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2289','12','3','DMRT9Y3Y8BPN44','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2290','12','3','DMRT9Y4BB8WUV7','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2291','12','3','DMRT9Y976NJY77','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2292','12','3','DMRT9Y9J87BAMX','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2293','12','3','DMRT9Y9NF9VB9M','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2294','12','3','DMRT9Y9XPGHCW9','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2295','12','3','DMRT9Y9YKUK9EV','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2296','12','3','DMRT9YB4YTPBA7','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2297','12','3','DMRT9YBU478BR6','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2298','12','3','DMRT9YCCV7CMFY','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2299','12','3','DMRT9YD7WVWPH3','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2300','12','3','DMRT9YDR4DA4AE','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2301','12','3','DMRT9YFHRWPDGK','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2302','12','3','DMRT9YGEGY4KH6','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2303','12','3','DMRT9YHYVAXH7D','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2304','12','3','DMRT9YKPVCKBMY','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2305','12','3','DMRT9YMYJDKMWK','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2306','12','3','DMRT9YNA6VU3RB','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2307','12','3','DMRT9YPJFV4CXJ','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2308','12','3','DMRT9YTWR3DYW3','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2309','12','3','DMRT9YWHFAXJKB','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2310','12','3','DMRT9YY3RR9V9M','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2311','12','3','DMRTA383E4A4EU','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2312','12','3','DMRTA38PHPCY8X','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2313','12','3','DMRTA3A66CNY4X','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2314','12','3','DMRTA3A9HYCWC7','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2315','12','3','DMRTA3EFVGWRTR','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2316','12','3','DMRTA3NRBUE7GU','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2317','12','3','DMRTA3RHV7Y3FR','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2318','12','3','DMRTA3W3WNHBUP','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2319','12','3','DMRTA3X6AY6E6W','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2320','12','3','DMRTA469VD9FVK','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2321','12','3','DMRTA46XCUKUXK','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2322','12','3','DMRTA47BKWHGKH','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2323','12','3','DMRTA4A3YAU3TB','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2324','12','3','DMRTA4A64HEGWT','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2325','12','3','DMRTA4AWRHH74E','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2326','12','3','DMRTA4C3VMREKN','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2327','12','3','DMRTA4GW7KWKU9','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2328','12','3','DMRTA4JPBFWJ7J','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2329','12','3','DMRTA4NUVJTJCR','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2330','12','3','DMRTA4NX6TEM7R','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2331','12','3','DMRTA4PFW9JHDJ','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2332','12','3','DMRTA4R84JF3RJ','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2333','12','3','DMRTA4TPU3GRV8','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2334','12','3','DMRTA4UTYW9HTJ','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2335','12','3','DMRTA644U8KRFP','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2336','12','3','DMRTA66TDEF3RM','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2337','12','3','DMRTA69D9GUYW6','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2338','12','3','DMRTA69D9WRC3D','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2339','12','3','DMRTA69YXPHRR8','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2340','12','3','DMRTA6KGW3VHDV','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2341','12','3','DMRTA6NGF7UU4D','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2342','12','3','DMRTA6TB7WMARE','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2343','12','3','DMRTA6VRVGN6E7','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2344','12','3','DMRTA73PHB3R7N','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2345','12','3','DMRTA7BV6VN7GM','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2346','12','3','DMRTA7CDRCTFKN','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2347','12','3','DMRTA7DC6D4N7D','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SELECT '2348','12','3','DMRTA7DCG7NGV9','Nov 4 2010 1:52PM','felicia','1', UNION ALL
SET IDENTITY_INSERT #WebTV_Promo_Codes OFF
SELECT * FROM #WebTV_Promo_Codes
October 24, 2012 at 9:27 am
Gohloum (10/24/2012)
Thanks for your response and I have read through the thread for creating sample data. However after following the directions for creating my first table, it throws an error at keyword UNION. Again, me not being a sql guy, can you give me some insight on how to fix?
Ofcourse! 😀
SELECT TOP 100 'SELECT ' + QUOTENAME(ID, '''') + ',' + QUOTENAME(ID_CAT, '''') + ',' +
QUOTENAME(ID_DEPT, '''') + ',' + QUOTENAME(CODE, '''') + ',' + QUOTENAME(INSERTDATE, '''') + ',' +
QUOTENAME(ADDED_BY, '''') + ',' + QUOTENAME(ACTIVE, '''') + ' UNION ALL'
FROM [SACD].[dbo].[WebTV_Promo_Codes]
WHERE ID_CAT <> 2;
The above will fix the first issue. The second issue is that you didn't remove the final "UNION ALL" from the code produced.
Here's what the final code should look like: -
IF OBJECT_ID('TempDB..#WebTV_Promo_Codes', 'U') IS NOT NULL
DROP TABLE #WebTV_Promo_Codes
CREATE TABLE #WebTV_Promo_Codes
(
[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ID_CAT] [int] NOT NULL,
[ID_DEPT] [int] NOT NULL,
[varchar](50) NOT NULL,
[INSERTDATE] [datetime] NOT NULL,
[ADDED_BY] [varchar](32) NOT NULL,
[ACTIVE] [int] NOT NULL
)
SET IDENTITY_INSERT #WebTV_Promo_Codes ON
INSERT INTO #WebTV_Promo_Codes
(ID, ID_CAT, ID_DEPT, CODE, INSERTDATE, ADDED_BY, ACTIVE)
SELECT '106','28','2','DMRT3B9BBETU8F','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2239','12','3','DMRT9V7A6JVYVV','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2240','12','3','DMRT9V7X4WYRYT','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2242','12','3','DMRT9VF674X9VD','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2243','12','3','DMRT9VK6Y37F3P','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2244','14','3','DMRT9VNRGYGFGJ','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2245','14','3','DMRT9VRA9GR9PN','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2246','14','3','DMRT9VWBCKRFF8','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2248','14','3','DMRT9W3CCHFWBB','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2250','12','3','DMRT9W64XMCNWB','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2251','12','3','DMRT9W6F9UYY47','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2252','12','3','DMRT9W8ARH6A3T','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2253','12','3','DMRT9W8J7WY3XN','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2254','12','3','DMRT9WB693UBHC','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2255','12','3','DMRT9WBATDPNRM','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2256','12','3','DMRT9WBBKTYDXP','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2257','12','3','DMRT9WD47EEWXD','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2259','12','3','DMRT9WETA4MAEK','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2260','48','3','DMRT9WGRH3WPYJ','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2261','12','3','DMRT9WGVFMVCEM','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2262','12','3','DMRT9WHHMU3GRJ','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2263','14','3','DMRT9WN6WJDV4A','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2264','27','3','DMRT9WNP9Y9PFE','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2265','12','3','DMRT9WNXXAGT7C','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2266','12','3','DMRT9WP4MTR7Y6','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2267','14','3','DMRT9WP84M364H','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2268','27','3','DMRT9WPDRUJ64R','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2269','12','3','DMRT9WUWUBFW9R','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2271','12','3','DMRT9WWYCW7UCB','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2273','28','3','DMRT9X4XFVMUU4','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2274','12','3','DMRT9X4XR6YH7W','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2275','12','3','DMRT9X7DP97RPU','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2276','27','3','DMRT9X97PDK4NJ','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2279','12','3','DMRT9XBU9B8UNC','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2280','12','3','DMRT9XBV9E7V7X','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2283','12','3','DMRT9XGV7FW67J','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2285','12','3','DMRT9XNE8BYPY9','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2286','12','3','DMRT9XR3FYBXWM','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2287','12','3','DMRT9XR6BNH8Y4','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2288','12','3','DMRT9Y3CGK9WPJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2289','12','3','DMRT9Y3Y8BPN44','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2290','12','3','DMRT9Y4BB8WUV7','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2291','12','3','DMRT9Y976NJY77','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2292','12','3','DMRT9Y9J87BAMX','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2293','12','3','DMRT9Y9NF9VB9M','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2294','12','3','DMRT9Y9XPGHCW9','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2295','12','3','DMRT9Y9YKUK9EV','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2296','12','3','DMRT9YB4YTPBA7','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2297','12','3','DMRT9YBU478BR6','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2298','12','3','DMRT9YCCV7CMFY','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2299','12','3','DMRT9YD7WVWPH3','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2300','12','3','DMRT9YDR4DA4AE','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2301','12','3','DMRT9YFHRWPDGK','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2302','12','3','DMRT9YGEGY4KH6','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2303','12','3','DMRT9YHYVAXH7D','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2304','12','3','DMRT9YKPVCKBMY','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2305','12','3','DMRT9YMYJDKMWK','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2306','12','3','DMRT9YNA6VU3RB','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2307','12','3','DMRT9YPJFV4CXJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2308','12','3','DMRT9YTWR3DYW3','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2309','12','3','DMRT9YWHFAXJKB','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2310','12','3','DMRT9YY3RR9V9M','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2311','12','3','DMRTA383E4A4EU','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2312','12','3','DMRTA38PHPCY8X','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2313','12','3','DMRTA3A66CNY4X','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2314','12','3','DMRTA3A9HYCWC7','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2315','12','3','DMRTA3EFVGWRTR','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2316','12','3','DMRTA3NRBUE7GU','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2317','12','3','DMRTA3RHV7Y3FR','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2318','12','3','DMRTA3W3WNHBUP','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2319','12','3','DMRTA3X6AY6E6W','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2320','12','3','DMRTA469VD9FVK','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2321','12','3','DMRTA46XCUKUXK','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2322','12','3','DMRTA47BKWHGKH','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2323','12','3','DMRTA4A3YAU3TB','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2324','12','3','DMRTA4A64HEGWT','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2325','12','3','DMRTA4AWRHH74E','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2326','12','3','DMRTA4C3VMREKN','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2327','12','3','DMRTA4GW7KWKU9','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2328','12','3','DMRTA4JPBFWJ7J','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2329','12','3','DMRTA4NUVJTJCR','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2330','12','3','DMRTA4NX6TEM7R','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2331','12','3','DMRTA4PFW9JHDJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2332','12','3','DMRTA4R84JF3RJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2333','12','3','DMRTA4TPU3GRV8','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2334','12','3','DMRTA4UTYW9HTJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2335','12','3','DMRTA644U8KRFP','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2336','12','3','DMRTA66TDEF3RM','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2337','12','3','DMRTA69D9GUYW6','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2338','12','3','DMRTA69D9WRC3D','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2339','12','3','DMRTA69YXPHRR8','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2340','12','3','DMRTA6KGW3VHDV','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2341','12','3','DMRTA6NGF7UU4D','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2342','12','3','DMRTA6TB7WMARE','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2343','12','3','DMRTA6VRVGN6E7','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2344','12','3','DMRTA73PHB3R7N','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2345','12','3','DMRTA7BV6VN7GM','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2346','12','3','DMRTA7CDRCTFKN','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2347','12','3','DMRTA7DC6D4N7D','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2348','12','3','DMRTA7DCG7NGV9','Nov 4 2010 1:52PM','felicia','1'
SET IDENTITY_INSERT #WebTV_Promo_Codes OFF
SELECT * FROM #WebTV_Promo_Codes
OK, so now we have some sample data, let me take a quick look and I'll post back in a few minutes.
October 24, 2012 at 9:51 am
Sorry, took a bit longer than I expected.
I think you're after something like this: -
DECLARE @WEEKS INT;
SET @WEEKS = 12;
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
DATEADD(dd, 7*-(N+1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1))
FROM CTE5)
SELECT a.N AS DateRange, b.Total
FROM TALLY a
OUTER APPLY (SELECT COUNT(1)
FROM #WebTV_Promo_Codes
WHERE INSERTDATE BETWEEN a.X AND a.N) b(Total);
Unfortunately, on your sample data that will produce "0" for all 12 weeks.
So, I knocked up 1 million rows of randomised sample data: -
--== CREATE SAMPLE DATA ==--
IF OBJECT_ID('tempdb..#WebTV_Promo_Codes', 'U') IS NOT NULL
BEGIN
DROP TABLE #WebTV_Promo_Codes;
END
--== FILL SAMPLE DATA WITH 1 MILLION ROWS OF RANDOM DATA ==--
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS ID_CAT,
(ABS(CHECKSUM(NEWID())) % 10) + 1 AS ID_DEPT,
REPLACE(CAST(NEWID() AS VARCHAR(36)),'-','') AS CODE,
RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/ + CAST('2010' AS DATETIME) /*(Start date, e.g. '2010-01-01 00:00:00*/ AS INSERTDATE,
'felicia' AS ADDED_BY,
CASE WHEN (ABS(CHECKSUM(NEWID())) % 2) + 1 = 2 THEN 1 ELSE 0 END AS ACTIVE
INTO #WebTV_Promo_Codes
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
The data looks like this: -
ID ID_CAT ID_DEPT CODE INSERTDATE ADDED_BY ACTIVE
----------- ----------- ----------- ---------------------------------- ----------------------- -------- -----------
1 55 2 F8569FB8B15E423BBA071A694180CB95 2041-01-11 06:15:17.300 felicia 0
Which is similar enough to your sample data for our purposes, I think.
I then deleted all of the data for a particular week: -
DELETE FROM #WebTV_Promo_Codes
WHERE INSERTDATE BETWEEN '2012-10-07 00:00:00.000' AND '2012-10-14 00:00:00.000';
And ran my code: -
--== START OF SOLUTION ==--
DECLARE @WEEKS INT;
SET @WEEKS = 12;
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
DATEADD(dd, 7*-(N+1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1))
FROM CTE5)
SELECT a.N AS DateRange, b.Total
FROM TALLY a
OUTER APPLY (SELECT COUNT(1)
FROM #WebTV_Promo_Codes
WHERE INSERTDATE BETWEEN a.X AND a.N) b(Total);
On this run, it produced the following (remember the sample data is randomised, so if you run it yourself it'll be different): -
DateRange Total
----------------------- -----------
2012-10-21 00:00:00.000 230
2012-10-14 00:00:00.000 0
2012-10-07 00:00:00.000 231
2012-09-30 00:00:00.000 240
2012-09-23 00:00:00.000 241
2012-09-16 00:00:00.000 230
2012-09-09 00:00:00.000 250
2012-09-02 00:00:00.000 232
2012-08-26 00:00:00.000 233
2012-08-19 00:00:00.000 207
2012-08-12 00:00:00.000 237
2012-08-05 00:00:00.000 212
Hope that answers everything, post back if you have any queries about what I did.
October 24, 2012 at 9:56 am
Ok, I got the goods, man! This will create the needed data to run the sql.
IF OBJECT_ID('TempDB..#WebTV_Promo_Codes', 'U') IS NOT NULL
DROP TABLE #WebTV_Promo_Codes
CREATE TABLE #WebTV_Promo_Codes
(
[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ID_CAT] [int] NOT NULL,
[ID_DEPT] [int] NOT NULL,
[varchar](50) NOT NULL,
[INSERTDATE] [datetime] NOT NULL,
[ADDED_BY] [varchar](32) NOT NULL,
[ACTIVE] [int] NOT NULL
)
SET IDENTITY_INSERT #WebTV_Promo_Codes ON
INSERT INTO #WebTV_Promo_Codes
(ID, ID_CAT, ID_DEPT, CODE, INSERTDATE, ADDED_BY, ACTIVE)
SELECT '2239','12','3','DMRT9V7A6JVYVV','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2240','12','3','DMRT9V7X4WYRYT','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2242','12','3','DMRT9VF674X9VD','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2243','12','3','DMRT9VK6Y37F3P','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2244','14','3','DMRT9VNRGYGFGJ','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2245','14','3','DMRT9VRA9GR9PN','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2246','14','3','DMRT9VWBCKRFF8','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2248','14','3','DMRT9W3CCHFWBB','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2250','12','3','DMRT9W64XMCNWB','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2251','12','3','DMRT9W6F9UYY47','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2252','12','3','DMRT9W8ARH6A3T','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2253','12','3','DMRT9W8J7WY3XN','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2254','12','3','DMRT9WB693UBHC','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2255','12','3','DMRT9WBATDPNRM','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2256','12','3','DMRT9WBBKTYDXP','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2257','12','3','DMRT9WD47EEWXD','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2259','12','3','DMRT9WETA4MAEK','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2260','48','3','DMRT9WGRH3WPYJ','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2261','12','3','DMRT9WGVFMVCEM','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2262','12','3','DMRT9WHHMU3GRJ','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2263','14','3','DMRT9WN6WJDV4A','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2264','27','3','DMRT9WNP9Y9PFE','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2265','12','3','DMRT9WNXXAGT7C','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2266','12','3','DMRT9WP4MTR7Y6','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2267','14','3','DMRT9WP84M364H','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2268','27','3','DMRT9WPDRUJ64R','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2269','12','3','DMRT9WUWUBFW9R','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2271','12','3','DMRT9WWYCW7UCB','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2273','28','3','DMRT9X4XFVMUU4','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2274','12','3','DMRT9X4XR6YH7W','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2275','12','3','DMRT9X7DP97RPU','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2276','27','3','DMRT9X97PDK4NJ','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2279','12','3','DMRT9XBU9B8UNC','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2280','12','3','DMRT9XBV9E7V7X','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2283','12','3','DMRT9XGV7FW67J','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2285','12','3','DMRT9XNE8BYPY9','Nov 4 2010 1:52PM','felicia','0' UNION ALL
SELECT '2286','12','3','DMRT9XR3FYBXWM','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2287','12','3','DMRT9XR6BNH8Y4','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2288','12','3','DMRT9Y3CGK9WPJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2289','12','3','DMRT9Y3Y8BPN44','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2290','12','3','DMRT9Y4BB8WUV7','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2291','12','3','DMRT9Y976NJY77','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2292','12','3','DMRT9Y9J87BAMX','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2293','12','3','DMRT9Y9NF9VB9M','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2294','12','3','DMRT9Y9XPGHCW9','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2295','12','3','DMRT9Y9YKUK9EV','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2296','12','3','DMRT9YB4YTPBA7','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2297','12','3','DMRT9YBU478BR6','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2298','12','3','DMRT9YCCV7CMFY','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2299','12','3','DMRT9YD7WVWPH3','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2300','12','3','DMRT9YDR4DA4AE','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2301','12','3','DMRT9YFHRWPDGK','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2302','12','3','DMRT9YGEGY4KH6','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2303','12','3','DMRT9YHYVAXH7D','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2304','12','3','DMRT9YKPVCKBMY','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2305','12','3','DMRT9YMYJDKMWK','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2306','12','3','DMRT9YNA6VU3RB','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2307','12','3','DMRT9YPJFV4CXJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2308','12','3','DMRT9YTWR3DYW3','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2309','12','3','DMRT9YWHFAXJKB','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2310','12','3','DMRT9YY3RR9V9M','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2311','12','3','DMRTA383E4A4EU','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2312','12','3','DMRTA38PHPCY8X','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2313','12','3','DMRTA3A66CNY4X','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2314','12','3','DMRTA3A9HYCWC7','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2315','12','3','DMRTA3EFVGWRTR','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2316','12','3','DMRTA3NRBUE7GU','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2317','12','3','DMRTA3RHV7Y3FR','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2318','12','3','DMRTA3W3WNHBUP','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2319','12','3','DMRTA3X6AY6E6W','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2320','12','3','DMRTA469VD9FVK','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2321','12','3','DMRTA46XCUKUXK','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2322','12','3','DMRTA47BKWHGKH','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2323','12','3','DMRTA4A3YAU3TB','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2324','12','3','DMRTA4A64HEGWT','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2325','12','3','DMRTA4AWRHH74E','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2326','12','3','DMRTA4C3VMREKN','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2327','12','3','DMRTA4GW7KWKU9','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2328','12','3','DMRTA4JPBFWJ7J','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2329','12','3','DMRTA4NUVJTJCR','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2330','12','3','DMRTA4NX6TEM7R','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2331','12','3','DMRTA4PFW9JHDJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2332','12','3','DMRTA4R84JF3RJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2333','12','3','DMRTA4TPU3GRV8','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2334','12','3','DMRTA4UTYW9HTJ','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2335','12','3','DMRTA644U8KRFP','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2336','12','3','DMRTA66TDEF3RM','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2337','12','3','DMRTA69D9GUYW6','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2338','12','3','DMRTA69D9WRC3D','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2339','12','3','DMRTA69YXPHRR8','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2340','12','3','DMRTA6KGW3VHDV','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2341','12','3','DMRTA6NGF7UU4D','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2342','12','3','DMRTA6TB7WMARE','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2343','12','3','DMRTA6VRVGN6E7','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2344','12','3','DMRTA73PHB3R7N','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2345','12','3','DMRTA7BV6VN7GM','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2346','12','3','DMRTA7CDRCTFKN','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2347','12','3','DMRTA7DC6D4N7D','Nov 4 2010 1:52PM','felicia','1' UNION ALL
SELECT '2348','12','3','DMRTA7DCG7NGV9','Nov 4 2010 1:52PM','felicia','1'
SET IDENTITY_INSERT #WebTV_Promo_Codes OFF
--SELECT * FROM #WebTV_Promo_Codes
IF OBJECT_ID('TempDB..#WebTV_Promo_Customer', 'U') IS NOT NULL
DROP TABLE #WebTV_Promo_Customer
CREATE TABLE #WebTV_Promo_Customer
(
[ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ID_CODE] [int] NOT NULL,
[COUNTRY] [varchar](32) NOT NULL,
[DELIVERY_DATE] [datetime] NOT NULL,
)
SET IDENTITY_INSERT #WebTV_Promo_Customer ON
INSERT INTO #WebTV_Promo_Customer
(ID, ID_CODE, COUNTRY, DELIVERY_DATE)
SELECT '2267','2238','United States','Mar 11 2012 8:43PM' UNION ALL
SELECT '2650','2239','New Zealand','Sep 4 2012 11:42PM' UNION ALL
SELECT '3056','2241','Canada','Oct 5 2012 5:43PM' UNION ALL
SELECT '3057','2242','Canada','Oct 5 2012 5:44PM' UNION ALL
SELECT '3058','2243','Canada','Oct 5 2012 5:46PM' UNION ALL
SELECT '3059','2244','United Kingdom','Oct 6 2012 5:48PM' UNION ALL
SELECT '3060','2245','United States','Oct 8 2012 12:08AM' UNION ALL
SELECT '3061','2246','Mexico','Oct 8 2012 3:18AM' UNION ALL
SELECT '3062','2247','United States','Oct 8 2012 9:06AM' UNION ALL
SELECT '3063','2248','United States','Oct 8 2012 12:11PM' UNION ALL
SELECT '3066','2249','United States','Oct 9 2012 6:29PM' UNION ALL
SELECT '3067','2250','United States','Oct 9 2012 10:02PM' UNION ALL
SELECT '3068','2251','Greece','Oct 11 2012 1:50PM' UNION ALL
SELECT '3069','2252','Greece','Oct 11 2012 1:53PM' UNION ALL
SELECT '3070','2253','New Zealand','Oct 11 2012 4:58PM' UNION ALL
SELECT '3075','2254','El Salvador','Oct 12 2012 4:29PM' UNION ALL
SELECT '3076','2255','United States','Oct 12 2012 6:04PM' UNION ALL
SELECT '3077','2256','United States','Oct 12 2012 6:09PM' UNION ALL
SELECT '3078','2257','United States','Oct 12 2012 6:10PM' UNION ALL
SELECT '3082','2258','Pakistan','Oct 12 2012 11:16PM' UNION ALL
SELECT '3086','2259','Niger','Oct 14 2012 11:06AM' UNION ALL
SELECT '3087','2260','United States','Oct 15 2012 7:50AM' UNION ALL
SELECT '3088','2261','United States','Oct 15 2012 12:42PM' UNION ALL
SELECT '3089','2262','Canada','Oct 15 2012 5:40PM' UNION ALL
SELECT '3095','2263','Italy','Oct 17 2012 12:32PM' UNION ALL
SELECT '3096','2264','United States','Oct 17 2012 1:44PM' UNION ALL
SELECT '3097','2265','United States','Oct 17 2012 2:31PM' UNION ALL
SELECT '3098','2266','United States','Oct 17 2012 3:12PM' UNION ALL
SELECT '3103','2267','United States','Oct 18 2012 12:36AM' UNION ALL
SELECT '3104','2268','United States','Oct 18 2012 7:41AM' UNION ALL
SELECT '3105','2269','United States','Oct 18 2012 8:56AM' UNION ALL
SELECT '3106','2270','United States','Oct 18 2012 9:00AM' UNION ALL
SELECT '3107','2271','United States','Oct 18 2012 9:14AM' UNION ALL
SELECT '3108','2272','United States','Oct 18 2012 9:53AM' UNION ALL
SELECT '3109','2273','United States','Oct 18 2012 10:15PM' UNION ALL
SELECT '3110','2274','United States','Oct 19 2012 8:08AM' UNION ALL
SELECT '3112','2275','Canada','Oct 19 2012 11:57PM' UNION ALL
SELECT '3117','2276','United Kingdom','Oct 20 2012 6:42PM' UNION ALL
SELECT '3118','2277','United States','Oct 21 2012 4:05PM' UNION ALL
SELECT '3121','2278','United States','Oct 22 2012 7:46AM' UNION ALL
SELECT '3128','2279','United States','Oct 23 2012 12:03PM' UNION ALL
SELECT '3129','2280','United States','Oct 23 2012 12:07PM' UNION ALL
SELECT '3130','2281','United States','Oct 23 2012 12:09PM' UNION ALL
SELECT '3134','2285','United States','Oct 23 2012 12:58PM'
SET IDENTITY_INSERT #WebTV_Promo_Customer OFF
Do I need to provide anything else?
October 24, 2012 at 10:02 am
Sorry, didn't see the join in there so provided a part solution.
Try this instead: -
--== START OF SOLUTION ==--
DECLARE @WEEKS INT;
SET @WEEKS = 12;
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
DATEADD(dd, 7*-(N+1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1))
FROM CTE5)
SELECT a.N AS DateRange, d.Total
FROM TALLY a
OUTER APPLY (SELECT COUNT(1)
FROM #WebTV_Promo_Customer b
INNER JOIN #WebTV_Promo_Codes c ON b.ID_CODE = c.ID
WHERE c.ACTIVE = 0 AND c.ID_CAT <> 2
AND DELIVERY_DATE BETWEEN a.X AND a.N) d(Total);
I modified that on the way to my car using my phone, so check the syntax before executing. I'll check for replies either tonight when I get home or tomorrow morning.
October 24, 2012 at 10:04 am
Oof... Just saw your reply. That went just a hair over my head. Would you mind adding a few comments in the code as the sections process to help me wrap my head around it? I think once I understand how your code is actually processing to product the results, then I am golden.
For instance, I do a lot of loops and switch/case statements in game code, so it was fairly easy to build my Dynamic statement because I understood how loops and switch/case work.
Any light on the topic would be most appreciated (and worth another beer for sure!):cool:
October 24, 2012 at 10:06 am
Trying your latest solution. Got a Incorrect syntax near keyword 'VALUES'
October 24, 2012 at 10:13 am
Gohloum (10/24/2012)
Oof... Just saw your reply. That went just a hair over my head. Would you mind adding a few comments in the code as the sections process to help me wrap my head around it? I think once I understand how your code is actually processing to product the results, then I am golden.For instance, I do a lot of loops and switch/case statements in game code, so it was fairly easy to build my Dynamic statement because I understood how loops and switch/case work.
Any light on the topic would be most appreciated (and worth another beer for sure!):cool:
--== START OF SOLUTION ==--
DECLARE @WEEKS INT;
SET @WEEKS = 12;
--== THIS PART OF THE CTE (CTE) BUILDS A DERRIVED TABLE WITH 10 ROWS
WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
--== THIS PART OF THE CTE (CTE1) BUILDS A DERRIVED TABLE WITH 100 ROWS
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
--== THIS PART OF THE CTE (CTE2) BUILDS A DERRIVED TABLE WITH 10,000 ROWS
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
--== THIS PART OF THE CTE (CTE3) BUILDS A DERRIVED TABLE WITH 100,000,000 ROWS
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
--== THIS PART OF THE CTE (CTE4) BUILDS A DERRIVED TABLE WITH 10,000,000,000,000,000 ROWS
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (--== THIS PROVIDES A 0 BASE FOR THE DATE CALCULATION
SELECT 0 UNION ALL
--== THIS CREATES NUMBERS FROM 1 TO THE NUMBER OF WEEKS REQUIRED
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT
--== THIS DATE CALCULATION LOOKS FOR LAST SUNDAY, THEN TAKES THE NUMBER
--== FROM CTE5 (0 TO THE NUMBER OF WEEKS REQUIRED-1) AND REMOVES 7 DAYS FOR EACH
--== ONE. THIS PROVIDES US WITH THE WEEKS FOR THE DATE RANGE
DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
--== THIS IS THE SAME, BUT 1 WEEK REMOVED
DATEADD(dd, 7*-(N+1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1))
FROM CTE5)
SELECT a.N AS DateRange, d.Total
FROM TALLY a
OUTER APPLY (--== THIS IS THE TOTAL NUMBER OF CODES PER WEEK
SELECT COUNT(1)
FROM #WebTV_Promo_Customer b
INNER JOIN #WebTV_Promo_Codes c ON b.ID_CODE = c.ID
WHERE c.ACTIVE = 0 AND c.ID_CAT <> 2
--== THIS LIMITS THE ROWS RETURNED TO ONLY THOSE INCLUDED IN OUR DATE
--== RANGE CALCULATIONS
AND DELIVERY_DATE BETWEEN a.X AND a.N) d(Total);
October 24, 2012 at 10:15 am
Gohloum (10/24/2012)
Trying your latest solution. Got a Incorrect syntax near keyword 'VALUES'
Are you using SQL Server 2005 ?
If so, change the solution to this: -
--== START OF SOLUTION ==--
DECLARE @WEEKS INT;
SET @WEEKS = 12;
--== THIS PART OF THE CTE (CTE) BUILDS A DERRIVED TABLE WITH 10 ROWS
WITH CTE(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1),
--== THIS PART OF THE CTE (CTE1) BUILDS A DERRIVED TABLE WITH 100 ROWS
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
--== THIS PART OF THE CTE (CTE2) BUILDS A DERRIVED TABLE WITH 10,000 ROWS
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
--== THIS PART OF THE CTE (CTE3) BUILDS A DERRIVED TABLE WITH 100,000,000 ROWS
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
--== THIS PART OF THE CTE (CTE4) BUILDS A DERRIVED TABLE WITH 10,000,000,000,000,000 ROWS
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (--== THIS PROVIDES A 0 BASE FOR THE DATE CALCULATION
SELECT 0 UNION ALL
--== THIS CREATES NUMBERS FROM 1 TO THE NUMBER OF WEEKS REQUIRED
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT
--== THIS DATE CALCULATION LOOKS FOR LAST SUNDAY, THEN TAKES THE NUMBER
--== FROM CTE5 (0 TO THE NUMBER OF WEEKS REQUIRED-1) AND REMOVES 7 DAYS FOR EACH
--== ONE. THIS PROVIDES US WITH THE WEEKS FOR THE DATE RANGE
DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
--== THIS IS THE SAME, BUT 1 WEEK REMOVED
DATEADD(dd, 7*-(N+1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1))
FROM CTE5)
SELECT a.N AS DateRange, d.Total
FROM TALLY a
OUTER APPLY (--== THIS IS THE TOTAL NUMBER OF CODES PER WEEK
SELECT COUNT(1)
FROM #WebTV_Promo_Customer b
INNER JOIN #WebTV_Promo_Codes c ON b.ID_CODE = c.ID
WHERE c.ACTIVE = 0 AND c.ID_CAT <> 2
--== THIS LIMITS THE ROWS RETURNED TO ONLY THOSE INCLUDED IN OUR DATE
--== RANGE CALCULATIONS
AND DELIVERY_DATE BETWEEN a.X AND a.N) d(Total);
--EDIT--
I'm about to set off for home now, so I'll check back later this evening to see if everything went OK.
October 24, 2012 at 12:04 pm
Yes, using 2005. I will try your changes now.
October 24, 2012 at 12:39 pm
Oh my.... That is seriously COOL! :w00t:
OK, I noticed something. My original code pulls from Sunday to Saturday week periods, and includes the most recent sunday to present (10-21-2012 to now) and the tally for the current week in progress which at this time is 4 in my live data.
I changed the header to make more sense, and your code is using that very cool TALLY and I am not sure how the 'N's fit into the code there, so I am unclear as how to edit this. So currently the code results in this:
WeekBeginDate Total
2012-10-21 00:00:00.000 16
2012-10-14 00:00:00.000 11
2012-10-07 00:00:00.000 3
2012-09-30 00:00:00.000 1
2012-09-23 00:00:00.000 0
2012-09-16 00:00:00.000 0
2012-09-09 00:00:00.000 1
2012-09-02 00:00:00.000 0
2012-08-26 00:00:00.000 0
2012-08-19 00:00:00.000 0
2012-08-12 00:00:00.000 0
2012-08-05 00:00:00.000 0
The result I need is for the date to be the beginning of a count series, not an end. This way when I process the current week we are in (2012-10-21 00:00:00.000) it returns the count up to today (GETDATE()). So date 2012-10-21 00:00:00.000 should be 4 (current week) and 2012-10-14 00:00:00.000 should be 16 (all totals basically shifted down one).
So again I think it's just a matter of how one looks at it, but from an accounting perspective, they want to know the progress for the current week, not wait until the week is closed before totaling... Or so this was the request from 'The Fish Bowl' (Executive wing) for how they want the data...
Does that explanation make sense? 🙂
October 24, 2012 at 1:36 pm
Gohloum (10/24/2012)
Oh my.... That is seriously COOL! :w00t:OK, I noticed something. My original code pulls from Sunday to Saturday week periods, and includes the most recent sunday to present (10-21-2012 to now) and the tally for the current week in progress which at this time is 4 in my live data.
I changed the header to make more sense, and your code is using that very cool TALLY and I am not sure how the 'N's fit into the code there, so I am unclear as how to edit this.
I think I understand.
Try this: -
--== START OF SOLUTION ==--
DECLARE @WEEKS INT;
SET @WEEKS = 12;
--== THIS PART OF THE CTE (CTE) BUILDS A DERRIVED TABLE WITH 10 ROWS
WITH CTE(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1),
--== THIS PART OF THE CTE (CTE1) BUILDS A DERRIVED TABLE WITH 100 ROWS
CTE1(N) AS (SELECT 1 FROM CTE a CROSS JOIN CTE b),
--== THIS PART OF THE CTE (CTE2) BUILDS A DERRIVED TABLE WITH 10,000 ROWS
CTE2(N) AS (SELECT 1 FROM CTE1 a CROSS JOIN CTE1 b),
--== THIS PART OF THE CTE (CTE3) BUILDS A DERRIVED TABLE WITH 100,000,000 ROWS
CTE3(N) AS (SELECT 1 FROM CTE2 a CROSS JOIN CTE2 b),
--== THIS PART OF THE CTE (CTE4) BUILDS A DERRIVED TABLE WITH 10,000,000,000,000,000 ROWS
CTE4(N) AS (SELECT 1 FROM CTE3 a CROSS JOIN CTE3 b),
CTE5(N) AS (--== THIS PROVIDES A 0 BASE FOR THE DATE CALCULATION
SELECT 0 UNION ALL
--== THIS CREATES NUMBERS FROM 1 TO THE NUMBER OF WEEKS REQUIRED
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (SELECT TOP (@WEEKS-1) N
FROM CTE4)a
),
TALLY(N, X) AS (SELECT
--== THIS DATE CALCULATION LOOKS FOR LAST SUNDAY, THEN TAKES THE NUMBER
--== FROM CTE5 (-1 TO THE NUMBER OF WEEKS REQUIRED-1) AND REMOVES 7 DAYS FOR EACH
--== ONE. THIS PROVIDES US WITH THE WEEKS FOR THE DATE RANGE
DATEADD(dd, 7*-(N-1), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1)),
--== THIS IS THE SAME, BUT 1 WEEK REMOVED
DATEADD(dd, 7*-N, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), -1))
FROM CTE5)
SELECT a.X AS WeekBeginDate, d.Total
FROM TALLY a
OUTER APPLY (--== THIS IS THE TOTAL NUMBER OF CODES PER WEEK
SELECT COUNT(1)
FROM #WebTV_Promo_Customer b
INNER JOIN #WebTV_Promo_Codes c ON b.ID_CODE = c.ID
WHERE c.ACTIVE = 0 AND c.ID_CAT <> 2
--== THIS LIMITS THE ROWS RETURNED TO ONLY THOSE INCLUDED IN OUR DATE
--== RANGE CALCULATIONS
AND DELIVERY_DATE BETWEEN a.X AND a.N) d(Total);
Results: -
WeekBeginDate Total
----------------------- -----------
2012-10-21 00:00:00.000 3
2012-10-14 00:00:00.000 16
2012-10-07 00:00:00.000 11
2012-09-30 00:00:00.000 3
2012-09-23 00:00:00.000 0
2012-09-16 00:00:00.000 0
2012-09-09 00:00:00.000 0
2012-09-02 00:00:00.000 1
2012-08-26 00:00:00.000 0
2012-08-19 00:00:00.000 0
2012-08-12 00:00:00.000 0
2012-08-05 00:00:00.000 0
Does that tally up with what you were expecting?
SELECT '3130','2281','United States','Oct 23 2012 12:09PM'
Has no corresponding ID in WebTV_Promo_Codes. If it should still be counted, let me know and I'll edit my solution.
Do my comments make sense? Do you understand what it's doing? Basically, instead of looping (which SQL is generally slow at doing) over each row, I've performed actions on the set instead.
If you take a look at this article (http://www.sqlservercentral.com/articles/T-SQL/62867/) by Jeff Moden[/url], he explains how a tally table can be used to replace a loop.
If you take a look at this article (http://www.sqlservercentral.com/articles/APPLY/69953/) by Paul White[/url], he explains (in a 2 part article) what APPLY is and how it works.
If you have any further questions, just ask. I probably won't pick them up until tomorrow, but chances are good that someone else may answer them. If they don't, then I'll answer tomorrow.
October 25, 2012 at 8:47 am
Thanks for those links! I have read the one on Tally and I completely understand the concept, especially from a performance issue. Very good stuff to know and I'm now aware of this as a solution. I'll read the article on APPLY next, although I did read one somewhere else when I first saw your code. One of my pet peeves is to actually know and comprehend code one uses, especially when help has been given online, which brings me to a few questions:
1 - I understand the top part as far as the CTE goes for creating the long table, however I am a bit fuzzy on the 'N', a, and b, and the roles they play up here. Are a and b different values than later when they are used as table aliases?
2 - In my normal coding day, I am more use to explicitly defined variables where it appears SQL allows more of an implied concept... In other words, N is not DECLARED as something. Can you shed a little light on this and I assume (N) and N are the same memory location within the entire context of the statement?
3 - I do understand aliases, and thus I see a is TALLY and b and c are my tables. So I am assuming that a.X is the X in TALLY (N, X). However what is interesting is TALLY(N, X) with an AS (SELECT...) really threw me at first. But now under closer examination, am I correct in assuming that TALLY is 2 columns with the 2 selected dates from the SELECT statement on the right side of the AS filling column 1 N and column 2 X and the row count is = to the row count of CTE5? If so then that's pretty darn slick!
4 - d.Total. I see d(Total) in my where clause but no other ref to 'd'. So I'm not quite sure how d made it to the SQL party. Need a little demystifying here.
I'll go check out the article on APPLY now...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply