how to join table to table AS temprery table

Question

0.00/5 (No votes)

See more:

SQL-server-2000

i get table from the below command

SQL

 SELECT * FROM [MATRIMONIAL].[dbo].User_profile1



 
  DECLARE @time_duration INT
   SET @time_duration=(SELECT Time_duration FROM [MATRIMONIAL].[dbo].User_profile1 WHERE       Pro_id='002')
  DECLARE @d1 DATETIME
  SET @d1= GETDATE();
  DECLARE @d2 DATETIME
SET @d2 = (SELECT Start_date FROM dbo.User_profile1 WHERE Pro_id='002' )

SELECT dbo.Left_membership(@d2,@d1,@time_duration)

Left_membership(@d2,@d1,@time_duration) is function
and want to bind the table in single grid-view how can i do this?

Posted 8-Feb-14 22:59pm

mukesh mourya

Updated 9-Feb-14 0:20am

agent_kruger

v2

Add a Solution

2 solutions

Solution 1

SQL

SELECT * 
FROM [MATRIMONIAL].[dbo].User_profile1
  LEFT JOIN dbo.Left_membership(@d2,@d1,@time_duration) as tbl on tbl.[field] = User_profile1.[field]

Posted 8-Feb-14 23:27pm

Kornfeld Eliyahu Peter

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Vedat Ozan Oner · Accepted Answer · 2014-02-08T23:32:00

Solution 2

you don't have to complicate things like that. let me give an example:

SQL

--create table USER_PROFILE (
--	ID int,
--	NAME varchar(50),
--	START datetime,
--	DURATION int
--)

--insert into USER_PROFILE(ID, NAME, START, DURATION)
--values 
--(1, 'user1', DATEADD(m, -2, getdate()), 90),
--(2, 'user2', DATEADD(m, -3, GETDATE()), 60)


select ID, NAME, DURATION-DATEDIFF(d, START, GETDATE())as SUBS_LEFT, 
(case when DATEADD(d, DURATION, START)>GETDATE() then 0 else 1 end) IS_ENDED 
from USER_PROFILE

then read here: http://msdn.microsoft.com/en-us/library/314t4see.aspx[^]
and here: http://msdn.microsoft.com/en-us/library/33w255ac%28v=vs.110%29.aspx[^]

1- create a project
2- add a dataset
3- create your datatable with the given query
4- place a gridview on your form.
5- set the datasource of the gridview as the datatable of the dataset

all code is automatically generated by both dataset designer and form designer. you don't need to write anything. if you like, you can change all that code later according to your needs. that is all.

[added]

SQL

select ID, NAME, DURATION-DATEDIFF(d, START, GETDATE())as SUBS_LEFT,
(case when DATEADD(d, DURATION, START)>GETDATE()
 then convert(varchar, DURATION-DATEDIFF(d, START, GETDATE()))
 else 'Expired' end) IS_ENDED
from USER_PROFILE

Posted 8-Feb-14 23:32pm

Vedat Ozan Oner

Updated 15-Feb-14 8:38am

v2

Comments

mukesh mourya 15-Feb-14 10:11am

@Vedat Ozan Oner, thanks to suggest me
but i wont to write "Expired" where i get negative value in column SUBS_LEFT, in grid-view .....please help me

Vedat Ozan Oner 15-Feb-14 12:24pm

then just replace 0 & 1 with "" & "Expired" as:

... then "" else "Expired") IS_ENDED

mukesh mourya 15-Feb-14 12:43pm

no not in column IS_ENDED i wont in column SUBS_LEFT where we get negative value only, in positive value as it is have

Vedat Ozan Oner 15-Feb-14 12:48pm

can you give me an example of output? I couldn't understand exactly what you want.

mukesh mourya 15-Feb-14 13:05pm

ok..

i have column 'remain_day' which has some remain days of membership of user, some remain days and some expired days(days is not more yet). and i get from this command

select *, Time_duration-DATEDIFF(d, Start_date, GETDATE())as SUBS_LEFT,
--(case when DATEADD(d, Time_duration, Start_date)>GETDATE() THEN '' else 'Expired' end) IS_ENDED
FROM dbo.User_profile1

sorry for bad english

Vedat Ozan Oner 15-Feb-14 13:16pm

no, I mean a sample output of query. Think as if you have the right query and you have executed it. what would you expect as output from the right query?

mukesh mourya 15-Feb-14 13:31pm

I am unable to do.
can you take my screen form team-viwer.?

Vedat Ozan Oner 15-Feb-14 14:19pm

I'm sorry, I can't do that.

mukesh mourya 15-Feb-14 14:21pm

ok..

mukesh mourya 15-Feb-14 14:27pm

ok..
i have value in column like -
1,
2,
-5,
10,
20,
-10,
-23,
-24,
24,
...etc

and i wont show as 'Expired' where value in negative just like as-
1,
2,
Expired,
10,
20,
Expired,
Expired,
Expired,
24,
...etc

Vedat Ozan Oner 15-Feb-14 14:38pm

I updated the solution

mukesh mourya 15-Feb-14 23:59pm

Thanks A lot .........