Database Discussion Boards

Re: Help performing a count

AdamskiR1-May-08 23:31

AdamskiR

1-May-08 23:31

Hi and thanks.

Afraid not though....

Re: Help performing a count

Blue_Boy1-May-08 23:41

Blue_Boy

1-May-08 23:41

Sorry for my unusefull answers.

I Love T-SQL

Re: Help performing a count

AdamskiR1-May-08 23:42

AdamskiR

1-May-08 23:42

No need to apologies, your help has been much appreciated, your not the only person it seems to have stumped! Sigh | :sigh:

Re: Help performing a count

Blue_Boy1-May-08 23:50

Blue_Boy

1-May-08 23:50

AdamskiR wrote:
your help has been much appreciated

It's my pleasure trying to help others...

Let me know if you find solution? If you have time to post explanation again then do it and after couple of hours I will try again to find solution.

If you post explanation write data how are stored on table, and write result which you want to get. Smile | :)

I Love T-SQL

Re: Help performing a count

AdamskiR9-May-08 4:08

AdamskiR

9-May-08 4:08

We got there in the end!

SELECT     d.DrugTherapy, ISNULL(c.count, 0) AS count<br />
FROM         DrugTherapy AS d LEFT OUTER JOIN<br />
                          (SELECT     COUNT(ReferralDrugTherapy.DTRID) AS count, ReferralDrugTherapy.DTID<br />
                            FROM          CardiacReferrals INNER JOIN<br />
                                                   ReferralDrugTherapy ON CardiacReferrals.ReferralID = ReferralDrugTherapy.ReferralID<br />
                            WHERE      (CardiacReferrals.DateSubmitted BETWEEN @from AND @to)<br />
                            GROUP BY ReferralDrugTherapy.DTID) AS c ON c.DTID = d.DTID

Many many thanks to everyone $Shucks | :-\$

Re: Help performing a count

Blue_Boy9-May-08 7:09

Blue_Boy

9-May-08 7:09

Cool,Great and Good News... Big Grin | :-D

I Love T-SQL

Don't torture yourself,let the life to do it for you.

HOW TO MERGE IN ONE!

kibromg24-Apr-08 4:25

kibromg

24-Apr-08 4:25

Hi All,

I want to count records any time from Mid day(12:00:00.000') of a given date up to the next day MID Day get all the count in to the first date.

For instance for a date '2008-04-23' count is made between '2008-04-23 12:00:00.000 and 2008-04-24 12:00:00.000'.

If my date Query is Between '2008-04-20' and '2008-04-23' then

1. '2008-04-20' --------- count on '2008-04-20 12:00:00.000 and 2008-04-21 12:00:00.000'.
2. '2008-04-21' ---------- count on '2008-04-21 12:00:00.000 and 2008-04-22 12:00:00.000'.
etc..
Can you give me a script for this one?

Thank you
regards

Re: HOW TO MERGE IN ONE! [modified]

Blue_Boy24-Apr-08 9:52

Blue_Boy

24-Apr-08 9:52

hope this will help you...

select count(*) from myTable <br />
where [columndate] >= substring(convert(varchar,columnName,20),1,10)+' 12:00:00.000' and<br />
[columndate] <= substring(convert(varchar,columnName,20),1,10)+' 12:00:00.000'

I Love T-SQL

modified on Thursday, April 24, 2008 4:26 PM

SQL 2000 ----- Extracting Rows from Delimited Strings

n1coltsfan24-Apr-08 2:35

n1coltsfan

24-Apr-08 2:35

The query I am passing the two letter strings like "UV+ 131.0000+ 0.0000" work fine when it goes to 3 and up like "TLOV+2048.0000+ 0.0000" it starts messing up the right and middle strings and showing the + sign delimiter.

Please Help !!!

select *,left(raw_payload,charindex('+',raw_payload,1)-1),
substring(left(raw_payload, 1-len(left(raw_payload,charindex('+',raw_payload,1)+1)),
len(raw_payload)),charindex('+',raw_payload,1)* charindex('+',raw_payload,1)-1 ),substring(right(raw_payload,charindex('+',raw_payload,1)*3),charindex('+',right(raw_payload,charindex('+',raw_payload,1)*3),1)+1,len(raw_payload))
from raw_data.dbo

Fieldname: raw_payload

"S9000+"
"UV+ 131.0000+ 0.0000"
"UV+ 132.0000+ 0.0000"
"UV+ 140.0000+ 0.0000"
"UV+ 141.0000+ 0.0000"
"UV+ 142.0000+ 0.0000"
"UVN+ 524.0000+ 0.0000"
"UVN+ 525.0000+ 0.0000"
"UVN+ 527.0000+ 0.0000"
"UVN+ 528.0000+ 0.0000
"TLOV+2044.0000+ 0.0000"
"TLOV+2045.0000+ 0.0000"
"TLOV+2046.0000+ 0.0000"
"TLOV+2047.0000+ 0.0000"
"TLOV+2048.0000+ 0.0000"
"TLOV+2049.0000+ 0.0000"
"OTLWV+2201.0000+ 0.0000"
"OTLWV+2202.0000+ 0.0000"
"OTD/ROV+2401.0000+ 0.0000"
"OTD/ROV+2402.0000+ 0.0000"
"OTD/ROV+2403.0000+ 0.0000"
"OTD/ROV+2404.0000+ 0.0000"
""OTD/ROV+2432.0000+ 0.0000"
"OTD/ROV+2436.0000+ 0.0000"
"OTD/ROV+2443.0000+ 0.0000"
"OTD/ROV+2444.0000+ 0.0000"
"OTD/ROV+2445.0000+ 0.0000"
"OTD/ROV+2446.0000+ 0.0000"
"G59WO+5324.0000+ 36.6190"
"TLMLV+5601.0000+ 0.0000"
"TLMLV+5602.0000+ 20.0000"
"TLMLV+5603.0000+ 0.0000"
"TLMLV+5605.0000+ 0.0000"
"TLMLV+5606.0000+ 75.0000"
"TLMLV+5607.0000+ 175.0000"

Re: SQL 2000 ----- Extracting Rows from Delimited Strings

Blue_Boy24-Apr-08 8:36

Blue_Boy

24-Apr-08 8:36

dude I can give you solution but it is "terrible code" how did i solved.
here it is

select *,<br />
substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1))) as a1,<br />
<br />
left(substring(raw_payload,3+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload)),<br />
charindex('+',<br />
substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload)))) as a2,<br />
<br />
substring(<br />
substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1))))+<br />
len(left(substring(raw_payload,3+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload)),<br />
charindex('+',<br />
substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload))))),len(raw_payload)),1,<br />
len(substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1))))+<br />
len(left(substring(raw_payload,3+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload)),<br />
charindex('+',<br />
substring(raw_payload,4+len(substring(left(raw_payload,charindex('+',raw_payload)-1),2,len(left(raw_payload,charindex('+',raw_payload)-1)))),len(raw_payload))))),len(raw_payload)))-1) as a3<br />
 from  raw_data.dbo

I Love T-SQL

Re: SQL 2000 ----- Extracting Rows from Delimited Strings

n1coltsfan25-Apr-08 4:42

n1coltsfan

25-Apr-08 4:42

left and middle work great right or the third column does not...

Re: SQL 2000 ----- Extracting Rows from Delimited Strings

Blue_Boy25-Apr-08 5:01

Blue_Boy

25-Apr-08 5:01

can you fix that with C# code or you must to fix it with T-SQL?

I Love T-SQL

Selection between ranges in sql server 2005

salon24-Apr-08 2:00

salon

24-Apr-08 2:00

I want to select data from ranges like

Column1 Column2
25000 10
50000 20
75000 30

I want records like upto 25000, I get 10
upto 50000, I get 20
upto or more than 75000, I get 30

Can anybody suggest me a simple query for that?

Thanks in advance,

Re: Selection between ranges in sql server 2005

Broken Bokken24-Apr-08 2:37

Broken Bokken

24-Apr-08 2:37

Use a sql case statement. http://doc.ddart.net/mssql/sql70/ca-co.htm[^]

SQL

SELECT Column1, 
CASE
WHEN Column1 <= 25000 THEN 10
WHEN Column1 > 25000 AND Column1 <= 50000 THEN 20
ELSE 30
END AS Column2
FROM MyTable

Broken Bokken

You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod

http://www.brokenbokken.com

Count number of records in related tables

Kevin Brydon24-Apr-08 0:54

Kevin Brydon

24-Apr-08 0:54

Hi.

I have database structure like this:

Organizations(OrganizationID, Name)
Members(MemberID, OrganizationID, Name)
Courses(CourseID, OrganizationID, Name)

I need to return the Organization Name, the number of members of that organization and the number of courses in that organization in one sql statement so I'd have a table like

[OrganizationName] [Number of Members] [Number of courses]
[Org A] [10] [22]
[Org B] [92] [11]
...

Is it possible without using a stored procedure?

Can anyone help me out? Any help would be much appreciated.

Re: Count number of records in related tables

Ashfield24-Apr-08 1:30

Ashfield

24-Apr-08 1:30

One way:

select o.Name, (select count(*) as MembersCount from Members m where m.OrganizationID= o.OrganizationID),(select count(*) as CoursesCount from Courses c where c.OrganizationID = o.OrganizationID)<br />
from Organization o

There are other alternatives depending on what database you are using

Bob
Ashfield Consultants Ltd

Re: Count number of records in related tables

Kevin Brydon24-Apr-08 2:49

Kevin Brydon

24-Apr-08 2:49

Great. Thanks very much!

Re: Count number of records in related tables

Ashfield24-Apr-08 3:18

Ashfield

24-Apr-08 3:18

No problem.

Bob
Ashfield Consultants Ltd

Diff b/W ADO & ADO.net

sonia.sardana23-Apr-08 23:46

sonia.sardana

23-Apr-08 23:46

I know only two differneces.
1) ADO wroks on Connected architecture, while ADO.Net on DisConnected Architecture.
2)ADO is used to create Client Side Cursors while ADo.net create both client as well as server.

IF FRNDS U KNOW DIFFERENCES OTHER THAN THESE PLZ DO REPLY

Re: Diff b/W ADO & ADO.net

Mycroft Holmes24-Apr-08 20:19

Mycroft Holmes

24-Apr-08 20:19

Notice the deluge of responses your question elicited.

1. Learn to spell
2. Learn to type english - your location is the US so presumable english is your 1st language
3. Learn to use google - see the 2nd response to this
clickety[^]

Never underestimate the power of human stupidity
RAH

Timeout Exception [modified]

Vimalsoft(Pty) Ltd23-Apr-08 23:25

Vimalsoft(Pty) Ltd

23-Apr-08 23:25

Good Morning All

I have a ASP Classic Application,that Connects to SQL Server, Sudddenly it gives a Timeout Exception like this

[Microsoft][ODBC SQL Server] Timeout Expired

What is Wrong

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

modified on Thursday, April 24, 2008 6:06 AM

Re: Timeout Exception

Mike Dimmick24-Apr-08 4:15

Mike Dimmick

24-Apr-08 4:15

To stop queries from taking a very long time to return and consuming resources on the server unnecessarily, and to stop them from blocking client software for an unbounded length of time, ODBC and other database communication libraries include timeouts on both the connection to the server and on executing a command.

Given your description I'd imagine you're using the ADO objects with the MSDASQL provider, possibly through a DSN, using the Microsoft SQL Server ODBC driver. You might get better results using the SQLOLEDB provider to remove some of the layers. In either case, you should be able to use the Command object's CommandTimeout property to set a more sensible timeout for your operation. The default timeout is 30 seconds.

If instead you're looking to reduce the amount of time taken to execute a given query, you need to look carefully at how the query is written and what indexes you have to support that query. Ideally you want a single index which contains all the columns that appear in your WHERE clause which are searchable (that is, using simple comparison operators to compare the column's actual value against some provided value). LIKE is searchable if you're searching for a prefix, that is, the wildcards appear after some static text (e.g. 'CP%'). Do be aware that the more indexes you have, the longer INSERT and UPDATE operations will take. Generally you have to plan your set of indexes across all the operations that will take place on a table.

Finally one reason that queries start to take longer amounts of time is if historical data accumulates in the table. Consider archiving data to a different table or database when it is no longer current, or even deleting it outright.

DoEvents: Generating unexpected recursion since 1991

Re: Timeout Exception

Vimalsoft(Pty) Ltd24-Apr-08 20:09

Vimalsoft(Pty) Ltd

24-Apr-08 20:09

Thanks

i will look into that

Re: Timeout Exception

GuyThiebaut24-Apr-08 5:52

GuyThiebaut

24-Apr-08 5:52

I think Mike has explained things very well - one thing I will add is that the setting timeout value to 0(zero) will usually set it to an infinite timeout i.e. you will not get a timeout (you may need to check your documentation on this).

We do this on our Access databases as ODBC can be very slow.

Regards

Guy

Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)

Re: Timeout Exception

Vimalsoft(Pty) Ltd24-Apr-08 20:03

Vimalsoft(Pty) Ltd

24-Apr-08 20:03

Thank you very much guys,

This Application was wrote in ASP Classic and i have no control over the code, and i always set the timeout of the command object to "0" for infinite.

Thanks for the Advice it make sense..

Thanks

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.