troubleshooting Question

SQL Query Grouping Data An a Sum Total

Avatar of pepps11976
pepps11976 asked on
Microsoft SQL Server 2008
25 Comments1 Solution229 ViewsLast Modified:
Hi Everyone

I have the following Code
SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) 
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.itran.it_doc, dbo.ihead.ih_orddate, dbo.ssale.ss_acode, 
                      LEFT(dbo.itran.it_anal, 3) AS group_anal, dbo.itran.it_anal, dbo.ssale.ss_adesc, dbo.sname.sn_account, dbo.ihead.ih_doc AS iheaddocnumber, 
                      dbo.[Anal Code].ss_prod
FROM         dbo.itran LEFT OUTER JOIN
                      dbo.ssale INNER JOIN
                      dbo.[Anal Code] ON dbo.ssale.ss_acode = dbo.[Anal Code].ss_acode ON dbo.itran.it_anal = dbo.ssale.ss_acode RIGHT OUTER JOIN
                      dbo.ihead INNER JOIN
                      dbo.slook ON dbo.ihead.ih_terrtry = dbo.slook.lk_code LEFT OUTER JOIN
                      dbo.sname ON dbo.ihead.ih_account = dbo.sname.sn_account ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.itran.it_anal <= 'ZZZ') AND (dbo.itran.it_doc <= 'WOR') AND (dbo.ihead.ih_sorder <> ' ') AND (dbo.ssale.ss_acode IS NOT NULL)
ORDER BY dbo.ihead.ih_account

what it does is tell me all orders that have been placed on the system and gives me the GBP amount (it_total_gbp) for that order, what i would like to do is group the data first by the Year (ih_orddate) then by the Product (ss_prod) then i would like the Total GBP Value (it_total_gbp) for the years. Then after i have done that i am assuming i could add another column where i could place the forcasted amount in for each product for the comming financial year?

Could somebody help me to acheive this?

John
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 25 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 25 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros