July 31, 2012 at 1:35 pm
Hi All,
I'm using SQL Server 2008.
I have the following problem which I could do with some help with if anyone has a spare few minutes of time...:-)
I have an SP which returns invoice records. I pass in as parameters, StartYear (int) and StartMonth (int) and a comma separated list of customers (varchar(max)).
I have a table with the following structure...
tblInvoices
intID int
intYear int
intMonth int
intCustomer int
monAmount money
I need to return 12 months (12 records) per passed in Customer. Don't worry about accumulating up Amounts. I can split up the passed in comma separated Customer IDs via an in-line UDF in the where clause so I have that point covered...
If there are not 12 existing records (one for each month of the year) for each customer to be retrieved (from the start month and year) I need to generate the missing records first prior to selecting them. So I need an insert at the start of the SP that adds the missing records (with the intCustomer and intYear and intMonth).
Can someone help me on this please?
The passed in month may be January which would mean I need to check all months up to and including December for the passed in year are present. The passed in month could be April in which case I would need to check through to the following March (spanning a year).
Below is the UDF I use to split the comma separated Customer IDs...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_parse_string_into_integer_table]
(@parseString VARCHAR(MAX) = null)
RETURNS @parsedstring TABLE (splitstring int)
as
BEGIN
declare @pos int
declare @splitstring varchar(255)
declare @strlen int
select @strlen = len(ltrim(@parsestring))
if @strlen<> 0
BEGIN
while @strlen > 0
BEGIN
select @pos = charindex(',',@parsestring)
if @pos = 0
BEGIN
insert into @parsedstring values ( @parsestring)
break
END
select @splitstring = substring(@parsestring,1,@pos-1)
insert into @parsedstring values( @splitstring)
select @strlen= @strlen - @pos
select @parsestring = substring(@parsestring,@pos+1,@strlen)
END
END
RETURN
END
I call this in a where clause of my selects as follows...
WHERE A.[CustomerID] IN (SELECT [splitstring] FROM dbo.udf_parse_string_into_integer_table(@CustomerIDs))
Many thanks for taking the time to read my ramblings:-)
July 31, 2012 at 1:42 pm
Well, there are better DelimitedSplit routines out there (http://qa.sqlservercentral.com/articles/Tally+Table/72993/, for a highly optimized 8K splitter (read the article and the discussion)).
Unfortunately, there is nothing else in your post for us to help you with. If you would read the first article I link to below in my signature block, it will walk you through what and how to post the information we need to give you the best possible answers.
July 31, 2012 at 1:47 pm
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 million characters (highly unlikely). I only posted the split code as I figured it would be simpler than expecting someone to also create a tally table.
I'll have a read of the link you mentioned then update this post.
Regards.
July 31, 2012 at 1:56 pm
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 = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [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
July 31, 2012 at 2:17 pm
One thing I think the article leaves out (I haven't read it in a long time) may be expected results. Based on your sample data, what do you expect to be returned? Don't describe, show; perhaps by creating a table called ExpectedResults and populating it using insert into statements with what should be returned by the query.
July 31, 2012 at 2:56 pm
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...:-)
July 31, 2012 at 2:58 pm
Charlottecb (7/31/2012)
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...:-)
Yes you described it, I'm asking you to show what you want. If you provide us with a table with what the results should be based on the sample data, you give us something to test to.
July 31, 2012 at 3:08 pm
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, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [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
Original Rows have a 0 in the last column. New rows have a 1 in the last column.
Passed In intMonth = 4, intYear = 2012 AND intCustomerIDs = '1,2,'
Many thanks
July 31, 2012 at 3:17 pm
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 [dbo].[Months](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
CONSTRAINT [PK_Months] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This is populated as follows...
INSERT INTO [Months] ([Year],[Month])
VALUES
(2011, 1),
(2011, 2),
(2011, 3),
(2011, 4),
(2011, 5),
(2011, 6),
(2011, 7),
(2011, 8),
(2011, 9),
(2011, 10),
(2011, 11),
(2011, 12),
(2012, 1),
(2012, 2),
(2012, 3),
(2012, 4),
(2012, 5),
(2012, 6),
(2012, 7),
(2012, 8),
(2012, 9),
(2012, 10),
(2012, 11),
(2012, 12),
(2013, 1),
(2013, 2),
(2013, 3),
(2013, 4),
(2013, 5),
(2013, 6),
(2013, 7),
(2013, 8),
(2013, 9),
(2013, 10),
(2013, 11),
(2013, 12)
GO
July 31, 2012 at 3:20 pm
I am a little late to this one but...
I need to return 12 months (12 records) per passed in Customer.
Your desired output does not come close to this.
CustID 1 has 14 rows
CustID 2 has 13 rows
CustID 3 has 2 rows (this shouldn't even be in the output based on the description).
Your description and your desired output as you have them hardcoded don't match up.
As an aside, this would be a LOT easier if you used datetime datatypes instead of ints. Using ints for portions of a date ends up being way more painful than using the proper datatypes in the first place.
_______________________________________________________________
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:25 pm
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 make sure that from the month and year passed in, there are at least 12 months worth of data with no gaps between them. If there are already months within this 12 months window then that's great, I don't need to add them. If there are gaps, I need to fill them. If there are months and years before or after my 12 month window, again I don't care.:-) What I have shown is the state of the table after the stored proc has run. If you look at the added rows (last column = 1) these are new rows which have been generated for customers 1 and 2 to fill the gaps between April 2012 and March 2013. Apologies if I didn't make this clear initially.
I know how to select the 12 months of data at the end of the SP. The problem I am trying to solve is at the start of the SP - I need to insert any missing rows to get the table into the state shown in my example...
With regard to your comment about being easier to do this with proper dates as opposed to separate months and years - I'd be happy to adjust the structure to a datetime column if I could see an easy solution to the problem...
Thanks.
July 31, 2012 at 3:33 pm
So your process is going to insert rows into the original table? And then you are going to select all the rows from the table regardless of which Customer IDs you pass in?
Wouldn't it make more sense to just get the data (including the missing rows with default values) when you select the data instead of creating dummy data in your database?
_______________________________________________________________
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:38 pm
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.
July 31, 2012 at 3:41 pm
With regard to your comment about being easier to do this with proper dates as opposed to separate months and years - I'd be happy to adjust the structure to a datetime column if I could see an easy solution to the problem...
I don't mean just for this problem. In general you should the proper datatypes. There is a reason we have a datetime datatype. If I suggested you should store a integer in a varchar field you would think I was crazy and tell me to use a int datatype? Why not do the same thing dates? With using them as ints like this you will constantly be casting them to varchar so you can add them together to cast as a date. yuck!!!
I am out of here for today but if nobody else posts a solution I will work something up in the morning.
_______________________________________________________________
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:44 pm
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...
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply