Many thanks for the response ChrisM,

What does your dbo.IF_Calendar function look like? I've tried replacing it with a join to my calendar table but it generates an error when...

Apologies,

Below is a small script to create the data 🙂

CREATE TABLE [dbo].[Salesman](

[SalesmanID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[Name] VARCHAR(50) NULL

CONSTRAINT [PK_Salesman] PRIMARY KEY CLUSTERED

(

[SalesmanID] ASC

)WITH (PAD_INDEX = OFF,...

Many thanks Lynn, That's exactly what I needed. I'd never have come up with that.:-D

Sorry, I've not been particularly clear on this...

I want to pass in one or more customers together with a start date (or start month\year).

I want to return back for all...

Hi Sean,

Thanks for your input on this:-) I guess if I was storing it as dates then the day part would always be a dummy and set to 01...

Hi Sean,

I'm only going to insert the rows for the passed in customers and I'm only going to select the rows for the passed in customers. The example data posted...

Hi Thanks for your response.

I disagree on the desired results - there were already rows in the table for customers 1, 2 and 3. All I need to do is...

I'm thinking I need some sort of numbers table - I've created a table below to join to (I just can't figure out how to join to it correctly)...

CREATE TABLE...

Ok,

CREATE TABLE [dbo].[tblExpectedResults](

[intID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[intCustomer] [int] NULL,

[intMonth] [int] NULL,

[intYear] [int] NULL,

[monAmount] [money] NULL,

[NewRow] [int]

CONSTRAINT [PK_tblExpectedResults] PRIMARY KEY CLUSTERED

(

[intID] ASC

)WITH (PAD_INDEX = OFF,...

Sorry, I thought I had clearly described what I wanted... I need a row per month\year inserted where a row for a year\month combination doesn't already exist for the customer...:-)

Here is setup code...

CREATE TABLE [dbo].[tblInvoice](

[intID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[intCustomer] [int] NULL,

[intMonth] [int] NULL,

[intYear] [int] NULL,

[monAmount] [money] NULL,

CONSTRAINT [PK_tblInvoice] PRIMARY KEY CLUSTERED

(

[intID] ASC

)WITH (PAD_INDEX ...

Hi Lynn,

Thanks for your response...

Actually I use a tally table to split, I only use the code posted if the length of the passed in string is greater than 2...

The inserts are done from the front-end application in a loop up via a stored procedure which does an Insert one record at a time.

Dev (12/20/2011)

---

Charlottecb (12/20/2011)

---

Thank you Dev and GilMonster for the responses.

Since I don't know exactly what time of the day the majority of records will be loaded, I...

Thank you Dev and GilMonster for the responses.

Since I don't know exactly what time of the day the majority of records will be loaded, I was thinking of putting...