sum function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sum function

Author

Topic

elodea
Starting Member

5 Posts

Posted - 2015-02-13 : 01:26:18

Hi,

Need some help here.I've run below query and get the result.

Select sum(convert(decimal(18,1),(datediff(second, signontxn.txn_datetime, signofftxn.txn_datetime)/convert(decimal(18,1),60))))
as [Total Setup Time]
from dbo.WorkOrder wo
left join dbo.Transactions createtxn on createtxn.work_order_id = wo.work_order_id and createtxn.txn_desc = 'CREATED'
left join dbo.Transactions signontxn on signontxn.work_order_id = wo.work_order_id and signontxn.txn_desc = 'SIGNED ON'
left join dbo.Transactions signofftxn on signofftxn.work_order_id = wo.work_order_id and signofftxn.txn_desc = 'SIGNED OFF'
left join dbo.UDTData createshift on createshift.tablename='Shift' and
convert(varchar(5),createtxn.txn_datetime,8) between createshift.key1 and createshift.key2
left join dbo.UDTData signonshift on signonshift.tablename='Shift' and
convert(varchar(5),signontxn.txn_datetime,8) between signonshift.key1 and signonshift.key2
left join dbo.UDTData signoffshift on signoffshift.tablename='Shift' and
convert(varchar(5),signofftxn.txn_datetime,8) between signoffshift.key1 and signoffshift.key2
left join dbo.TCMS_Employee createname on createname.eno = createtxn.emp_id
left join dbo.TCMS_Employee signonname on signonname.eno = signontxn.emp_id
left join dbo.TCMS_Employee signoffname on signoffname.eno = signofftxn.emp_id
where wo.work_center = 'WIREBOND' AND wo.status <> 'TERMINATED'
and signontxn.txn_datetime is not null
and signofftxn.txn_datetime is not null
and createtxn.txn_datetime between convert(datetime, '2014-07-01') and convert(datetime, '2015-01-31')
group by wo.conv_desc

Result:

Total Setup Time
292479.2
814682.9
2466365.7
50336.5
294000.6

Now i want to get the sum of the Total Setup Time.How can i do that?Please help me.

Thanks.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-02-13 : 02:12:58

Select sum(convert(decimal(18,1),(datediff(second, signontxn.txn_datetime, signofftxn.txn_datetime)/convert(decimal(18,1),60))))
as [Total Setup Time]
from dbo.WorkOrder wo
left join dbo.Transactions createtxn on createtxn.work_order_id = wo.work_order_id and createtxn.txn_desc = 'CREATED'
left join dbo.Transactions signontxn on signontxn.work_order_id = wo.work_order_id and signontxn.txn_desc = 'SIGNED ON'
left join dbo.Transactions signofftxn on signofftxn.work_order_id = wo.work_order_id and signofftxn.txn_desc = 'SIGNED OFF'
left join dbo.UDTData createshift on createshift.tablename='Shift' and
convert(varchar(5),createtxn.txn_datetime,8) between createshift.key1 and createshift.key2
left join dbo.UDTData signonshift on signonshift.tablename='Shift' and
convert(varchar(5),signontxn.txn_datetime,8) between signonshift.key1 and signonshift.key2
left join dbo.UDTData signoffshift on signoffshift.tablename='Shift' and
convert(varchar(5),signofftxn.txn_datetime,8) between signoffshift.key1 and signoffshift.key2
left join dbo.TCMS_Employee createname on createname.eno = createtxn.emp_id
left join dbo.TCMS_Employee signonname on signonname.eno = signontxn.emp_id
left join dbo.TCMS_Employee signoffname on signoffname.eno = signofftxn.emp_id
where wo.work_center = 'WIREBOND' AND wo.status <> 'TERMINATED'
and signontxn.txn_datetime is not null
and signofftxn.txn_datetime is not null
and createtxn.txn_datetime between convert(datetime, '2014-07-01') and convert(datetime, '2015-01-31')
~~group by wo.conv_desc~~

elodea
Starting Member

5 Posts

Posted - 2015-02-16 : 02:57:54

Thank u!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources