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
SELECT TOP (100) PERCENT
YEAR(ih_orddate) [Year],
SUM
(
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 [Total it_total_gbp],
SUM
(
CASE
WHEN dbo.itran.it_fcrate = 0 THEN
0
ELSE
(dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100)
END)
) AS [Total forecast]
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)
GROUP BY
YEAR(ih_orddate),
ss_prod
ORDER BY
dbo.ihead.ih_account
is what you want?
SELECT TOP (100) PERCENT YEAR(dbo.ihead.ih_orddate) AS Year, SUM(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 [Total it_total_gbp],
SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN 0 ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total forecast],
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)
GROUP BY YEAR(dbo.ihead.ih_orddate), dbo.[Anal Code].ss_prod, dbo.ihead.ih_account
ORDER BY dbo.ihead.ih_account
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.ihead.ih_account, dbo.ihead.ih_name, dbo.itran.it_doc,
dbo.ihead.ih_orddate, dbo.ihead.ih_sorder, dbo.ihead.ih_terrtry, dbo.slook.lk_desc, 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
FROM 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 LEFT OUTER JOIN
dbo.ssale RIGHT OUTER JOIN
dbo.itran ON dbo.ssale.ss_acode = dbo.itran.it_anal ON dbo.ihead.ih_doc = dbo.itran.it_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
SELECT TOP (100) PERCENT YEAR(dbo.ihead.ih_orddate) AS Year, SUM(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 [Total it_total_gbp],
SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN 0 ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total forecast],
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)
GROUP BY YEAR(dbo.ihead.ih_orddate), dbo.[Anal Code].ss_prod
ORDER BY year
but it shows the wrong values when i compare to a graph i created based on the Order value view which i mentioned above.SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN 0 ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total forecast]
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.ihead.ih_account, dbo.ihead.ih_name, dbo.itran.it_doc,
dbo.ihead.ih_orddate, dbo.ihead.ih_sorder, dbo.ihead.ih_terrtry, LEFT(dbo.itran.it_anal, 3) AS group_anal, dbo.itran.it_anal, dbo.ihead.ih_doc AS iheaddocnumber,
dbo.[Anal Code].ss_prod
FROM dbo.[Anal Code] INNER JOIN
dbo.itran ON dbo.[Anal Code].ss_acode = dbo.itran.it_anal RIGHT OUTER JOIN
dbo.ihead 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.[Anal Code].ss_prod = 'Components') AND (dbo.ihead.ih_orddate >= '07/01/2010') AND (dbo.ihead.ih_orddate <= '06/30/2011')
ORDER BY dbo.ihead.ih_account
SELECT TOP (100) PERCENT YEAR(dbo.ihead.ih_orddate) AS Year, SUM(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 [Total it_total_gbp], dbo.[Anal Code].ss_prod
FROM dbo.[Anal Code] INNER JOIN
dbo.itran ON dbo.[Anal Code].ss_acode = dbo.itran.it_anal RIGHT OUTER JOIN
dbo.ihead 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.[Anal Code].ss_prod = 'Components') AND (dbo.ihead.ih_orddate >= '07/01/2010') AND (dbo.ihead.ih_orddate <= '06/30/2011')
GROUP BY YEAR(dbo.ihead.ih_orddate), dbo.[Anal Code].ss_prod
ORDER BY dbo.[Anal Code].ss_prod
Open in new window