SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 proc to get sales from last 12 mts - SOME DEBUGGIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqldev80
Yak Posting Veteran

68 Posts

Posted - 09/22/2006 :  15:55:24  Show Profile  Reply with Quote
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 - 09/22/2006 :  16:06:36  Show Profile  Reply with Quote
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


Go to Top of Page

sqldev80
Yak Posting Veteran

68 Posts

Posted - 09/22/2006 :  16:15:21  Show Profile  Reply with Quote
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
Go to Top of Page

sqldev80
Yak Posting Veteran

68 Posts

Posted - 09/22/2006 :  16:37:18  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 09/22/2006 :  17:00:19  Show Profile  Reply with Quote
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
Go to Top of Page

sqldev80
Yak Posting Veteran

68 Posts

Posted - 09/22/2006 :  20:05:52  Show Profile  Reply with Quote
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





Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 09/22/2006 :  21:36:24  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/22/2006 :  21:36:36  Show Profile  Reply with Quote
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
Go to Top of Page

sqldev80
Yak Posting Veteran

68 Posts

Posted - 09/22/2006 :  23:10:14  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/23/2006 :  15:54:18  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000