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.
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 resultAlso I need to break the count if thestartmonth 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 REASONso i have 2 tablesdeclare @seller table (seller_id int, seller_name varchar(20))insert @seller values (1, 'James') union allvalues (2, 'SHAWN') union allvalues (2, 'TINA') ---and other table with sales datadeclare @sales table (seller_id int, saledate datetime, price month)insert @salesvalues (1, '2/4/2006', 200) union allvalues (1, '4/4/2006', 200) union allvalues (1, '6/4/2006', 200) union allvalues (2, '11/4/2005',200) union allvalues (3, '3/4/2006', 100) union allvalues (2, '8/4/2006', 300) union allvalues (3, '5/4/2006', 100) drop table #sellergodrop table #salesgocreate table #seller (seller_id int, seller_name varchar(20))insert #seller select 1, 'James' union allselect 2, 'SHAWN' union allselect 3, 'TINA' union allselect 4, 'CINDI' ---and other table with sales datacreate table #sales (seller_id int, saledate datetime, price money)insert #salesselect 1, '2/4/2006', 200 union allselect 1, '4/4/2006', 200 union allselect 1, '6/4/2006', 200 union allselect 2, '11/4/2005',200 union allselect 3, '3/4/2006', 100 union allselect 2, '8/4/2006', 300 union allselect 3, '5/4/2006', 100 union allselect 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 <=11select @startmonth = @startmonth + 1else 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 ONEBUT I NEED TO BREAK WHEN DATE EXCEEDS PRESENT DATEalter 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 + 1if @startmonth > 12BEGINselect @startmonth = 1select @startyear = @startyear + 1ENDelse 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 YEARBY ADDING THIS AT THE ENDIF @startyear > datepart(year, getdate())BREAKELSE CONTINUEBUT 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 VERSIONNEED HELP DEBUGGING THIS ONEcreate table #seller (seller_id int, seller_name varchar(20))insert #seller select 1, 'JAMES' union allselect 2, 'SHAWN' union allselect 3, 'TINA' union allselect 4, 'CINDI' ---and other table with sales datacreate table #sales (seller_id int, saledate datetime, price money)insert #salesselect 1, '2/4/2006', 200 union allselect 1, '4/4/2006', 200 union allselect 1, '6/4/2006', 200 union allselect 2, '11/4/2005',200 union allselect 3, '3/4/2006', 100 union allselect 2, '8/4/2006', 300 union allselect 3, '5/4/2006', 100 union allselect 4, '3/5/1999', 99999 GO alter proc salesreport @startmonth int , @startyear int as declare @count int select @count = 1 -- exec salesreport 2, 1999while @count <=12 BEGIN if @startyear > datepart(year, getdate()) ---should be able to check both month and year at the same timePRINT 'PLEASE ENTER CURRENT YEAR'BREAK ELSEcontinue 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 + 1if @startmonth > 12BEGINselect @startmonth = 1select @startyear = @startyear + 1ENDelse continueIf @startmonth > datepart(month, getdate()) and @startyear < datepart(year, getdate())continueelse breakIf @startmonth = datepart(month, getdate()) and @startyear = datepart(year, getdate())BREAKelse CONTINUE/*IF @startyear > datepart(year, getdate())BREAKELSE 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 sejoin #sales sa on sa.seller_id = se.seller_idGroup BY se.seller_name ,dateadd(month, datediff(month, 0, sa.saledate), 0)--where sa.saledate >= ?--and sa.saledate < ? Be One with the OptimizerTG |
|
|
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 verionI should be able to break it if the date is > current datebut its not breaking. I still get till then end of 2006 Just need to figure that one line outdrop table #sellergodrop table #salesgocreate table #seller (seller_id int, seller_name varchar(20))insert #seller select 1, 'JAMES' union allselect 2, 'SHAWN' union allselect 3, 'TINA' union allselect 4, 'CINDI' ---and other table with sales datacreate table #sales (seller_id int, saledate datetime, price money)insert #salesselect 1, '2/4/2006', 200 union allselect 1, '4/4/2006', 200 union allselect 1, '6/4/2006', 200 union allselect 2, '11/4/2005',200 union allselect 3, '3/4/2006', 100 union allselect 2, '8/4/2006', 300 union allselect 3, '5/4/2006', 100 union allselect 4, '3/5/1999', 99999 GO alter proc salesreport @startmonth int , @startyear int as declare @count int select @count = 1 -- exec salesreport 2, 2007while @count <=12 BEGIN if @startyear > datepart(year, getdate()) ---should be able to check both month and year at the same timeBEGINPRINT 'PLEASE ENTER CURRENT YEAR'BREAK ENDELSE 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_nameselect @count = @count +1 select @startmonth = @startmonth + 1if @startmonth > 12BEGINselect @startmonth = 1select @startyear = @startyear + 1ENDelse continue/*If @startmonth > datepart(month, getdate()) and @startyear < datepart(year, getdate())continueelse breakIf @startmonth = datepart(month, getdate()) and @startyear = datepart(year, getdate())BREAKelse CONTINUE*/IF (@startmonth > datepart(month, getdate()) and @startyear = datepart(year, getdate()) ) BREAKELSE 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\YYYYI 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 oncreate table #seller (seller_id int, seller_name varchar(20))insert #seller select 1, 'JAMES' union allselect 2, 'SHAWN' union allselect 3, 'TINA' union allselect 4, 'CINDI' ---and other table with sales datacreate table #sales (seller_id int, saledate datetime, price money)insert #salesselect 1, '2/4/2006', 200 union allselect 1, '4/4/2006', 200 union allselect 1, '6/4/2006', 200 union allselect 2, '11/4/2005',200 union allselect 3, '3/4/2006', 100 union allselect 2, '8/4/2006', 300 union allselect 3, '5/4/2006', 100 union allselect 4, '3/5/1999', 99999 GO --this version just returns a row where there are salesdeclare @startmonth int ,@startyear int select @startmonth = 5 ,@startyear = 2005declare @startDate datetimeset @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 sejoin #sales sa on sa.seller_id = se.seller_idwhere sa.saledate >= @startDateand 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 salesdeclare @startmonth int ,@startyear int select @startmonth = 5 ,@startyear = 2005--local variabledeclare @startDate datetimeset @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 ) seleft 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] >= @startDateand se.[month] < dateadd(year, 1, @startdate)and se.[month] < getdate()Group BY se.seller_name ,se.[month]order by se.[month] ,se.seller_namedrop table #sellergodrop table #salesgoOUTPUT:seller_name Month TOTAL SALES TOTAL AMOUNT -------------------- ------------------------------------------------------ ----------- --------------------- SHAWN 2005-11-01 00:00:00.000 1 200.0000JAMES 2006-02-01 00:00:00.000 1 200.0000TINA 2006-03-01 00:00:00.000 1 100.0000JAMES 2006-04-01 00:00:00.000 1 200.0000seller_name Month TOTAL SALES TOTAL AMOUNT -------------------- ------------------------------------------------------ ----------- --------------------- CINDI 2005-05-01 00:00:00.000 0 .0000JAMES 2005-05-01 00:00:00.000 0 .0000SHAWN 2005-05-01 00:00:00.000 0 .0000TINA 2005-05-01 00:00:00.000 0 .0000CINDI 2005-06-01 00:00:00.000 0 .0000JAMES 2005-06-01 00:00:00.000 0 .0000SHAWN 2005-06-01 00:00:00.000 0 .0000TINA 2005-06-01 00:00:00.000 0 .0000CINDI 2005-07-01 00:00:00.000 0 .0000JAMES 2005-07-01 00:00:00.000 0 .0000SHAWN 2005-07-01 00:00:00.000 0 .0000TINA 2005-07-01 00:00:00.000 0 .0000CINDI 2005-08-01 00:00:00.000 0 .0000JAMES 2005-08-01 00:00:00.000 0 .0000SHAWN 2005-08-01 00:00:00.000 0 .0000TINA 2005-08-01 00:00:00.000 0 .0000CINDI 2005-10-01 00:00:00.000 0 .0000JAMES 2005-10-01 00:00:00.000 0 .0000SHAWN 2005-10-01 00:00:00.000 0 .0000TINA 2005-10-01 00:00:00.000 0 .0000CINDI 2005-11-01 00:00:00.000 0 .0000JAMES 2005-11-01 00:00:00.000 0 .0000SHAWN 2005-11-01 00:00:00.000 1 200.0000TINA 2005-11-01 00:00:00.000 0 .0000CINDI 2005-12-01 00:00:00.000 0 .0000JAMES 2005-12-01 00:00:00.000 0 .0000SHAWN 2005-12-01 00:00:00.000 0 .0000TINA 2005-12-01 00:00:00.000 0 .0000CINDI 2006-01-01 00:00:00.000 0 .0000JAMES 2006-01-01 00:00:00.000 0 .0000SHAWN 2006-01-01 00:00:00.000 0 .0000TINA 2006-01-01 00:00:00.000 0 .0000CINDI 2006-02-01 00:00:00.000 0 .0000JAMES 2006-02-01 00:00:00.000 1 200.0000SHAWN 2006-02-01 00:00:00.000 0 .0000TINA 2006-02-01 00:00:00.000 0 .0000CINDI 2006-03-01 00:00:00.000 0 .0000JAMES 2006-03-01 00:00:00.000 0 .0000SHAWN 2006-03-01 00:00:00.000 0 .0000TINA 2006-03-01 00:00:00.000 1 100.0000CINDI 2006-04-01 00:00:00.000 0 .0000JAMES 2006-04-01 00:00:00.000 1 200.0000SHAWN 2006-04-01 00:00:00.000 0 .0000TINA 2006-04-01 00:00:00.000 0 .0000 Be One with the OptimizerTG |
|
|
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 intdeclare @startyear intdeclare @startdate datetimeselect @startmonth = 12, @startyear = 2005select @startdate = dateadd(month,((@startyear-1900)*12)+@startmonth-1,0)set nocount ondeclare @SELLER table (SELLER_ID int, SELLER_NAME varchar(20))insert @SELLER select 1, 'JAMES' union allselect 2, 'SHAWN' union allselect 3, 'TINA' union allselect 4, 'CINDI' declare @SALES table (SELLER_ID int, SALEDATE datetime, PRICE money)insert @SALESselect 1, '2/4/2006', 200 union allselect 1, '4/4/2006', 200 union allselect 1, '6/4/2006', 200 union allselect 2, '11/4/2005',200 union allselect 3, '3/4/2006', 100 union allselect 2, '8/4/2006', 300 union allselect 3, '5/4/2006', 100 union allselect 4, '3/5/1999', 99999 set nocount offprint '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_IDwhere 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.0000JAMES 2006 4 1 200.0000JAMES 2006 6 1 200.0000SHAWN 2006 8 1 300.0000TINA 2006 3 1 100.0000TINA 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 MIKEI 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 WELLthanks 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 MIKEI 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 WELLthanks 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 |
|
|
|
|
|
|
|