function - select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

function - select query

Author

Topic

akas
Starting Member

42 Posts

Posted - 2008-07-16 : 08:21:59

Hi,

I have one function in which i have to pass camp, startdate and enddate..and have to found totalquantity of sales by month/monthly

when i m passing date from sep 2006 - jul 2008 i m getting correct result..but when i m passing oct 2006-jul 2008 i m not getting oct 2006,nov 2006 result but it gives from dec 2006-jul2008..

CREATE function [dbo].[QuantityByMonth]
(@Camp varchar(50),
@startdate datetime,
@enddate datetime)
returns table
As
return
(
Select t.Period, t.PeriodInt, t.Camp,t.Itemnmbr, t.ItemTitle, sum(Totalquantity) as TotalQuantity
from (
select
REPLACE(RIGHT(CONVERT(VARCHAR(9), vs.date, 6), 6), '', ' ') AS Period,

RIGHT(CONVERT(VARCHAR(10), vs.date, 103), 7) as PeriodInt,

vs.camp,
vs.itemnmbr,
vs.ItemTitle,
sum(vs.quantity) as totalquantity

From victsalestab vs

Where vs.Camp = @Camp and (vs.date between @startdate and @enddate)

group by vs.itemnmbr,vs.camp, vs.date, vs.ItemTitle

) as t

group by t.Period, t.PeriodInt, t.Camp,t.Itemnmbr, t.ItemTitle

)

select * from QuantityByMonth('campd','9/21/2006','7/31/2008')
order by itemnmbr

Oct 06 10/2006 06-5840 Risk 126.00
Oct 07 10/2007 06-5840 Risk 2394.00
Nov 06 11/2006 06-5840 Risk 100.00
Nov 07 11/2007 06-5840 Risk 2394.00
..................................
Jul 08 07/2008 06-5840 Risk 5000.000

Oct 06 10/2006 06-5841 Essentials 101.00
Oct 07 10/2007 06-5841 Essentials 200.00
Nov 06 11/2006 06-5841 Essentials 10.00
Nov 07 11/2007 06-58401 Essentials 345.00
Dec 06 12/2006 06-5841 Essentials 400.00
..................................
Jul 08 07/2008 06-5841 Essentials 5000.000

but when i m
select * from QuantityByMonth('campd','11/21/2006','7/31/2008')
order by itemnmbr)
i m not getting that red lines..oct 2006,nov 2006..as there is totalquantity for that month...but oct 2007 and nov 2007 rows r getting..

can u tell me the suggetions what's wrong here?

thanks for ur help..

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-16 : 08:29:52

i'm not sure i understand...? you're passing a start date of 21st November 2006 and wondering why it doesn't return data for October 2006?

Em

akas
Starting Member

42 Posts

Posted - 2008-07-16 : 08:42:19

sorry!! typing mistake..

passing oct 2006 ...

select * from QuantityByMonth('campd','10/21/2006','7/31/2008')
order by itemnmbr

and wants oct 2006,nov2006 onwards..

i m getting oct 2006, nov 2006 for the first itemnmbr only not for all itemnmbrs...for rest of the itemnmbrs getting from dec 2006..which i wrong as i want for oct 2006,nov 2006 also..

can anybody have some idea?

thanks..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 09:33:30

quote:
Originally posted by akas

sorry!! typing mistake..

passing oct 2006 ...

select * from QuantityByMonth('campd','10/21/2006','7/31/2008')
order by itemnmbr

and wants oct 2006,nov2006 onwards..

i m getting oct 2006, nov 2006 for the first itemnmbr only not for all itemnmbrs...for rest of the itemnmbrs getting from dec 2006..which i wrong as i want for oct 2006,nov 2006 also..

can anybody have some idea?

thanks..

but you sample data shows data for itemnumber 06-5841 for Nov 2006 also. Can you explain with some sample data what's the discrepancy you're seeing?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources