How to design database for sales tables in sql ??

Rajneesh Chaubey

8y1.7k

Hi,I have a employee table having following data:-

E_ID E_Name E_Zone Username Password

How do I design the tbltarget where I can keep track of targets for every employee on monthly basis. e.g. we can keep track of E_ID 501 has got how many target in sept month of 2017 and how many he achieved. I have designed the table like this way(below)

tbl_target :-

TID EID Target month Achieved
1 504 25 May 17
2 501 28 Jan 15
3 502 29 June 15
4 503 30 April 16
5 503 25 June 17
6 501 29 April 22
7 502 29 June 19
8 504 30 March 20
9 505 25 July 19
10 501 29 May 24

So, there is repetition of rows(E_ID). Is there any effective method that you guys know that will work out here?? Pls let me know.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
oops minor mistake in the second line

SELECT EID
, [YYYY-MM] = CAST([Year] As Varchar) + '-' + [Month]
, [Target] = Sum(Target)
, [Achived] = Sum(Achived)
, [Work to do] = Sum(Target) - Sum(Achived)
FROM tblXYZ
GROUP BY [EID] , [Year] , [Month]
Shahriar Nikkhah8y ago
Your view/query will have to look like the mentioned , you will have to add the year and test the query your self

SELECT EID
, [YYYY-MM] = CAST([Year] As Varchar) + [Month]
, [Target] = Sum(Target)
, [Achived] = Sum(Achived)
, [Work to do] = Sum(Target) - Sum(Achived)
FROM tblXYZ
GROUP BY [EID] , [Year] , [Month]

Good luck
Piyush Agarwal8y ago
That's correct. You should also include the year column in your table so that you can easily filter your data from your table.
Ronika Jency8y ago
Your tbl_target table missing the column name of year. If you include that column name then you can filter the data 501 employee id may month of 2017. or else you will have two 501 record with same month.

Another option also you can add one more column Created_Date in that you can get year.
Khaja Moizuddin8y ago
You need to join both the tables and get/track the employee records