May 19, 2004 at 1:26 pm
I have a table that has the following information
StockCode, NetSalesValue, GlYear, GlPeriod
What I Need to do is create a query that would give me the following information.
Current Given information
glyear states for Fiscal Year = '2005'
glPeriod states for Fiscal Period = '1'
1. current year sales for each stockcode
ex "select stockcode, netsalesvalue, glyear, glperiod from ArTrnDetail where glyear = '2005' and glperiod = '1' group by stockcode order by stockcode"
2. Last year sales for each stockcode
ex "select stockcode, netsalesvalue, glyear, glperiod from ArTrnDetail where glyear = '2004' and glperiod = '1' group by stockcode order by stockcode"
3. current year last 3 months sales
4. last years last 3 months sales
5. current year last 6 months sales
6. last years last 6 months sales
7. current year last 12 months sales
8. last years last 12 months sales
9. current years sales To date
10. last years sales to date
May 19, 2004 at 2:01 pm
I forgot the table is created to hold individual sales so ever time an item is sold it created a new record i need to total up each stockcode
May 20, 2004 at 6:51 am
DECLARE @GlYear int, @GlPeriod int
SET @GlYear = 2005
SET @GlPeriod = 1
select stockcode,
sum(case when GlYear = @GlYear AND GlPeriod = @GlPeriod
then netsalesvalue else 0 end) as [current year sales],
sum(case when GlYear = @GlYear - 1 AND GlPeriod = @GlPeriod
then netsalesvalue else 0 end)
as [Last year sales],
sum(case when (GlYear = @GlYear AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 3)
OR (GlYear = @GlYear - 1 AND GlPeriod > (@GlPeriod + 12) - 3)
then netsalesvalue else 0 end)
as [current year last 3 months sales],
sum(case when (GlYear = @GlYear - 1 AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 3)
OR (GlYear = @GlYear - 2 AND GlPeriod > (@GlPeriod + 12) - 3)
then netsalesvalue else 0 end)
as [last years last 3 months sales],
sum(case when (GlYear = @GlYear AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 6)
OR (GlYear = @GlYear - 1 AND GlPeriod > (@GlPeriod + 12) - 6)
then netsalesvalue else 0 end)
as [current year last 6 months sales],
sum(case when (GlYear = @GlYear - 1 AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 6)
OR (GlYear = @GlYear - 2 AND GlPeriod > (@GlPeriod + 12) - 6)
then netsalesvalue else 0 end)
as [last years last 6 months sales],
sum(case when (GlYear = @GlYear AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 12)
OR (GlYear = @GlYear - 1 AND GlPeriod > (@GlPeriod + 12) - 12)
then netsalesvalue else 0 end)
as [current year last 12 months sales],
sum(case when (GlYear = @GlYear - 1 AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 12)
OR (GlYear = @GlYear - 2 AND GlPeriod > (@GlPeriod + 12) - 12)
then netsalesvalue else 0 end)
as [last years last 12 months sales],
sum(case when (GlYear = @GlYear AND GlPeriod <= @GlPeriod)
then netsalesvalue else 0 end)
as [current years sales To date],
sum(case when (GlYear = @GlYear - 1 AND GlPeriod <= @GlPeriod)
then netsalesvalue else 0 end)
as [last years sales to date]
from ArTrnDetail
group by stockcode
order by stockcode
Far away is close at hand in the images of elsewhere.
Anon.
May 21, 2004 at 11:01 am
Thank u this code worked great but i want to be able to put this into a store procdure
i want to have the user pass the glyear, glperiod and the where clause
ex (where stockcode >= '1100' and stockcode =< '9999')
having trouble getting the where clause to pass please help
also i dont want item that have a zero value for each colume not to show
May 24, 2004 at 2:13 am
CREATE PROCEDURE yourprocedurename
@GlYear int,
@GlPeriod int,
@stockcodefrom char(4),
@stockcodeto char(4)
AS
select stockcode,
[current year sales],
[Last year sales],
[current year last 3 months sales],
[last years last 3 months sales],
[current year last 6 months sales],
[last years last 6 months sales],
[current year last 12 months sales],
[last years last 12 months sales],
[current years sales To date],
[last years sales to date]
FROM (select stockcode,
sum(case when GlYear = @GlYear AND GlPeriod = @GlPeriod
then netsalesvalue else 0 end) as [current year sales],
sum(case when GlYear = @GlYear - 1 AND GlPeriod = @GlPeriod
then netsalesvalue else 0 end)
as [Last year sales],
sum(case when (GlYear = @GlYear AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 3)
OR (GlYear = @GlYear - 1 AND GlPeriod > (@GlPeriod + 12) - 3)
then netsalesvalue else 0 end)
as [current year last 3 months sales],
sum(case when (GlYear = @GlYear - 1 AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 3)
OR (GlYear = @GlYear - 2 AND GlPeriod > (@GlPeriod + 12) - 3)
then netsalesvalue else 0 end)
as [last years last 3 months sales],
sum(case when (GlYear = @GlYear AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 6)
OR (GlYear = @GlYear - 1 AND GlPeriod > (@GlPeriod + 12) - 6)
then netsalesvalue else 0 end)
as [current year last 6 months sales],
sum(case when (GlYear = @GlYear - 1 AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 6)
OR (GlYear = @GlYear - 2 AND GlPeriod > (@GlPeriod + 12) - 6)
then netsalesvalue else 0 end)
as [last years last 6 months sales],
sum(case when (GlYear = @GlYear AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 12)
OR (GlYear = @GlYear - 1 AND GlPeriod > (@GlPeriod + 12) - 12)
then netsalesvalue else 0 end)
as [current year last 12 months sales],
sum(case when (GlYear = @GlYear - 1 AND GlPeriod <= @GlPeriod AND GlPeriod > @GlPeriod - 12)
OR (GlYear = @GlYear - 2 AND GlPeriod > (@GlPeriod + 12) - 12)
then netsalesvalue else 0 end)
as [last years last 12 months sales],
sum(case when (GlYear = @GlYear AND GlPeriod <= @GlPeriod)
then netsalesvalue else 0 end)
as [current years sales To date],
sum(case when (GlYear = @GlYear - 1 AND GlPeriod <= @GlPeriod)
then netsalesvalue else 0 end)
as [last years sales to date]
from ArTrnDetail
where stockcode >= @stockcodefrom char(4),
and stockcode =< @stockcodeto
group by stockcode
) a
where not ([current year sales] = 0
and [Last year sales] = 0
and [current year last 3 months sales] = 0
and [last years last 3 months sales] = 0
and [current year last 6 months sales] = 0
and [last years last 6 months sales] = 0
and [current year last 12 months sales] = 0
and [last years last 12 months sales] = 0
and [current years sales To date] = 0
and [last years sales to date] = 0)
order by stockcode
GO
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply