Query with a calculation

Hello Friends

I have 3 tables

1) Product
Id, ShortName

2) IncomingStock
Id, ProductId, Quantity, InDate

3) OutGoingStock
Id, OutDate, ProductId, Quantity

I need to get the results like this
Product name, quantity in stock today

the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent)

Thank you for your time
Sara

Thanks to a dear friend of mine... i have got the query up and running

select p.ProductId,p.ShortName,SUM(t.qty) as QuantityOnStock
from Product p
left join
(select ProductId,sum(Quantity) as qty
from IncommingStock where InDate <=getdate()
group by ProductId
union all
select ProductId,sum(-Quantity) as qty
from OutGoingStock where OutDate<=getdate()
group by ProductId) t
on p.ProductId=t.ProductId
group by p.ProductId,p.ShortName

Query to count holidays

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.I did something similar in a previous life. I created a master calendar table with columns for the date plus flag (bit) columns for weekends and holidays (and a third flag as I recall called working day). I then created a user-defined function which would take two dates as an input and return the number of "work" days elapsed between the two.

Perhaps not elegant, but it did work.

The master calendar table was also useful for reporting purposes. In your case, you might want a front-end interface to edit the calendar and identify which days are working versus non-working.

Regards,

hmscott

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.|||Only way since holidays are uniqu to countries...

works well though