|
I will lookup later again and I will notify you
I Love T-SQL
|
|
|
|
|
Many thanks, if i crack it in the mean time without blowing my head off ill let you know!
|
|
|
|
|
ok, if I crack it too I will tell you. On my mind i have solution but I must seriozly deal with it, coz until now I didn't have time to deal with it seriozly.
See u later.
I Love T-SQL
|
|
|
|
|
declare @fromdate as varchar(15)<br />
declare @todate as varchar(15)<br />
set @fromdate = '01/02/2008'<br />
set @todate = '05/02/2008'<br />
select ReferralDrugTherapy.dtid ,count(ReferralDrugTherapy.dtid) as countAll,DrugTherapy.DrugTherapy <br />
from ReferralDrugTherapy,CardiacReferrals,DrugTherapy<br />
where ReferralDrugTherapy.referralid =CardiacReferrals.referralid and datesubmitted <br />
between convert(varchar,@fromdate,103) and convert(varchar,@todate,103) and DrugTherapy.dtid = ReferralDrugTherapy.dtid<br />
group by ReferralDrugTherapy.dtid ,DrugTherapy.DrugTherapy<br />
union all<br />
select dtid,0,DrugTherapy.DrugTherapy from DrugTherapy where dtid not in (<br />
select ReferralDrugTherapy.dtid from ReferralDrugTherapy,CardiacReferrals,DrugTherapy<br />
where ReferralDrugTherapy.referralid =CardiacReferrals.referralid and datesubmitted <br />
between convert(varchar,@fromdate,103) and convert(varchar,@todate,103) and DrugTherapy.dtid = ReferralDrugTherapy.dtid<br />
group by ReferralDrugTherapy.dtid ,DrugTherapy.DrugTherapy)<br />
order by ReferralDrugTherapy.dtid asc
I Love T-SQL
|
|
|
|
|
Hi and thanks.
Afraid not though....
|
|
|
|
|
 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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
