How to Insert varchar values into table using loop

Question

I got a table called ATMONTH where i need to check the count of customers for each month

Table structure is like

CREATE TABLE [dbo].[ATMONTH](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Month] [varchar](50) NULL,
[COUNT OF CUSTOMER] [varchar] (50) NULL,
[RefMonthStart] [varchar](50) NULL, 
[RefMonthEnd] [varchar](50) NULL)

i'm accepting a output like

1 1month 3000 0 30

2 2Month 4500 31 60

3 3month 4000 61 90

4 4Month 6000 91 120

.

.

.

24 24Month .. .. ..

25 >24Month .. .. ..

where count of customer i'm refering to other table.. here only month, refmonthstart and

refmonthend column must be manually inserted where refmonthstart and refmonthend columns

are number of days in in a month

how will i do it...??

Thanks in Advance

Can you please share the query you are using to insert this data now? — shree.pat18
– shree.pat18, Commented Jun 18, 2014 at 6:53
I strong belive the poster should be actively giving responses to the answers by commenting or accepting the answer. And if the poster is not interested in getting the answer, then they should not be posting questions here and wasting others time who is spending their time just to help the poster. — Nithin Gangadharan
– Nithin Gangadharan, Commented Jun 24, 2014 at 4:38
I'm extremely sorry tat I didn't respond back to your answer as I was hooked up with some other work and got busy with it. And thank you so very much for your response your answer was very much useful to me. Thank you once again Nithin Gangadharan — user3737182
– user3737182, Commented Jun 24, 2014 at 8:25

Nithin Gangadharan · Accepted Answer · 2014-06-18 12:19:58Z

Try the following query

DML:

CREATE TABLE [dbo].[ATMONTH](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Month] [varchar](50) NULL,
    [COUNT OF CUSTOMER] [varchar] (50) NULL,
    [RefMonthStart] [varchar](50) NULL, 
    [RefMonthEnd] [varchar](50) NULL)

INSERT INTO [dbo].[ATMONTH]([Month])
VALUES ('Jan'),
('Feb'),
('Mar'),
('Apr'),
('May'),
('Jun'),
('Jul'),
('Aug'),
('Sep'),
('Oct'),
('Nov'),
('Dec');


CREATE TABLE dbo.MonthsAndDays
(
    [Month] VARCHAR(3),
    Days SMALLINT
)

INSERT INTO dbo.MonthsAndDays([Month],Days)
VALUES ('Jan',31),
('Feb',28),
('Mar',31),
('Apr',30),
('May',31),
('Jun',30),
('Jul',31),
('Aug',31),
('Sep',31),
('Oct',30),
('Nov',30),
('Dec',31);

Query:

DECLARE @refmonthstart INT = 0

;WITH CTE AS
(
    SELECT a.ID,
           a.Month,
           a.[COUNT OF CUSTOMER],
           a.[RefMonthStart],
           a.[RefMonthEnd],
           b.Days Days,
           ROW_NUMBER() OVER ( ORDER BY a.[ID] ASC) i
    FROM [dbo].[ATMONTH] a
    INNER JOIN dbo.MonthsAndDays b
    ON a.[Month] = b.[Month]
)
,
ResultSet AS
(
    SELECT ID,
           Month,
           [COUNT OF CUSTOMER],
           @refmonthstart AS RefMonthStart,
           Days + @refmonthstart AS RefMonthEnd,
           i
    FROM CTE
    WHERE i = 1

    UNION ALL

    SELECT  T.ID,
            T.Month,
            T.[COUNT OF CUSTOMER],
            R.RefMonthEnd + 1,
            T.Days + R.RefMonthEnd,
            T.i
    FROM ResultSet R
    INNER JOIN CTE T
    ON  R.i + 1 = T.i 

)
SELECT *
FROM ResultSet
OPTION (MAXRECURSION 1000)

Query will recursively calculate refmonthstart and refmonthend value. Set the MAXRECURSION value depending upon the number of rows you have in the table. You can set the initial refmonthstart value where @refmonthstart is declared, I have set it to 0(zero)

Collectives™ on Stack Overflow

How to Insert varchar values into table using loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related