Help :Is this Query right? How to Get the Result:

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 :Is this Query right? How to Get the Result:

Author

Topic

nithin11
Starting Member

6 Posts

Posted - 2010-09-27 : 03:25:43

TABLE_1 STRUCTURE:
ID varchar(50) PRIMARYKEY
STATE varchar(50)
TIME datetime

TABLE_2 STRUCTURE:
ID varchar(50) PRIMARYKEY
PRTY varchar(50)

TABLE_1 SAMPLE DATA:
ID STATE TIME
1 N 2010-09-24 00:00:00.000
2 E 2010-09-23 00:00:00.000
3 N 2010-09-22 00:00:00.000
4 N 2010-09-17 00:00:00.000
5 E 2010-09-16 00:00:00.000
6 E 2010-09-14 00:00:00.000
7 N 2010-09-14 00:00:00.000
8 N 2010-09-11 00:00:00.000
9 N 2010-09-09 00:00:00.000

TABLE_2 SAMPLE DATA:
ID PRTY
1 F
2 F
3 F
4 F
5 F
6 F
7 F
8 F
9 F

QUERY1:

SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T1_TIME DESC

GETTING RESULT:
T1_COUNT T1_TIME
3 2010-09-25
4 2010-09-18
2 2010-09-11

QUERY2:

SELECT COUNT(T1.ID) AS T2_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw,T1.TIME)), T1.TIME),101) AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T2_TIME DESC

GETTING RESULT:
T2_COUNT T2_TIME
1 2010-09-25
2 2010-09-18

Sample Query:
SELECT * FROM (SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) UNION SELECT COUNT(T1.ID) AS T2_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw,T1.TIME)), T1.TIME),101) AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101)) AS inLineView ORDER BY T1_TIME DESC

RESULT:
T1_COUNT T1_TIME
1 2010-09-25
3 2010-09-25
2 2010-09-18
4 2010-09-18
2 2010-09-11
(Please Give me idea or correct the sample Query to get Result like follows)
NEEDED RESULT:
T1_COUNT T1_TIME T2_COUNT T2_TIME
1 2010-09-25 3 2010-09-25
2 2010-09-18 4 2010-09-18
2 2010-09-11

Thanks,
Nithin

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-27 : 04:02:11

Please post table structures and sample data for clear understanding of requirement to help you.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-27 : 06:05:30

quote:
Originally posted by nithin11

Sample Query:

Thanks,
Nithin

No query - no result

No, you're never too old to Yak'n'Roll if you're too young to die.

nithin11
Starting Member

6 Posts

Posted - 2010-09-27 : 07:08:03

nithin11
Starting Member

6 Posts

Posted - 2010-09-27 : 07:14:05

quote:
Originally posted by webfred

quote:
Originally posted by nithin11

Sample Query:

Thanks,
Nithin

No query - no result

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources