Help me in query, - SQL Server Forums

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)

Help me in query,

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-09-08 : 02:35:22

Hi,

My Table Structure is
MyTable(Order_id,Order_date,Purchased date,Status)

And My output must be as below
---------------------------------
Order date|Purchased date|Status
---------------------------------
6/7/2008 |11/7/2008 |Completed
--------|--------- |Completed
--------|--------- |Completed
12/7/2008 |15/7/2008 |In-progress
--------|--------- |In-progress
--------------------------------------

How to get output as above
ie, OrderDate and Purchase date should not repeat,only status have to repeat, ie, order date and purchase date must show empty for second time....

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-08 : 03:07:45

quote:
Originally posted by ganeshkumar08

Hi,

My Table Structure is
MyTable(Order_id,Order_date,Purchased date,Status)

And My output must be as below
---------------------------------
Order date|Purchased date|Status
---------------------------------
6/7/2008 |11/7/2008 |Completed
--------|--------- |Completed
--------|--------- |Completed
12/7/2008 |15/7/2008 |In-progress
--------|--------- |In-progress
--------------------------------------

How to get output as above
ie, OrderDate and Purchase date should not repeat,only status have to repeat, ie, order date and purchase date must show empty for second time....

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

Where do you want to show this output?

Madhivanan

Failing to plan is Planning to fail

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-09-08 : 03:14:44

Hi

I got solution

Insert Into MyOrder
Select 1,'02/08/08','02/10/08','Completed' Union All
Select 2,'02/09/08','02/15/08','Completed' Union All
Select 3,'02/12/08','02/13/08','Completed' Union All
Select 4,'02/12/08','02/14/08','Completed' Union All
Select 5,'09/07/08','09/14/08','In Progress' Union All
Select 6,'09/08/08','09/14/08','In Progress' Union All
Select 7,'09/08/08','09/14/08','In Progress' Union All
Select 8,'02/08/08','02/10/08','Completed' Union All
Select 9,'02/09/08','02/15/08','Completed' Union All
Select 10,'02/12/08','02/13/08','Completed' Union All
Select 11,'02/12/08','02/14/08','Completed' Union All
Select 12,'09/07/08','09/14/08','In Progress' Union All
Select 13,'09/08/08','09/14/08','In Progress' Union All
Select 14,'09/08/08','09/14/08','In Progress'

Select * into #Temp From
(
Select Row_Number() Over(PARTITION BY OrderDate,PurchaseDate,Status Order By Status) As RowNum,
convert(varchar,OrderDate,103) As 'OrderDate',convert(varchar,PurchaseDate,103) As 'PurchaseDate',Status
From MyOrder
)A
Update #Temp
Set OrderDate='',
PurchaseDate=''
Where RowNum>1
Select OrderDate,PurchaseDate,Status From #Temp
Drop Table #Temp

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-08 : 03:25:10

But you haven't answered to my question. This is the formation and should be done in the Front end application

Madhivanan

Failing to plan is Planning to fail

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-09-08 : 03:51:59

madhivanan, i want to show output in asp.net..

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-08 : 04:44:21

quote:
Originally posted by ganeshkumar08

madhivanan, i want to show output in asp.net..

Solutions are easy. Understanding the problem, now, that's the hard part

Then you should do this formation when you show data in Grid.
With the formation done at backend, how can you identity rows that belong to order date 6/7/2008?

Madhivanan

Failing to plan is Planning to fail

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-09-08 : 05:19:01

i wont do any thing in front end, i just bind the data which i got from back end..

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-08 : 05:27:04

quote:
Originally posted by ganeshkumar08

i wont do any thing in front end, i just bind the data which i got from back end..

Solutions are easy. Understanding the problem, now, that's the hard part

If it is only for display then no problem
But keep in mind that it is more effecient to do this in front end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources