December 3, 2007 at 7:37 am
hi need help
this is the orginal code by-Jeff Moden
-------------------------------------
DECLARE @Sequence TABLE
(
Modulo INT,Value INT,ID INT
)
DECLARE @val_id INT
set @val_id='5555567'
INSERT INTO @Sequence
(Modulo,Value)
SELECT 0,1 UNION ALL --בוקר
SELECT 1,1 UNION ALL -- בוקר
SELECT 2,2 UNION ALL -- ערב
SELECT 3,2 UNION ALL -- ערב
SELECT 4,3 UNION ALL -- לילה
SELECT 5,3 UNION ALL -- לילה
SELECT 6,4 UNION ALL --לילה
SELECT 7,5 -- חופש
SELECT v.Number-1+CAST('20070101' AS DATETIME) AS Date,s.Value ,@val_id AS ID
FROM Master.dbo.spt_Values v,@Sequence s
WHERE (v.Number-1) % 8 = s.Modulo
AND v.Type = 'P'
AND v.Number-1++CAST('20070101' AS DATETIME) <= '20071231'
------------------------------------------------------------------
and i need to add this (add pattern)
---------------------------------------------------------
DECLARE @Sequence TABLE
(
Modulo INT,Value INT,ID INT,unit INT,Val INT
)
DECLARE @val_id INT
set @val_id='5555567'
INSERT INTO @Sequence
(Modulo,Value)
SELECT 0,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,3 UNION ALL
SELECT 6,4 UNION ALL
SELECT 7,5
(unit,Val)
SELECT 0,1 UNION ALL --UNIT 1
SELECT 1,1 UNION ALL -- UNIT 2
SELECT 2,2 UNION ALL -- UNIT 3
SELECT 3,2 UNION ALL -- UNIT 4
SELECT v.Number-1+CAST('20070101' AS DATETIME) AS Date,s.Value ,@val_id AS ID,s.Val
FROM Master.dbo.spt_Values v,@Sequence s
WHERE (v.Number-1) % 8 = s.Modulo
and (v.Number-1) % 4 = s.val
AND v.Type = 'P'
AND v.Number-1++CAST('20070101' AS DATETIME) <= '20071231'
--------------------------------------------------
TNX
December 3, 2007 at 8:13 am
Why did you need to start a new thread for this ? All the context of your problem is on the original thread.
December 3, 2007 at 1:06 pm
i am sorry
you right
ilan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply