Help With Stored Proceedure Please

[John Webb]

Hope someone can help.

I am trying to write a stored proceedure to display sales activity by month
and then sum all the columbs.
The problem is that our sales year starts in April and end in March.
So far I have been able to get the sales info my using to sp's, one that say
month >3 and the other says <4. I pass in a year parameter, that for this
years figures would be 2003 for sp1 and 2004 for sp4.
I am sure there is a better way.
Below is a copy of one of my sp's.
Hope you are able to help.

John

ALTER PROCEDURE dbo.sp_SalesAna lFigures_P1
(@Year nvarchar(50),

@CCode varchar(50),

@SCode varchar(50),

@OType varchar(50))

AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te) AS
Month, SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS Sales,

SUM(dbo.Invoice ItemsCostQry.To talCost) AS Cost,
SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet -
dbo.InvoiceItem sCostQry.TotalC ost) AS Margin,

COUNT(dbo.Invoi ceHeaderTbl.Inv oiceNo) AS NoOfInvoices,
AVG(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS AverageValue

FROM dbo.InvoiceHead erTbl INNER JOIN

dbo.InvoiceItem sCostQry ON dbo.InvoiceHead erTbl.InvoiceNo =
dbo.InvoiceItem sCostQry.Invoic eNo

WHERE (DATEPART(yyyy, dbo.InvoiceHead erTbl.InvoiceDa te) = @Year) AND
(dbo.InvoiceHea derTbl.CompanyC ode LIKE @CCode) AND

(dbo.InvoiceHea derTbl.SalesMan Code LIKE @SCode) AND
(dbo.InvoiceHea derTbl.OrderTyp e LIKE @OType)

GROUP BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)

HAVING (DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te) > 3)

ORDER BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)

[louis nguyen]

Re: Help With Stored Proceedure Please

> I am trying to write a stored proceedure to display sales activity by month[color=blue]
> and then sum all the columbs.
> The problem is that our sales year starts in April and end in March.
> So far I have been able to get the sales info my using to sp's, one that say
> month >3 and the other says <4. I pass in a year parameter, that for this
> years figures would be 2003 for sp1 and 2004 for sp4.
> I am sure there is a better way.
> Below is a copy of one of my sp's.
> Hope you are able to help.[/color]

Hi John,

I think you're looking for this. Use DATEADD to subtract 3 months and
then compare the year against your parameter.

INVOICEDATE TRANSFORMED SALES_YEAR
Apr03 -> Jan03 -> 2003
Mar04 -> Dec03 -> 2003

WHERE Year(Dateadd(mo nth,-3,invoicedate)) = @year

However, the problem w/ this technique is the where clause will have
to scan/convert every row in your table to do the comparison. If you
have million+ rows, the query will be very slooooow. What I would do
is create a temp table with every day in the year and join it. I
would also use a permanent tally table.

-- Louis

[John Bell]

Re: Help With Stored Proceedure Please

Hi

One way to do this would be to have a calendar table with fiscal
months/years in it.

Alternatively you could also use case such as

SELECT DATEPART(mm,T.I nvoiceDate) AS Month,
SUM(T.InvoiceTo talNet) AS Sales,
SUM(C.TotalCost ) AS Cost,
SUM(T.InvoiceTo talNet - C.TotalCost) AS Margin,
COUNT(T.Invoice No) AS NoOfInvoices,
AVG(T.InvoiceTo talNet) AS AverageValue
FROM dbo.InvoiceHead erTbl T INNER JOIN
dbo.InvoiceItem sCostQry C ON T.InvoiceNo = C.InvoiceNo
WHERE CASE WHEN DATEPART(mm, T.InvoiceDate) > 3 THEN DATEPART(yyyy,
T.InvoiceDate) ELSE DATEPART(yyyy, T.InvoiceDate) -1 END = @Year
AND T.CompanyCode LIKE @CCode
AND T.SalesManCode LIKE @SCode
AND T.OrderType LIKE @OType
GROUP BY DATEPART(mm, T.InvoiceDate)
ORDER BY DATEPART(yyyy, T.InvoiceDate), DATEPART(mm, T.InvoiceDate)

John



"John Webb" <johnmartinwebb @hotmail.com> wrote in message
news:3fbe27ca$0 $13755$afc38c87 @news.easynet.c o.uk...[color=blue]
> Hope someone can help.
>
> I am trying to write a stored proceedure to display sales activity by[/color]
month[color=blue]
> and then sum all the columbs.
> The problem is that our sales year starts in April and end in March.
> So far I have been able to get the sales info my using to sp's, one that[/color]
say[color=blue]
> month >3 and the other says <4. I pass in a year parameter, that for this
> years figures would be 2003 for sp1 and 2004 for sp4.
> I am sure there is a better way.
> Below is a copy of one of my sp's.
> Hope you are able to help.
>
> John
>
> ALTER PROCEDURE dbo.sp_SalesAna lFigures_P1
> (@Year nvarchar(50),
>
> @CCode varchar(50),
>
> @SCode varchar(50),
>
> @OType varchar(50))
>
> AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)[/color]
AS[color=blue]
> Month, SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS Sales,
>
> SUM(dbo.Invoice ItemsCostQry.To talCost) AS Cost,
> SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet -
> dbo.InvoiceItem sCostQry.TotalC ost) AS Margin,
>
> COUNT(dbo.Invoi ceHeaderTbl.Inv oiceNo) AS NoOfInvoices,
> AVG(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS AverageValue
>
> FROM dbo.InvoiceHead erTbl INNER JOIN
>
> dbo.InvoiceItem sCostQry ON dbo.InvoiceHead erTbl.InvoiceNo =
> dbo.InvoiceItem sCostQry.Invoic eNo
>
> WHERE (DATEPART(yyyy, dbo.InvoiceHead erTbl.InvoiceDa te) = @Year) AND
> (dbo.InvoiceHea derTbl.CompanyC ode LIKE @CCode) AND
>
> (dbo.InvoiceHea derTbl.SalesMan Code LIKE @SCode) AND
> (dbo.InvoiceHea derTbl.OrderTyp e LIKE @OType)
>
> GROUP BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)
>
> HAVING (DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te) > 3)
>
> ORDER BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)
>
>[/color]

[John Webb]

Re: Help With Stored Proceedure Please

John / Louis,

Thank you both for your reply.

I have been able to achive what I wanted by using a Between statement on the
invoice date. ie Between 1-apr-@Year And 31-mar-@Year+1

Although this works, you both suggest a seperate table containing dates.

Could you please give me a little more information on what you mean, and
perhaps a reason why that would be better than the approach I currenlty
have.

Many thanks

John

"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:Ef7wb.9029 $Sn5.76522532@n ews-text.cableinet. net...[color=blue]
> Hi
>
> One way to do this would be to have a calendar table with fiscal
> months/years in it.
>
> Alternatively you could also use case such as
>
> SELECT DATEPART(mm,T.I nvoiceDate) AS Month,
> SUM(T.InvoiceTo talNet) AS Sales,
> SUM(C.TotalCost ) AS Cost,
> SUM(T.InvoiceTo talNet - C.TotalCost) AS Margin,
> COUNT(T.Invoice No) AS NoOfInvoices,
> AVG(T.InvoiceTo talNet) AS AverageValue
> FROM dbo.InvoiceHead erTbl T INNER JOIN
> dbo.InvoiceItem sCostQry C ON T.InvoiceNo = C.InvoiceNo
> WHERE CASE WHEN DATEPART(mm, T.InvoiceDate) > 3 THEN DATEPART(yyyy,
> T.InvoiceDate) ELSE DATEPART(yyyy, T.InvoiceDate) -1 END = @Year
> AND T.CompanyCode LIKE @CCode
> AND T.SalesManCode LIKE @SCode
> AND T.OrderType LIKE @OType
> GROUP BY DATEPART(mm, T.InvoiceDate)
> ORDER BY DATEPART(yyyy, T.InvoiceDate), DATEPART(mm, T.InvoiceDate)
>
> John
>
>
>
> "John Webb" <johnmartinwebb @hotmail.com> wrote in message
> news:3fbe27ca$0 $13755$afc38c87 @news.easynet.c o.uk...[color=green]
> > Hope someone can help.
> >
> > I am trying to write a stored proceedure to display sales activity by[/color]
> month[color=green]
> > and then sum all the columbs.
> > The problem is that our sales year starts in April and end in March.
> > So far I have been able to get the sales info my using to sp's, one that[/color]
> say[color=green]
> > month >3 and the other says <4. I pass in a year parameter, that for[/color][/color]
this[color=blue][color=green]
> > years figures would be 2003 for sp1 and 2004 for sp4.
> > I am sure there is a better way.
> > Below is a copy of one of my sp's.
> > Hope you are able to help.
> >
> > John
> >
> > ALTER PROCEDURE dbo.sp_SalesAna lFigures_P1
> > (@Year nvarchar(50),
> >
> > @CCode varchar(50),
> >
> > @SCode varchar(50),
> >
> > @OType varchar(50))
> >
> > AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)[/color]
> AS[color=green]
> > Month, SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS Sales,
> >
> > SUM(dbo.Invoice ItemsCostQry.To talCost) AS Cost,
> > SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet -
> > dbo.InvoiceItem sCostQry.TotalC ost) AS Margin,
> >
> > COUNT(dbo.Invoi ceHeaderTbl.Inv oiceNo) AS NoOfInvoices,
> > AVG(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS AverageValue
> >
> > FROM dbo.InvoiceHead erTbl INNER JOIN
> >
> > dbo.InvoiceItem sCostQry ON dbo.InvoiceHead erTbl.InvoiceNo =
> > dbo.InvoiceItem sCostQry.Invoic eNo
> >
> > WHERE (DATEPART(yyyy, dbo.InvoiceHead erTbl.InvoiceDa te) = @Year) AND
> > (dbo.InvoiceHea derTbl.CompanyC ode LIKE @CCode) AND
> >
> > (dbo.InvoiceHea derTbl.SalesMan Code LIKE @SCode) AND
> > (dbo.InvoiceHea derTbl.OrderTyp e LIKE @OType)
> >
> > GROUP BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)
> >
> > HAVING (DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te) > 3)
> >
> > ORDER BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)
> >
> >[/color]
>
>[/color]

