Stats: 3,190,238 members, 7,939,912 topics. Date: Wednesday, 04 September 2024 at 02:03 PM |
Nairaland Forum / Science/Technology / Programming / SQL Table Problem (1144 Views)
Top 20 Websites To Learn Coding With Java, Python, SQL, Algorithms, And Git For / Urgent Help Needed On How To Save Fingerprint To Sql Using U Are U 4500 And Java / SQL Table Normalization (2) (3) (4)
SQL Table Problem by fidelism: 4:37am On Nov 28, 2016 |
I want to create a column in sql table that will only display current year (example below Column B). This means that every year, the column automatically updates itself. There will also be another column (Column C) with figures that resulted from subtraction of current year (B) from another year (Column A). Example. Column A is Year of Employment. Column B is Current Year. Column C is A-B.. Help out pls |
Re: SQL Table Problem by heritage2009(m): 5:20am On Nov 28, 2016 |
fidelism: SELECT DateReturn FROM tblClaims WHERE tblClaims.DateReturn BETWEEN GETDATE()-3 AND GETDATE() |
Re: SQL Table Problem by larisoft: 6:48am On Nov 28, 2016 |
good job @op. but pls , only column a should be stored in ur database. b and c should be computed in ur business logic. think about this...what happens when u need to change the date of employment? in ur current db design u ll have to also change the other columns. keep coding bro. |
Re: SQL Table Problem by samm(m): 8:57pm On Nov 28, 2016 |
I second Larisoft's recommendation as far as computing column B and C. Select Year(EmploymentDate) - Year(Current_Timestamp) From YourTanleame If you are already storing Employment Year: Select EmploymentYear - Year(Current_Timestamp) From YourTableName However if you must store the current year and depending on your DB system (my example is based on MS SQL Server), you can do this: Alter Table YourTableName Add CurrentYear AS (Year(Current_Timestamp)) Let me know if you need further clarification. |
Re: SQL Table Problem by fidelism: 7:21am On Nov 29, 2016 |
Can we chat on whatsapp 08062539412 |
Re: SQL Table Problem by fidelism: 7:41am On Nov 29, 2016 |
samm: Can we chat on whatsapp 08062539412 |
Re: SQL Table Problem by paranorman(m): 4:10pm On Nov 29, 2016 |
oga OP, all you need is a calculated field in your SQL query. for a table to be well normalized, calculated fields must not be part of the original design. so your column 'C' should not be part of your DB table, instead, you generate it using a query - calculated virtual fields your table only need a column containing info about each employee's employment year, say 'employmentYear'; then query your db thus: SELECT study the following mysql functions CONCAT(A, B) FORMAT(X, D) CURRDATE(), CURRTIME() NOW() MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() HOUR(), MINUTE(), SECOND() DATEDIFF(A, B) SUBTIMES(A, B) FROMDAYS(INT) |
Re: SQL Table Problem by fidelism: 9:06pm On Nov 29, 2016 |
what I want to achieve. I am working with an ECM solution that pulls data from data base. The columns I mentioned and the information therein are important data for the ECM solution to do what it is expected to do. For example, when a staff choose 20 Years on the ECM solution, the solution is expected to pull up all the data of staff whose EmploymentYear-CurentYear = 20 year of service. As long as a Column 'c' exists on the databass that has years of service, the ECM solution can handle the rest. I need the current year to be the server dateyear so that at every Jan 1, it automatically update without external interference |
Re: SQL Table Problem by paranorman(m): 9:58am On Nov 30, 2016 |
Bro, if you want to sacrifice performance for flexibility, then you can create another column for the current date and/or time. MySQL has a function for that. Alternatively, you can create a View using the query I wrote up there. Sql views are saved and updated automatically,they are 'read only' too. |
Re: SQL Table Problem by paranorman(m): 10:18am On Nov 30, 2016 |
Template of the view code: CREATE VIEW viewNoServiceYear AS After the view as been saved in your employee database, Query the view thus: SELECT FROM viewNoServiceYear When creating the view, include the fields/columns of your target from their respective tables from your database. This should solve your problem. Remember views are 'read only'. Content of your database cannot be modified from a view. So, views are safe and are updated automatically. |
(1) (Reply)
IPSAS Accounting Software For Nigeria's Federal Government / I Need Someone To Help Me Interegate Cashenvoy With Drupal Ubercart / I Can't Solve This... HELP
(Go Up)
Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health religion celebs tv-movies music-radio literature webmasters programming techmarket Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 19 |