| Author |
 Topic  |
|
|
sqldev80
Yak Posting Veteran
68 Posts |
 Posted - 09/22/2006 : 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 - 09/22/2006 : 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 - 09/22/2006 : 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 - 09/22/2006 : 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
Flowing Fount of Yak Knowledge
USA
5501 Posts |
Posted - 09/22/2006 : 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 - 09/22/2006 : 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
Flowing Fount of Yak Knowledge
USA
5501 Posts |
Posted - 09/22/2006 : 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)
USA
7007 Posts |
Posted - 09/22/2006 : 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 - 09/22/2006 : 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)
USA
7007 Posts |
Posted - 09/23/2006 : 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 |
|
|
| |
Topic |
|
|
|
proc to get sales from last 12 mts - SOME DEBUGGIN