[John Bell]

Re: Help With Stored Proceedure Please

Hi

Aaron did something similar here:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=us49FdLpDHA.2772%40TK2MSFTNGP12.phx.gbl

Add columns for the fiscal information and anything else you may want such
as holidays etc.

John

"John Webb" <johnmartinwebb @hotmail.com> wrote in message
news:3fc10d3d$0 $109$65c69314@m ercury.nildram. net...[color=blue]
> John / Louis,
>
> Thank you both for your reply.
>
> I have been able to achive what I wanted by using a Between statement on[/color]
the[color=blue]
> invoice date. ie Between 1-apr-@Year And 31-mar-@Year+1
>
> Although this works, you both suggest a seperate table containing dates.
>
> Could you please give me a little more information on what you mean, and
> perhaps a reason why that would be better than the approach I currenlty
> have.
>
> Many thanks
>
> John
>
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> news:Ef7wb.9029 $Sn5.76522532@n ews-text.cableinet. net...[color=green]
> > Hi
> >
> > One way to do this would be to have a calendar table with fiscal
> > months/years in it.
> >
> > Alternatively you could also use case such as
> >
> > SELECT DATEPART(mm,T.I nvoiceDate) AS Month,
> > SUM(T.InvoiceTo talNet) AS Sales,
> > SUM(C.TotalCost ) AS Cost,
> > SUM(T.InvoiceTo talNet - C.TotalCost) AS Margin,
> > COUNT(T.Invoice No) AS NoOfInvoices,
> > AVG(T.InvoiceTo talNet) AS AverageValue
> > FROM dbo.InvoiceHead erTbl T INNER JOIN
> > dbo.InvoiceItem sCostQry C ON T.InvoiceNo = C.InvoiceNo
> > WHERE CASE WHEN DATEPART(mm, T.InvoiceDate) > 3 THEN DATEPART(yyyy,
> > T.InvoiceDate) ELSE DATEPART(yyyy, T.InvoiceDate) -1 END = @Year
> > AND T.CompanyCode LIKE @CCode
> > AND T.SalesManCode LIKE @SCode
> > AND T.OrderType LIKE @OType
> > GROUP BY DATEPART(mm, T.InvoiceDate)
> > ORDER BY DATEPART(yyyy, T.InvoiceDate), DATEPART(mm, T.InvoiceDate)
> >
> > John
> >
> >
> >
> > "John Webb" <johnmartinwebb @hotmail.com> wrote in message
> > news:3fbe27ca$0 $13755$afc38c87 @news.easynet.c o.uk...[color=darkred]
> > > Hope someone can help.
> > >
> > > I am trying to write a stored proceedure to display sales activity by[/color]
> > month[color=darkred]
> > > and then sum all the columbs.
> > > The problem is that our sales year starts in April and end in March.
> > > So far I have been able to get the sales info my using to sp's, one[/color][/color][/color]
that[color=blue][color=green]
> > say[color=darkred]
> > > month >3 and the other says <4. I pass in a year parameter, that for[/color][/color]
> this[color=green][color=darkred]
> > > years figures would be 2003 for sp1 and 2004 for sp4.
> > > I am sure there is a better way.
> > > Below is a copy of one of my sp's.
> > > Hope you are able to help.
> > >
> > > John
> > >
> > > ALTER PROCEDURE dbo.sp_SalesAna lFigures_P1
> > > (@Year nvarchar(50),
> > >
> > > @CCode varchar(50),
> > >
> > > @SCode varchar(50),
> > >
> > > @OType varchar(50))
> > >
> > > AS SELECT TOP 100 PERCENT DATEPART(mm,[/color][/color][/color]
dbo.InvoiceHead erTbl.InvoiceDa te)[color=blue][color=green]
> > AS[color=darkred]
> > > Month, SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS Sales,
> > >
> > > SUM(dbo.Invoice ItemsCostQry.To talCost) AS Cost,
> > > SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet -
> > > dbo.InvoiceItem sCostQry.TotalC ost) AS Margin,
> > >
> > > COUNT(dbo.Invoi ceHeaderTbl.Inv oiceNo) AS NoOfInvoices,
> > > AVG(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS AverageValue
> > >
> > > FROM dbo.InvoiceHead erTbl INNER JOIN
> > >
> > > dbo.InvoiceItem sCostQry ON dbo.InvoiceHead erTbl.InvoiceNo =
> > > dbo.InvoiceItem sCostQry.Invoic eNo
> > >
> > > WHERE (DATEPART(yyyy, dbo.InvoiceHead erTbl.InvoiceDa te) = @Year) AND
> > > (dbo.InvoiceHea derTbl.CompanyC ode LIKE @CCode) AND
> > >
> > > (dbo.InvoiceHea derTbl.SalesMan Code LIKE @SCode) AND
> > > (dbo.InvoiceHea derTbl.OrderTyp e LIKE @OType)
> > >
> > > GROUP BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)
> > >
> > > HAVING (DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te) > 3)
> > >
> > > ORDER BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[louis nguyen]

