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
 General SQL Server Forums
 New to SQL Server Programming
 Need help with Select Statement

Author  Topic 

jwells
Starting Member

17 Posts

Posted - 2013-06-27 : 10:41:04
My business uses Access 2010 on all my computers. I have a select statement that works on some computers and not others. The statement calculates totals for an invoice by summing the time in labor, labor cost by summing labor * rate, summing parts cost and miscellaneous cost. Calculates taxes.

On the computers where it doesn't work the result I get is something like:

WOMin = #NAME
WOLabor = #NAME
WOParts = 120.29
WOOther = 29.27
TaxParts = 7.28
TaxOther = 1.72

Here is the select statement:

SELECT (SELECT SUM(DATEDIFF(minute, Start, [WOTripsTimestamps].[RETURN])) FROM WOTripsTimestamps INNER JOIN dbo.WOTrips ON dbo.WOTripsTimestamps.WOTripID = dbo.WOTrips.ID WHERE WOID = 22931) AS WOMin,

Isnull((SELECT SUM(DATEDIFF(minute, WOTripsTimestamps.Start, [WOTripsTimestamps].[RETURN]) * (WOTripsTimestamps.CustRate + WOTripsTimestamps.CustRate * WOTripsTimestamps.Overtime * .5) / 60) FROM WOTripsTimestamps INNER JOIN dbo.WOTrips ON dbo.WOTripsTimestamps.WOTripID = dbo.WOTrips.ID WHERE WOID = 22931),0) AS WOLabor,

isnull((SELECT SUM(WOParts.Quantity * WOParts.Price) FROM WOParts INNER JOIN dbo.WOTrips ON dbo.WOParts.WOTripID = dbo.WOTrips.ID WHERE WOID = 22931),0) as WOParts, isnull((SELECT SUM(WOOtherCharges.Quantity * WOOtherCharges.Price) FROM dbo.WOOtherCharges INNER JOIN dbo.WOTrips ON dbo.WOOtherCharges.WOTripID = dbo.WOTrips.ID WHERE WOID = 22931),0) AS WOOther,

isnull((SELECT SUM(WOParts.Quantity * WOParts.Price * 0 * WOParts.taxable) FROM WOParts INNER JOIN dbo.WOTrips ON dbo.WOParts.WOTr
ipID = dbo.WOTrips.ID WHERE WOID = 22931),0) as TaxParts,

isnull((SELECT SUM(WOOtherCharges.Quantity * WOOtherCharges.Price * 0 * woothercharges.taxable) FROM dbo.WOOtherCharges INNER JOIN dbo.WOTrips ON dbo.WOOtherCharges.WOTripID = dbo.WOTrips.ID WHERE WOID = 22931),0) AS TaxOther

I suspect my select statement is in the wrong format and there may be some libraries on some computers that don't work with it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 11:53:21
Are you using Access or t-sql query? if former, better to post it in Access forum. This is sql server forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-06-27 : 13:55:56
Hi jwells,

As visakh16 pointed out, your would get better answers from forum dedicated to access.

I have tried to "disassemble" your sql and "assemble" it "my way". This is what I came up with (I have not syntax checked it, as I don't have access to an access database):

select t.ID
,t.WOMin
,t.WOLabor
,isnull(p.WOParts,0) as WOParts
,isnull(o.WOOther,0) as WOOther
,isnull(p.TaxParts,0) as TaxParts
,isnull(o.TaxOther,0) as TaxOther
from (select t.ID
,sum(datediff('n',tt.Start,tt.RETURN)) as WOMin
,sum(datediff('n',tt.Start,tt.RETURN)
*(tt.CustRate+tt.CustRate*tt.Overtime/2)
/60
) as WOLabor
from dbo.WOTrips as t
inner join dbo.WOTripsTimestamps tt
on tt.WOTripID=t.WOTrips.ID
where t.WOID=22931
group by t.ID
) as t
left outer join (select t.ID
,sum(p.Quantity*p.Price) as WOParts
,sum(p.Quantity*p.Price*0*p.taxable) as TaxParts
from dbo.WOTrips as t
inner join dbo.WOParts as p
on p.WOTripID=t.ID
where t.WOID=22931
group by t.ID
) as p
left outer join (select t.ID
,sum(o.Quantity*o.Price) as WOOther
,sum(o.Quantity*o.Price*0*o.taxable) as TaxOther
from dbo.WOTrips as t
inner join dbo.WOOtherCharges as o
on o.WOTripID=t.ID
where t.WOID=22931
group by t.ID
) as o

Hope this helps you in the right direction.
Go to Top of Page
   

- Advertisement -