Yearly sales report

pkfox

Hi all, is there a way to select records from two tables where the right hand side table may or may not have a matching record ? What I'm trying to do is produce a yearly report of sales per month per salesperson and if there are no entries for a given month I still want a record for the salesperson but showing a zero amount. I've looked at various *solutions* on the web but they all produce a null set which is not what I want. Null values for the sales is acceptable but I always want the salesperson name returned // Table 1 ( Salesperson ) Salesperson Pete Paul Mary // Table2 ( Sales ) Salesperson. Sale date. SaleAmount Pete. 01/01/2015. 1000 Pete. 01/01/2015. 2000 I would like the report to show Salesperson. Month. Sumofmonthsales Pete Jan. 3000 Pete. Feb. 0 etc... all through to December, and ditto for the rest of the salespersons. Hope this makes sense ( sorry about the formatting )

We can’t stop here, this is bat country - Hunter S Thompson RIP

Mycroft Holmes

Yes it is possible - almost anything is possible. This is one solution

SELECT Person,
S.Mth,
s.Yr,
s.Amount
FROM SalesPersonTable P
LEFT JOIN (SELECT SalesPersonID,
DATEPART(MONTH, SalseDate) Mth,
DATEPART(YEAR, salesDate) Yr,
SUM(amount) Amount
FROM Sales
WHERE DATEPART(YEAR, salesDate) = 2014
GROUP BY SalesPersonID,
DATEPART(MONTH, SalseDate),
DATEPART(YEAR, salesDate)) S ON S.SalesPersonID = P.SalesPersonID

Never underestimate the power of human stupidity RAH

pkfox

Hi there and thank you for your time, please accept my apologies for the misinformation I was typing from memory on the train, the actual data structures are as follows , as you can see the first table can have many records for owner_id and many records for the same month

Table 1
owner_id close_date opp_id
Peter 2014-01-02 00:00:00.000 1
Peter 2014-01-02 00:00:00.000 2
Peter 2014-02-04 00:00:00.000 3
Peter 2014-02-04 00:00:00.000 4
Peter 2014-03-06 00:00:00.000 5
Peter 2014-03-06 00:00:00.000 6
Peter 2014-04-08 00:00:00.000 7
Paul 2014-01-02 00:00:00.000 8
Paul 2014-01-02 00:00:00.000 9
Paul 2014-02-02 00:00:00.000 10
Paul 2014-02-02 00:00:00.000 11
Paul 2014-03-02 00:00:00.000 12
Paul 2014-04-02 00:00:00.000 13
Paul 2014-04-02 00:00:00.000 14
Mary 2014-06-01 00:00:00.000 15

Table 2
opp_id amtfinanced
1 2000.00
2 12000.00
3 12000.00
4 13000.00
5 14000.00
6 15000.00
7 16000.00
8 12000.00
9 13000.00
10 14000.00
11 15000.00
12 16000.00
13 17000.00
14 18000.00
15 1000000.00
17 2000000.00

is this still doable ?

We can’t stop here, this is bat country - Hunter S Thompson RIP

Umer Akram

you can do this with a simple calendar table. in which you can have all the dates which you required. something like this Note: I have used variable table for the demonstration purpose.

Declare @CalendarTable table
(
[DateKey] [int] NOT NULL,
[FullDateAlternateKey] [date] NOT NULL,
[DayNumberOfWeek] [tinyint] NOT NULL,
[EnglishDayNameOfWeek] [nvarchar](10) NULL,
[DayNumberOfMonth] [tinyint] NOT NULL,
[DayNumberOfYear] [smallint] NOT NULL,
[WeekNumberOfYear] [tinyint] NULL,
[EnglishMonthName] [nvarchar](10) NULL,
[MonthNumberOfYear] [tinyint] NOT NULL,
[CalendarQuarter] [tinyint] NOT NULL,
[CalendarYear] [smallint] NOT NULL,
[CalendarSemester] [tinyint] NULL
)

-- Populate the table with a TALLY table approach.
Declare @StartDate datetime
Declare @EndDate datetime

Select @StartDate = '20140101',
@EndDate = '20141231'

;WITH cTally
AS
(
select top 1100 ROW_NUMBER() Over (order by (select null)) - 1 as num
from syscolumns c
cross join syscolumns cc
)

INSERT INTO @CalendarTable(DateKey, FullDateAlternateKey, DayNumberOfWeek,
EnglishDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear,
WeekNumberOfYear, EnglishMonthName, MonthNumberOfYear,
CalendarQuarter, CalendarYear)
SELECT
CONVERT(INT, CONVERT(nvarchar(12), DATEADD(dd,num,@StartDate), 112)),
DATEADD(dd,num,@StartDate),
DATEPART(dw, DATEADD(dd,num,@StartDate)),
DATENAME(dw, DATEADD(dd,num,@StartDate)),
DATEPART(dd, DATEADD(dd,num,@StartDate)),
DATEPART(dayofyear, DATEADD(dd,num,@StartDate)),
DATEPART(week, DATEADD(dd,num,@StartDate)),
DATENAME(MONTH, DATEADD(dd,num,@StartDate)),
DATEPART(mm, DATEADD(dd,num,@StartDate)),
DATEPART(qq, DATEADD(dd,num,@StartDate)),
DATEPART(yyyy, DATEADD(dd,num,@StartDate))
from cTally
Where DATEADD(dd,num,@StartDate) <= @EndDate

this is how you will use this. Let get your sample data in a proper format for better readability.

Declare @Table1 table
(
owner_id varchar(10),
close_date datetime,
opp_id int
)

INSERT INTO @Table1
(
owner_id,
close_date,
opp_id
)

select 'Peter' , '2014-01-02 00:00:00.000', 1 union all
select 'Peter' , '2014-01-02 00:00:00.000', 2 union all
select 'Peter' , '2014-02-04 00:00:00.000', 3 union all
select 'Peter' , '2014-02-04 00:00:00.000', 4 union all
select 'Peter' , '2014-03-06 00:00:00.000', 5 union all
select 'Peter' , '2014-03-06 00:00:00.000', 6 union all
select 'Peter' ,

pkfox

Impressive thanks I'll have a try when I get back to work

We can’t stop here, this is bat country - Hunter S Thompson RIP

pkfox

Hi there I tried your solution and it worked perfectly - thanks very much.

We can’t stop here, this is bat country - Hunter S Thompson RIP

Umer Akram

Great :)