|
 Sorry for my unusefull answers.
I Love T-SQL
|
|
|
|
|
No need to apologies, your help has been much appreciated, your not the only person it seems to have stumped! 
|
|
|
|
|
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. 
I Love T-SQL
|
|
|
|
|
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 
|
|
|
|
|
Cool,Great and Good News... 
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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"
|
|
|
|
|
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
|
|
|
|
|
left and middle work great right or the third column does not...
|
|
|
|
|
can you fix that with C# code or you must to fix it with T-SQL?
I Love T-SQL
|
|
|
|
|
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,
|
|
|
|
|
Use a sql case statement. http://doc.ddart.net/mssql/sql70/ca-co.htm[^]
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
|
No problem.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Thanks
i will look into that
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
|
|
|
|
|
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)
|
|
|
|
|
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
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
|
|
|
|
|
I am working on a database create script for SQL server 2005. It's getting to be pretty large (20 tables so far) and the gist of the script is to dropall the tables, create the tables, and then add in any default data. I have the last two pieces working correctly, and previously I was dropping each table individually. I found some code to help drop all tables and modified it.
DECLARE @Count int
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
WHILE @Count > 0
BEGIN
EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'
END
IF @Count = 0
BEGIN
SET @Message = '--- Tables dropped successfully ---'
END
ELSE
BEGIN
SET @Message = '--- Error dropping tables ---'
END
PRINT @Message
The problem is that because I have foreign keys, the scrip throws errors. I tried a try-catch but then the query hangs. Ideally, I want the while loop to ignore errors, or at least not display them so that any errors displayed are errors with the create script. I do not want the exec within the while to rollback, which is what I believe the try-catch is doing. Any ideas how to do this?
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
