Conversion from Time to Decimal<\/p>\n
IF OBJECT_ID('Work_Summary') IS NOT NULL DROP TABLE Work_Summary\r\nIF OBJECT_ID('Job_Details') IS NOT NULL DROP TABLE Job_Details\r\nIF OBJECT_ID('Product') IS NOT NULL DROP TABLE Product\r\n\r\nCREATE TABLE Job_Details (\r\nJob_Number int PRIMARY KEY\r\n)\r\n\r\ninsert into Job_Details (Job_Number) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)\r\n\r\nCREATE TABLE Product (\r\nProduct_Code char(8) PRIMARY KEY\r\n)\r\n\r\ninsert into Product (Product_Code) values ('PC_1001'),('PC_1002'),('PC_1003'),('PC_1004'),('PC_1005'),('PC_1006'),('PC_1007'),('PC_1008'),('PC_1009'),('PC_1010'),('PC_1011'),('PC_1012'),('PC_1013'),('PC_1014'),('PC_1015')\r\n\r\nCREATE TABLE Work_Summary (\r\nInvoice_No INT NOT NULL IDENTITY PRIMARY KEY,\r\nInvoice_Date DATE,\r\nOrder_Quantity INT,\r\nJob_man_hours TIME,\r\nJob_Desc CHAR (40),\r\nLabour_Cost_Per_Hour DECIMAL(9,2),\r\nJob_Number INT REFERENCES Job_Details (Job_Number),\r\nProduct_Code CHAR (8) REFERENCES Product (Product_Code),\r\nTotal_Cost DECIMAL (9,2)\r\n)\r\n\r\n\r\n\r\nSET IDENTITY_INSERT Work_Summary ON\r\n\r\n\r\nINSERT INTO Work_Summary \r\n(Invoice_No,Invoice_Date,Order_Quantity,Job_man_hours,Job_Desc,Labour_Cost_Per_Hour,Job_Number,Product_Code)\r\nVALUES (1,'2017\/05\/18',2,'1:20:00','Description',100.00,1,'PC_1001'),\r\n (2,'2017\/05\/18',6,'2:30:00','Description',75.00,2,'PC_1002'),\r\n (3,'2017\/05\/18',7,'3:10:00','Description',50.00,3,'PC_1003'),\r\n (4,'2017\/05\/18',1,'4:35:00','Description',20.00,4,'PC_1004'),\r\n (5,'2017\/05\/18',9,'5:50:00','Description',15.00,5,'PC_1005'),\r\n (6,'2017\/05\/18',11,'6:30:00','Description',10.00,6,'PC_1006'),\r\n (7,'2017\/05\/18',1,'2:00:10','Description',18.95,7,'PC_1007'),\r\n (8,'2017\/05\/18',6,'2:30:50','Description',19.99,8,'PC_1008'),\r\n (9,'2017\/05\/18',8,'3:00:25','Description',40.00,9,'PC_1009'),\r\n (10,'2017\/05\/18',9,'1:30:18','Description',30.00,10,'PC_1010'),\r\n (11,'2017\/05\/18',14,'2:00:10','Description',20.95,11,'PC_1011'),\r\n (12,'2017\/05\/18',3,'2:30:11','Description',9.99,12,'PC_1012'),\r\n (13,'2017\/05\/18',6,'3:00:45','Desription',199.99,13,'PC_1013'),\r\n (14,'2017\/05\/18',8,'3:30:34','Description',200.00,14,'PC_1014'),\r\n (15,'2017\/05\/18',9,'4:00:54','Description',500.00,15,'PC_1015')\r\n\r\n\t\t\t\r\nSET IDENTITY_INSERT Work_Summary OFF\r\n\r\nupdate Work_Summary set Total_Cost = Labour_Cost_Per_Hour * ((CAST(DATEPART(hh, Job_man_hours) AS float) +\r\n\t\tCAST(DATEPART(mi, Job_man_hours) AS float) \/ 60 +\r\n\t\tCAST(DATEPART(ss, Job_man_hours) AS float) \/ 3600)) where invoice_no in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)\r\n\r\nselect * from Work_Summary where invoice_no in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Conversion from Time to Decimal IF OBJECT_ID(‘Work_Summary’) IS NOT NULL DROP TABLE Work_Summary IF OBJECT_ID(‘Job_Details’) IS NOT NULL DROP TABLE Job_Details IF OBJECT_ID(‘Product’) IS NOT NULL DROP TABLE Product CREATE TABLE Job_Details ( Job_Number int PRIMARY KEY ) insert into Job_Details (Job_Number) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15) CREATE TABLE Product ( Product_Code char(8) PRIMARY KEY ) insert into … Continue reading “SQL Convert Time to Decimal”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-72","post","type-post","status-publish","format-standard","hentry","category-t-sql"],"_links":{"self":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/72","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/comments?post=72"}],"version-history":[{"count":1,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/72\/revisions"}],"predecessor-version":[{"id":73,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/posts\/72\/revisions\/73"}],"wp:attachment":[{"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/media?parent=72"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/categories?post=72"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.polysyncronism.com\/wordpress\/wp-json\/wp\/v2\/tags?post=72"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}