|
Well say if i put in 01/01/2008 - 01/05/2008 it pulls back records as there are ones within that range. (But also will not display those that dont have anything as 0, just wont display) But say i put in 01/05/2008 - 02/05/2008 then it pulls back nothing
|
|
|
|
|
between dates 01/05/2008 - 02/05/2008 you need this result :
DTID DrugTherapy CountNumbers<br />
2 Drug2 0<br />
3 Drug3 0<br />
1 Drug1 5<br />
4 Drug4 1
I Love T-SQL
|
|
|
|
|
Yes, so if there is none made, it to display a 0 so i can display reports on a page.
Thanks
|
|
|
|
|
change date format to dd/MM/yyyy from regoinal Settings on control panel, then
on date parameters ad values like this '01/01/2008' - '02/01/2008'
by that changes,query works perfeclty for me
here it is
SELECT CASE WHEN dbo.ReferralDrugTherapy.DTID IS NULL THEN
(SELECT TOP 1 t1.DTID
FROM DrugTherapy AS t1
WHERE t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, dbo.DrugTherapy.DrugTherapy,
COUNT(dbo.ReferralDrugTherapy.DTID) AS numbers
FROM dbo.ReferralDrugTherapy FULL OUTER JOIN
dbo.DrugTherapy ON dbo.ReferralDrugTherapy.DTID = dbo.DrugTherapy.DTID
left outer join CardiacReferrals on dbo.ReferralDrugTherapy.referralid = CardiacReferrals.referralid
and CardiacReferrals.datesubmitted between '01/01/2008' and '01/05/2008'
GROUP BY dbo.ReferralDrugTherapy.DTID, dbo.DrugTherapy.DrugTherapy, dbo.DrugTherapy.DTID
I Love T-SQL
|
|
|
|
|
My date settings are already set to that.
The query that you have given me also just gives the total made, no matter what dates you enter you always get the total, it doesnt calculate any differences.
Thanks for your help so far!
|
|
|
|
|
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
|
|
|
|
|
I guess this time I got the right solution
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"
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|