Calculate Age Based On Dob
Aug 26, 2004
hi all;
I have seen posts on numerous websites to get the age from a dob column I am faced with the same problem but get this error:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals divide, type equals datetime.
when I run the following query:
SELECT ROUND((currentdate - DOB)/365.24,0) FROM FL1_A_Backup
All I need is a simple statement that calcualtes the age from the dob column(smalldatetime) then creates a new row which it puts the data into.
I have already created a CurrentDate column which has a (getdate()) value attached to it.
This is nothing fancy or complicated and I just need to figure out from the year I'm not really concerned about if the person has a bday tomorrow or so on.
I know I am probably doing something wrong with this query and am not shy to say that I have only been using ms sql for a couple of months (basic select statements).
View 14 Replies
ADVERTISEMENT
Mar 10, 2008
I am trying to create a report in SRS that will calculate a Composite Score based on the Sum of other scores and then that Sum is divided by 12.
Here is a copy of the Query I have created to calculate the numbers I want to use, but when I create the expression in SRS the calculations don't come out correctly. The expression that I use in SRS is at the bottom of the Script.
Thanks in Advance
--6 Trait_03052008.sql
--Pull test scores for 6 Trait out of Campus for SRS reports.
SELECT s.lastname+','+' '+s.firstname AS Student, e.grade, sm.lastname+','+' '+sm.firstname AS Teacher, sc.name AS School,
t.name AS Test, t.code, ts.rawscore,
CASE WHEN t.name = '1 Ideas' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 3 AS Numeric)END AS IComp,
CASE WHEN t.name = '2 Organization' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 3 AS Numeric) END AS OComp,
CASE WHEN t.name = '3 Voice' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 2 AS Numeric)END AS VComp,
CASE WHEN t.name = '4 Word Choice' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 2 AS Numeric)END AS WComp,
CASE WHEN t.name = '5 Sentence Fluency' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore AS Numeric) END AS SComp,
CASE WHEN t.name = '6 Conventions' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore AS Numeric)END AS CComp
FROM testscore ts
JOIN student s ON s.personid = ts.personid
JOIN roster r ON r.personid = s.personid
JOIN test t ON t.testid = ts.testid
JOIN calendar ca ON ca.calendarid = s.calendarid
JOIN enrollment e ON e.enrollmentid = s.enrollmentid and e.calendarid = ca.calendarid
JOIN section se ON se.sectionid = r.sectionid
JOIN course c ON c.courseid = se.courseid AND c.calendarid = ca.calendarid
JOIN school sc ON sc.schoolid = s.schoolid
JOIN staffmember sm ON sm.personid = se.teacherpersonid AND sm.schoolid = ca.schoolid
WHERE t.code LIKE '6%' AND c.calendarid =50 AND c.homeroom = 1
GROUP BY s.lastName,s.firstName,e.grade,sm.lastName,sm.firstName,sc.name,t.name,t.code,ts.rawScore
ORDER BY [Student], t.code, t.name DESC
Expression being used in SRS to calculate the Composite Score.
=SUM(Fields!IComp.Value)+(Fields!OComp.Value)+(Fields!VComp.Value)+(Fields!WComp.Value)+(Fields!SComp.Value)+(Fields!CComp.Value)/12
View 4 Replies
View Related
Apr 10, 2008
I have a number of databases with large tables. I need to update them from time to time. I want to get the recordcount of the table and calculate based on that the amount of time it would take to update the table. Any idea who I can do this? I'm using coldfusion 8 with sql to do this. Any advice would be appreciate!
Thanks
Shuvi
View 2 Replies
View Related
Oct 30, 2015
Col1 Col2 Col3 Col4
54763.00 21 0 0
--Row1
59574.00 23 0 0
--Row2
64085.00 20 0 0
--Row3
0.0 0.00 0 0 0
--Row4
I'm trying to calculate Average of Col1 of above table based on below scenario:
CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3
ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4
View 7 Replies
View Related
Jun 4, 2015
I have a table "PrintArea" with below table structure.
The Details column have " " separated data.
CREATE TABLE [dbo].[PrintArea](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccessDate] [datetime] NULL,
[IPAddress] [varchar](16) NULL,
[SerialNum] [varchar](256) NULL,
[Description] [varchar](256) NULL,
[Details] [varchar](2048) NULL,
)
This is how the data lookis like.
INSERT [dbo].[PrintArea] ([ID], [AccessDate], [IPAddress], [SerialNum], [Description], [Details]) VALUES (104280, CAST(0x0000A28A00A88310 AS DateTime), N'70.89.39.161', N'InSign-f529c824-85a6-4a64-8c59-e9f786d718f3-YM2AS31367,BFEBFBFF000306A9', N'Printing', N'Headboard_Insert_4
1
1
7938
5579
[Code] ...
I want to calculate totalAreaPrinted based on below formula from Details column.
View 2 Replies
View Related
Feb 7, 2000
I have a client with no backup of MASTERdb or Help_rev_devices, all I have is a couple of .dmp's of the production data. In order to run DISK REINIT I need to know the size of the database, is there any way of finding out the size of the database device, if all you have to go with is a backup dump file?
I know it won't be pretty.
`Ken
View 1 Replies
View Related
Jan 18, 2015
I want to calculate stock aging (qty, cost) based on the on hand quantity.
Currently I am recording only in/out transaction only.
For ex: Item A i have 115 pieces (Balance stock) as on to day.
Transaction History
---------------------
Lot 1 - 01/01/2015 - 50
Lot 2 - 10/02/2015 - 50
Lot 3 - 11/03/2015 - 50
Lot 4 - 15/04/2014 - 50
I want to calculate cost of balance qty as shown below.
Jan -
Feb - 15 @ 1.1
Mar - 50 @ 0.90
Apr - 50 @ 1.2
Database schema
--------------------
CREATE TABLE [dbo].[StockManagement](
[Uniid] [int] IDENTITY(1,1) NOT NULL,
[StockCode] [int] NULL,
[TransactionDate] [datetime] NULL,
[TransactionTime] [time](0) NULL,
[Code] .....
View 0 Replies
View Related
Jan 5, 2015
I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
For this :
I calculated datediff + 1 and got sub jobs days BUT
say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014
PhaniMarketing NULL (Do not fall in Req Dt)
PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec)
PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days)
Total days 31
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
View 4 Replies
View Related
Jun 5, 2014
I am trying to build a query which will be used in an automated report to calculate failure rates of systems based on cases opened through support. Here is where I am stuck. Some systems may have multiple cases opened within the same span of another cases however we would consider this one failure:
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase22013-07-12 16:27:50.0002013-07-12 16:29:12.000
System ACase32013-07-12 17:30:32.0002013-07-12 17:40:11.000
System ACase42013-07-12 19:00:24.0002013-07-12 19:04:14.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
Lets say System A generated those 5 cases however Case 2,3 and 4 all happened within the same period as Case 1 so those 4 cases should count as one failure so my end result should be
System ACase12013-07-11 13:17:09.0002013-07-15 12:05:03.000
System ACase52013-10-01 18:02:23.0002013-10-01 18:11:26.000
And that system should show me 2 failures. I was thinking of using a temp table but not sure if that is possible as I am stumped on how to compare the dates to be able to validate if they fall within the range of an older case and whether or not to include them into the new Temp Table.
View 8 Replies
View Related
Jan 5, 2015
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
View 0 Replies
View Related
Mar 18, 2014
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
View 2 Replies
View Related
Apr 10, 2014
I have a query that calculate the total amount of order details based on a particular order:
Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID
My question is what if I wanted to create a formula to something like:
UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount
Do I need to create a function for that? Also is it possible to have m y query as a table variable?
View 7 Replies
View Related
Oct 7, 2006
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
View 1 Replies
View Related
Feb 13, 2007
Hi,
I am trying to execute a query which calculate % on group by. here is the query
SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))
/ (
SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID)) FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))
)*100 AS [%]
FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_IDWHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL')) GROUP BY C.USR_HIGHEST_DEGREE
My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.
Any help would be appreciate.
View 3 Replies
View Related
Mar 30, 2007
I have an SQL statement which returns the Top 10 states with the number of visitorsSELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors FROM [Customer] WHERE Customer.year = '2006' GROUP BY Customer.state ORDER BY COUNT(Customer.state) DESCSo far this is what I havestate| visitorsMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9But what i need is to calculate the total for the Visitors column in my SQL so that is like soMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?Thank you
View 8 Replies
View Related
Feb 13, 2008
Hi all,
I'm having the table in the following structure,
UId - int
Pct - int
Amt - money
Example
UId Pct Amt
12 25 1500
12 30 2500
12 45 2000
i want to calculate to calculate the sum of amount for the UId 12 in the following manner,
sum of ( Amt * (Pct/100)) for UId = 12
how to write query in sql server 2000?
when i calculate if the value (Pct/100) < 0 that is 0.25 then it will take it as 0 so i'm getting all the values as 0.
Thanks!
View 2 Replies
View Related
Mar 7, 2001
I have a table that has a providername and totals field.
I want to be able to calculate the MTD and YTD totals for each provider.
EX. Provider MTD YTD
Bob 100.00 300.00
But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks
Ron
View 3 Replies
View Related
Nov 10, 2006
Can anyone help me with how to calculate the exact age.
Thanks in advance.
View 1 Replies
View Related
Jan 25, 2004
I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?
View 8 Replies
View Related
Apr 15, 2008
I want to calculate rate as percentage using code below:
SELECT OrderBy, NumShiped/Total AS Rate FROM Order
I always got Rate = 0 even thought NumShiped = 80 and Total = 100
What is problem?
View 3 Replies
View Related
May 30, 2008
Hello,
I have a date of birth column in my table and I want to calculate age based on this date. Here is the code I am trying to use in my select statement.
DateDiff ("yyyy",(a.date_of_birth),CurrentDate)
Apparently CurrentDate is not recognized by SQL. What is the best way to do this?
View 9 Replies
View Related
Jan 4, 2008
I have the table with list of 1500 employees with the following headings.
Surname, Ini,title,gender,Race,IdNo,FirstName
Then I need to calculate the age of each employee but I am stuk.
Please assist me.
View 8 Replies
View Related
May 6, 2008
Hello every one, I am trying to get the total percentage of a column
Regions
Workbooks Required
Workbooks Sent
Workbooks Returned
Workbooks Complete
A
20
21
20
18
B
33
33
33
30
C
19
29
18
16
D
9
18
8
8
Totals
Thanks in advance for any tips/solutions.
View 5 Replies
View Related
Mar 7, 2004
Need to calculate the Grade age based on the birthdate and Nov month and 30th Day of the current year.
I have a working datediff statement but I need to always but in the current year. I would like to have the statement get the current year. Then if the age is greater than xx and less than xx your age level is "xyz"
This works DateDiff("d" [Birthdate], 11/30/2004) /365.25 will return the age.
I want to replace the 2004 with a getdate yyyy so I do not need to maintain this statement.
Thanks in advance of a reply
Gary
View 2 Replies
View Related
Nov 12, 1999
Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.
View 3 Replies
View Related
Dec 30, 2003
I am having trouble creating a sp for the following situation:
The database contains a record of the mileage of trucks in the fleet. At the end of every month, a snapshot is collected of the odometer. The data looks like this:
Truck Period Reading
1 1/31/03 55102
2 1/31/03 22852
1 2/28/03 62148
2 2/28/03 32108
1 3/31/03 69806
2 3/31/03 52763
How can I calculate the actually miles traveled during the month in a query?
TIA,
Rob
View 7 Replies
View Related
Apr 28, 2004
b/w two colums?
i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanks
View 8 Replies
View Related
Aug 29, 2013
I have requirement in which i need to calculate percentage value based on billamount and concession amount..to calculate the percentage part and show the o/p.
SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],BM.TOTAL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.BILL_AMOUNT_M AS [TOTAL_AMOUNT],
FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'
[code]....
View 5 Replies
View Related
Apr 20, 2015
What is the best way, or a good way, or even a way to calculate age using a DOB field when the DOB field is sometimes NULL so that the result does not return NULL? Would prefer to just return an empty string.
I have this, which works, but returns NULL when DOB field is NULL.
floor(datediff(day, DOB, CURRENT_TIMESTAMP) / 365.25) as Age
And this obviously returns an error
isnull(floor(datediff(day, c.DOB, CURRENT_TIMESTAMP) / 365.25),'') as Age.
I prefer not to write a function.
View 3 Replies
View Related
Dec 13, 2005
Hi, Please help.
I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.
Please, help.
Thk
SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O
View 3 Replies
View Related
Dec 13, 2005
Hi, Please help.
I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.
Please, help.
Thk
SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O
View 1 Replies
View Related
Apr 12, 2006
Hi, everybody,
I'm new to SQL, hope you could help me.
I have a table named salary in sql server side, it contains several fields such as housing,overtime, netwage,totalnet gross, tax, factor...etc, the totalnet=netwage+housing+overtime, and gross=totalnet+tax.etc... and tax is calculated from a funcion below:
function taxn(ran:double):double;
var
saSum: Double;
begin
saSum := ran - 1600;
if saSum <=0 then result := 0
else if saSum <= 475 then Result := (saSum-0)/(1 - 0.05)*0.05-0
else if saSum < 1825 then Result := (saSum-25)/(1 - 0.1)*0.1-25
else if saSum < 4375 then Result := (saSum-125)/(1 - 0.15) *0.15-125
else if saSum <= 16375 then Result := (saSum-375)/(1 - 0.2)*0.2-375
else if saSum <= 31375 then Result := (saSum-1375)/(1 - 0.25)*0.25-1375
else if saSum <= 45375 then Result := (saSum-3375)/(1 - 0.3)*0.3-3375
else if saSum <= 58375 then Result := (saSum-6375)/(1 - 0.35)*0.35-6375
else if saSum <= 70375 then Result := (saSum-10375)/(1 - 0.4)*0.4-10375
else if saSum > 70375 then Result := (saSum-15375)/(1 - 0.45)*0.45-15375;
end;
ran=totalnet.
when the table loaded into a DBGrid, it is filled with housing, overtime and netwage, how to calculate other fields and display all in the DBGrid?
I tried to use onCalcFields events as below:
procedure TForm1.ADODataSet1CalcFields(DataSet: TDataSet);
var
totalnet,tax,taxedsalary:extended;
begin
with DataSet do
begin
FieldByName('totalnet').Value :=fieldbyname('housing').Value+fieldbyname('overtime').Value+fieldbyname('netwage').Value;
totalnet := fieldbyname('totalnet').Value;
fieldByName('taxedsalary').Value := roundto(taxbase(totalnet)+0.0001,-2);
tax:=roundto((taxn(totalnet)+0.0001),-2);
fieldbyname('totalgross').Value := totalnet+tax;
fieldbyname('tax').Value := tax;
fieldbyname('taxrate').Value :=floattostr(100*taxraten(totalnet))+'%';
fieldbyname('factor').Value:=factorn(totalnet);
end;
end;
but nothing happened.
Could you shed some lights?
THank you in advance.
View 1 Replies
View Related
Mar 22, 2007
Hi, I'm having huge troubles calculating overtime work in my db.
I have a table containing working hours that looks like this:
Userid Login Logout
---------------------------------------------------
2 21.03.2007 12:00:0021.03.2007 23:00:00
2 22.03.2007 08:00:0021.03.2007 17:00:00
To complicate things I also have a table that stores overtime rates:
From To Percent
----------------------------
00:00:00 20:00:00 0
20:00:00 22:00:00 20
22:00:00 24:00:00 50
(That percentage part isn't that important in this case.)
Now here's the tricky part, I want to transform those tables into something like this:
Userid 00:00-20:00 20:00-22:00 22:00-24:00
------------------------------------------
2 8 2 1
2 9 0 0
I'm pretty sure this requires a complicated sql query, but I have no clue where to start,
can someone push me in the right direction?
Any help is appreciated.
View 4 Replies
View Related
