IT cost tracker

Question

IT cost tracker

imran.shah

Old Hand

Points: 364
More actions
June 22, 2015 at 6:13 am

#297995

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63434 More actions · Answer 1

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?

I would do this with a correlated subquery... something like this:

DECLARE @ProductID INT = 1;

SELECT od.ProductID

FROM Sales.OrderDetails od

WHERE od.ProductID != @ProductID

AND EXISTS (

SELECT 1

FROM Sales.OrderDetails od2

WHERE od2.OrderID = od.OrderID /*join to outer query*/

/* filter for IT Costs in the subquery (here) */

AND od2.ProductID = @ProductID)

imran.shah Old Hand Points: 364 More actions · Answer 2

Thanks for the prompt reply maybe I am doing something wrong as I said I am a novice. I tried to use the example you gave to no avail.

Declare @account INT=1;

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.company in (@Company))

and exists (select 1 from GLJrnDtl where SegValue1 like '3%' and JournalLine >= 1)

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

This doesn't work, the data with this query shows everything.

pietlinden SSC Guru Points: 63434 More actions · Answer 3

Imran,

Given that you're a novice and haven't posted much here before, it's a good idea that you read Jeff Moden's article Forum Etiquette: How to post data/code on a forum to get the best help[/url].

If you post your CREATE TABLE/INSERT T-SQL statements, helping you would be a LOT easier. (Read Jeff's article... it's great and easy to follow.)

You can use SSRS to do the summaries if you want. What is your output supposed to look like? (Is it a crosstab/matrix?) If you are doing using a matrix, then you don't need to do the totals/grouping in T-SQL, because SSRS will do that part for you.

Once you've provided that, it will be a lot easier to help you.

If you're not totally new to T-SQL, you might want to read Itzik Ben-Gan's book on T-SQL Querying. (I have the one for version 2008), Inside MS SQL Server 2008: T-SQL Querying. Sure it costs about $50, but well worth it.

Post back and someone can help you sort it out.

Pieter

imran.shah Old Hand Points: 364 More actions · Answer 4

I wasn't able to use the formatting directly from the forum but this is what I have achieved thus far.

pietlinden SSC Guru Points: 63434 More actions · Answer 5

This will return all APInvoiceNum values that contain at least one IT-related expense.

SELECT APInvoiceNum AS Invoice

FROM GLJrnDt1

WHERE GlJrnDt1.SegValue BETWEEN '310100' AND '330500'

/* You may not need these extra filters here */

AND SourceModule='ap'

AND JournalCode='pj'

AND JEDate>'2015/06/01'

So you use that as a subquery

SELECT Company

,JournalLine

,InvoiceDate

,Invoice

,SegValue1

FROM GLJrnDt1 jrn

-- subquery in the where clause. not the alias of the table GLJrnDt1

WHERE EXISTS (SELECT 1

FROM GLJrnDt1

WHERE Invoice = jrn.Invoice -- which gets used here.

AND SegValue1 BETWEEN '310100' AND '330500')

imran.shah Old Hand Points: 364 More actions · Answer 6

Thanks for getting back to me Pieter, I have tried and failed with the sub-query. I don't understand what you are referencing in the example. Invoice = jrn.Invoice. There is 1 table, the general ledger journal table (Gljrndtl) which shows invoices and what categories costs have fallen into, this is determined by the segvalue1 code.

Any other table e.g. APINVHED, shows purchase invoices but will not show where the costs have fallen, it will show an invoice on 1 row of data.

pietlinden SSC Guru Points: 63434 More actions · Answer 7

Here's one I did based on the database provided by Itzik Ben-Gan in his book. It's the same kind of Customers/Orders/Line Items database. What I'm doing is showing all the Orders and OrderDetails for any Orders containing @ChosenProductID (in my case, I picked 51... If I were a die-hard Douglas Adams fan, I would have chosen 42... At any rate, it's just a random product.)

SELECT so.CustID

, so.OrderDate

, so.OrderID

, od.ProductID

FROM Sales.Orders so INNER JOIN Sales.OrderDetails od

ON so.OrderID=od.OrderID

WHERE EXISTS (SELECT 1

FROM Sales.OrderDetails od2

WHERE ProductID=51

AND od2.OrderID=od.OrderID)

ORDER BY so.OrderID, od.ProductID;

The important part(s) to take note of are the different aliases od and od2 pointing at the same table, and then the join between the two instances of the same table in the EXISTS where clause.

The query inside the EXISTS subquery returns all OrderIDs that contain the ProductID specified by @ChosenProductID. Then the join from the inner query on od2 joins to od, so it filters the outer query, so I get ALL OrderHeader and OrderDetail records where the condition is true.

His database is here[/url],so you can see a simplified example. (Best i can do if you don't provide create table statements etc!) And that AdventureWorks thing is enough to give me a migraine. BTW, his book is GREAT. Absolutely worth buying if you're planning on learning T-SQL.