July 7, 2011 at 4:17 am
I have a table of issued goods to various budget codes.
I can do a select on a month of all issued goods and group by budget code. I can also select the total cost of goods issued per month by budget code. What I need to do is something like below that itemises what was issued per budget code and then sums
Product Qty BudgetCode TotalCost
a 2 1234 90
b 3 1234 34
124
a 1 5678 45
45
c 20 4680 46
a 1 4680 45
91
Not sure where to start, some sort of nested while loop, any ideas?
Thanks
James
July 7, 2011 at 6:42 am
I have adapted the code to select only items from this month
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT Product, qty, CostCenter, Customer, TotalCost
FROM IssueStock
Where IssueDate between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
AND CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)
UNION ALL
SELECT NULL, NULL, CostCenter, Customer, SUM(TotalCost)
FROM IssueStock
Where IssueDate between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
AND CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)
Group BY CostCenter, Customer
What I would like is as per attachment report.png
What I get is as per attachment sqlreport.png
Any ideas?
Thanks
July 7, 2011 at 6:58 am
Hi
It is a report that will be generated each month by one of the stores clerks.
I will look into reporting.
James
July 8, 2011 at 4:18 am
Hi
Had a look at Report Builder and was exactly what was needed, after about 5 attempts got the exact layout I needed. Thanks
James
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply