if it is possible to do insert store proc for this table2 like this?

Question

if it is possible to do insert store proc for this table2 like this?

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
April 18, 2012 at 2:38 am

#272879

for example 2 tables are there
these are the tables table1
CREATE TABLE [dbo].[tblTable1](
[syst] [int] IDENTITY(1,1) NOT NULL,
[duedatefrom] [datetime] NULL,
[todate] [datetime] NULL,
[duedatecount] [int] NULL,
PRIMARY KEY CLUSTERED
(
[syst] 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
tables table2
CREATE TABLE [dbo].[tblTable2](
[syst1] [int] IDENTITY(1,1) NOT NULL,
[syst] [int] NULL,
[duedate] [datetime] NULL,
[duedatecount] [int] NULL,
PRIMARY KEY CLUSTERED
(
[syst1] 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
ALTER TABLE [dbo].[tblTable2] WITH CHECK ADD FOREIGN KEY([syst])
REFERENCES [dbo].[tblTable1] ([syst])
GO
the output of the first table is like this iand i insertedthe value
the output for the first table is table1
syst duedatefrom todate duedatecount
1 2012-04-18 13:20:59.920 2012-05-03 13:20:59.920 15
now iam trying for second table i am just trying to insert store proc automatically it has to increase date an and also reduce count and output like this table2
syst1 syst duedate duedatecount
1 1 2012-04-18 15
2 1 2012-04-19 14
3 1 2012-04-20 13
4 1 2012-04-21 12
5 1 2012-04-22 11
..
..
15 1 0 0
i tried insert query
insert into dbo.tblTable2
(
syst1
syst,
duedate,
duedatecount
)
select
dateadd(day,15,GETDATE())
datediff(day,duedate,getdate())
select datediff(day,'2007-12-15','2008-12-15')

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

this should get you close

;WITH CTE(n) AS (SELECT 0 UNION ALL select TOP 500 ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) FROM sys.objects a, sys.objects b)

SELECT B.N + 1, syst, DATEADD (DAY, B.N, duedatefrom) AS duedate, duedatecount - B.N AS duedatecount

FROM tblTable1 a

CROSS APPLY CTE b

WHERE duedatecount - B.N > = 0

ORDER BY syst,n

the cte is creating a tally table with 500 rows (you can increase or decrease this if you need more or less numbers). then we select the number from the tally table and add 1 (to get our 1 through 16), add the number as days to get our increasing date column, subtract the number from our count to get the descending duedatecount.

for a detailed explination on tally tables read this article http://www.sqlservercentral.com/articles/T-SQL/62867/

EDIT: thanks jeff i think i have finally gotten it through my head.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 2

thanks it works fine and i got the result from your code itself