SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Choosing the Birthday of a Customer by month?


Choosing the Birthday of a Customer by month?

Author
Message
chef423
chef423
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2485 Visits: 403
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
Jeff Moden
SSC Guru
SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)

Group: General Forum Members
Points: 463754 Visits: 43803

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
saravanatn
saravanatn
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 382
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/*



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)

Group: General Forum Members
Points: 463754 Visits: 43803
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
saravanatn
saravanatn
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 382
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
chef423
chef423
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2485 Visits: 403
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
Lynn Pettis
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204042 Visits: 40038
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
chef423
chef423
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2485 Visits: 403
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
ScottPletcher
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42521 Visits: 7925
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204042 Visits: 40038
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search