Query help

microsoft.public.sqlserver.programming

22 Nov 2006 10:09 AM

SQL_help

Hi,

The question is mentioned below the DDL.

CREATE TABLE BUDGET
(
emp_id INT,
task_name VARCHAR(20),
allotted INT,
spend INT,
commn INT,
balance INT,
date_spend DATETIME
)
GO

INSERT INTO budget VALUES(1,'TASK1',1000,800,25,175,'JAN 1 2005')
INSERT INTO budget VALUES(1,'TASK2',8000,7800,200,0,'JAN 2 2005')
INSERT INTO budget VALUES(1,'TASK3',16000,10000,2000,4000,'JAN 26 2005')
INSERT INTO budget VALUES(1,'TASK4',100,70,2,28,'JAN 30 2005')
INSERT INTO budget VALUES(1,'TASK5',5000,4000,500,500,'FEB 2 2005')
INSERT INTO budget VALUES(1,'TASK6',15000,10000,2000,3000,'FEB 25 2005')
INSERT INTO budget VALUES(1,'TASK7',20000,10000,1000,9000,'MAR 12 2005')

INSERT INTO budget VALUES(2,'TASK8',15000,14000,500,500,'FEB 21 2005')
INSERT INTO budget VALUES(2,'TASK9',600,500,20,80,'FEB 26 2005')
INSERT INTO budget VALUES(3,'TASK10',1000,900,10,90,'MAR 09 2005')

--Requirement

1. Need to find out the total sum of 'spend' and 'commn' for all tasks in
the month of jan 2005 by empid "1". I have written the below query. Is it
the correct and optimised one?

SELECT SUM(spend)+ SUM(commn)
FROM dbo.budget
WHERE emp_id=1
AND YEAR(date_spend) = 2005
AND MONTH(date_spend) = 1

2. Also, I need to place a CHECK constraint on 'balance' column . Need to
check if 'balance' = 'allotted' - ('spend' + 'commn'). How to set that?

Thanks!
SQL_help