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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cost Tracker in SSRS 08
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IMZSHAH
Starting Member

United Kingdom
12 Posts

Posted - 06/22/2015 :  07:45:09  Show Profile  Reply with Quote
Hi Guys,
I need some help and you guys have been so helpful in the past. I’m a novice but I have created an IT cost tracker within SSRS 2008. This IT tracker has a start and end date parameter, it also has a parameter relating to IT costs e.g.

Select gljrndtl.segvalue1 as Account,
Case Segvalue1
When '310100' Then 'Software license'
When '310200' Then 'Software Maintenance'
When '310300' Then 'Hardware Maintenance'
When '315100' Then 'IT Consumables Hardware'
When '315200' Then 'IT Services'
When '318100' Then 'Computer Equipment Rental'
When '330100' Then 'Telephone'
When '330300' Then 'Mobile'
When '330500' Then 'Internet'
END as 'IT Cost Type'
from Gljrndtl
Where Gljrndtl.segvalue1 like '31%' or Gljrndtl.segvalue1 like '33%'
Group by gljrndtl.segvalue1


This parameter limits the data specific to IT related costs which is correct but I want to see IT costs that appear on an invoice which may have costs other than IT associated with it e.g. I have an invoice, journal line 1 = IT costs, journal line 2 =travel costs, I want to see the net value. Can someone give me pointers on how I can achieve this?

This is the main query


SELECT GLJrnDtl.Company, Vendor.Name, Vendor.VendorID, APInvHed.InvoiceDate, APInvHed.DueDate, DATEDIFF(dd, APInvHed.DueDate, GETDATE()) AS [Days Over], GLJrnDtl.APInvoiceNum, GLJrnDtl.GLAccount, GLJrnDtl.JournalLine,
GLAcctDisp.AccountDesc, APInvHed.Description, GLJrnDtl.VendorNum, GLJrnDtl.CurrencyCode,
GLJrnDtl.BookCreditAmount * - 1 + GLJrnDtl.BookDebitAmount AS [GL Invoice Amount], GLJrnDtl.JEDate, APInvHed.ApplyDate, gljrndtl.segvalue1, gljrndtl.segvalue2, gljrndtl.segvalue3

FROM GLJrnDtl LEFT OUTER JOIN
GLAcctDisp ON GLJrnDtl.GLAccount = GLAcctDisp.GLAccount AND GLJrnDtl.Company = GLAcctDisp.Company RIGHT OUTER JOIN
Vendor ON Vendor.VendorNum = GLJrnDtl.VendorNum AND GLJrnDtl.Company = Vendor.Company RIGHT OUTER JOIN
APInvHed ON GLJrnDtl.VendorNum = APInvHed.VendorNum AND GLJrnDtl.APInvoiceNum = APInvHed.InvoiceNum AND APInvHed.Company = GLJrnDtl.Company AND
APInvHed.ApplyDate = GLJrnDtl.JEDate AND APInvHed.ApplyDate BETWEEN @startdate AND @enddate

WHERE (GLJrnDtl.JEDate BETWEEN @startdate AND @enddate) AND (APInvHed.ApplyDate = GLJrnDtl.JEDate) AND (GLJrnDtl.SourceModule = 'AP') AND
(GLJrnDtl.JournalCode = 'pj') AND (Vendor.Name NOT LIKE 'statpro%')
and (Gljrndtl.segvalue1 in (@Account))
and (Gljrndtl.company in (@Company))

GROUP BY GLJrnDtl.Company, Vendor.Name, Vendor.VendorID, GLJrnDtl.APInvoiceNum, GLJrnDtl.GLAccount, GLJrnDtl.JournalLine, GLAcctDisp.AccountDesc,
APInvHed.Description, GLJrnDtl.VendorNum, GLJrnDtl.BookCreditAmount * - 1 + GLJrnDtl.BookDebitAmount, GLJrnDtl.JEDate, APInvHed.ApplyDate,
APInvHed.DueDate, APInvHed.InvoiceDate, GLJrnDtl.CurrencyCode,gljrndtl.segvalue1, gljrndtl.segvalue2, gljrndtl.segvalue3

ORDER BY GLJrnDtl.Company, Vendor.Name, GLJrnDtl.APInvoiceNum, GLJrnDtl.JournalLine, Vendor.VendorID, GLJrnDtl.JEDate


Excuse me if I haven't listed the question appropriately, give me pointers and I'll fix up.

IMZSHAH
Starting Member

United Kingdom
12 Posts

Posted - 06/23/2015 :  06:43:48  Show Profile  Reply with Quote
Maybe this will help.



Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000