Re: Help With Stored Proceedure Please

Hi John,

Actually, your where clause "where invoicedate Between 1-apr-@Year And
31-mar-@Year+1", should work. The where clause "where
year(dateadd(mo nth,-3,invoicedate)) =@year" would be very slow.

For example, suppose you want to look up the topic 'indexes' in a
reference book. Method A) look at the index pages in the back of the
book, which will give the page numbers to jump to. Method B) flip
through every page of the book. MSSQL attempts to automatically
create and use indexes, Method A. It will create an index on your col
invoicedate. If the where clause is on invoicedate, everyone is
happy. But if the where clause is on year(dateadd(.. .)), it is forced
to use method B.

The temp table I suggested would be overkill for your query. It would
be useful, if you want to want for example, records for every Monday
for the year 2003. Use a while loop or CELKO's tally table technique,
to create a temp table called #Mondays with every Monday. Then

select a.*
from myTable as a
join #mondays as b
on a.invoicedate = b.mondays

All of these comments are generalizations . Depending on the details
of your DB, behavior will wildly differ.

-- Louis

[John Webb]

Re: Help With Stored Proceedure Please

Many thanks to you both for your help and advise.

My sp nows runs much faster!

"John Webb" <johnmartinwebb @hotmail.com> wrote in message
news:3fbe27ca$0 $13755$afc38c87 @news.easynet.c o.uk...[color=blue]
> Hope someone can help.
>
> I am trying to write a stored proceedure to display sales activity by[/color]
month[color=blue]
> and then sum all the columbs.
> The problem is that our sales year starts in April and end in March.
> So far I have been able to get the sales info my using to sp's, one that[/color]
say[color=blue]
> month >3 and the other says <4. I pass in a year parameter, that for this
> years figures would be 2003 for sp1 and 2004 for sp4.
> I am sure there is a better way.
> Below is a copy of one of my sp's.
> Hope you are able to help.
>
> John
>
> ALTER PROCEDURE dbo.sp_SalesAna lFigures_P1
> (@Year nvarchar(50),
>
> @CCode varchar(50),
>
> @SCode varchar(50),
>
> @OType varchar(50))
>
> AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)[/color]
AS[color=blue]
> Month, SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS Sales,
>
> SUM(dbo.Invoice ItemsCostQry.To talCost) AS Cost,
> SUM(dbo.Invoice HeaderTbl.Invoi ceTotalNet -
> dbo.InvoiceItem sCostQry.TotalC ost) AS Margin,
>
> COUNT(dbo.Invoi ceHeaderTbl.Inv oiceNo) AS NoOfInvoices,
> AVG(dbo.Invoice HeaderTbl.Invoi ceTotalNet) AS AverageValue
>
> FROM dbo.InvoiceHead erTbl INNER JOIN
>
> dbo.InvoiceItem sCostQry ON dbo.InvoiceHead erTbl.InvoiceNo =
> dbo.InvoiceItem sCostQry.Invoic eNo
>
> WHERE (DATEPART(yyyy, dbo.InvoiceHead erTbl.InvoiceDa te) = @Year) AND
> (dbo.InvoiceHea derTbl.CompanyC ode LIKE @CCode) AND
>
> (dbo.InvoiceHea derTbl.SalesMan Code LIKE @SCode) AND
> (dbo.InvoiceHea derTbl.OrderTyp e LIKE @OType)
>
> GROUP BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)
>
> HAVING (DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te) > 3)
>
> ORDER BY DATEPART(mm, dbo.InvoiceHead erTbl.InvoiceDa te)
>
>[/color]