Click here to Skip to main content
14,699,089 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,
I have one issue in my SQL Script.I need to Insert a data for the following Conditions.

i have a itemmaster ,it contains 100 REcords, i also Create Calendar for the year,
I need map with ITEMMASTER an Calendar Table.
No of days in Year :365
No of Records in ITEM Master :100
Condition :
EAch ITEMCODE add for Each Dates(365)
How to do this?
Posted
Comments
phil.o 5-Oct-15 5:01am
   
What have you tried? Where are you stuck?

   
Comments
Maciej Los 5-Oct-15 6:32am
   
5ed!
Another option is to use CTE[^].

For further information, please see: Using Common Table Expressions[^]
WITH common_table_expression (Transact-SQL)[^]

DECLARE @items TABLE(ItemMaster INT IDENTITY(1,1), ItemName VARCHAR(30))

INSERT INTO @items(ItemName)
VALUES ('A'), ('B'), ('C')


;WITH CTE AS
(
    SELECT 1 As NoOfDay
    UNION ALL
    SELECT NoOfDay +1 AS NoOfDay
    FROM CTE
    WHERE NoOfDay < 366
)
SELECT t2.ItemMaster, CONVERT(DATE, DATEADD(DD, t1.NoOfDay, GETDATE())) AS MyDate
FROM CTE AS t1, @items AS t2
ORDER BY t2.ItemMaster, t1.NoOfDay  
OPTION (MAXRECURSION 0)


Result:
ItemMaster  MyDate
1   2015-10-06
1   2015-10-07
1   2015-10-08
1   2015-10-09
1   2015-10-10
...
2   2015-10-06
2   2015-10-07
2   2015-10-08
2   2015-10-09
2   2015-10-10
...
3   2015-10-06
3   2015-10-07
3   2015-10-08
3   2015-10-09
3   2015-10-10
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900