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
 General SQL Server Forums
 New to SQL Server Programming
 Please help me.

Author  Topic 

uday424
Starting Member

6 Posts
Posted - 2009-10-21 : 14:18:27
Below are the results of two queries, which are queried for the same table. Please help me in combining these two tables based on common columns which are DATE, DAY AND VOLUME.


Date Day Start_time RID Volume

2008-05-02 Friday 11:20:43.0000000 2 890
2008-05-02 Friday 11:20:43.0000000 17 1094
2008-05-02 Friday 11:20:43.0000000 23 1030
2008-05-02 Friday 11:20:43.0000000 34 825
2008-05-02 Friday 11:20:43.0000000 39 1320
2008-05-02 Friday 11:20:43.0000000 48 1862
2008-05-02 Friday 11:20:43.0000000 49 5290


Date Day End_time RID Volume
2008-05-02 Friday 11:35:53.0000000 2 728
2008-05-02 Friday 11:35:53.0000000 17 825
2008-05-02 Friday 11:35:53.0000000 23 920
2008-05-02 Friday 11:35:53.0000000 34 708
2008-05-02 Friday 11:35:53.0000000 39 1101
2008-05-02 Friday 11:35:53.0000000 48 1366
2008-05-02 Friday 11:35:53.0000000 49 4457

I need it like this:

Date Day Start_time RID Volume End_time Volume

2008-05-02 Friday 11:20:43.0000000 2 890 11:35:53.0000000 728
2008-05-02 Friday 11:20:43.0000000 17 1094 11:35:53.0000000 825
2008-05-02 Friday 11:20:43.0000000 23 1030 11:35:53.0000000 920
2008-05-02 Friday 11:20:43.0000000 34 825 11:35:53.0000000 708
2008-05-02 Friday 11:20:43.0000000 39 1320 11:35:53.0000000 1101
2008-05-02 Friday 11:20:43.0000000 48 1862 11:35:53.0000000 1366
2008-05-02 Friday 11:20:43.0000000 49 5290 11:35:53.0000000 4457

X002548
Not Just a Number

15586 Posts
Posted - 2009-10-21 : 14:23:46
quote:
Originally posted by uday424

Below are the results of two queries, which are queried for the same table.



OK, be a good idea if you show us the queries

quote:


Please help me in combining these two tables based on common columns which are DATE, DAY AND VOLUME.



Sure...sounds like you want GROUP BY...but we still need to see the queries, and what you want to do with the columns that you want "combined".


and you can use [ code] [ /code] tags (with out the space, to keep data formatted correctly.




Date            Day     Start_time             RID     Volume

2008-05-02	Friday	11:20:43.0000000	2	890
2008-05-02	Friday	11:20:43.0000000	17	1094
2008-05-02	Friday	11:20:43.0000000	23	1030
2008-05-02	Friday	11:20:43.0000000	34	825
2008-05-02	Friday	11:20:43.0000000	39	1320
2008-05-02	Friday	11:20:43.0000000	48	1862
2008-05-02	Friday	11:20:43.0000000	49	5290


Date            Day       End_time             RID      Volume
2008-05-02	Friday	11:35:53.0000000	2	728
2008-05-02	Friday	11:35:53.0000000	17	825
2008-05-02	Friday	11:35:53.0000000	23	920
2008-05-02	Friday	11:35:53.0000000	34	708
2008-05-02	Friday	11:35:53.0000000	39	1101
2008-05-02	Friday	11:35:53.0000000	48	1366
2008-05-02	Friday	11:35:53.0000000	49      4457


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-10-21 : 14:35:23
You didn't post your queries.....come on now

And let's discuss two parts

You have


Date            Day			End_time				RID			Volume
2008-05-02		Friday		11:20:43.0000000		2			890

Date            Day			End_time				RID			Volume
2008-05-02		Friday		11:35:53.0000000		2			728


And want


Date            Day			End_time				RID			Volume		End Time 2			RID 2
2008-05-02		Friday		11:20:43.0000000		2			890			11:35:53.0000000	728



Combine would be the wrong word.....

What if you had 10 RID's and End Times?





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

uday424
Starting Member

6 Posts
Posted - 2009-10-21 : 14:41:32
These are the two tables and their related queries:

Date            Day     Start_time             RID     Volume

2008-05-02	Friday	11:20:43.0000000	2	890
2008-05-02	Friday	11:20:43.0000000	17	1094
2008-05-02	Friday	11:20:43.0000000	23	1030
2008-05-02	Friday	11:20:43.0000000	34	825
2008-05-02	Friday	11:20:43.0000000	39	1320
2008-05-02	Friday	11:20:43.0000000	48	1862
2008-05-02	Friday	11:20:43.0000000	49	5290



Date            Day       End_time             RID      Volume

2008-05-02	Friday	11:35:53.0000000	2	728
2008-05-02	Friday	11:35:53.0000000	17	825
2008-05-02	Friday	11:35:53.0000000	23	920
2008-05-02	Friday	11:35:53.0000000	34	708
2008-05-02	Friday	11:35:53.0000000	39	1101
2008-05-02	Friday	11:35:53.0000000	48	1366
2008-05-02	Friday	11:35:53.0000000	49      4457


select date, DATENAME(dw, Date) as Day, time as Start_time, rid, sum(volume) as Sum_of_volume
from t_2009 where time = '11:20:43'
group by rid, time, date
order by date, rid, time

select date, DATENAME(dw, Date) as Day, time as End_time, rid, sum(volume) as Sum_of_volume
from t_2009 where time = '11:35:53'
group by rid, time, date
order by date, rid, time



I want the table to be like this. I already posted the two tables and their related queries. 

Date        Day   Start_time      RID Volume    End_time     Volume

2008-05-02 Friday 11:20:43.0000000 2	890  11:35:53.0000000 728
2008-05-02 Friday 11:20:43.0000000 17	1094 11:35:53.0000000 825
2008-05-02 Friday 11:20:43.0000000 23	1030 11:35:53.0000000 920
2008-05-02 Friday 11:20:43.0000000 34	825  11:35:53.0000000 708
2008-05-02 Friday 11:20:43.0000000 39	1320 11:35:53.0000000 1101
2008-05-02 Friday 11:20:43.0000000 48	1862 11:35:53.0000000 1366
2008-05-02 Friday 11:20:43.0000000 49	5290 11:35:53.0000000 4457
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-10-21 : 14:47:23
How can [Date] Just be 2008-05-02 From your query

Is is it varchar or char?




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

uday424
Starting Member

6 Posts
Posted - 2009-10-21 : 14:50:21
it is nvarchar(10), but i guess it has got nothing to do with this issue right!!!
Go to Top of Page

uday424
Starting Member

6 Posts
Posted - 2009-10-21 : 15:41:09
Please help me Brett...
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-10-21 : 15:41:42
k

Try this


SELECT
		  [Date]
		, DATENAME(dw, [Date]) AS [Day]
		, rid_Start
		, [Time] AS Start_Time
		, SUM(volume) AS SUM_of_volume_Start
		, rid_End
		, [Time] AS End_Time
		, SUM(volume) AS SUM_of_volume_End
FROM (
  SELECT 
		  [Date]
		, rid AS Rid_Start
		, [Time] AS Start_Time
		, DATENAME(dw, [Date]) AS [Day]
		, SUM(volume) AS SUM_of_volume_Start
	FROM  t_2009 WHERE [Time] = '11:20:43'
GROUP BY  [Date], rid   , [Time]
) AS s
INNER JOIN (
  SELECT 
		  [Date]
		, rid AS Rid_End
		, [Time] AS End_Time
		, DATENAME(dw, [Date]) AS [Day]
		, SUM(volume) AS SUM_of_volume_End
	FROM  t_2009 WHERE [Time] = '11:35:53'
GROUP BY  [Date], rid   , [Time]
) AS e
on s.[Date] = e.[Date]




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

uday424
Starting Member

6 Posts
Posted - 2009-10-21 : 15:58:01
Thanks for the quick reply , Brett, but it not leading me to the required result.

I executed the query which u posted with slight modifications. Below is the query and its result.



SELECT [Date], DATENAME(dw, [Date]) AS [Day], Rid_Start, Start_Time,
SUM_of_volume_Start, Rid_End, End_Time, SUM_of_volume_End
FROM (
SELECT
distinct[Date] as Date1
, rid AS Rid_Start
, [Time] AS Start_Time
, DATENAME(dw, [Date]) AS [Day]
, SUM(volume) AS SUM_of_volume_Start
FROM t_2009_WHERE [Time] = '11:20:43'
GROUP BY [Date], rid , [Time]
) AS s
INNER JOIN (
SELECT
distinct[Date]
, rid AS Rid_End
, [Time] AS End_Time
, DATENAME(dw, [Date]) AS [Day]
, SUM(volume) AS SUM_of_volume_End
FROM t_2009 WHERE [Time] = '11:35:53'
GROUP BY [Date], rid , [Time]
) AS e
on s.Date1 = e.Date



2008-05-02	Friday	2	11:20:43.0000000	890	2 11:35:53.0000000	728
2008-05-02	Friday	2	11:20:43.0000000	890	17	11:35:53.0000000	825
2008-05-02	Friday	2	11:20:43.0000000	890	23	11:35:53.0000000	920
2008-05-02	Friday	2	11:20:43.0000000	890	34	11:35:53.0000000	708
2008-05-02	Friday	2	11:20:43.0000000	890	39	11:35:53.0000000	1101
2008-05-02	Friday	2	11:20:43.0000000	890	48	11:35:53.0000000	1366
2008-05-02	Friday	2	11:20:43.0000000	890	444	11:35:53.0000000	105
2008-05-02	Friday	2	11:20:43.0000000	890	427	11:35:53.0000000	171
2008-05-02	Friday	2	11:20:43.0000000	890	431	11:35:53.0000000	150
2008-05-02	Friday	2	11:20:43.0000000	890	432	11:35:53.0000000	152
2008-05-02	Friday	2	11:20:43.0000000	890	435	11:35:53.0000000	0
2008-05-02	Friday	2	11:20:43.0000000	890	436	11:35:53.0000000	160
2008-05-02	Friday	2	11:20:43.0000000	890	439	11:35:53.0000000	92
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2009-10-21 : 17:55:22
Aren't the basically the same?


And all you have to do is move around columns?

What's wrong with it?

Also, the queries really don't make much sense



Date 		Day 	Start_time 	 RID 	Volume  End_time 	 Volume

2008-05-02 	Friday 	11:20:43.0000000 2 	890 	11:35:53.0000000 728


Date 		Day 	RID_Start	Start_time 	 		Volume_Start

2008-05-02	Friday	2		11:20:43.0000000 		890	

			RID_End	End_time 	 			Volume_End
			2 		11:35:53.0000000 		728



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site