create table for set of data

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 740 Views - Last Post: 27 October 2017 - 04:52 AM

#1 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

create table for set of data

Posted 19 October 2017 - 08:52 AM

Hi,

I am facing an issue regarding the creation of tables and datas.

Below is the assignment target for each person assigned for each day. Person A to be assigned on 1 Nov t hit 200 targets, Person B to be assigned on 2 Nov to hit 200 targets, Person C to be assigned on 3 Nov to hit 200 targets
Date   | Person | Target 
1-Nov  |  A     | 200
2 Nov  |  B     | 200
3-Nov  |  C     | 200
4-Nov  |  D     | 200



But the issue comes here, on 1 Nov,if Person A hits target of 200, he has finished his target for the day and Person B will take over the next 200.When Person B hit target of 200, Person C will take over the next 200 this time. If Person C has not hit target of 200. He will continue the next day(2 Nov). On 3 Nov, it will automatically assign to Person D even when Person C has not reach the 200 target, reason is because it is the next day. So Person C can rest and Person D will take over the first 200 target. So record ends up as
Date   | Person | Target 
1-Nov  |  A     | 200
1-Nov  |  B     | 200
1-Nov  |  C     | 150
2 Nov  |  C     | 20
3-Nov  |  D     | 100



Is there any way to create a table and column to cater for this scenario? As this is required.
Tried using IDs (eg. 1, 2, 3) but there might be issues in retrieving data.

This post has been edited by unknownmaster80: 19 October 2017 - 08:54 AM


Is This A Good Question/Topic? 0
  • +

Replies To: create table for set of data

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: create table for set of data

Posted 19 October 2017 - 09:00 AM

A database holds data. The business rules and logic are outside of that. So your scenario and process is handled elsewhere was the database just holds the information to be used.
Was This Post Helpful? 0
  • +
  • -

#3 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

Re: create table for set of data

Posted 19 October 2017 - 10:41 AM

View Postmodi123_1, on 19 October 2017 - 09:00 AM, said:

A database holds data. The business rules and logic are outside of that. So your scenario and process is handled elsewhere was the database just holds the information to be used.


yes. so now i'm trying to create a table but which caters for the scenarios. Is there a way to do so?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: create table for set of data

Posted 19 October 2017 - 11:11 AM

What's wrong with what you have? You track dates, user names, and amounts. Seems to be all what you need for your scenario.
Was This Post Helpful? 0
  • +
  • -

#5 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

Re: create table for set of data

Posted 19 October 2017 - 11:42 AM

View Postmodi123_1, on 19 October 2017 - 11:11 AM, said:

What's wrong with what you have? You track dates, user names, and amounts. Seems to be all what you need for your scenario.



But the issue comes here, on 1 Nov,if Person A hits target of 200, he has finished his target for the day and Person B will take over the next 200.When Person B hit target of 200, Person C will take over the next 200 this time. If Person C has not hit target of 200. He will continue the next day(2 Nov). On 3 Nov, it will automatically assign to Person D even when Person C has not reach the 200 target, reason is because it is the next day. So Person C can rest and Person D will take over the first 200 target. So record ends up as
Date   | Person | Target
1-Nov  |  A     | 200
1-Nov  |  B     | 200
1-Nov  |  C     | 150
2 Nov  |  C     | 20
3-Nov  |  D     | 100

2 Nov  |  B     | 0 - included data referring to below table
3-Nov  |  C     | 0 - included data referring to below table
4-Nov  |  D     | 0 - included data referring to below table



Is there any way to create a table and column to cater for this scenario? As this is required.
Tried using IDs (eg. 1, 2, 3) but there might be issues in retrieving data.


Example on 1 Nov, it can assign person accordingly based on the given dates as below.
Working:
Referring to below table, on 1 nov it can created records from Person A to C to become table above

Date   | Person | Target
1-Nov  |  A     | 200
2 Nov  |  B     | 200
3-Nov  |  C     | 200
4-Nov  |  D     | 200



Problem:
1. On 2 Nov it wont be able to "calculate" it hits 150(target of 200 not met) from previous day.
2. On 3 Nov it could not "calculate" even when target of 200 is not met. It will automatically assign from Person C to Person D.

The table would not have work on the given scenarios.
Example creating additional columns like adding IDs to the table but it does not seem to work on resolving 1 and 2..
Is there a way to create a table and column to cater for this scenario 1 and 2

This post has been edited by unknownmaster80: 19 October 2017 - 11:57 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: create table for set of data

Posted 19 October 2017 - 11:51 AM

Again.. that is all 'business logic'. You can pull data and store it to meet your needs.. what you do with it in terms of who is active, who isn't, who has stuff to carry over to the next day, and who is done is all business logic and not database logic.
Was This Post Helpful? 0
  • +
  • -

#7 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

Re: create table for set of data

Posted 19 October 2017 - 12:03 PM

View Postmodi123_1, on 19 October 2017 - 11:51 AM, said:

Again.. that is all 'business logic'. You can pull data and store it to meet your needs.. what you do with it in terms of who is active, who isn't, who has stuff to carry over to the next day, and who is done is all business logic and not database logic.


Yes..this is a business logic. But there is still a need to create a database column or something to resolve this business logic?
Or else it wouldn't be able to resolve 1 and 2.

This post has been edited by unknownmaster80: 19 October 2017 - 12:13 PM

Was This Post Helpful? 0
  • +
  • -

#8 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

Re: create table for set of data

Posted 19 October 2017 - 12:29 PM

--

This post has been edited by unknownmaster80: 19 October 2017 - 12:40 PM

Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: create table for set of data

Posted 19 October 2017 - 12:33 PM

No.. you pull the data back.. make your comparison, count of days, etc in your code.. the data doesn't do that.
Was This Post Helpful? 0
  • +
  • -

#10 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

Re: create table for set of data

Posted 19 October 2017 - 12:38 PM

View Postmodi123_1, on 19 October 2017 - 12:33 PM, said:

No.. you pull the data back.. make your comparison, count of days, etc in your code.. the data doesn't do that.


okie..i have tried creating a business logic but it doesn't seem to work..
as it doesn't know how to read that on 2 nov it should automatically assign to Person C and
on 3 nov it will assign to Person D due to the next assignment of person..that's why i'm trying to see if i can create something on the database side
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: create table for set of data

Posted 19 October 2017 - 12:43 PM

I have no clue on how you tried your business logic out but break the problem apart.

You need an ordered list of people? Super.

SELECT USER NAMEs 
FROM TABLE 
ORDER BY what ever


Great.. You need to know the last person to have data entered? Cool beans. Most databases have a 'MAX' aggregate to get the max value in a column.. say the last date. Get the row. Compare data for your quantity value.. and then compare it to who is next in your list of people.. etc.. etc.
Was This Post Helpful? 0
  • +
  • -

#12 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

Re: create table for set of data

Posted 19 October 2017 - 01:03 PM

View Postmodi123_1, on 19 October 2017 - 12:43 PM, said:

I have no clue on how you tried your business logic out but break the problem apart.

You need an ordered list of people? Super.

SELECT USER NAMEs 
FROM TABLE 
ORDER BY what ever


Great.. You need to know the last person to have data entered? Cool beans. Most databases have a 'MAX' aggregate to get the max value in a column.. say the last date. Get the row. Compare data for your quantity value.. and then compare it to who is next in your list of people.. etc.. etc.


Yes! that is the issue. If you take the max value, it would have referred data
4-nov Person D 200
(which is somehow wrong)

Y
Date   | Person | Target
1-Nov  |  A     | 200
2 Nov  |  B     | 200
3-Nov  |  C     | 200
4-Nov  |  D     | 200 



Z
Date   | Person | Target
1-Nov  |  A     | 200
1-Nov  |  B     | 200
1-Nov  |  C     | 150
2 Nov  |  C     | 20    
3-Nov  |  D     | 100


But let's say the date is only 2 nov
looking from Y..Person B is suppose to hit target of 200. But looking from Z, B had already done on 1 nov, C also did on 1 nov but it did not hit target of 200. So on 2 nov Person C will continue. Z and Y is the same table. So if you search by max it would have read 4-nov Person D 200 which is the wrong data as said above..

This post has been edited by unknownmaster80: 19 October 2017 - 01:05 PM

Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: create table for set of data

Posted 19 October 2017 - 01:17 PM

I don't follow why there is different tables now.
Was This Post Helpful? 0
  • +
  • -

#14 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

Re: create table for set of data

Posted 19 October 2017 - 01:31 PM

View Postmodi123_1, on 19 October 2017 - 01:17 PM, said:

I don't follow why there is different tables now.


All these data(Y and Z) are from the same table. But Y is just an allocated date, person and target, so the target is always 200. Apparently only on Z is when the person is trying to start their target...so on 1 nov itself, table (combining Y and Z) may be displayed as

Date   | Person | Target
1-Nov  |  A     | 200 - On Nov 1, have 550 data and will be assign to A, B and C referring to 'allocated date, person and target'
1-Nov  |  B     | 200
1-Nov  |  C     | 150
2 Nov  |  B     | 200 - allocated date, person and target
3-Nov  |  C     | 200 - allocated date, person and target
4-Nov  |  D     | 200 - allocated date, person and target


notice it is still not yet 2 nov but there is allocated date, person and target
those under allocated date, person and target has not started at all..so value is 0 and the purpose is to see who is the next allocated person to continue the target if e.g if 1 nov have more than 200 data, it will assign to the next person in queue..

If reading seperately to see in a different way..
Y
Date   | Person | Target
1-Nov  |  A     | 200 - allocated date, person and target
2 Nov  |  B     | 200 - allocated date, person and target
3-Nov  |  C     | 200 - allocated date, person and target
4-Nov  |  D     | 200 - allocated date, person and target


Z
Date   | Person | Target
1-Nov  |  A     | 200
1-Nov  |  B     | 200
1-Nov  |  C     | 150


is also the reason it might be a database problem..might need to create either new column/table

This post has been edited by unknownmaster80: 19 October 2017 - 02:40 PM

Was This Post Helpful? 0
  • +
  • -

#15 unknownmaster80   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 109
  • Joined: 06-August 11

Re: create table for set of data

Posted 27 October 2017 - 04:39 AM

found solution for this. instead of doing this with 2 repeated person A. one for allocated and one which done on the day itself..

combining Y and Z table
Date   | Person | Target
1-Nov  |  A     | 200 - allocated date, person and target
1-Nov  |  A     | 200 


to become this table only
Date   | Person | Target | Indicator
1-Nov  |  A     | 200    | Y


The indicator will change only when the person done on the day itself.
1 Nov is the allocated date and doesn't change. But it does not mean the person did on 1 nov.
the person might be doing on 30 Oct instead because that day the previous people did their targets and A is next on the queue.
But the allocated date still stays as 1 Nov as we only need to know if the next person did their targets.
Hope this helps :)/>/>

This post has been edited by andrewsw: 27 October 2017 - 04:52 AM
Reason for edit:: remove previous quote, use the REPLY button

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2