September 29, 2009 at 3:46 am
Im trying to generate a report that lists the consultants and their monthly totals.
Even if the monthly total is 0 (ie no records for that month) Id still like that month to appear in the report.
MNTH_TBL is a table with Column MNTH. rows 1 - 12
RSM_KPI is a table with KPI's that I'd like to total
SELECT ISNULL(RSM_KPI.USERNAME, '') AS "CONSULTANT", MNTH_TBL1.MNTH AS "MNTH"
FROM MNTH_TBL1
LEFT JOIN RSM_KPI ON MNTH_TBL1.MNTH = Cast(Month(RSM_KPI.RECORDED_DATETIME) as TinyInt)
AND Year(RSM_KPI.RECORDED_DATETIME) = '2009'
GROUP BY RSM_KPI.USERNAME, MNTH_TBL1.MNTH
ORDER BY RSM_KPI.USERNAME, MNTH_TBL1.MNTH
Currently Im trying to list the consultants and months 1 - 12, but it only returns data where there is data in the KPI table.
Any suggestions as to how I would do this?
Thanks for your help.
September 29, 2009 at 4:15 am
You need to create a list of all the month / years you are interested in then left outer join that to your table.
September 29, 2009 at 4:24 am
Dave Ballantyne (9/29/2009)
You need to create a list of all the month / years you are interested in then left outer join that to your table.
Hi Dave,
Thanks for your reply.
I thought that was what I was doing?
What am I doing wrong?
Don
September 29, 2009 at 4:27 am
Try this, note the "LEFT OUTER JOIN"
SELECT ISNULL(RSM_KPI.USERNAME, '') AS "CONSULTANT", MNTH_TBL1.MNTH AS "MNTH"
FROM MNTH_TBL1
LEFT OUTER JOIN RSM_KPI ON MNTH_TBL1.MNTH = Cast(Month(RSM_KPI.RECORDED_DATETIME) as TinyInt)
AND Year(RSM_KPI.RECORDED_DATETIME) = '2009'
GROUP BY RSM_KPI.USERNAME, MNTH_TBL1.MNTH
ORDER BY RSM_KPI.USERNAME, MNTH_TBL1.MNTH
September 29, 2009 at 4:32 am
Sorry ignore that , LEFT JOIN and LEFT OUTER JOIN are synonymous....
Can you provide a sample script ?
September 29, 2009 at 5:00 am
Dave Ballantyne (9/29/2009)
Sorry ignore that , LEFT JOIN and LEFT OUTER JOIN are synonymous....Can you provide a sample script ?
Hi Dave,
Could you clarify what sample script you need?
The script that I included in my first post returns:
CONSULTANT MNTH
consname 1
consname 2
consname 3
It only returns info where there is any in the RSM_KPI table.
September 29, 2009 at 5:04 am
I thought this is similar to the below requirement.
http://www.sqlservercentral.com/Forums/Topic794441-338-1.aspx
See if it helps and if not you can look at the format in which they have given the sample data and result. It would help to give you the query. Thanks.
---------------------------------------------------------------------------------
September 29, 2009 at 5:09 am
This works fine , yes ?
drop table #MNTH_TBL1
go
drop table #RSM_KPI
go
create table #MNTH_TBL1(
MNTH integer
)
insert into #MNTH_TBL1 values(1)
insert into #MNTH_TBL1 values(2)
insert into #MNTH_TBL1 values(3)
insert into #MNTH_TBL1 values(4)
create table #RSM_KPI
(
UserName varchar(10),
Recorded_DateTime datetime
)
insert into #RSM_KPI values('Dave','01jan2009')
insert into #RSM_KPI values('Dave','01feb2009')
go
SELECT ISNULL(RSM_KPI.USERNAME, '') AS "CONSULTANT", MNTH_TBL1.MNTH AS "MNTH"
FROM #MNTH_TBL1 MNTH_TBL1
LEFT OUTER JOIN #RSM_KPI RSM_KPI ON MNTH_TBL1.MNTH = Cast(Month(RSM_KPI.RECORDED_DATETIME) as TinyInt)
AND Year(RSM_KPI.RECORDED_DATETIME) = '2009'
GROUP BY RSM_KPI.USERNAME, MNTH_TBL1.MNTH
ORDER BY RSM_KPI.USERNAME, MNTH_TBL1.MNTH
So can you provide the insert statements for your data where it fails ?
September 29, 2009 at 5:49 am
Dave Ballantyne (9/29/2009)
This works fine , yes ?
drop table #MNTH_TBL1
go
drop table #RSM_KPI
go
create table #MNTH_TBL1(
MNTH integer
)
insert into #MNTH_TBL1 values(1)
insert into #MNTH_TBL1 values(2)
insert into #MNTH_TBL1 values(3)
insert into #MNTH_TBL1 values(4)
create table #RSM_KPI
(
UserName varchar(10),
Recorded_DateTime datetime
)
insert into #RSM_KPI values('Dave','01jan2009')
insert into #RSM_KPI values('Dave','01feb2009')
go
SELECT ISNULL(RSM_KPI.USERNAME, '') AS "CONSULTANT", MNTH_TBL1.MNTH AS "MNTH"
FROM #MNTH_TBL1 MNTH_TBL1
LEFT OUTER JOIN #RSM_KPI RSM_KPI ON MNTH_TBL1.MNTH = Cast(Month(RSM_KPI.RECORDED_DATETIME) as TinyInt)
AND Year(RSM_KPI.RECORDED_DATETIME) = '2009'
GROUP BY RSM_KPI.USERNAME, MNTH_TBL1.MNTH
ORDER BY RSM_KPI.USERNAME, MNTH_TBL1.MNTH
So can you provide the insert statements for your data where it fails ?
When I run the script against the tables that you provided, it works, but when I try run the script ( I take out the #MNT_TBL1 and #RSM_KPI refs) it doesnt include the months without info.
Heres the code to create the KPI table.
USE [TMP]
GO
/****** Object: Table [dbo].[RSM_KPI] Script Date: 09/29/2009 11:29:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RSM_KPI](
[RECID] [varchar](15) NOT NULL,
[KPI_ID] [tinyint] NOT NULL,
[RECORDED_DATETIME] [datetime] NOT NULL,
[EFFECTIVE_DATETIME] [datetime] NOT NULL,
[USERNAME] [varchar](8) NULL,
[JOB_RECID] [varchar](15) NULL,
[CANDIDATE_ACCOUNTNO] [varchar](20) NULL,
[WORKFLOW_STEP] [varchar](30) NULL,
[SOURCE_RECID] [varchar](15) NULL,
[VALUE] [money] NOT NULL,
[CLIENT_ACCOUNTNO] [varchar](20) NULL,
[CLIENT_CONTACT_RECID] [varchar](15) NULL,
[WORKFLOW_ID] [numeric](18, 0) NULL,
PRIMARY KEY CLUSTERED
(
[RECID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
I've attached an excel spreadsheet with a couple rows....
September 29, 2009 at 5:59 am
Can you post the same for the months table ?
Even with completely empty KPI table , you should still be getting all the rows in the months table.
September 29, 2009 at 6:17 am
Dave Ballantyne (9/29/2009)
Can you post the same for the months table ?Even with completely empty KPI table , you should still be getting all the rows in the months table.
Dave, thank you very much for your help.
I was being retarded.
For some reason I was expecting the data to appear as:
CONSULTANT MNTH
cons1 1
cons1 2
cons1 3
4
5
...
cons2 1
cons2 2
cons2 3
4
5
...
Where the empty consultant fields were the rows without data.
What was actually happening was that the all theempty consultant names were being grouped together.
So I was seeing:
CONSULTANT MNTH
4
5
cons1 1
cons1 2
cons1 3
cons2 1
cons2 2
cons2 3
I think I've got it now.
Thanks again for your time.
Don
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply