Select 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)

Select query

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2011-08-11 : 10:01:30

I have the following two table.
CREATE TABLE #TEMP1(MID INT,MNO VARCHAR(50),MDATE DATETIME,MSTOP INT,MMILES VARCHAR(50),MHRS VARCHAR(50),
OSTART INT,OEND INT,DAAM DATETIME,DDPM DATETIME,SH BIT)

CREATE TABLE #TEMP2(STID INT,STNAME VARCHAR(50),MID INT)

INSERT INTO #TEMP1 VALUES(1,'PI0805201101','08/02/2011',10,'77056','42:16',1046,2101,'8/02/2010 06:00:00 AM','8/02/2011 7:15:00 PM',1)
INSERT INTO #TEMP1 VALUES(2,'PI0805201102','08/03/2011',5,'46056','24:19',186,251,'8/03/2010 06:30:29 AM','8/03/2011 7:00:00 PM',1)
INSERT INTO #TEMP1 VALUES(3,'PI0805201103','08/04/2011',6,'20466','12:11',10465,21016,'8/04/2010 08:00:00 AM','8/04/2011 2:30:00 PM',1)
INSERT INTO #TEMP1 VALUES(4,'PI0805201104','08/04/2011',2,'104656','08:00',6046,9101,'8/04/2010 07:30:00 AM','8/04/2011 3:30:00 PM',1)

INSERT INTO #TEMP1 VALUES(5,'PI0805201105','08/03/2011',2,'656','07:00',100,800,'8/03/2010 07:30:00 AM','8/03/2011 3:30:00 PM',0)

INSERT INTO #TEMP2 VALUES(1,'NAME1',1)
INSERT INTO #TEMP2 VALUES(2,'NAME2',1)
INSERT INTO #TEMP2 VALUES(3,'NAME3',1)
INSERT INTO #TEMP2 VALUES(4,'NAME4',1)
INSERT INTO #TEMP2 VALUES(5,'NAME5',1)
INSERT INTO #TEMP2 VALUES(1,'FNAME1',2)
INSERT INTO #TEMP2 VALUES(2,'FNAME2',2)
INSERT INTO #TEMP2 VALUES(3,'FNAME3',2)
INSERT INTO #TEMP2 VALUES(1,'ENAME1',3)
INSERT INTO #TEMP2 VALUES(2,'ENAME2',3)
INSERT INTO #TEMP2 VALUES(3,'ENAME3',3)
INSERT INTO #TEMP2 VALUES(4,'ENAME4',3)
INSERT INTO #TEMP2 VALUES(1,'LNAME1',4)
INSERT INTO #TEMP2 VALUES(2,'LNAME2',4)
INSERT INTO #TEMP2 VALUES(1,'LNAME5',5)

Now i need the following result

MDATE,STIDCOUNT,MIDCOUNT,(OEND-OSTART),(DDPM-DAAM),MSTOP,MMILES,MHRS,SH
08/02/2011,5,1,1055,13:15,10,77056,42:16,1
08/03/2011,1,1,700,08:00,2,656,07:00,0
08/03/2011,3,1,65,12:30,5,46056,24:19,1
08/04/2011,6,2,13606,14:30,8,125122,20:11,1

The columns are separated by commas.IF the SH flag is diiferent in one date we can show it as two rows.IF the SH flag is same in one date we can show a single row.

Please help me.Also reply if it is not possible.

Thanks & Regards
Binto Thomas

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-11 : 10:21:16

Is this is a homework problem?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163951&SearchTerms=STIDCOUNT

Jim

Everyday I learn something that somebody else already knew

binto
Yak Posting Veteran

59 Posts

Posted - 2011-08-12 : 08:21:56

That result is wrong.

Thanks & Regards
Binto Thomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources