DECLARE @main TABLE (Type VARCHAR(6), EmployeeID INT, CenterName VARCHAR(7), CenterEmployeeName VARCHAR(50), Title VARCHAR(5), cc_app_status VARCHAR(10), Month VARCHAR(10), Year INT)
DECLARE @customerCalendar TABLE (ID INT IDENTITY, month VARCHAR(10), year INT)
INSERT INTO @customerCalendar (month, year) VALUES
('Q1', 2013),('January', 2013),('February', 2013),('March', 2013),
('Q2', 2013),('April', 2013),('May', 2013),('June', 2013),
('Q3', 2013),('July', 2013),('August', 2013),('September', 2013),
('Q4', 2013),('October', 2013),('November', 2013),('December', 2013)
INSERT INTO @main (type, employeeID, centerName, centerEmployeeName, title, cc_app_status, month, year) VALUES
('Master', 123, 'Center1', 'Mike Hammond', 'Admin', 'Approved', 'January', 2013),
('Master', 111, 'Center2', 'Sam Smith', 'Admin', 'Denied', 'February', 2013),
('Master', 333, 'Center3', 'Sara Douglas', 'Admin', 'Denied', 'June', 2013),
('Master', 444, 'Center4', 'Richard Franks', 'Admin', 'Pending', 'June', 2013),
('Master', 818, 'Center1', 'Tom Hinder', 'Admin', 'Denied', 'April', 2013),
('Master', 114, 'Center2', 'Rick Jones', 'Admin', 'Approved', 'April', 2013),
('Master', 182, 'Center3', 'Bob Segar', 'Admin', 'Denied', 'December', 2013),
('Master', 194, 'Center4', 'Jay Franks', 'Admin', 'Denied', 'Q1', 2013),
('Master', 280, 'Center1', 'Tim Tims', 'Admin', 'Pending', 'Q2', 2013),
('Master', 364, 'Center1', 'Tummy Tipperton', 'Admin', 'Pending', 'December', 2013)
SELECT COUNT(*)
FROM @main m
LEFT OUTER JOIN @customerCalendar cc
ON m.month = cc.month
AND m.year = cc.year
SELECT COUNT(*)
FROM @main
Based on your example data there is no problem.
Try this:
SELECT month, year
FROM customerCalendar
GROUP BY month, year
HAVING COUNT(*) > 1
If you get rows, you have dupes in your calendar table.
Please provide example data as tables in the future. Make it easy for us to help you.
No comments:
Post a Comment