change Once a month the number from 1 to 3 to 9

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

change Once a month the number from 1 to 3 to 9

Author

Topic

midan1
Starting Member

39 Posts

Posted - 2008-07-08 : 20:44:29

hi need help to this problem

i have table employee

i need to change to the employee Once a month the number from 1 to 3 to 9 (once a month)

in the first day of the month evry month

and loop back to 1 .3 .9

evry month next number from 1 to 3 3 to 9 9 to 1 and loop

this is my table

empid unit unit_date

-----------------------------------------------------------

11111 1 01/07/2008

2222 3 01/07/2008

3333 9 01/07/2008

i need to see it like this

(on this month)

empid unit unit_date new_unit

--------------------------------------------------------------------------------

11111 1 01/07/2008 1

2222 3 01/07/2008 3

3333 9 01/07/2008 9

---------------------------------------------------------------------------------------------------------------

now next month '01/08008'

empid unit unit_date new_unit

--------------------------------------------------------------------------------

11111 1 01/08/2008 3

2222 3 01/08/2008 9

3333 9 01/08/2008 1

how tnx

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-07-08 : 22:57:11

I would just derrive it from the starting values and the date. No need to update anything.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 23:15:20

[code]DECLARE @TABLE TABLE
(
empid int,
unit int,
unit_date datetime
)

INSERT INTO @TABLE
SELECT 1111, 1, '20080701' UNION ALL
SELECT 2222, 3, '20080701' UNION ALL
SELECT 3333, 9, '20080701'

DECLARE @unit TABLE
(
id int,
unit int
)

INSERT INTO @unit
SELECT 1, 1 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 9

DECLARE @today datetime

SELECT @today = '2008-07-09'

SELECT t.empid, t.unit, t.unit_date,
new_unit = power(3, (u.id + DATEDIFF(MONTH, unit_date, @today) - 1) % 3)
FROM @TABLE t
INNER JOIN @unit u ON t.unit = u.unit
/*
empid unit unit_date new_unit
----------- ----------- ----------- -----------
1111 1 2008-07-01 1
2222 3 2008-07-01 3
3333 9 2008-07-01 9
*/
SELECT @today = '2008-08-08'

SELECT t.empid, t.unit, t.unit_date,
new_unit = power(3, (u.id + DATEDIFF(MONTH, unit_date, @today) - 1) % 3)
FROM @TABLE t
INNER JOIN @unit u ON t.unit = u.unit
/*

empid unit unit_date new_unit
----------- ----------- ----------- -----------
1111 1 2008-07-01 3
2222 3 2008-07-01 9
3333 9 2008-07-01 1
*/

SELECT @today = '2008-09-19'

SELECT t.empid, t.unit, t.unit_date,
new_unit = power(3, (u.id + DATEDIFF(MONTH, unit_date, @today) - 1) % 3)
FROM @TABLE t
INNER JOIN @unit u ON t.unit = u.unit
/*

empid unit unit_date new_unit
----------- ----------- ----------- -----------
1111 1 2008-07-01 9
2222 3 2008-07-01 1
3333 9 2008-07-01 3
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

midan1
Starting Member

39 Posts

Posted - 2008-07-09 : 02:42:11

TNX TNX
question
how can i do it with the same logic
with only
1,3

tnx for all

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 02:44:42

1, 3 is easier. You can just simply use

case when unit = 1 then 3
when unit = 3 then 1
end

KH
[spoiler]Time is always against us[/spoiler]

midan1
Starting Member

39 Posts

Posted - 2008-07-09 : 04:19:36

please whare to put it
and how
i must to use %2
it only tow numbers 1,3

SELECT	@today	= '2008-07-09'

SELECT	t.empid, t.unit, t.unit_date,
	new_unit	= power(2????, (u.id + DATEDIFF(MONTH, unit_date, @today) - 1) % 2 ????)
FROM	@TABLE t
	INNER JOIN @unit u	ON	t.unit	= u.unit

TNX foe the help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 04:25:49

if you only have 1 and 3 why must you make your query so complicated ?

KH
[spoiler]Time is always against us[/spoiler]

midan1
Starting Member

39 Posts

Posted - 2008-07-09 : 05:01:52

tnx
but how can i know what be in the future 1 OR 3 ?
for example
what be the value in 01/11/2008 or 01/12/2008 ???

TNX for all

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 07:19:54

for only 2 value 1 or 3,
just find the number of month in-between and if odd then . .. if even then . . .

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 07:36:38

Changed the script a bit. Making use of a @unit table.

Change the value of @no_units and run and see the result

DECLARE @TABLE TABLE
(
	empid		int, 
	unit 		int,
	unit_date	datetime
)

INSERT INTO @TABLE
SELECT	1111, 1, '20080701'	UNION ALL
SELECT	2222, 3, '20080701'	UNION ALL
SELECT	3333, 9, '20080701'

DECLARE @unit TABLE
(
	id	int,
	unit	int
)

INSERT INTO @unit
SELECT	0,  1	UNION ALL
SELECT	1,  3	UNION ALL
SELECT	2,  9	UNION ALL
SELECT	3, 11	UNION ALL
SELECT	4, 13

DECLARE @no_units	int

SELECT	@no_units	= 2

SELECT	t.empid, t.unit, t.unit_date, new_date = today,
	new_unit	= n.unit
FROM	@TABLE t
	CROSS JOIN
	(
		SELECT	today = '2008-07-09'	UNION ALL
		SELECT	today = '2008-08-03'	UNION ALL
		SELECT	today = '2008-09-23'	UNION ALL
		SELECT	today = '2008-10-27'	UNION ALL
		SELECT	today = '2008-11-15'	UNION ALL
		SELECT	today = '2008-12-31'
	) d
	INNER JOIN @unit u	ON	t.unit	= u.unit
	INNER JOIN @unit n	ON	n.id	= (u.id + DATEDIFF(MONTH, unit_date, today)) % @no_units
ORDER BY empid, unit, unit_date, new_date

/*
empid       unit        unit_date   new_date   new_unit    
----------- ----------- ----------- ---------- ----------- 
       1111           1 2008-07-01  2008-07-09           1 
       1111           1 2008-07-01  2008-08-03           3 
       1111           1 2008-07-01  2008-09-23           1 
       1111           1 2008-07-01  2008-10-27           3 
       1111           1 2008-07-01  2008-11-15           1 
       1111           1 2008-07-01  2008-12-31           3 
       2222           3 2008-07-01  2008-07-09           3 
       2222           3 2008-07-01  2008-08-03           1 
       2222           3 2008-07-01  2008-09-23           3 
       2222           3 2008-07-01  2008-10-27           1 
       2222           3 2008-07-01  2008-11-15           3 
       2222           3 2008-07-01  2008-12-31           1 
       3333           9 2008-07-01  2008-07-09           1 
       3333           9 2008-07-01  2008-08-03           3 
       3333           9 2008-07-01  2008-09-23           1 
       3333           9 2008-07-01  2008-10-27           3 
       3333           9 2008-07-01  2008-11-15           1 
       3333           9 2008-07-01  2008-12-31           3 

(18 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

midan1
Starting Member

39 Posts

Posted - 2008-07-09 : 09:54:56

wow you are genius TNX!
please can you remove the
do i must this "CROSS JOIN" ?

CROSS JOIN
	(
		SELECT	today = '2008-07-09'	UNION ALL
		SELECT	today = '2008-08-03'	UNION ALL
		SELECT	today = '2008-09-23'	UNION ALL
		SELECT	today = '2008-10-27'	UNION ALL
		SELECT	today = '2008-11-15'	UNION ALL
		SELECT	today = '2008-12-31'
	) d

i don't need this field "new_date"
i need to put your code in a view

tnx for your help and support

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 10:00:59

yes. You may removed it and replace today with the actual var or column name.

The cross join is just to test few months in a single query rather than one by one as post previously.

KH
[spoiler]Time is always against us[/spoiler]

midan1
Starting Member

39 Posts

Posted - 2008-07-09 : 10:14:16

like this ?

DECLARE @unit TABLE
(
	id	int,
	unit	int
)

INSERT INTO @unit
SELECT	0,  1	UNION ALL
SELECT	1,  3	UNION ALL
SELECT	2,  9	UNION ALL
SELECT	3, 11	UNION ALL
SELECT	4, 13

DECLARE @no_units	int

SELECT	@no_units	= 2

SELECT	t.empid, t.unit, t.unit_date, new_date = today,
	new_unit	= n.unit
FROM	[nili8].[dbo].[v_emp9] t
	CROSS JOIN
	(
		SELECT	today = '01/08/2008' ---- do i must this ???????????????????????	
	) d
	INNER JOIN @unit u	ON	t.unit	= u.unit
	INNER JOIN @unit n	ON	n.id	= (u.id + DATEDIFF(MONTH, unit_date, today)) % @no_units
ORDER BY empid, unit, unit_date, new_date

TNX

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 19:31:49

[code]
declare @today datetime
select @today = '20080801' -- or use getdate() if you are using current date

SELECT t.empid, t.unit, t.unit_date, new_date = @today,
new_unit = n.unit
FROM [nili8].[dbo].[v_emp9] t
CROSS JOIN
(
SELECT today = '01/08/2008' ---- do i must this ???????????????????????
) d
INNER JOIN @unit u ON t.unit = u.unit
INNER JOIN @unit n ON n.id = (u.id + DATEDIFF(MONTH, unit_date, @today)) % @no_units
ORDER BY empid, unit, unit_date, new_date
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources