July 31, 2012 at 3:44 pm
Charlottecb (7/31/2012)
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 shows all data in the table (the last column = 0 if it existed prior to running the SP and the last column = 1 after the SP has run).
Regarding your idea of not inserting but instead just selecting and including the missing rows on the fly... If you think it is easier to do in that way then can you enlighten me on it. I thought adding the missing rows first then doing the select would be easier, but if you can suggest a way to do this without initially inserting them then that would work for me.
I think that somewhat depends on what you want out of this. Do you want all existing PLUS the "artificial" rows? The artificial rows being generated only for customer ids being passed in?
I am a bit confused on what you want for output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2012 at 3:53 pm
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 passed in customers, all of their existing records.
However, where the customer(s) have missing records [checked against the passed in start date (or month\year) as a starting check point for a period 12 months added to the passed in date] I must have no gaps for (year\month). If there are gaps (e.g. months missing then I need dummy rows returned to fill the gaps. If there are gaps in the data outwith this 12 months I don't care, I still return them as is at the start or end depending if they occur before or after my 12 month window.
Sorry if this still sounds a bit unclear...
July 31, 2012 at 4:14 pm
Here is what I came up with based on what you provided. Feel free to modify as needed (and you will as I used the DelimitedSplit routine I have in my Sandbox database).
Hopefully, this gives you what you are requesting. It looks close to your expected results.
-- Create test tables and insert sample data
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 = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
INSERT INTO [dbo].[tblInvoice]
([intCustomer],[intMonth],[intYear],[monAmount])
VALUES (1, 6, 2011, 500.00),
(1, 7, 2012, 250.00),
(1, 8, 2012, 0.00),
(1, 10, 2012, 250.00),
(1, 1, 2013, 112.75),
(2, 1, 2011, 112.75),
(2, 4, 2012, 112.00),
(2, 7, 2012, 0.00),
(3, 5, 2012, 0.00),
(3, 6, 2012, 7.95);
GO
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, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
INSERT INTO [dbo].[tblExpectedResults]
([intCustomer],[intMonth],[intYear],[monAmount],[NewRow])
VALUES (1, 6, 2011, 500.00, 0),
(1, 7, 2012, 250.00, 0),
(1, 8, 2012, 0.00, 0),
(1, 10, 2012, 250.00, 0),
(1, 1, 2013, 112.75, 0),
(2, 1, 2011, 112.75, 0),
(2, 4, 2012, 112.00, 0),
(2, 7, 2012, 0.00, 0),
(3, 5, 2012, 0.00, 0),
(3, 6, 2012, 7.95, 0),
(1, 4, 2012, 0.00, 1),
(1, 5, 2012, 0.00, 1),
(1, 6, 2012, 0.00, 1),
(1, 9, 2012, 0.00, 1),
(1, 11, 2012, 0.00, 1),
(1, 12, 2012, 0.00, 1),
(1, 1, 2013, 0.00, 1),
(1, 2, 2013, 0.00, 1),
(1, 3, 2013, 0.00, 1),
(2, 5, 2012, 0.00, 1),
(2, 6, 2012, 0.00, 1),
(2, 8, 2012, 0.00, 1),
(2, 9, 2012, 0.00, 1),
(2, 10, 2012, 0.00, 1),
(2, 11, 2012, 0.00, 1),
(2, 12, 2012, 0.00, 1),
(2, 1, 2013, 0.00, 1),
(2, 2, 2013, 0.00, 1),
(2, 3, 2013, 0.00, 1);
GO
-- Create the stored procedure to return the requested data
create procedure dbo.GetCustomerInvoices(
@Customers varchar(8000),
@sYear int,
@sMonth int
)
as
begin
with
dTally(n) as (select 0 union all select row_number() over (order by (select null)) from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
Calendar(
CalendarDate
) as (
select
dateadd(mm,@sMonth - 1 + n,dateadd(yy,@sYear - 1900,0))
from
dTally
),
BaseData(
CustomerId,
InvoiceDate,
Amount
) as (
select
i.intCustomer,
dateadd(mm,i.intMonth - 1,dateadd(yy,i.intYear - 1900,0)),
i.monAmount
from
[dbo].[tblInvoice] i
inner join dbo.DelimitedSplit8K(@Customers,',') ds
on (i.intCustomer = cast(ds.Item as int))
where
dateadd(mm,i.intMonth - 1,dateadd(yy,i.intYear - 1900,0)) >= dateadd(mm,@sMonth - 1,dateadd(yy,@sYear - 1900,0)) and
dateadd(mm,i.intMonth - 1,dateadd(yy,i.intYear - 1900,0)) < dateadd(mm,@sMonth - 1,dateadd(yy,@sYear - 1900 + 1,0))
),
Customers(
CustomerId
) as (
select distinct
CustomerId
from
BaseData
)
select
cus.CustomerId,
cal.CalendarDate,
isnull(bd.Amount,0) Amount
from
Calendar cal
cross join Customers cus
left outer join BaseData bd
on (cal.CalendarDate = bd.InvoiceDate
and cus.CustomerId = bd.CustomerId)
order by
cus.CustomerId,
cal.CalendarDate;
end;
go
-- Test the procedure
exec dbo.GetCustomerInvoices '1,2,3',2012,6;
go
-- Drop the procedure (clean up the Sandbox)
drop procedure dbo.GetCustomerInvoices;
go
-- Drop the tables/data (clean up the Sandbox)
drop table [dbo].[tblInvoice];
drop table [dbo].[tblExpectedResults];
go
July 31, 2012 at 4:15 pm
Just thought of a change in the code. Be back in a few.
July 31, 2012 at 4:19 pm
Slight change to the 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 = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
INSERT INTO [dbo].[tblInvoice]
([intCustomer],[intMonth],[intYear],[monAmount])
VALUES (1, 6, 2011, 500.00),
(1, 7, 2012, 250.00),
(1, 8, 2012, 0.00),
(1, 10, 2012, 250.00),
(1, 1, 2013, 112.75),
(2, 1, 2011, 112.75),
(2, 4, 2012, 112.00),
(2, 7, 2012, 0.00),
(3, 5, 2012, 0.00),
(3, 6, 2012, 7.95);
GO
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, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
INSERT INTO [dbo].[tblExpectedResults]
([intCustomer],[intMonth],[intYear],[monAmount],[NewRow])
VALUES (1, 6, 2011, 500.00, 0),
(1, 7, 2012, 250.00, 0),
(1, 8, 2012, 0.00, 0),
(1, 10, 2012, 250.00, 0),
(1, 1, 2013, 112.75, 0),
(2, 1, 2011, 112.75, 0),
(2, 4, 2012, 112.00, 0),
(2, 7, 2012, 0.00, 0),
(3, 5, 2012, 0.00, 0),
(3, 6, 2012, 7.95, 0),
(1, 4, 2012, 0.00, 1),
(1, 5, 2012, 0.00, 1),
(1, 6, 2012, 0.00, 1),
(1, 9, 2012, 0.00, 1),
(1, 11, 2012, 0.00, 1),
(1, 12, 2012, 0.00, 1),
(1, 1, 2013, 0.00, 1),
(1, 2, 2013, 0.00, 1),
(1, 3, 2013, 0.00, 1),
(2, 5, 2012, 0.00, 1),
(2, 6, 2012, 0.00, 1),
(2, 8, 2012, 0.00, 1),
(2, 9, 2012, 0.00, 1),
(2, 10, 2012, 0.00, 1),
(2, 11, 2012, 0.00, 1),
(2, 12, 2012, 0.00, 1),
(2, 1, 2013, 0.00, 1),
(2, 2, 2013, 0.00, 1),
(2, 3, 2013, 0.00, 1);
GO
create procedure dbo.GetCustomerInvoices(
@Customers varchar(8000),
@sYear int,
@sMonth int
)
as
begin
with
dTally(n) as (select 0 union all select row_number() over (order by (select null)) from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
Calendar(
CalendarDate
) as (
select
dateadd(mm,@sMonth - 1 + n,dateadd(yy,@sYear - 1900,0))
from
dTally
),
SelectCustomers(
CustomerId
) as (
select
cast(ds.Item as int)
from
dbo.DelimitedSplit8K(@Customers,',') ds
),
BaseData(
CustomerId,
InvoiceDate,
Amount
) as (
select
i.intCustomer,
dateadd(mm,i.intMonth - 1,dateadd(yy,i.intYear - 1900,0)),
i.monAmount
from
[dbo].[tblInvoice] i
inner join SelectCustomers sc
on (i.intCustomer = cast(sc.CustomerId as int))
where
dateadd(mm,i.intMonth - 1,dateadd(yy,i.intYear - 1900,0)) >= dateadd(mm,@sMonth - 1,dateadd(yy,@sYear - 1900,0)) and
dateadd(mm,i.intMonth - 1,dateadd(yy,i.intYear - 1900,0)) < dateadd(mm,@sMonth - 1,dateadd(yy,@sYear - 1900 + 1,0))
)
select
cus.CustomerId,
cal.CalendarDate,
isnull(bd.Amount,0) Amount
from
Calendar cal
cross join SelectCustomers cus
left outer join BaseData bd
on (cal.CalendarDate = bd.InvoiceDate
and cus.CustomerId = bd.CustomerId)
order by
cus.CustomerId,
cal.CalendarDate;
end;
go
exec dbo.GetCustomerInvoices '1,2,3',2012,6;
go
drop procedure dbo.GetCustomerInvoices;
go
drop table [dbo].[tblInvoice];
drop table [dbo].[tblExpectedResults];
go
August 1, 2012 at 1:17 am
Many thanks Lynn, That's exactly what I needed. I'd never have come up with that.:-D
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply