Query help - 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 2000 Forums

SQL Server Development (2000)

Query help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-08-08 : 11:16:59

[code]
DECLARE @MT TABLE (TNO INT, TID1 INT,TID2 INT,TID3 INT,TID4 INT,TID5 INT,TID6 INT,TID7 int, Dt DATETIME)
INSERT @MT
SELECT '256' , '1', '1','1','1','1','1','1','5-4-2007' UNION ALL
SELECT '257', '1', '','','','','','', '5-4-2007' UNION ALL
SELECT '258','', '1','','','','','', '6-4-2007' UNION ALL
SELECT '259', '1', '1','1','','','','', '7-4-2007' UNION ALL
SELECT '259', '', '','','','1','','','4-4-2007' UNION ALL
SELECT '260', '', '','1','','','','','7-4-2007' UNION ALL
SELECT '261','1', '','','','','','','5-4-2007' UNION ALL
SELECT '262', '1', '','','','','','1','6-4-2007' UNION ALL
SELECT '263', '', '','','1','1','1','1','7-4-2007'

--select * from @MT

Condition:If there is data in more than one columns of (TID1,TID2,TID3,TID4,TID5,TID6,TID7)
Then I want the output to be count 1.
Please let me know if am not clear.

select * from @MT
where dt between '2007-04-04 00:00:00.000' and '2007-05-04 00:00:00.000'

For the above code i get the below output

TNO TID1 TID2 TID3 TID4 TID5 TID6 TID7 Dt
--- ---- --- -- ----- --- ----- -- ---------------
256 1 1 1 1 1 1 1 2007-05-04 00:00:00.000
257 1 0 0 0 0 0 0 2007-05-04 00:00:00.000
259 0 0 0 0 1 0 0 2007-04-04 00:00:00.000
261 1 0 0 0 0 0 0 2007-05-04 00:00:00.000

for the above the results

I need an output with counts:

BookDesc count
------------- -----
Morethanonebook 1

Since for the Tno 256 there are more than one columns have the data so i want the output to be count 1.[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-08 : 11:19:53

where is the BookDesc column comes from ?

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-08-08 : 11:46:51

Its a header i have put it..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-08 : 11:51:05

[code]SELECT [BookDesc] = 'Morethanonebook', [COUNT] = COUNT(*)
FROM @MT
WHERE Dt BETWEEN '2007-04-04 00:00:00.000' AND '2007-05-04 00:00:00.000'
AND (TID1 + TID2 + TID3 + TID4 + TID5 + TID6 + TID7) > 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-08-08 : 20:10:26

Thanks Khtan!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources