Choosing the Birthday of a Customer by month?

• chef423

  SSCertifiable

  Points: 5279

  More actions

  November 21, 2017 at 2:34 pm

  #340567

  CREATE TABLE birthdays(
 cust_id  INTEGER NOT NULL PRIMARY KEY
,cust_fname NVARCHAR(50) NOT NULL
,cust_lname NVARCHAR(50) NOT NULL
,cust_dob DATETIME NOT NULL
);
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (94,N'Jamie',N'Aguiar','2017-06-02 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (313,N'Alexandra',N'Borges','1995-01-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (317,N'Rachelle',N'Borges','1995-01-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (346,N'Keith',N'Brady','1993-03-29 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (361,N'Kelsea',N'Britto','1994-03-25 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (715,N'Tia',N'Delguidice','1999-02-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (994,N'Holly',N'Hamilton','2017-11-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (1110,N'ISABELLE',N'HYDER','1993-04-06 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (1295,N'RAELYN',N'LITTLE','1995-02-15 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (1403,N'ALLISON',N'RIPA','1993-10-14 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (1486,N'Rayvon',N'Miller','1984-11-09 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (1559,N'Alexandra',N'Sousa','1989-09-17 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (1897,N'Patrick',N'Snow','1976-10-10 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (1749,N'Justine',N'Zienowicz','1998-03-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2027,N'Luis',N'Sierra','1977-10-26 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2047,N'Reilly',N'Lindsey','1993-08-03 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2057,N'Jessica',N'Couto','1983-12-10 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2058,N'Lisa',N'Asadoorian','1980-05-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2065,N'Carmen',N'Rugel','1965-03-28 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2066,N'Amanda',N'Hook','1975-05-28 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2070,N'Christina',N'McGilvry','1984-06-08 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2071,N'Victoria',N'Cabral','1992-05-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2068,N'Katherine',N'Martin','2015-05-19 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2082,N'Alicia',N'Marcaurelle','1984-11-09 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2117,N'JAMES',N'CASKEN','1991-09-22 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2127,N'Rebecca',N'Cousens','1994-03-14 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2130,N'Michelle',N'Alves','1969-10-18 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2147,N'Erin',N'Myers','1996-05-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2157,N'Hannah',N'Nasser','2000-09-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2158,N'Maggie',N'Jankuska','2001-03-22 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2166,N'Greg',N'Morris','1985-03-08 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2169,N'Amanda',N'Turner','1983-10-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2172,N'Katie',N'Sullivan','1992-05-22 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2174,N'Christina',N'Halliday','2016-02-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2075,N'Brittany',N'Gage','1993-06-02 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2162,N'Ashley',N'Mitchell','1994-05-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2178,N'Ellen',N'Tuttle','2016-12-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2199,N'Kathy',N'Wilson','2016-12-15 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2200,N'Gregory',N'Deluca','2016-06-14 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2205,N'Brittany',N'Melanson','1994-02-22 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2208,N'Buddy',N'Trinkle','1952-12-09 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2209,N'Brittany',N'Kosboski','1987-01-22 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2218,N'Jessica',N'Armour','1996-03-02 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2233,N'Erik',N'Gabrielson','1996-09-09 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2238,N'Michael',N'Cranson','1971-12-14 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2239,N'Lyn',N'Spano','1973-09-05 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2242,N'Matthew',N'Lawber','1991-09-16 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2244,N'Suzanne',N'Hole','1964-01-31 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2245,N'Yanika',N'Reynolds','1989-12-29 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2248,N'Ifeanyi',N'Onyeraba','1993-12-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2254,N'Lindsay',N'Fletcher','1985-03-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2255,N'Baleigh',N'Payne','1996-02-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (1973,N'Ashley',N'Yanek','1990-11-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2191,N'Mariah',N'Rosario','1990-07-17 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2194,N'Kylee-Rae',N'Davis','2016-06-13 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2243,N'Melissa',N'Mullens','1973-04-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2247,N'reece',N'bennett','1998-10-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2250,N'Marion',N'Thomson','1987-02-21 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2257,N'Monique',N'Calhoun','1982-10-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2259,N'Lindsay',N'Walker','1995-07-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2260,N'Katie',N'Persechino','1995-03-22 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2263,N'Hugo',N'Pop','1988-09-15 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2264,N'Sandra',N'Escaleira','1981-10-15 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2266,N'Aimee',N'Norigian','1977-03-01 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2267,N'Rian',N'Mccarthy','1995-11-17 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2270,N'Melanie',N'MacDonald','1977-11-20 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2273,N'Ian',N'Coyne','1993-12-16 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2274,N'David',N'Johnson','1958-07-01 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2276,N'Kirsten',N'Cunneen','2016-12-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2280,N'Taylor',N'Cleary','1996-03-05 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2282,N'Jessica',N'Gray','1974-05-13 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2283,N'Mark',N'Genga','1963-10-03 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2287,N'Jerilyn',N'Silvia','1968-07-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2297,N'Lori',N'Stewart','1968-01-21 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2302,N'Rebekah',N'High','1999-02-20 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2306,N'Joan',N'Gastero','1955-10-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2311,N'Aaron',N'Prendergast','1996-02-16 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2317,N'Kate',N'Sisk','1968-10-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2318,N'Ed',N'Sisk','2016-06-30 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2319,N'Pat',N'Galuska','1949-03-14 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2265,N'Doug',N'Desmond','1984-12-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2272,N'Caroline',N'Rodriguez','1966-12-16 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2277,N'Madison',N'Payseur','2016-06-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2288,N'Lauren',N'Paquin','1986-04-30 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2290,N'Chelsea',N'Porreca','1989-12-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2303,N'Victoria',N'Powell','1999-08-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2304,N'alexandra',N'perry','1992-07-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2307,N'Brian',N'Combra','1983-06-25 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2309,N'Elena',N'Jestings','1997-04-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2315,N'Tina',N'Couture','1989-10-01 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2329,N'Marcus',N'Cabral','2016-06-03 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2334,N'Gwge',N'Shadid','2016-09-29 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2335,N'Ashley',N'Luis','1992-02-20 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2343,N'Judy',N'Taylor','1950-01-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2344,N'Michelle',N'Kelley','1966-02-05 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2348,N'Elizabeth',N'Allen','1976-01-15 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2351,N'Marlen',N'Oliva','1995-03-01 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2355,N'Georgia',N'Allen','1999-06-09 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2357,N'Nicholas',N'Hieber','2016-09-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2323,N'Samantha',N'Costa','1996-07-23 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2327,N'Natatia',N'Miranda','1994-05-13 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2339,N'Nicole',N'McGovern','1995-05-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2340,N'Nicole',N'McGovern','1995-05-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2341,N'Ryan',N'Kelley','1993-03-18 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2346,N'Kara',N'Merryfield','1995-09-17 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2347,N'Nora',N'Donahue','2016-04-02 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2359,N'Sarah',N'Smyth','1983-05-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2361,N'Valerie',N'Bruno','1984-06-07 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2364,N'Matthew',N'Petersen','2016-09-04 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2367,N'Diana',N'Garcia','1972-04-02 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2373,N'Sara',N'Powell','1993-06-15 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2374,N'Fred',N'Kelley','1964-03-30 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2375,N'Manni',N'Jimenez','1992-05-06 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2376,N'Savannah',N'Baker','1971-07-31 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2365,N'Barbara',N'Rosa','1981-01-12 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2371,N'Rosemary',N'Kelly','1994-06-30 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2372,N'Megan',N'Mcguire','1988-02-18 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2450,N'Van',N'Brockmann','1988-04-10 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2451,N'Unique',N'Skinner','2003-10-07 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2452,N'Victoria',N'Laureano','1996-07-31 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2453,N'Colin',N'Kelley','1996-03-21 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2454,N'James',N'Gizzi','1993-05-25 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2457,N'Mark',N'Grande','1963-10-21 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2458,N'Gail',N'Archambault','1966-09-07 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2460,N'Gabrielle',N'Basile','1989-02-03 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2461,N'Lea',N'Adams','1996-03-03 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2462,N'Michael',N'Homer','1964-09-30 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2463,N'Marisa',N'Scott','1994-09-28 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2464,N'Victoria',N'Bellaflore','1994-06-21 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2465,N'Gabrielle',N'Simpson','1997-03-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2466,N'Rosabrina',N'Laterza','1997-01-01 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2470,N'Kaitlin',N'Lagodich','1993-11-19 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2471,N'Curran',N'Carr','1978-02-10 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2473,N'Taylor',N'Vaccaro','1996-03-21 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2474,N'Gina',N'Tegtmeier','1964-07-08 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2475,N'Tracy',N'Tegtmeier','1964-09-29 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2478,N'Katie',N'Hutzel','1994-09-28 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2481,N'Sue',N'Halliwell','2016-01-10 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2482,N'Amanda',N'Marini','1994-04-26 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2487,N'Emily',N'Sarsfield','1994-01-31 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2488,N'Jackie',N'Ofria','1994-06-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2491,N'Brooke',N'Saunders','1993-04-10 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2493,N'Jethro',N'Ramos','1980-02-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2494,N'Allison',N'Gichinger','1994-03-02 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2497,N'Kelsey',N'Davidson','1994-10-05 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2498,N'Denise',N'Yates','1965-10-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2505,N'Sam',N'Loughborough','1983-11-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2507,N'Paige',N'Sprague','1995-02-13 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2508,N'Tallyn',N'Scioli','1993-08-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2088,N'Mike',N'Annunziaoa','1983-06-13 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2094,N'Sabrina',N'Broomfield','1998-10-17 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2095,N'Ana',N'Riordan','1999-08-26 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2116,N'Lynne',N'Eagles','1943-09-29 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2122,N'Kimberly',N'Matthews','1971-07-26 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2123,N'Maddie',N'Olaynack','2001-04-10 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2131,N'Kathleen',N'Meegan','1991-06-09 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2145,N'Sean',N'Norcross','1987-04-28 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2149,N'Rebecca',N'Cleary','1970-03-26 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2150,N'Madison',N'Cleary','1996-05-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2468,N'Michael',N'Toppa','1976-01-18 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2476,N'Doreen',N'O''Loughlin','1975-07-17 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2479,N'Emily',N'Roberts','1982-06-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2483,N'Jamie',N'Toner','1975-10-16 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2489,N'Shawn',N'Gienty','1987-10-29 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2499,N'Savannah',N'Eversole','1993-04-30 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2501,N'Sarah',N'Houde','1978-01-23 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2504,N'Matthew',N'Hift','1989-08-10 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2506,N'Victoria',N'Hardy','2016-11-13 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2509,N'Sarah',N'Goldberg','1986-11-07 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2512,N'Mary',N'Edwards','1962-10-22 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2513,N'Stephanie',N'Buell','1992-12-21 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2514,N'Harry',N'Harvey','1968-03-26 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2516,N'Colleen',N'Edwards','1996-07-16 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2517,N'Alanna',N'Shea','1996-05-27 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2521,N'Julie',N'Oloughlin','1988-06-06 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2524,N'Lydia',N'Wilson','2016-06-14 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2525,N'Matt',N'Engel','2016-09-28 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2526,N'Jason',N'Peters','1974-05-11 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2528,N'Christina',N'Jones','1998-03-26 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2530,N'Rich',N'Santello','1963-06-25 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2532,N'Amanda',N'Coffey','1963-05-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2533,N'Mark',N'Valliere','1977-07-14 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2536,N'Jeremy',N'Anderson','1983-05-20 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2539,N'Lily',N'Van Petten','2016-06-20 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2554,N'Kathren',N'Santello','2016-06-02 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2555,N'Alexa',N'Steenbruggen','1992-12-24 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2633,N'Cory',N'Hicks','1979-10-16 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2634,N'Laura',N'Smolenski','2016-07-03 00:00:00.000');
INSERT INTO birthdays(cust_id,cust_fname,cust_lname,cust_dob) VALUES (2636,N'michaela',N'olson','2016-02-11 00:00:00.000');

  --DECLARE @Yesterday DATETIME = DATEADD(DAY,-1,CAST(GETDATE() AS DATE));
--   @MonthStart DATETIME =DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0);

  select cust_id, cust_fname, cust_lname, cust_dob from birthdays
where cust_active = 1 and cust_dob > '1900-01-01 00:00:00.000'

  Looking to write a simple query that will return the Birthday's, by month if I choose a Date Range in the month spcified
  So this code (below) because I chose January, will ignore the year and day.
  select cust_id, cust_fname, cust_lname, cust_dob from birthdays
where cust_active = 1 and cust_dob between '1995-01-26 00:00:00.000' and '1995-01-27 00:00:00.000'

  So that query (should) will return all birthdays in the month of Jan, no matter the year or date.
  The obvious is the @MonthStart code above, I just dont know how to lay it out correctly.

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  November 21, 2017 at 8:46 pm

  #1969112

  If finding rows by birth month without regard to year or day is something important, it's probably worthwhile to materialize the birth month in the table using a persisted computed column.  Like this....

  
 CREATE TABLE dbo.birthdays
        (
         cust_id    INTEGER         NOT NULL PRIMARY KEY
        ,cust_fname NVARCHAR(50)    NOT NULL
        ,cust_lname NVARCHAR(50)    NOT NULL
        ,cust_dob   DATETIME        NOT NULL
        ,cust_dobmo AS DATEPART(mm,cust_dob) PERSISTED --<----<<< Added this column    
        )
;


  Then the code become trivial for such things.  For example...

  
DECLARE @pSomeDate DATETIME = GETDATE(); --Could be a parameter for a Stored Procedure or iTVF

   SELECT *
     FROM dbo.birthdays
    WHERE cust_dobmo = DATEPART(mm,@pSomeDate)
  ;
  

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  November 22, 2017 at 12:55 am

  #1969121

  Hi,

  This might fitting into your requirement.

  SELECT *
  FROM  (
                SELECT Month(cust_dob) AS modified_date,
                       cust_id,
                       cust_fname,
                       cust_lname,
                       cust_dob
                FROM   birthdays)a;

  OR

  
  

  SELECT * FROM  (
                SELECT Month(cust_dob) AS modified_date,
                       cust_id,
                       cust_fname,
                       cust_lname,
                       cust_dob
                FROM   birthdays)a
  WHERE  modified_date IN
         (
                SELECT Month(cust_dob) AS modified_date
                FROM   birthdays)  /* Use where conditions as per your requirement/*

  Saravanan

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  November 22, 2017 at 7:27 am

  #1969169

  saravanatn - Wednesday, November 22, 2017 12:55 AM

  Hi,

  This might fitting into your requirement.

  SELECT *
  FROM  (
                SELECT Month(cust_dob) AS modified_date,
                       cust_id,
                       cust_fname,
                       cust_lname,
                       cust_dob
                FROM   birthdays)a;

  OR

  
  

  SELECT * FROM  (
                SELECT Month(cust_dob) AS modified_date,
                       cust_id,
                       cust_fname,
                       cust_lname,
                       cust_dob
                FROM   birthdays)a
  WHERE  modified_date IN
         (
                SELECT Month(cust_dob) AS modified_date
                FROM   birthdays)  /* Use where conditions as per your requirement/*

  One way or another, that's going to guarantee at least one index scan and possibly 2 on the second bit of code even after you add "where conditions as per your requirement".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  November 22, 2017 at 8:24 am

  #1969182

  Hi Jeff,

  Thanks for point it out and your contribution to SQL is immense . I am a tester when I run my query it usually takes lot of time.What are the ways to practically improve query performance.

  Regards,
  Saravanan

  Saravanan

• chef423

  SSCertifiable

  Points: 5279

  More actions

  November 22, 2017 at 8:51 am

  #1969193

  Jeff Moden - Tuesday, November 21, 2017 8:46 PM

  If finding rows by birth month without regard to year or day is something important, it's probably worthwhile to materialize the birth month in the table using a persisted computed column.  Like this....

  
 CREATE TABLE dbo.birthdays
        (
         cust_id    INTEGER         NOT NULL PRIMARY KEY
        ,cust_fname NVARCHAR(50)    NOT NULL
        ,cust_lname NVARCHAR(50)    NOT NULL
        ,cust_dob   DATETIME        NOT NULL
        ,cust_dobmo AS DATEPART(mm,cust_dob) PERSISTED --<----<<< Added this column    
        )
;


  Then the code become trivial for such things.  For example...

  
DECLARE @pSomeDate DATETIME = GETDATE(); --Could be a parameter for a Stored Procedure or iTVF

   SELECT *
     FROM dbo.birthdays
    WHERE cust_dobmo = DATEPART(mm,@pSomeDate)
  ;
  

  Thanks Jeff. The original table I get the Customer data from is dbo.Customers

  So I cannot replace birthdays with that name is says the table is already created.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 22, 2017 at 8:53 am

  #1969194

  chef423 - Wednesday, November 22, 2017 8:51 AM

  Jeff Moden - Tuesday, November 21, 2017 8:46 PM

  If finding rows by birth month without regard to year or day is something important, it's probably worthwhile to materialize the birth month in the table using a persisted computed column.  Like this....

  
 CREATE TABLE dbo.birthdays
        (
         cust_id    INTEGER         NOT NULL PRIMARY KEY
        ,cust_fname NVARCHAR(50)    NOT NULL
        ,cust_lname NVARCHAR(50)    NOT NULL
        ,cust_dob   DATETIME        NOT NULL
        ,cust_dobmo AS DATEPART(mm,cust_dob) PERSISTED --<----<<< Added this column    
        )
;


  Then the code become trivial for such things.  For example...

  
DECLARE @pSomeDate DATETIME = GETDATE(); --Could be a parameter for a Stored Procedure or iTVF

   SELECT *
     FROM dbo.birthdays
    WHERE cust_dobmo = DATEPART(mm,@pSomeDate)
  ;
  

  Thanks Jeff. The original table I get the Customer data from is dbo.Customers

  So I cannot replace birthdays with that name is says the table is already created.

  Then add the computed column to the Customer table.

• chef423

  SSCertifiable

  Points: 5279

  More actions

  November 22, 2017 at 10:12 am

  #1969218

  Lynn Pettis - Wednesday, November 22, 2017 8:52 AM

  chef423 - Wednesday, November 22, 2017 8:51 AM

  Jeff Moden - Tuesday, November 21, 2017 8:46 PM

  If finding rows by birth month without regard to year or day is something important, it's probably worthwhile to materialize the birth month in the table using a persisted computed column.  Like this....

  
 CREATE TABLE dbo.birthdays
        (
         cust_id    INTEGER         NOT NULL PRIMARY KEY
        ,cust_fname NVARCHAR(50)    NOT NULL
        ,cust_lname NVARCHAR(50)    NOT NULL
        ,cust_dob   DATETIME        NOT NULL
        ,cust_dobmo AS DATEPART(mm,cust_dob) PERSISTED --<----<<< Added this column    
        )
;


  Then the code become trivial for such things.  For example...

  
DECLARE @pSomeDate DATETIME = GETDATE(); --Could be a parameter for a Stored Procedure or iTVF

   SELECT *
     FROM dbo.birthdays
    WHERE cust_dobmo = DATEPART(mm,@pSomeDate)
  ;
  

  Thanks Jeff. The original table I get the Customer data from is dbo.Customers

  So I cannot replace birthdays with that name is says the table is already created.

  Then add the computed column to the Customer table.

  ALTER TABLE Customers
ADD cust_dobmo AS DATEPART(mm,cust_dob) PERSISTED

• ScottPletcher

  SSC Guru

  Points: 101120

  More actions

  November 22, 2017 at 11:54 am

  #1969244

  Or take a step back and consider whether "dob" shouldn't be stored as separate columns to begin with, in accordance with standard data normalization (if you need to constantly look only at the month of a date, that might make it an atomic value, even though that's not normally true for most date columns). 

  Don't fall into the trap of trying to store data a certain way just because it's displayed a certain way.  Remember, you can always combine/reformat columns to display data as needed.

  The easiest way is probably to "fudge" a bit and store dob as a date but with a default year of 1900, and store the actual birth year in a separate column.  This makes checking the birth month (and day, if needed) very easy and standard, viz:
  cust_dob >= '19000101' AND cust_dob < '19000201'
  It also makes checking min age rather easy:
  --check if cust is at least 18 yo
  cust_yob <= '1998' OR (cust_yob = 1997 AND ...)

  Or you could store y&m&d separately, with of course a check constraint to verify that the values yielded a valid date.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 22, 2017 at 12:05 pm

  #1969248

  I'd rather store the date as a date and use computed columns if I need the month, day, or year separately on a regular basis.

• ScottPletcher

  SSC Guru

  Points: 101120

  More actions

  November 22, 2017 at 12:25 pm

  #1969251

  Lynn Pettis - Wednesday, November 22, 2017 12:05 PM

  I'd rather store the date as a date and use computed columns if I need the month, day, or year separately on a regular basis.

  I don't know if computed columns still prevent parallel execution plans and/or still have other execution quirks. 

  But it doesn't really matter.  Either way, I'd still prefer to properly normalize data whenever possible, and for the standard reason: it prevents issues and more work later.  You can bet that sometime after you compute and store the month, they'll be a requirement to notify based on birthdays in the upcoming week, and you're back to adjusting the table yet again to add yet another computed column, day.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 22, 2017 at 12:34 pm

  #1969252

  ScottPletcher - Wednesday, November 22, 2017 12:25 PM

  Lynn Pettis - Wednesday, November 22, 2017 12:05 PM

  I'd rather store the date as a date and use computed columns if I need the month, day, or year separately on a regular basis.

  I don't know if computed columns still prevent parallel execution plans and/or still have other execution quirks. 

  But it doesn't really matter.  Either way, I'd still prefer to properly normalize data whenever possible, and for the standard reason: it prevents issues and more work later.  You can bet that sometime after you compute and store the month, they'll be a requirement to notify based on birthdays in the upcoming week, and you're back to adjusting the table yet again to add yet another computed column, day.

  I think I covered that in my post, or am I imagining that I wrote month, day, or year?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 22, 2017 at 12:37 pm

  #1969253

  Or, have a calendar table and reference it by the date and have it break the dates into constituent parts.

• ScottPletcher

  SSC Guru

  Points: 101120

  More actions

  November 22, 2017 at 12:44 pm

  #1969254

  Lynn Pettis - Wednesday, November 22, 2017 12:34 PM

  ScottPletcher - Wednesday, November 22, 2017 12:25 PM

  Lynn Pettis - Wednesday, November 22, 2017 12:05 PM

  I'd rather store the date as a date and use computed columns if I need the month, day, or year separately on a regular basis.

  I don't know if computed columns still prevent parallel execution plans and/or still have other execution quirks. 

  But it doesn't really matter.  Either way, I'd still prefer to properly normalize data whenever possible, and for the standard reason: it prevents issues and more work later.  You can bet that sometime after you compute and store the month, they'll be a requirement to notify based on birthdays in the upcoming week, and you're back to adjusting the table yet again to add yet another computed column, day.

  I think I covered that in my post, or am I imagining that I wrote month, day, or year?

  If you're going to store them all separately, then it's redundant to store them combined, and again violates normal form (redundant data).

  I'm not against computed columns per se, but I don't believe they are the proper solution for this problem.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 22, 2017 at 12:48 pm

  #1969255

  ScottPletcher - Wednesday, November 22, 2017 12:44 PM

  Lynn Pettis - Wednesday, November 22, 2017 12:34 PM

  ScottPletcher - Wednesday, November 22, 2017 12:25 PM

  Lynn Pettis - Wednesday, November 22, 2017 12:05 PM

  I'd rather store the date as a date and use computed columns if I need the month, day, or year separately on a regular basis.

  I don't know if computed columns still prevent parallel execution plans and/or still have other execution quirks. 

  But it doesn't really matter.  Either way, I'd still prefer to properly normalize data whenever possible, and for the standard reason: it prevents issues and more work later.  You can bet that sometime after you compute and store the month, they'll be a requirement to notify based on birthdays in the upcoming week, and you're back to adjusting the table yet again to add yet another computed column, day.

  I think I covered that in my post, or am I imagining that I wrote month, day, or year?

  If you're going to store them all separately, then it's redundant to store them combined, and again violates normal form (redundant data).

  I'm not against computed columns per se, but I don't believe they are the proper solution for this problem.

  A date is a date and should be stored as a date. Just saying.