Help with CURRENT DATE Query

Hello, I have a table that lists my bank transactions. I have a uniqueid in
the first field and several other fields...one of which is DATE... now, I
want to run a query where my view is based upon the DATE. however I want to
only show transactions that are the same date as the system date (this is to
see how much work my employees have done). Please see my example below, this
code works...
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE ([date] = '9/9/2007')
However, where it has 9/9/2007, I want it to be TODAYS Date
Thanks for your help in advance!WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
"SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> Hello, I have a table that lists my bank transactions. I have a uniqueid
> in
> the first field and several other fields...one of which is DATE... now,
> I
> want to run a query where my view is based upon the DATE. however I want
> to
> only show transactions that are the same date as the system date (this is
> to
> see how much work my employees have done). Please see my example below,
> this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Try:
SELECT transid, [date], description, amt, taxamt
FROM dbo.taxtransactions
WHERE
[date] >= convert(char(8), getdate(), 112) and
and [date] < dateadd(day, 1, convert(char(8), getdate(), 112))
go
AMB
"SQL Brad" wrote:
> Hello, I have a table that lists my bank transactions. I have a uniqueid in
> the first field and several other fields...one of which is DATE... now, I
> want to run a query where my view is based upon the DATE. however I want to
> only show transactions that are the same date as the system date (this is to
> see how much work my employees have done). Please see my example below, this
> code works...
> SELECT transid, [date], description, amt, taxamt
> FROM dbo.taxtransactions
> WHERE ([date] = '9/9/2007')
> However, where it has 9/9/2007, I want it to be TODAYS Date
> Thanks for your help in advance!
>|||Aaron...thanks for your help, it worked perfectly!! I also changed the 0 to
a 1 and it went to yesterday....very much appreciated!
"Aaron Bertrand [SQL Server MVP]" wrote:
> WHERE [date] = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
>
> "SQL Brad" <SQLBrad@.discussions.microsoft.com> wrote in message
> news:58DEF545-F6C1-4086-B247-9080CEFBC125@.microsoft.com...
> > Hello, I have a table that lists my bank transactions. I have a uniqueid
> > in
> > the first field and several other fields...one of which is DATE... now,
> > I
> > want to run a query where my view is based upon the DATE. however I want
> > to
> > only show transactions that are the same date as the system date (this is
> > to
> > see how much work my employees have done). Please see my example below,
> > this
> > code works...
> >
> > SELECT transid, [date], description, amt, taxamt
> > FROM dbo.taxtransactions
> > WHERE ([date] = '9/9/2007')
> >
> > However, where it has 9/9/2007, I want it to be TODAYS Date
> >
> > Thanks for your help in advance!
> >
>
>sql

Help with complex query

Hi Everyone,

I need help writing the following query. I have to group my data by Department and have a field that will calculation the number of minutes that employee worked in that department. So basically I take the total number of minutes worked in the department and divide it by the total number of minutes the employee worked for the specified date range.

--

Agent Name: John Doe

Date Range: 1/1/2007 - 6/30/2007

RowID Work Minutes in Dept Total Work Minutes Dept

1 26355 52920 Service

2 9000 52920 Parts

3 17565 52920 Dispatch

Service = 26355 / 52920 = 0.499 = 50%

Parts = 9000 / 52920 = 0.17 = 17%

Dispatch= 17565 / 52920 = 0.33 = 33%

--

How can I accomplish this?

I am using SQL Server 2005 Express

Thank You

Assuming your table is the grouped sum's by department:

Code Snippet

create table #t1 (RowID int, [Work Minutes] int, [Total Work Minutes] int, Dept varchar(20) )

insert into #t1

select 1, 26355, 52920, 'Service'

union all select 2, 9000, 52920, 'Parts'

union all select 3, 17565, 52920, 'Dispatch'

select Dept, ' = ' + convert(varchar(15), [Work Minutes]) + ' / ' + convert(varchar(15), [Total Work Minutes]),

round(([Work Minutes]*100.00)/[Total Work Minutes], 0) as 'Percentage'

from #t1

|||

DaleJ,

The table data is not grouped. Thats what makes this query complex.

|||

SamCosta wrote:

DaleJ,

The table data is not grouped. Thats what makes this query complex.

Can you right click the tables that you are using and choose "Script Table As..." and "Create To.." and post those back here. Once we have your structure we can help further.|||

And some additional sample data.

It's not that difficult, but would like to get it right the first time (or two )

|||

Code Snippet

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),
Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

This query outputs:

EmpID DeptID AvgPLevel DeptWorkMI

28899 Service 2 17244

28899 Parts 3 9000

28899 Dispatch 1 27836

I need to then group the query results by EmpID to get a total Average Productivity Level

Result:

EmpID AvgPLevel

28899 2

How to calculate total productivity level:

(2 * 17244 / 54080) + (3 * 9000 / 54080) + (1 * 27836 / 54080) = 1.65 = Level 2

54080 is the total number of minutes worked in ALL departs. (17244 + 9000 + 27836 = 54080)

Thank You

|||

See if this does what you need:

Code Snippet

;WITH base

AS

(

SELECT E.EMPID,

E.DEPTID,

CMS.Productivity(CMS.TrueCalls(SUM(D.ti_stafftime), SUM(D.ti_availtime), SUM(D.acdcalls)), SUM(D.acdcalls),

Agent.AbsentPercentage(SUM(CONVERT(int, A.TOTALABSENT)), SUM(CONVERT(int, A.TOTALWORKMI))), E.DEPTID) AS AvgPLevel,

SUM(CONVERT(int, A.TOTALWORKMI)) AS DEPTWORKMI

FROM CMS.dAgent AS D INNER JOIN dbo.EMP_DEPT_ASSOC AS E ON D.EmployeeID = E.EMPID AND D.row_date >= E.STARTDATE AND D.row_date

<= E.STOPDATE INNER JOIN EmpAbsents As A ON D.row_date = A.ROW_DATE AND D.EmployeeID = A.EMPID

WHERE (D.row_date BETWEEN @.FromDate AND @.ToDate) AND (D.EmployeeID = @.EmpID)

GROUP BY E.ID, E.DEPTID

),

Totals

AS

(

SELECT EmpID, SUM(DeptWorkMi) AS TotalMinutes

FROM base

GROUP BY EmpID

)

SELECT b.EmpID, ROUND(SUM(1.0 * b.AvgPLevel * b.DeptWorkMI / t.TotalMinutes), 0) as AvgPLevel

FROM base b

INNER JOIN Totals AS t

ON b.EmpID = t.EmpID

GROUP BY b.EmpID

|||

Thank you DaleJ. I was able to solve this problem using the CTE query example you provided.

HELP with case statement [Divide by zero error encountered.] !

Hi,
I have a case statement that has been giving me hell for the past day, can
anyone suggest another way of validating is those field have zeros or not.
The field types are numberic, looking at the table their is no Null values i
n
there a few hundred zeros (0, .000). I have tried this statement in many wa
y
still the same result.
Divide by zero error encountered.
CASE when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end as [dollar_turns],in your case statement you r not checking for dids.supplier_cost = 0
and in your else clause you are divinding by
( (dihs.qty_total) * dids.supplier_cost )
hope thishelps
rgds
abhishek
"ITDUDE27" wrote:

> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],
>|||By any chance are you rinning SQL 2005? I seem to recall someone else
having a similar issue where the optimizer evaluated the whole
statement rather than one test at a time.
Stu
ITDUDE27 wrote:
> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],|||Hi
Your assumption is wrong :)
Let me explain it.
For simplicity I am showing the columns as a(supplier_cost)
,b(qty_total),c(revenue)
lets say the table is like this...
a b c
1 1 1
0 0 0
For all the cases the sum is going to be 1 (for all three columns)
But analyze this..
sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
its actually 1/(1*1) + 0/(0*0)
I guess maybe you are trying for something like this in your else clause..
sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
If not change your logic accordingly.
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This thread illustrates exactly why one of the best ways to get help is to
include the *ACTUAL* table DDL code *AND* some sample data INSERT
statements.
No so many back-and-forths just trying to understand the issue.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6DEF9528-1748-4905-ACEE-0567A384126B@.microsoft.com...
> Hi
> Your assumption is wrong :)
> Let me explain it.
> For simplicity I am showing the columns as a(supplier_cost)
> ,b(qty_total),c(revenue)
> lets say the table is like this...
> a b c
> 1 1 1
> 0 0 0
> For all the cases the sum is going to be 1 (for all three columns)
> But analyze this..
> sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> its actually 1/(1*1) + 0/(0*0)
> I guess maybe you are trying for something like this in your else clause..
> sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
> If not change your logic accordingly.
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>sql

Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.I should give some more info to make the analysis easier...

My raw data looks like this

Date Type
20040101 Meal
20040101 Meal
20040101 Meal

I want the SQL to add a Count column and the Fact table data should look like this (one count per day).

Date Type Count
20040101 Meal 1
20040101 Meal 0
20040101 Meal 0

The only way I have been able to come close is to have a count of '1' for each line item, or to have to sum all the meals and have only one line per day. Is this possible to create without having to sum?

Thanks. Sorry I wasn't more clear.|||I still don't understand what you are trying to do. To get a count of meals, why don't you just
SELECT COUNT(*) From Table WHERE Type = 'Meal'

Do you want to count the number of days that have one meal so that if a single day has multiple meals, it just counts as one:
SELECT COUNT(*) From Table WHERE Type = 'Meal' GROUP BY datepart(year, Date), datepart(dayofyear, Date)|||Yes, I want to count one meal per day, however I still want the other line items to show as zero, because they have costs.

I'll try your SQL, but won't it drop the other 2 line items?

Thanks.|||select date expression
, count(*) as numberofmeals
, sum(costs)/count(*) as averagecostofmeal
from yourtable
group by date expression|||another solution :
select Date, Type into ##temp from yourtable group by Date, Type;
select Type,count(type) from ##temp group by type;

Help with an SQL statement

I am adding a column to my fact table to count the number of occurrences in another field. For example, I will enter a '1' in my new field named 'Count' whenever the 'Type' field has 'Meal'. What I am doing is counting the number of meals in the system so I can add up all the costs and divide by the count to get average cost per meal.

The problem is that I don't want to count every line item. I only want to input a '1' in my 'Count' field for each meal per day. I am unsure how to do this. Here is what I have:

case when T1."Type" = 'Meal' then '1' else null end

I tried using "and max(T1."Date")" but it was not working.

Please help me develop this SQL so that I only have one count per day.

Thanks.This is tough to help you do. The problem is that we (or I at least) don't understand your schema, so I'm not sure what you've got stored how.

One thing that I'd strongly suggest is to avoid using reserved words like "Count". You can use them, but it makes everything more work. In this case, I'd suggest using meal_count because it avoids the collision with a reserved word and it is more meaningful to some poor bozo like me that might try to help you!

-PatP|||This does not help. I was only giving you an example, I am using the 'Count' for the column title only, and am returning the same rows if I change the column title. I want to edit the SQL so I only count the number of days they had a meal charged.

Thanks.|||I'm sorry, I was trying to explain that I didn't know enough about your problem to help. If you can post the CREATE TABLE statements for your tables, and the SELECT statement (all of it), then I could get a lot closer. As it is, I don't know enough to give you anything more than guesses.

-PatP

Help with a substring query

I need help capturing information from a free text field.
The 10 examples below contain examples of the information I am trying
to capture.
In each cell I am trying to capture the number between 'TranID=' and
the next '&'.
So in the fisrt cell I would like to capture 14078800.

Things you will need to keep in mind are;

The number is not of fixed length.
'TranID' will always precede the number
The number will always be followed by an '&'
The '&' sign can occur multiple times in the text.

Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv

Regards,
Ciarnchudson007@.hotmail.com wrote:
> I need help capturing information from a free text field.
> The 10 examples below contain examples of the information I am trying
> to capture.
> In each cell I am trying to capture the number between 'TranID=' and
> the next '&'.
> So in the fisrt cell I would like to capture 14078800.
> Things you will need to keep in mind are;
> The number is not of fixed length.
> 'TranID' will always precede the number
> The number will always be followed by an '&'
> The '&' sign can occur multiple times in the text.
>
> Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv

Hint: use LIKE.

Kind regards

robert|||--something like this:
declare @.tranid_position int, @.amp_position int,@.string varchar(8000),
@.rest_of_string varchar(8000), @.Result_string varchar(8000)
set
@.string='Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.P*ostCSDelivery&OtherFlag0par=deliv
'
set @.tranid_position=charindex('&TranID=',@.string)
set @.rest_of_string= substring(@.string,@.tranid_position+8,8000)
set @.amp_position=charindex('&',@.rest_of_string)
set @.Result_string=left(@.rest_of_string,@.amp_position-1)
select @.Result_string|||Here you go...

CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO

INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'

SELECT
SUBSTRING(QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-7
)
AS TransID
FROM QueryStringTest ORDER BY TransID

DROP TABLE QueryStringTest

You could also look into regular expressions.|||That worked perfectly.
Much appreciated.|||I've just encountered a little problam.
Contrary to the criteria I provided earlier there are cells which end
with the TranID, like for example

OtherID=1638256785230&TranID=12345

How do I edit the script to capture these records?

Regards,
Ciarn|||There may be a better way but this should work...

CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO

INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'OtherID=1638256785230&TranID=12345'

SELECT
CASE WHEN
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
) = 0
THEN
RIGHT(QueryString,
LEN(QueryString)-
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-6
)
ELSE
SUBSTRING(QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-7
)
END AS TransID
FROM QueryStringTest ORDER BY TransID

DROP TABLE QueryStringTest|||(chudson007@.hotmail.com) writes:
> I've just encountered a little problam.
> Contrary to the criteria I provided earlier there are cells which end
> with the TranID, like for example
> OtherID=1638256785230&TranID=12345
> How do I edit the script to capture these records?

Here is a query, a little different from figitals:

SELECT convert(int, str2)
FROM (SELECT str2 =
CASE WHEN str1 LIKE '%[^0-9]%'
THEN substring(str1, 1, patindex('%[^0-9]%', str1) - 1)
ELSE str1
END
FROM (SELECT str1 = substring(str,
charindex('TranID=', str) + len('TranId='),
len(str))
FROM QueryStringTest) AS a) AS b

By using nested derived tables, it is possibly easier to see the
solution step for step. Or it's more confusing. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Perfect!
Thanks

Help with a simple query please

I have Two Tables, TableA and TableB, both containing a common field,
Feild1.

How do I find all records in TableA, where Field1 is not in TableB?

Regards,
CiarnSELECT A.*
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.col1 = B.col1
WHERE B.col1 IS NULL

--
David Portas
SQL Server MVP
--|||Try

SELECT
A.*

FROM
TableA A
LEFT JOIN TableB B ON
A.Field1 = B.Field1|||Sorry, didn't read it properly. David's answer is correct.

Help wit Query to list duplicates

I am trying to create a list of those cases where a specific field value was
duplicate with a set of rows. The query I am trying is shown below (it is
listing all rows instead of just those where there is a duplicate value in
the field Eqp1Voc.
ID is an unique key for each row.
What did I overlook?
Wayne
========================================
=
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores A JOIN DCScores B
ON A.ID <> B.ID
AND A.Eqp1Voc = B.Eqp1Voc)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
========================================
=> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
(SELECT B.ID
FROM DCScores
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Thanks for the quick response but that doesn't return any rows (and I know
there are duplicates in that field!) With your suggestion, the query now
looks like this:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores B
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238nw0CEQFHA.688@.TK2MSFTNGP14.phx.gbl...
> (SELECT B.ID
> FROM DCScores
> Group by B.ID
> HAVING COUNT(Eqp1Voc) >1)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Sorry for that missed up something...
Here it goes:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE Eqp1Voc IN
(SELECT Eqp1Voc
FROM DCScores
Group by Eqp1Voc
HAVING COUNT(Eqp1Voc) > 1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Jens.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Jens;
Thanks again but with that change, it returns all rows, not just those with
a duplicate value?
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23FGm0YEQFHA.204@.TK2MSFTNGP15.phx.gbl...
> Sorry for that missed up something...
> Here it goes:
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE Eqp1Voc IN
> (SELECT Eqp1Voc
> FROM DCScores
> Group by Eqp1Voc
> HAVING COUNT(Eqp1Voc) > 1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> Jens.
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Try this:
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
Group By Eqp1Voc
Having Count(*) > 1)
WHERE CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
"Wayne Wengert" wrote:

> I am trying to create a list of those cases where a specific field value w
as
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>
>|||Thanks for the suggestion but that yields an error: "Incorrect syntax near
the keyword 'WHERE'."
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> Try this:
> SELECT UnitName, Eqp1Judge,
> Eqp1Voc
> FROM DCScores S
> Where Exists
> (Select * From DCScores
> Where Eqp1Voc = S.Eqp1Voc
> Group By Eqp1Voc
> Having Count(*) > 1)
> WHERE CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> "Wayne Wengert" wrote:
>
was
is
in|||Replace this "WHERE CircuitID" WITH "AND CircuitID"
Jens Smeyer.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Thanks for the suggestion but that yields an error: "Incorrect syntax near
> the keyword 'WHERE'."
> Wayne
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> was
> is
> in
>|||Jens;
I took that advice and also realized that the Where clause needs to be
applied to that inner select. I changed it to the following and now it
works.
I appreciate all the help.
==================================
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Group By Eqp1Voc
Having Count(*) > 1)
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
==================================
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KCoZCFQFHA.2380@.TK2MSFTNGP10.phx.gbl...
> Replace this "WHERE CircuitID" WITH "AND CircuitID"
> Jens Smeyer.
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
near
(it
value
>

Help w/SQL Mobile to SQL Compact Edition

Hello

We currently use SQL Mobile and SQL Server 2005. We depend heavily on merge replication for data to the field handheld devices.

We are going to be starting some new projects and I was wondering about moving to SQL Compact Edition as the first step to migrating all of our applications.

I am concerned, however, about the server side and replication. If we simply deploy SQL Compact Edition will there need to be any changes to the server for replication?

Thanks,

- will

Yes, that should be the case. SQL CE is basiclay just a newer version of SQL Mobile.

|||

Are you saying that there isn't anything on the server that I need to do...or that I will need to install and test the newer versions of the server tools?

|||The server tools have been update slightly, so you should use these. Download from: http://www.microsoft.com/downloads/details.aspx?FamilyID=4e45f676-e69a-4f7f-a016-c1585acf4310&displaylang=en

Help w/SQL Mobile to SQL Compact Edition

Hello

We currently use SQL Mobile and SQL Server 2005. We depend heavily on merge replication for data to the field handheld devices.

We are going to be starting some new projects and I was wondering about moving to SQL Compact Edition as the first step to migrating all of our applications.

I am concerned, however, about the server side and replication. If we simply deploy SQL Compact Edition will there need to be any changes to the server for replication?

Thanks,

- will

Yes, that should be the case. SQL CE is basiclay just a newer version of SQL Mobile.

|||

Are you saying that there isn't anything on the server that I need to do...or that I will need to install and test the newer versions of the server tools?

|||The server tools have been update slightly, so you should use these. Download from: http://www.microsoft.com/downloads/details.aspx?FamilyID=4e45f676-e69a-4f7f-a016-c1585acf4310&displaylang=en

Help Using a field value in a url jump statement

I have a report that uses the table control, the last column has id's for
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fine
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
"535d58cc-a1b3-da11-9864-001320020c86" & "');"
but this one does not allow for the pointer hand to show up when cusror is
over the row
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
Fields!res_dealCompanyid.Value & "');"
Do I need to convert Fields!res_dealCompanyid.Value to a string?
Can I assign it to a variable and then use it in the Jump to URL?
I do not know what else to try
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> "535d58cc-a1b3-da11-9864-001320020c86" & "');"
> but this one does not allow for the pointer hand to show up when cusror is
> over the row
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> Fields!res_dealCompanyid.Value & "');"
> Do I need to convert Fields!res_dealCompanyid.Value to a string?
> Can I assign it to a variable and then use it in the Jump to URL?
> I do not know what else to try
> Mike

Help Urgent

Hi K. Babu

I am using Access as back end and VB 6.0 as front end. In access I have field name reporting date and I have fixed its property as date. Which stores data as MM/DD/YYYY.

I want to fecth reports on the bases of only Months and not on exact dates.

Since, crystal report selection formula data type is string, and my access datatype for the field is date, my problem is when I write my code only on months, the crystal report gives me message "Error in formula".

How should I write my code so that selection formula searches only months instead of comparing entire date field in the database and give me reports?

I will appreciate, if you could guide me ASAP.

RakeshI haven't used the SelectionFormula very much, but here's my thoughts...

If you want all the records for the month of April (for example), can't you just search for the records that are between 04/01/2004 and 04/30/2004? That way you still get the data you want and you're giving the query what it wants (a full date instead of just a month).

Help translating Access SQL to T-SQL

Can someone tell me what this Where clause (from MS Access) should be T-SQL?

Basically, if the value in the [reason] field contains 'DIST' then the row should return as long as the value in [movement] is greater than or equal to 1.

Where IIf([reason] LIKE '%DIST%',Val([movement]),1)>=1

Thanks!

something like this. "iif" is not a valid sql fucntion

where reason like '%dist%' and movement=> 1

val is also not valid so you may use cast or convert

therefore

where reason like '%dist%' and cast (movement, money) => 1

|||

You can do below:

where case when reason like '%DIST%' then sign(Movement) end = 1

-- or

where reason like '%DIST%'

and sign(Movement) = 1

-- or below which will use index on Movement if available

where reason like '%DIST%'

and Movement >= 1

|||

Replace the IIF() with Case:

Case when [reason] like '%DIST%' then...

Am I correct in intrepreting Val() as converting to a number (saves me a google)? Then you want to use Convert().

Final syntax:

Where Case When [reason] Like '%Dist%' then Convert(int, [movement] ) Else 1 End >= 1

You could also use NULL with the Else case.

....Guess I'm a slow typist...

|||

Thanks anomolous! That was the right answer. I just had to change int to real because [movement] holds a decimal value.

Yes, Val() in Access converts a string to a number And if it's NULL, Val() will return 0. Will Convert() return a 0 for a NULL value? Or do I need to use a different function so that I get a 0 if [movement] holds a NULL value?

|||

You have to use coalesce or isnull to check for NULL values. All built-ins return NULL for NULL input. Do something like:

where reason like '%DIST%'

and cast(coalesce(Movement, '') as int) >= 1

Note that CAST or CONVERT in TSQL will return error if the value cannot be converted. I don't know the behavior of VAL in Access. So in that case, you will have to do additional checks like:

where reason like '%DIST%'

and cast isnumeric(Movement) when 1 then cast(coalesce(Movement, '') as int) end >= 1

Even use of ISNUMERIC will not work for all cases since it checks for integer, numeric and money data type conversion semantics. So it is possible that you could have values that can convert to money but not int. So you will be better off actually modifying the schema such that Movement column is integer and it stores only integer values. Mixing different data types in a string column and manipulating it using TSQL is problematic in lot of ways. And it is often done incorrectly leading to bad performance due to conversions, wrong results, run-time errors and so on.

help Simple question

Dear all,
I need to do some analysis on particular records.
For that I have one Table named EVENT in which I have a field named CODE.
From that field value I can get many entries with same CODE value.
Is there a way to extract the value of CODE field which occurs more often in
a table ?
thanks
regards
SergeHi
create table #test
(
col int not null primary key,
code int
)
insert into #test values (1,100)
insert into #test values (2,100)
insert into #test values (3,200)
insert into #test values (4,100)
insert into #test values (5,200)
insert into #test values (6,800)
select top 1 code,count(*)as occur from #test
group by code
order by occur desc
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:2FC5F7A1-C1F4-4033-82B9-8F84EF8FFDD7@.microsoft.com...
> Dear all,
> I need to do some analysis on particular records.
> For that I have one Table named EVENT in which I have a field named CODE.
> From that field value I can get many entries with same CODE value.
> Is there a way to extract the value of CODE field which occurs more often
> in
> a table ?
> thanks
> regards
> Serge

Help required to update SQL

I have a table named Car with the field name as Position. I want to
update the Position field. If i enter a new value as 4 for position
which already exist, then the existing value 4 and all the below items
like 5,6 and 7 must be incremented by 1
Position
1
2
3
4
5
6
7
How can this be achievedmora wrote:
> I have a table named Car with the field name as Position. I want to
> update the Position field. If i enter a new value as 4 for position
> which already exist, then the existing value 4 and all the below items
> like 5,6 and 7 must be incremented by 1
>
> Position
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> How can this be achieved
UPDATE Car SET Position = Position + 1 WHERE Position >= 4 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CREATE TRIGGER blahblah INSTEAD OF INSERT AS
BEGIN
declare @.pos int
select @.pos = inserted.position
UPDATE table1 SET position = position+1 WHERE position >= @.pos
INSERT INTO table1 SELECT * from inserted
END
-- please verify the syntax before using - I'm just giving an idea,
don't have a server/BOL around to test & debug it.|||>> I have a table named Car with the field [sic] name as position. I want to update th
e position field [sic]. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Your vageu narrative is also wrong. Columns are not fields. POSITION()
s a reserved word in Standard SQL. Since you used a singular name, you
must have one car; otherwise you would have used a collective or plural
noun.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" , "below" and "last" are
totally meaningless.
I am going to make a guess at what you meant. Do you have a motorpool
in which you assign parkging spaces?
CREATE TABLE Motorpool
(space_nbr INTEGER NOT NULL PRIMARY KEY
CHECK (space_nbr > 0),
vin CHAR(17) NOT NULL);
Re-arrange the display order based on the space_nbr column:
CREATE PROCEDURE SwapParkingSpacees (@.old_space_nbr INTEGER,
@.new_space_nbr INTEGER)
AS
UPDATE Motorpool
SET space_nbr
= CASE space_nbr
WHEN @.old_space_nbr
THEN @.new_space_nbr
ELSE space_nbr + SIGN(@.old_space_nbr - @.new_pos)
END
WHERE space_nbr BETWEEN @.old_space_nbr AND @.new_space_nbr
OR space_nbr BETWEEN @.new_space_nbr AND @.old_space_nbr;
When you want to drop a few rows, remember to close the gaps with this:
CREATE PROCEDURE CloseParkingSpaceGaps()
AS
UPDATE Motorpool
SET space_nbr
= (SELECT COUNT (F1.space_nbr)
FROM Motorpool AS F1
WHERE F1.space_nbr <= Motorpool.space_nbr);
To insert a new car into the motorpool, add the new vehicle to the "end
of the line" and then swap it with the target parking space.

Help Required Regarding Formatting of Fields

Hello,
I am using SQL SERVER Reporting Services 2005. I am having a formatting problem. The problem is that I hav a textbox in which numeric field is coming. I want to show the currency format of the Regional Setting of the user pc. I have set the Language setting of the textbox to Default. Now when I am changing the Regional setting from US to UK then in the criteria section the datetime criteria (calendar's value) is changed according to the UK setting, but in the report section the selected date time is not changed (coming in US setting). And the currecy format is also not changing to UK format. Any help ?

Regards,

FurqanSet the format to =User!Language instead of default. I think that will work

Help Reqd Urgently (SQL Query)

Pls consider the following table:--

Table Name: ChemoAdmin

Fields:

Field Name: Field Type: Typical Value:

patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg

I require the following output using one query:--

DATE 09/17/2004 09/21/2004

Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg

Pls help.You mean you'd like a specify a daterange and have it shown above the other results returned by the same select-statement (seperated by an empty line)?

Is there any reason why you need this in a single sql-statement?|||It is better if this can be done using one select statement. Because, I would like to create a view that would give the output as shown.|||I don't see a way of doing that in a view. You might want to create a stored procedure instead that produces both the daterange as well as the select output. However, I feel that your aim is off.|||this requires a full outer join

however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this case

i always write a full outer join as a left outer join unioned with a right outer join where there's no matching row (and then i always flip the right outer join over into a left)
select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
left outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t2.stationDate = '2004-09-21'
where t1.stationDate = '2004-09-17'
union all
select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t2
left outer
join ChemoAdmin as t1
on t2.drugName
= t1.drugName
and t1.stationDate = '2004-09-17'
where t2.stationDate = '2004-09-21'
and t1.drugName is null|||this requires a full outer join

however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this caseOk, that just begs the question: Why do you avoid FULL OUTER JOIN when it does exactly what is needed? While I use FULL and CROSS joins rather sparingly, when they do exactly what I want I'll cheerfully use the little beggars.

-PatP|||because not every database supports FULL OUTER (yes, i know that microsoft sql server does, but it's easier remembering the workaround than trying to remember which database supports it)

but more importantly, in this instance the join condition include conditions on only one table or the other

i'm not certain that this will produce the same results:select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
full outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t1.stationDate = '2004-09-17'
and t2.stationDate = '2004-09-21'
feel like testing it for us? :)|||Because your code tests both tables, it effectively reduces the join to a conventional INNER join. Any result set rows that "miss" (either right or left) will fail due to the comparison with NULL. The only difference between the two (FULL versus unions of left and right) would be if you used UNION instead of UNION ALL.

-PatP|||so you're saying that the FULL OUTER example that i posted will not work?

so what would you do to make it work in this particular example?

don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see it|||Can any drug in your list have more than 2 dosages ? if so joining the table just twice wont work. Use the following query to find the maximum number of doses for any drug in that table.

select max(counter) max_number from
(select drugName, count(*) counter from ChemoAdmin
group by ChemoAdmin) derived

if the max_number is very high and likely to be a variable then the best way is to use a stored procedure.

if it is just 2 or 3 then, the you can use the self left outer joins not a full outer join

for instance if the max_number is 2 then

select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
left outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t1.stationDate >= '2004-09-17'
and t1.stationDate < '2004-09-21'
and t2.stationDate >= '2004-09-17'
and t2.stationDate < '2004-09-21'
and t1.dose <> t2.dose
and t1.doseUnit <> t2.doseUnit

if the max_number is 3 then join the result again with the table and so on, but do not join too many times though because it would cause performance problems.|||so you're saying that the FULL OUTER example that i posted will not work?

so what would you do to make it work in this particular example?

don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see itI didn't pooh on anything. In my first post I just asked why you avoided a FULL join when I use them occaisionally.

The point that I raised in my second post is that if you look at things from the set based perspective, if either set it empty (returning NULL in SQL) then the WHERE clause comparison has to fail, reducing the result set to the intersection (an INNER join in SQL) or less.

Sorry if you got the impression that I was denigrating your work around for a full join. I didn't mean to do that at all.

I'm still not at all convinced that we understand what the original poster wanted. They showed us a third of the equation (the results they want), without giving us either the input data or the rules they used to determine the output. Before we debate the relative merits of solutions, we need to understand what the rules are!

-PatP|||I'm still not at all convinced that we understand what the original poster wanted. so true

but then, the most interesting threads are where the original poster asks something innocuous (to them) and we just run with it in all sorts of directions

:rolleyes:

Help Reqd Urgently (SQL Query)

Pls consider the following table:--

Table Name: ChemoAdmin

Fields:

Field Name: Field Type: Typical Value:

patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg

I require the following output using one query:--

DATE 09/17/2004 09/21/2004

Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg

Pls help.Sorry, but I'm confused. What is your question?

-PatP