Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Dyn SQL - Need results to show when count is...
Add to briefcase
16 posts, Page 1 of 2
12»»

Dyn SQL - Need results to show when count is 0 group by datetime Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Gohloum
Posted Wednesday, October 24, 2012 8:15 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2012 8:19 AM
Points: 8, Visits: 29
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!
Post #1376508
Cadavre
Posted Wednesday, October 24, 2012 8:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486
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/), 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!

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1376511
Gohloum
Posted Wednesday, October 24, 2012 9:13 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2012 8:19 AM
Points: 8, Visits: 29
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,
	[CODE] [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,
	[EMAIL] [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
Post #1376540
Cadavre
Posted Wednesday, October 24, 2012 9:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486
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,
	[CODE] [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.

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1376550
Cadavre
Posted Wednesday, October 24, 2012 9:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486
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.

--EDIT--

Wrote up solution based on incorrect sample data.

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1376560
Gohloum
Posted Wednesday, October 24, 2012 9:56 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2012 8:19 AM
Points: 8, Visits: 29
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,
	[CODE] [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?
Post #1376565
Cadavre
Posted Wednesday, October 24, 2012 10:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486
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.

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1376568
Gohloum
Posted Wednesday, October 24, 2012 10:04 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2012 8:19 AM
Points: 8, Visits: 29
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!)
Post #1376569
Gohloum
Posted Wednesday, October 24, 2012 10:06 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2012 8:19 AM
Points: 8, Visits: 29
Trying your latest solution. Got a Incorrect syntax near keyword 'VALUES'
Post #1376572
Cadavre
Posted Wednesday, October 24, 2012 10:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486
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!)


--== 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);

Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1376575
« Prev Topic | Next Topic »

Add to briefcase
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.