proc to get sales from last 12 mts - SOME DEBUGGIN

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 2000 Forums

SQL Server Development (2000)

proc to get sales from last 12 mts - SOME DEBUGGIN

Author

Topic

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-22 : 15:55:24

I need a query to find sales by month of a sales person
This needs some debugging as you will see from the result

Also I need to break the count if the
startmonth and start year exceeds getdate()

something like

IF @startyear >= datepart(year, getdate())and @startmonth >= datepart(month, getdate())

BREAK
ELSE
CONTINUE

BUT THIS IS NOT WORKING FOR SOME REASON

so i have 2 tables

declare @seller table (seller_id int, seller_name varchar(20))
insert @seller
values (1, 'James') union all
values (2, 'SHAWN') union all
values (2, 'TINA')

---and other table with sales data

declare @sales table (seller_id int, saledate datetime, price month)
insert @sales
values (1, '2/4/2006', 200) union all
values (1, '4/4/2006', 200) union all
values (1, '6/4/2006', 200) union all
values (2, '11/4/2005',200) union all
values (3, '3/4/2006', 100) union all
values (2, '8/4/2006', 300) union all
values (3, '5/4/2006', 100)

drop table #seller

go

drop table #sales

go

create table #seller (seller_id int, seller_name varchar(20))
insert #seller
select 1, 'James' union all
select 2, 'SHAWN' union all
select 3, 'TINA' union all
select 4, 'CINDI'

---and other table with sales data

create table #sales (seller_id int, saledate datetime, price money)
insert #sales
select 1, '2/4/2006', 200 union all
select 1, '4/4/2006', 200 union all
select 1, '6/4/2006', 200 union all
select 2, '11/4/2005',200 union all
select 3, '3/4/2006', 100 union all
select 2, '8/4/2006', 300 union all
select 3, '5/4/2006', 100 union all
select 4, '3/5/1999', 99999

GO

alter proc salesreport @startmonth int , @startyear int

as

declare @count int

select @count = 1

while @count <=12

BEGIN

Select
se.seller_name,
convert (varchar(20), @startmonth) +'/'+convert(varchar(20) ,@startyear) as 'Month',
sum(case when sa.saledate is not null and @startmonth = datepart(month, sa.saledate)
and @startyear = datepart(year, sa.saledate) then 1 else 0 end)[TOTAL SALES],
sum(case when sa.saledate is not null and @startmonth = datepart(month, sa.saledate)
and @startyear = datepart(year, sa.saledate)then 1 else 0 end)[TOTAL AMOUNT]

From
#seller se,
#sales sa

WHERE

se.seller_id = sa.seller_id

Group BY
se.seller_name

select @count = @count +1

if @startmonth <=11

select @startmonth = @startmonth + 1
else select @startyear = @startyear + 1
select @startmonth = 1

/*
IF @startyear >= datepart(year, getdate())and @startmonth >= datepart(month, getdate())

BREAK
ELSE
CONTINUE
*/

END

------ exec salesreport 5, 2006

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-22 : 16:06:36

OK I HAVE THE MONTHS PART WORKING CORRECTLY IN THIS ONE
BUT I NEED TO BREAK WHEN DATE EXCEEDS PRESENT DATE

alter proc salesreport @startmonth int , @startyear int

as

declare @count int

select @count = 1

while @count <=12

BEGIN

Select
se.seller_name,
convert (varchar(20), @startmonth) +'/'+convert(varchar(20) ,@startyear) as 'Month',
sum(case when sa.saledate is not null and @startmonth = datepart(month, sa.saledate)
and @startyear = datepart(year, sa.saledate) then 1 else 0 end)[TOTAL SALES],
sum(case when sa.saledate is not null and @startmonth = datepart(month, sa.saledate)
and @startyear = datepart(year, sa.saledate)then sa.price else 0 end) [TOTAL AMOUNT]

From
#seller se,
#sales sa

WHERE

se.seller_id = sa.seller_id

Group BY
se.seller_name

select @count = @count +1

select @startmonth = @startmonth + 1
if @startmonth > 12
BEGIN

select @startmonth = 1
select @startyear = @startyear + 1
END
else continue
/*
IF @startyear >= datepart(year, getdate())and @startmonth >= datepart(month, getdate())

BREAK
ELSE
CONTINUE
*/

END

-- exec salesreport 5, 2006

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-22 : 16:15:21

I HAVE FIXED THE QUERY TO LIMIT RESULTS TO CURRENT YEAR

BY ADDING THIS AT THE END

IF @startyear > datepart(year, getdate())
BREAK
ELSE
CONTINUE

BUT NOW I NEED TO COMPARE MONTH AS WELL HMMMMMMMMMMMMMMMM

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-22 : 16:37:18

THIS IS THE NEXT VERSION

NEED HELP DEBUGGING THIS ONE

create table #seller (seller_id int, seller_name varchar(20))
insert #seller
select 1, 'JAMES' union all
select 2, 'SHAWN' union all
select 3, 'TINA' union all
select 4, 'CINDI'

---and other table with sales data

create table #sales (seller_id int, saledate datetime, price money)
insert #sales
select 1, '2/4/2006', 200 union all
select 1, '4/4/2006', 200 union all
select 1, '6/4/2006', 200 union all
select 2, '11/4/2005',200 union all
select 3, '3/4/2006', 100 union all
select 2, '8/4/2006', 300 union all
select 3, '5/4/2006', 100 union all
select 4, '3/5/1999', 99999

GO

alter proc salesreport @startmonth int , @startyear int

as

declare @count int

select @count = 1 -- exec salesreport 2, 1999

while @count <=12

BEGIN

if @startyear > datepart(year, getdate()) ---should be able to check both month and year at the same time
PRINT 'PLEASE ENTER CURRENT YEAR'
BREAK

ELSE
continue

Select
se.seller_name SELLER ,
convert (varchar(20), @startmonth) +'/'+convert(varchar(20) ,@startyear) as 'MONTH',
sum(case when sa.saledate is not null and @startmonth = datepart(month, sa.saledate)
and @startyear = datepart(year, sa.saledate) then 1 else 0 end)[TOTAL SALES],
sum(case when sa.saledate is not null and @startmonth = datepart(month, sa.saledate)
and @startyear = datepart(year, sa.saledate)then sa.price else 0 end) [TOTAL AMOUNT]

From
#seller se, -- exec salesreport 1, 2007
#sales sa

WHERE

se.seller_id = sa.seller_id

Group BY
se.seller_name

select @count = @count +1

select @startmonth = @startmonth + 1

if @startmonth > 12

BEGIN

select @startmonth = 1
select @startyear = @startyear + 1
END
else continue

If @startmonth > datepart(month, getdate()) and @startyear < datepart(year, getdate())
continue
else
break

If @startmonth = datepart(month, getdate()) and @startyear = datepart(year, getdate())
BREAK
else
CONTINUE

/*IF @startyear > datepart(year, getdate())
BREAK
ELSE
CONTINUE*/

/*
IF @startyear >= datepart(year, getdate())and @startmonth >= datepart(month, getdate())

BREAK
ELSE
CONTINUE
*/

END

-- exec salesreport 5, 2006

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 17:00:19

Are you using Query Analyzer as your front end report writer?

Assuming you are sending these results to some other application where you can easily format the [Month] column how about something like this?


Select	se.seller_name
	,dateadd(month, datediff(month, 0, sa.saledate), 0) as [Month]
	,count(*)	as [TOTAL SALES]
	,sum(sa.price)	as [TOTAL AMOUNT] 
From	#seller se
join	#sales sa on sa.seller_id  = se.seller_id

Group BY se.seller_name
	,dateadd(month, datediff(month, 0, sa.saledate), 0)

--where	sa.saledate >= ?
--and	sa.saledate < ?

Be One with the Optimizer
TG

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-22 : 20:05:52

NO this does not solves the problem. The user selects the last 12 months for which he wants to see. I am enclosing my latest verion

I should be able to break it if the date is > current date
but its not breaking. I still get till then end of 2006 Just need to figure that one line out

drop table #seller
go
drop table #sales
go

create table #seller (seller_id int, seller_name varchar(20))
insert #seller
select 1, 'JAMES' union all
select 2, 'SHAWN' union all
select 3, 'TINA' union all
select 4, 'CINDI'

---and other table with sales data

create table #sales (seller_id int, saledate datetime, price money)
insert #sales
select 1, '2/4/2006', 200 union all
select 1, '4/4/2006', 200 union all
select 1, '6/4/2006', 200 union all
select 2, '11/4/2005',200 union all
select 3, '3/4/2006', 100 union all
select 2, '8/4/2006', 300 union all
select 3, '5/4/2006', 100 union all
select 4, '3/5/1999', 99999

GO

alter proc salesreport @startmonth int , @startyear int

as

declare @count int

select @count = 1 -- exec salesreport 2, 2007

while @count <=12

BEGIN

if @startyear > datepart(year, getdate()) ---should be able to check both month and year at the same time

BEGIN
PRINT 'PLEASE ENTER CURRENT YEAR'

BREAK
END

ELSE
IF @startyear <= datepart(year, getdate())

Select
se.seller_name SELLER ,
convert (varchar(20), @startmonth) +'/'+convert(varchar(20) ,@startyear) as 'MONTH',
sum(case when sa.saledate is not null and @startmonth = datepart(month, sa.saledate)
and @startyear = datepart(year, sa.saledate) then 1 else 0 end)[TOTAL SALES],
sum(case when sa.saledate is not null and @startmonth = datepart(month, sa.saledate)
and @startyear = datepart(year, sa.saledate)then sa.price else 0 end) [TOTAL AMOUNT]

From
#seller se, -- exec salesreport 6, 2006
#sales sa

WHERE

se.seller_id = sa.seller_id

Group BY
se.seller_name

select @count = @count +1

select @startmonth = @startmonth + 1

if @startmonth > 12

BEGIN

select @startmonth = 1
select @startyear = @startyear + 1
END
else continue

/*
If @startmonth > datepart(month, getdate()) and @startyear < datepart(year, getdate())
continue
else
break

If @startmonth = datepart(month, getdate()) and @startyear = datepart(year, getdate())
BREAK
else
CONTINUE
*/

IF (@startmonth > datepart(month, getdate()) and @startyear = datepart(year, getdate()) )
BREAK
ELSE
CONTINUE

/*
IF @startyear >= datepart(year, getdate())and @startmonth >= datepart(month, getdate())

BREAK
ELSE
CONTINUE
*/

END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-22 : 21:36:24

why do all the looping and date conversions? Doesn't one of these work for you?
Again, you can use the front end presentation software to format the [Month] column as MM\YYYY

I think the reason your code is not "breaking" after current date is because you are "continueing" before your test for current date. Maybe if you remove the "else continue"s the code will fall through to the loop's END block allowing all your checks to be evaluated.


set nocount on

create table #seller (seller_id int, seller_name varchar(20))
insert #seller 
select 1, 'JAMES' union all
select 2, 'SHAWN' union all
select 3, 'TINA' union all
select 4, 'CINDI' 

---and other table with sales data

create table #sales (seller_id int, saledate datetime, price money)
insert #sales
select 1, '2/4/2006', 200 union all
select 1, '4/4/2006', 200 union all
select 1, '6/4/2006', 200 union all
select 2, '11/4/2005',200 union all
select 3, '3/4/2006', 100 union all
select 2, '8/4/2006', 300 union all
select 3, '5/4/2006', 100 union all
select 4, '3/5/1999', 99999 


GO 
--this version just returns a row where there are sales

declare @startmonth int 
	,@startyear int 

select @startmonth = 5
	,@startyear = 2005

declare @startDate datetime
set @startDate = convert(datetime, convert(varchar,@startyear) 
	+ '-' + convert(varchar,@startmonth) + '/01')


Select	se.seller_name
	,dateadd(month, datediff(month, 0, sa.saledate), 0) as [Month]
	,count(*)	as [TOTAL SALES]
	,sum(sa.price)	as [TOTAL AMOUNT] 
From	#seller se
join	#sales sa on sa.seller_id  = se.seller_id

where	sa.saledate >= @startDate
and	sa.saledate < dateadd(year, 1, @startdate)
and	sa.saledate < getdate()

Group BY se.seller_name
	,dateadd(month, datediff(month, 0, sa.saledate), 0)


go

--This version returns a row for each month and each seller even with no sales

declare @startmonth int 
	,@startyear int 

select @startmonth = 5
	,@startyear = 2005

--local variable
declare @startDate datetime
set @startDate = convert(datetime, convert(varchar,@startyear) 
	+ '-' + convert(varchar,@startmonth) + '/01')

select	se.seller_name
	,[Month]
	,count(sa.price)	as [TOTAL SALES]
	,isNull(sum(sa.price),0)as [TOTAL AMOUNT] 
from	(
	select	dateadd(month, n, @startDate) [month]
		,seller_id
		,seller_name
	from	(
		select 0 n union select 1 union select 2 union select 3 union 
		select 5 union select 6 union select 7 union select 8 union
		select 9 union select 10 union select 11
		) numbers
	cross join #seller s
	) se
left join #sales sa
		on sa.seller_id = se.seller_id
		and sa.saledate >= se.[month]
		and sa.saledate < dateadd(month, 1, se.[month])
where	se.[month] >= @startDate
and	se.[month] < dateadd(year, 1, @startdate)
and	se.[month] < getdate()

Group BY se.seller_name
	,se.[month]

order by se.[month]
	,se.seller_name

drop table #seller
go
drop table #sales
go

OUTPUT:
seller_name          Month                                                  TOTAL SALES TOTAL AMOUNT          
-------------------- ------------------------------------------------------ ----------- --------------------- 
SHAWN                2005-11-01 00:00:00.000                                1           200.0000
JAMES                2006-02-01 00:00:00.000                                1           200.0000
TINA                 2006-03-01 00:00:00.000                                1           100.0000
JAMES                2006-04-01 00:00:00.000                                1           200.0000

seller_name          Month                                                  TOTAL SALES TOTAL AMOUNT          
-------------------- ------------------------------------------------------ ----------- --------------------- 
CINDI                2005-05-01 00:00:00.000                                0           .0000
JAMES                2005-05-01 00:00:00.000                                0           .0000
SHAWN                2005-05-01 00:00:00.000                                0           .0000
TINA                 2005-05-01 00:00:00.000                                0           .0000
CINDI                2005-06-01 00:00:00.000                                0           .0000
JAMES                2005-06-01 00:00:00.000                                0           .0000
SHAWN                2005-06-01 00:00:00.000                                0           .0000
TINA                 2005-06-01 00:00:00.000                                0           .0000
CINDI                2005-07-01 00:00:00.000                                0           .0000
JAMES                2005-07-01 00:00:00.000                                0           .0000
SHAWN                2005-07-01 00:00:00.000                                0           .0000
TINA                 2005-07-01 00:00:00.000                                0           .0000
CINDI                2005-08-01 00:00:00.000                                0           .0000
JAMES                2005-08-01 00:00:00.000                                0           .0000
SHAWN                2005-08-01 00:00:00.000                                0           .0000
TINA                 2005-08-01 00:00:00.000                                0           .0000
CINDI                2005-10-01 00:00:00.000                                0           .0000
JAMES                2005-10-01 00:00:00.000                                0           .0000
SHAWN                2005-10-01 00:00:00.000                                0           .0000
TINA                 2005-10-01 00:00:00.000                                0           .0000
CINDI                2005-11-01 00:00:00.000                                0           .0000
JAMES                2005-11-01 00:00:00.000                                0           .0000
SHAWN                2005-11-01 00:00:00.000                                1           200.0000
TINA                 2005-11-01 00:00:00.000                                0           .0000
CINDI                2005-12-01 00:00:00.000                                0           .0000
JAMES                2005-12-01 00:00:00.000                                0           .0000
SHAWN                2005-12-01 00:00:00.000                                0           .0000
TINA                 2005-12-01 00:00:00.000                                0           .0000
CINDI                2006-01-01 00:00:00.000                                0           .0000
JAMES                2006-01-01 00:00:00.000                                0           .0000
SHAWN                2006-01-01 00:00:00.000                                0           .0000
TINA                 2006-01-01 00:00:00.000                                0           .0000
CINDI                2006-02-01 00:00:00.000                                0           .0000
JAMES                2006-02-01 00:00:00.000                                1           200.0000
SHAWN                2006-02-01 00:00:00.000                                0           .0000
TINA                 2006-02-01 00:00:00.000                                0           .0000
CINDI                2006-03-01 00:00:00.000                                0           .0000
JAMES                2006-03-01 00:00:00.000                                0           .0000
SHAWN                2006-03-01 00:00:00.000                                0           .0000
TINA                 2006-03-01 00:00:00.000                                1           100.0000
CINDI                2006-04-01 00:00:00.000                                0           .0000
JAMES                2006-04-01 00:00:00.000                                1           200.0000
SHAWN                2006-04-01 00:00:00.000                                0           .0000
TINA                 2006-04-01 00:00:00.000                                0           .0000

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-22 : 21:36:36

I don't understand why you ignored TG's suggestion. His approach is far better than what you are trying to do.

If you are going to adk for help here, you should pay atention to the advise people give you and try the code they suggest.


declare @startmonth int
declare @startyear int
declare	 @startdate datetime

select  @startmonth = 12, @startyear = 2005

select   @startdate = 
	dateadd(month,((@startyear-1900)*12)+@startmonth-1,0)

set nocount on
declare @SELLER table (SELLER_ID int, SELLER_NAME varchar(20))
insert  @SELLER 
select 1, 'JAMES' union all
select 2, 'SHAWN' union all
select 3, 'TINA' union all
select 4, 'CINDI' 

declare @SALES table (SELLER_ID int, SALEDATE datetime, PRICE money)
insert @SALES
select 1, '2/4/2006', 200 union all
select 1, '4/4/2006', 200 union all
select 1, '6/4/2006', 200 union all
select 2, '11/4/2005',200 union all
select 3, '3/4/2006', 100 union all
select 2, '8/4/2006', 300 union all
select 3, '5/4/2006', 100 union all
select 4, '3/5/1999', 99999 

set nocount off

print 	'Select seles for 12 month starting ' +
	convert(varchar(10),@startdate,121)
print ''

select
	se.SELLER_NAME,
	YEAR = year( sa.SALEDATE ) ,
	MONTH = month( sa.SALEDATE ) ,
	count(*)	as [TOTAL SALES]  ,
	sum(sa.PRICE)	as [TOTAL AMOUNT] 
from
	@SELLER se
	join
	@sales sa
	on sa.SELLER_ID  = se.SELLER_ID
where
	sa.SALEDATE >=  @startdate and
	sa.SALEDATE <  dateadd(month,12, @startdate)
group by
	se.SELLER_NAME,
	year( sa.SALEDATE ) ,
	month( sa.SALEDATE ) 
order by
	se.SELLER_NAME,
	year( sa.SALEDATE ) ,
	month( sa.SALEDATE )

Results:


select seles for 12 month starting 2005-12-01
 
SELLER_NAME          YEAR        MONTH       TOTAL SALES TOTAL AMOUNT          
-------------------- ----------- ----------- ----------- --------------------- 
JAMES                2006        2           1           200.0000
JAMES                2006        4           1           200.0000
JAMES                2006        6           1           200.0000
SHAWN                2006        8           1           300.0000
TINA                 2006        3           1           100.0000
TINA                 2006        5           1           100.0000

(6 row(s) affected)

CODO ERGO SUM

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-22 : 23:10:14

THANK YOU BOTH TG AND MIKE

I just wanted to know if there is a way to make my code work as its more simple to understand also this code would later be migrated to ACCESS so the simpler its kept the easier it would be.

Is there a way to modify my code to show results only where sales are there. This is just the way business requirement is.

The report will be modified for presentation in ACCESS AS WELL

thanks for helping

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-23 : 15:54:18

quote:
Originally posted by sqldev80

THANK YOU BOTH TG AND MIKE

I just wanted to know if there is a way to make my code work as its more simple to understand also this code would later be migrated to ACCESS so the simpler its kept the easier it would be.

Is there a way to modify my code to show results only where sales are there. This is just the way business requirement is.

The report will be modified for presentation in ACCESS AS WELL

thanks for helping

Both TG and I have tried to offer you better solutions, and both of them actually work.

Your code is a real mess and even if you eventually get it working it will run slower.

You don't seem inclined to take any advise, so I will just say good luck.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources