April 6, 2012 at 1:29 am
This my emp table i want to insert value in other table
into emp_no table
emp_id att_day lve_day
------ ------------------------------ ------------------------------
1 1 10
2 5 10
3 10 15
i want output like this
emp_n days
11
12
13
14
15
16
17
18
19
110
25
26
27
28
29
210
anyone can help?
April 6, 2012 at 2:52 am
Assuming you have a Tally table (see the related link in my signature if you don't know what that is) you could use this table in an OUTER APPLY query.
If you'd like to see the coded version, please provide ready to use sample data as described in the first link in my signature.
April 6, 2012 at 2:52 am
Try this:
DECLARE @Tab TABLE
( emp_id INT
,att_day INT
,lve_day INT
)
;
INSERT INTO @Tab
SELECT 1, 1 ,10
UNION ALL SELECT 2, 5 ,10
UNION ALL SELECT 3, 10 ,15
;
; WITH Tens (N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1
)
, Hundreds(N) AS
(
SELECT T1.N
FROM Tens T1
CROSS JOIN Tens T2
)
, Thousands (N) AS
(
SELECT T1.N
FROM Hundreds T1
CROSS JOIN Hundreds T2
)
, Millions(N) AS
(
SELECT T1.N
FROM Thousands T1
CROSS JOIN Hundreds T2
)
, Numbers(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)
FROM Millions
)
SELECT T.emp_id , N.N
FROM @Tab T
CROSS JOIN Numbers N
WHERE N.N BETWEEN T.att_day AND T.lve_day
ORDER BY T.emp_id , N.N
April 6, 2012 at 3:06 am
i want to pick values from database
April 6, 2012 at 3:09 am
is it will work on sql server
April 6, 2012 at 3:14 am
paramjit4 (4/6/2012)
is it will work on sql server
Why don't you give it a try? :unsure:
April 6, 2012 at 3:20 am
ok
April 6, 2012 at 3:32 am
if you don't mind..can you tell me how it work!!
thx in advance
April 6, 2012 at 3:46 am
it's not working on sql server 2000
April 6, 2012 at 3:53 am
So what? You're in a 2005 forum.
If I told you that my car was 'not working', would you know how to fix it?
April 6, 2012 at 3:55 am
paramjit4 (4/6/2012)
it's not working on sql server 2000
You posted in the SQL 2005 forum and didn't mention the solution should work on SQL2000.
Neither the table variable nor the CTE will work.
As mentioned before I recommend to use a physical tally table (see the related link in my signature).
Since the OUTER APPLY is available on SS2K5 and up, you'll need to use the CROSS JOIN approach ColdCoffee used.
And to repeat what I stated before: if you'd like to see a coded solution, please provide ready to use sample data. It's called "help us to help you".
April 6, 2012 at 3:58 am
can you give me coords on sql server 2000.,,if you don't mind
sorry :
April 6, 2012 at 4:02 am
paramjit4 (4/6/2012)
can you give me coords on sql server 2000.,,if you don't mindsorry :
Yes, I'll post a coded solution for 2000 if you take te time and post ready to use sample data (again, see the first link in my signature how to do it).
Also, I'll use a tally table.You might take the time to read the related article in the mean time..
April 6, 2012 at 4:56 am
thx alot it working on 2005
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy