Database

Blue_Boy25-Apr-08 5:00

25-Apr-08 5:00

I think I got it (just change date values in according to your format string, instead yyyy-MM-dd set your format)

SELECT     CASE WHEN dbo.ReferralDrugTherapy.DTID IS NULL THEN<br />
                          (SELECT     TOP 1 t1.DTID<br />
                            FROM          DrugTherapy AS t1<br />
                            WHERE      t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, dbo.DrugTherapy.DrugTherapy, <br />
                      COUNT(dbo.ReferralDrugTherapy.DTID) AS numbers<br />
FROM         dbo.ReferralDrugTherapy FULL OUTER JOIN<br />
                      dbo.DrugTherapy ON dbo.ReferralDrugTherapy.DTID = dbo.DrugTherapy.DTID  <br />
<br />
			left outer join  CardiacReferrals on  dbo.ReferralDrugTherapy.referralid = CardiacReferrals.referralid<br />
			and CardiacReferrals.datesubmitted between '2008-01-01' and '2008-02-21'			<br />
GROUP BY dbo.ReferralDrugTherapy.DTID, dbo.DrugTherapy.DrugTherapy, dbo.DrugTherapy.DTID

I Love T-SQL

AdamskiR25-Apr-08 5:13

25-Apr-08 5:13

Not quite, it doesnt seem to matter what date range you put it, it always pulls back all the results for each drug type. I tried changing it slightly so the dates in a where to

SELECT     CASE WHEN dbo.ReferralDrugTherapy.DTID IS NULL THEN<br />
                          (SELECT     TOP 1 t1.DTID<br />
                            FROM          DrugTherapy AS t1<br />
                            WHERE      t1.DrugTherapy IN (dbo.DrugTherapy.DrugTherapy)) ELSE dbo.ReferralDrugTherapy.DTID END AS dtid, DrugTherapy.DrugTherapy, <br />
                      COUNT(ReferralDrugTherapy.DTID) AS numbers<br />
FROM         ReferralDrugTherapy FULL OUTER JOIN<br />
                      DrugTherapy ON ReferralDrugTherapy.DTID = DrugTherapy.DTID LEFT OUTER JOIN<br />
                      CardiacReferrals ON ReferralDrugTherapy.ReferralID = CardiacReferrals.ReferralID<br />
WHERE     (CardiacReferrals.DateSubmitted BETWEEN @from AND @to)<br />
GROUP BY ReferralDrugTherapy.DTID, DrugTherapy.DrugTherapy, DrugTherapy.DTID

but all that did was give me the same problem where if it doesnt find anything it brings back nothing instead of 0 values. Argh! WTF | :WTF:

Blue_Boy25-Apr-08 5:17

25-Apr-08 5:17

write date values which you are giving?

I Love T-SQL

AdamskiR25-Apr-08 5:19

25-Apr-08 5:19

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

Blue_Boy25-Apr-08 5:29

25-Apr-08 5:29

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

AdamskiR25-Apr-08 5:32

25-Apr-08 5:32

Yes, so if there is none made, it to display a 0 so i can display reports on a page.

Thanks

Blue_Boy25-Apr-08 5:35

25-Apr-08 5:35

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

AdamskiR25-Apr-08 5:45

25-Apr-08 5:45

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!

Blue_Boy25-Apr-08 5:54

25-Apr-08 5:54

I will lookup later again and I will notify you

I Love T-SQL

AdamskiR25-Apr-08 5:56

25-Apr-08 5:56

Many thanks, if i crack it in the mean time without blowing my head off ill let you know!

Blue_Boy25-Apr-08 6:00

25-Apr-08 6:00

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

Blue_Boy25-Apr-08 9:10

25-Apr-08 9:10

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

AdamskiR1-May-08 23:31

1-May-08 23:31

Hi and thanks.

Afraid not though....

Blue_Boy1-May-08 23:41

1-May-08 23:41

Sorry for my unusefull answers.

I Love T-SQL

AdamskiR1-May-08 23:42

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:

Blue_Boy1-May-08 23:50

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

AdamskiR9-May-08 4:08

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 | :-\$

Blue_Boy9-May-08 7:09

Re: HOW TO MERGE IN ONE! [modified]

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

Blue_Boy24-Apr-08 9:52

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

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

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

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

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

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