RE: Dyn SQL - Need results to show when count is 0 group by datetime

Gohloum

SSC Enthusiast

Points: 142

October 24, 2012 at 9:56 am

#1552341

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?