July 22, 2009 at 10:27 am
Hi,
I'm trying to pull out some data from our SQL database for Reporting Services. Basically, I need to see the total items someone has bought for the last 12 months. I've used grouping but as the dates are individual i can't group them but i need the date column in to filter on it. Here's my Query so far. Any help would be great.
SELECT LEFT(SLCustomerAccount.CustomerAccountNumber, 4) AS [Account No], SOPOrderReturnLine.ItemCode,
SUM(SOPInvoiceCreditLine.InvoiceCreditQuantity) AS Expr2, SOPInvoiceCredit.DocumentDate AS Expr1
FROM SLCustomerAccount INNER JOIN
SOPInvoiceCredit ON SLCustomerAccount.SLCustomerAccountID = SOPInvoiceCredit.CustomerID INNER JOIN
SOPInvoiceCreditLine ON SOPInvoiceCredit.SOPInvoiceCreditID = SOPInvoiceCreditLine.SOPInvoiceCreditID INNER JOIN
SOPOrderReturnLine ON SOPInvoiceCreditLine.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID
GROUP BY LEFT(SLCustomerAccount.CustomerAccountNumber, 4), SOPOrderReturnLine.ItemCode, SOPInvoiceCredit.DocumentDate
HAVING (SOPOrderReturnLine.ItemCode = @Code) AND (SOPInvoiceCredit.DocumentDate >= GETDATE() - 365)
ORDER BY [Account No]
Thanks!!
July 22, 2009 at 10:22 pm
Hi!
I think what you're asking is a T-SQL question. You'll probably get an error if you use HAVING without an aggregate expression. Why don't you put the conditions in a WHERE clause before the GROUP BY, instead?
July 23, 2009 at 1:20 am
Hi Nate,
sorry but I'm pretty new to T-SQL, you mean like this?
SELECT LEFT(SLCustomerAccount.CustomerAccountNumber, 4) AS [Account No], SOPOrderReturnLine.ItemCode,
SUM(SOPInvoiceCreditLine.InvoiceCreditQuantity) AS Expr2, SOPInvoiceCredit.DocumentDate AS Expr1
FROM SLCustomerAccount INNER JOIN
SOPInvoiceCredit ON SLCustomerAccount.SLCustomerAccountID = SOPInvoiceCredit.CustomerID INNER JOIN
SOPInvoiceCreditLine ON SOPInvoiceCredit.SOPInvoiceCreditID = SOPInvoiceCreditLine.SOPInvoiceCreditID INNER JOIN
SOPOrderReturnLine ON SOPInvoiceCreditLine.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID
WHERE (SOPOrderReturnLine.ItemCode = @Code) AND (SOPInvoiceCredit.DocumentDate >= GETDATE() - 365)
GROUP BY LEFT(SLCustomerAccount.CustomerAccountNumber, 4), SOPOrderReturnLine.ItemCode, SOPInvoiceCredit.DocumentDate
ORDER BY [Account No]
July 23, 2009 at 1:41 am
Yep, I think you'll have better luck that way.
July 23, 2009 at 1:47 am
Thanks, just tried it but got the same results. Please see example below;
ACCNO1CATNO11.0000009/09/2008 00:00:00
ACCNO1CATNO11.0000002/12/2008 00:00:00
ACCNO1 CATNO11.0000006/04/2009 00:00:00
ACCNO1 CATNO12.0000008/06/2009 00:00:00
Ideally i'd like to see
ACCNO1 CATNO1 5.00000 (not really bothered about the date to be honest, but i need the filter on it)
ACCNO2 CATNO1 ETC
You reckon I should post this to T-SQL?
Cheers
Andy
July 23, 2009 at 2:26 am
andrewgoodwin (7/23/2009)
not really bothered about the date
Hi Andy,
Then leave the date out of the items returned in your query, and keep it in the WHERE clause:
SELECT LEFT(SLCustomerAccount.CustomerAccountNumber, 4) AS [Account No]
, SOPOrderReturnLine.ItemCode
, SUM(SOPInvoiceCreditLine.InvoiceCreditQuantity) AS TotalCreditQuantity
FROM SLCustomerAccount
INNER JOIN SOPInvoiceCredit ON SLCustomerAccount.SLCustomerAccountID = SOPInvoiceCredit.CustomerID
INNER JOIN SOPInvoiceCreditLine ON SOPInvoiceCredit.SOPInvoiceCreditID = SOPInvoiceCreditLine.SOPInvoiceCreditID
INNER JOIN SOPOrderReturnLine ON SOPInvoiceCreditLine.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID
WHERE (SOPOrderReturnLine.ItemCode = @Code) AND (SOPInvoiceCredit.DocumentDate >= DATEADD(d,-365,GETDATE())
GROUP BY LEFT(SLCustomerAccount.CustomerAccountNumber, 4)
, SOPOrderReturnLine.ItemCode
ORDER BY [Account No]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply