How to get type of parameter passed in stored procedure?

Hi ,

I wrote a stored procedure with one uinqueidentifier parameter.Depending upon the value passed during execution type of parameter passed is changing.

Example:

CREATE PROCEDURE ABC_Proc

@.ABC uniqueidentifier = NULL

Execution:

EXEc ABC_Proc ' '

If I pass the uiqueidentifier from the same table then the program executes properly.If same uinqueidentifier from other table or NULL or '' is passed the following error is displayed

"conversion failed when converting from a character string to unique identifier."

Questions:IS there a way to get the datatype of parameter passed in the querry?so, that I can modify accordingly.

Thanks,

Kodela.

Hi Kodela,

The easiest way is to use the new sql_variant datatype as the parameter, and then within the procedure, you can test for the base datatype and execute the appropriate action:

declare @.v sql_variant

set @.v = 123

if SQL_VARIANT_PROPERTY ( @.v , 'BaseType' ) = 'int'
begin
print 'int type passed'
end
else if SQL_VARIANT_PROPERTY ( @.v ,'BaseType' ) = 'uniqueidentifier'
begin
print 'uid type passed'
end

Cheers,
Rob

|||

You could use sql_variant, but the best thing to do is to treat parameters as strongly typed and only pass proper values to that parameter. You can get the type of the parameter from the information_schema:

CREATE PROCEDURE ABC_Proc
@.ABC uniqueidentifier = NULL
as
select @.ABC
go

select parameter_name, data_type
from information_schema.parameters
where specific_name = 'ABC_Proc'
and specific_schema = 'dbo'

|||

Robert Varga wrote:

use the new sql_variant datatype

Robert,

There is a reason that the variant datatype has been removed from Visual Studio.Net -it opens the code to potential attack vectors. Similarly, it is not a particularly good thing to use in SQL Server. Often the use of variants demonstrates a poor design, or just plain sloppy coding. -NOT drawing aspersions to you or your techniques, just commenting...

Regards,

|||

Hi Arnie,

I have to say I disagree completely. The SQL_VARIANT datatype was introduced in SQL 2005 for good reason. Just as there are valid reasons for using cursors, there are valid reasons for using the sql_variant datatype, and blanket statements alluding to their use constituting "poor design" are flawed.

In reality, we've been using varchar's as "variant" datatypes for years - ( CONVERT(varchar(15), GETDATE(), 103) anyone? ) and the new datatype removes precision related errors.

Cheeers,
Rob

|||

I actually have to agree that the SQL_VARIANT datatype should generally be avoided (as should converting dates to varchar values too, since that is usually a formatting gimmick that we *should* let the presentation layer handle :) for the same reason that VB programmers shouldn't generally use Option Strict (I think that was (is?) the syntax).

Especially in this situation, picking the proper datatype is part of the contract between caller and callee. In your example, it would not be possible to tell a GUID from a character value, certainly not when being passed in as a parameter, since it would be passed as a string of characters (or as a binary value) which could not be distinguished in code.

It is not completely useless when used for sparse property tables where you define the metadata of the column at runtime is a pretty good use, but generally having a column of unknown type means more code, more work, and greater chance for errors later.

|||

Hi Louis,

Sure; just as liberal use of, say, cursors is not a good thing, neither is the use of generic datatypes. My point is that there are circumstances in which the use of sql_variant (or cursors) are valid and should not be discounted by zealots simply because some Wrox book said they were "bad".

Cheers,
Rob

|||

Louis,

You demonstrate my point precisely. There is no need to use a sql_variant in this situation. The metadata is available. Strongly typed variables/parameters reduce coding and coding errors.

Using an object datatype (the .NET replacement for 'variant' has it's place and needs, and slq_variant has it's places and usages. However, in my opinion, it should not be used in a situation where the exact need does not exist. This is not one of those situations.

And gosh, if one wanted to morph a varchar() parameter, the datatype could be just another input parameter. For me, strongly typing is the key. If the dataype is known when the call is made, it should be strongly typed.

How to get two different SQL servers to talk to one another

Hi all I need to create a view for another sql server on the network.

I can create a view for two to databases on the same sql server to talk but how do you do it for a differend sql server??

CREATE VIEW dbo.Revocations_View
AS
SELECT TM#, LastName, FirstName, MI, SSN, [I/R #], Date, ReasonofRevocation, Notes, Termination, Conditional, WasEmployeeFined, LicenseSuspension,
Status
FROM LicensingActions.dbo.Revocations_TblUse sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp) to put the servers on "speaking terms" with each other. In a secured network, you may have to deal with Security Account Delegation (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp). After you've resolved that, you need to use four part names (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_5vvp.asp) and you're in business.

-PatP|||thanks pat thats exaclty what I need
appreciate it :)|||a fine meal and big bottle of wine outta do it. could'nt resist.

How to get total page count for individual groups

Hello,

I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.

For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,

For 1st group

'Page 1 of 3’ when user click next page it should be 'Page 2 of 3' etc

Similarly for other groups as wll.

Thanks!

Hello,

Brian Welcker has a blog post that covers this:

http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx

but you will want to modify his code slightly to use a hash table as he notes at the end.

Let me know if you need assistance implementing this solution or if this doesn't solve your issue.

Larry

|||

Hi Larry,

Thanks a lot for the reply.

I am able to implement the code but I am not able to get the total page count for the individual group.

Let say there are total 15 Pages in the report and there are 3 groups with page break then I need something like

For Group 1 paging would be -> Page 1 of 3, Page 2 of 3.....etc

For Group 1 paging would be -> Page 1 of 5, Page 2 of 5, Page 3 of 5...etc

For Group 1 paging would be -> Page 1 of 7

Currently I am getting like

Group 1 - Page1 of 15, Page 2 of 15...ect

Group 2 - Page 1 of 15, Page 2 of 15..etc

Group 3 - Page 1 of 15, Page 2 of 15..etc

Thanks

|||

I see. I didn't fully read your original post. Take a look at the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1807270&SiteID=1

where Lisa posts a method of doing what you want. It requires some external code as the pages in a report are rendered in a single pass and it would require two passes (one to count and one to update) to accomplish what you are wanting.

Larry

how to get total number of variables using scripttask

I want to loop thru all the variables in my package and set number of variables that had variable-name begin w/LOCAL, so I can use the name to generate a dynamic SQL query for the next EXEC SQL Task. any one know how to do this.

Use the count property on the variables collection.|||

that is what I did, Dts.Varaiables.count, but it did not return the right numbers. I have over 10 variables and it return 1.

|||

Have you added your 10+ variables to either the ReadOnlyVariables or ReadWriteVariables list? The Dts.Variables collection is generated based on those preset list of variables.

Thanks,
Patrik

|||

no, I did not.

OK, let me back out a little bit, I try to use the event handler as a trigger for my custom audit logging, for each task when I am done, I want to use a script to find out what is in my variables pool, loop thru them and build a query, (“select count(*) from ‘[“ + @.variable1), and then the next execute SQL task will execute this and return the count to a variable so I can have the OnPostExecute event write out the record counts, so I only have 1 variable “MySQL” in the ReadWriteVariables list, since what I want is when I create a new package, all I have to do is to add or remove local variables, no code changes is need it. if I have to put in all the variables, then I defeat my intention, is there a work around for this? Any suggestion is greatly appreciated.

|||

What sort of processing are you trying to achieve with the Script Task? Which variables are you using and looking for in the loop? Is the collection of variables you're interested in always the same? If you don't fill in the ReadOnlyVariables and ReadWriteVariables properties you can do the work using the VariableDispenser (http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.variabledispenser.aspx); however, you would still need to know the name of the variables you're interested in reading.

If you're using the Script Task just to create a SQL command statement for the Execute SQL Task you could look at using expressions (http://msdn2.microsoft.com/en-us/library/ms141214.aspx).

Cheers,
Patrik

|||

Hi..

You can read all variables by using ActiveX Script Task instead of using Script Task.

Function Main()
Dim oVal

Dim isSystem, varType, varName
Dim str
MSGBOX "Total Variable = " & DTSGlobalVariables.Count

For each oVal in DTSGlobalVariables
isSystem = oVal.SystemVariable
varType = oVal.DataType
varName = oVal.QualifiedName

MSGBOX varName & " / " & varType & " / " & "System:" & isSystem

Next
Main = DTSTaskExecResult_Success

End Function

HTH

ADConsulting / SQLLeader.com / Daeseong Han

how to get top three salary getters from table employee

Dear All,
i want to know how to get top three salary getters from the employee(eid , ename, salary) table

i tried this
select top 3 salary from employee order by salary desc

but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500
then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200

how can i do it

please help

thanks

You can run this query:

SELECT * FROM YourTable
WHERE Salary IN (
SELECT DISTINCT TOP 3 Salary FROM YourTable
ORDER BY Salary DESC )
ORDER BY Salary DESCsql

How to get Top 3 records in the group level?

Hi all,

I have a query which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. I tried using 'select top 3....', but the result is not what I wanted. It doesn't consider the grouping. Is there any way to achieve this task?

Please let me know if some body has any idea.

Thx.

The answer depends on whether or not you have SQL Server 2005 or SQL Server 2000. If you are using SQL Server 2005 you can use the ROW_NUMBER function. What is also needed is to know what field you are ordering your groups by (to get the top 3).

Dave

|||Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

How about this:

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(3), text_data NVARCHAR(50));

INSERT INTO t1 VALUES ('A', 'Text for A1');
INSERT INTO t1 VALUES ('A', 'Text for A2');
INSERT INTO t1 VALUES ('A', 'Text for A3');
INSERT INTO t1 VALUES ('A', 'Text for A4');
INSERT INTO t1 VALUES ('B', 'Text for B1');
INSERT INTO t1 VALUES ('B', 'Text for B2');
INSERT INTO t1 VALUES ('B', 'Text for B3');
INSERT INTO t1 VALUES ('B', 'Text for B4');
INSERT INTO t1 VALUES ('C', 'Text for C1');
INSERT INTO t1 VALUES ('C', 'Text for C2');
INSERT INTO t1 VALUES ('C', 'Text for C3');
INSERT INTO t1 VALUES ('C', 'Text for C4');

-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)

|||

Amde:

I set up a mock-up to compare Lucky's query with a query based on ROW_NUMBER. I created the T1 table with 32767 distinct R_ID entries and 30 distinct TEXT_DATA entries for each R_ID -- nearly 1,000,000 rows. When I ran Lucky's query with the table without any indexes it took about 102 seconds with 6.6 million logical IOs. I then added an index based on R_ID only and repeated the run. Again, it took about 102 seconds with 6.6 million logical IOs. I then looked at the SHOWPLAN and verified that the plan was still using a TABLE scan and was not using the index. This is expected because use of the index would require a prohibitive amount of bookmark lookups. I lastly dropped that index and added a cover index based on both R_ID and TEXT_DATA and repeated the run. This time it took 17 seconds with 3.1 million logical IOs.

Next, I repeated this using a query based on ROW_NUMBER. I first ran the query without any indexes. It took 7263 ms with 7709 logical IOs. I added an index based on R_ID only and it again took about 7600 ms with 7709 logical IOs. I then dropped that index and added an index based on both R_ID and TEXT_DATA. This query then took 2406 ms with 7706 logical IOs. This is the script I used for testing:

set nocount on
use tempdb
drop table t1
go

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(5), text_data NVARCHAR(50));
--exec sp_help t1

insert into t1
select convert (nvarchar (5), i.iter),
'Text for ' + convert (nvarchar(5), i.iter)
+ '-' + convert (nchar(2), j.iter)
from mugambo.dbo.small_iterator (nolock) i
inner join mugambo.dbo.small_iterator (nolock) j
on j.iter <= 30
--select * from t1

--drop index t1.t1_ndx
--create index t1_ndx on t1(r_id)
create index t1_ndx on t1(r_id, text_data)
update statistics t1
go

declare @.begDt datetime set @.begDt = getdate()

/*
--insert into @.receiver
-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)
*/

set statistics io on

select r_id,
text_data
from ( select r_id,
row_number () over
(partition by R_ID order by text_data)
as seq,
text_data
from t1
) x
where seq <= 3

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- r_id text_data
-- -- --
-- 1 Text for 1-1
-- 1 Text for 1-10
-- ...
-- 9999 Text for 9999-11

-- Table 't1'. Scan count 1, logical reads 7706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2220

|||

Hi,

keeping same table as Lucky_p has dipicted , just try this

select *from

(SELECT i.r_id, i.text_data,

DENSE_RANK() OVER (PARTITION BY i.r_id order by text_data) as RANK

FROM t1 i) A where rank<=3

Madhu

|||

That's all nice and i agree my solution is not fast when the table is large.

Do you have a fast solution for SQL 2000 too? All your solutions are for SQL 2005.

|||

Lucky:

I cannot come up with a method for SQL Server 2000 that is any faster than your version. Hopefully, one of the senior members can shed some light; I struck out.

Dave

|||

I found an alternative that might be of some use:

declare @.top3 table
( r_id nvarchar (5) not null,
seq integer not null,
text_data nvarchar (50) not null,

primary key (r_id, seq)
)

insert into @.top3
select r_id,
1,
min(text_data)
from t1
group by r_id

insert into @.top3
select r_id,
2,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a

insert into @.top3
select r_id,
3,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a
where seq = 2

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- Table '#33ADF722'. Scan count 0, logical reads 66286, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 1, logical reads 7640, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 97120, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98558, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 100528, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98552, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 2766

|||

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

|||

Lucky P wrote:

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

again with little modification u can fine tune this script.. eliminate Temp table

Select *from

(SELECT top 100 R_ID , text_data,

(SELECT COUNT(*) FROM T1 AS b

WHERE b.R_ID = a.R_ID

AND (b.text_data < a.text_data

OR (b.text_data = a.text_data))) AS rownum

FROM T1 AS a

ORDER BY R_ID, text_data) A

where rownum<4

Madhu

|||

I tested these other queries too with my "million-row" mockup and here is what I think:

If running SQL Server 2005 use either Madhu's or my first query|||

Your getting a partial result because of these line:

(SELECT top 100 R_ID , text_data,

If You change it to

(SELECT top 100 PERCENT R_ID , text_data,

You should get all the rows....

|||

Thanks for catching that lucky; I tested this one too and your original query is still much faster.

Dave

|||

ms sql server

data:

idno Email Category
1 a@.a.com c
2 b@.b.com c
3 b@.b.com c
4 b@.b.com c

only unique email address record are requierd.
1 a@.a.com c
2 b@.b.com c

what could be the query for such requirements.

How to get Top 3 records in the group level?

Hi all,

I have a query which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. I tried using 'select top 3....', but the result is not what I wanted. It doesn't consider the grouping. Is there any way to achieve this task?

Please let me know if some body has any idea.

Thx.

The answer depends on whether or not you have SQL Server 2005 or SQL Server 2000. If you are using SQL Server 2005 you can use the ROW_NUMBER function. What is also needed is to know what field you are ordering your groups by (to get the top 3).

Dave

|||Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

How about this:

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(3), text_data NVARCHAR(50));

INSERT INTO t1 VALUES ('A', 'Text for A1');
INSERT INTO t1 VALUES ('A', 'Text for A2');
INSERT INTO t1 VALUES ('A', 'Text for A3');
INSERT INTO t1 VALUES ('A', 'Text for A4');
INSERT INTO t1 VALUES ('B', 'Text for B1');
INSERT INTO t1 VALUES ('B', 'Text for B2');
INSERT INTO t1 VALUES ('B', 'Text for B3');
INSERT INTO t1 VALUES ('B', 'Text for B4');
INSERT INTO t1 VALUES ('C', 'Text for C1');
INSERT INTO t1 VALUES ('C', 'Text for C2');
INSERT INTO t1 VALUES ('C', 'Text for C3');
INSERT INTO t1 VALUES ('C', 'Text for C4');

-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)

|||

Amde:

I set up a mock-up to compare Lucky's query with a query based on ROW_NUMBER. I created the T1 table with 32767 distinct R_ID entries and 30 distinct TEXT_DATA entries for each R_ID -- nearly 1,000,000 rows. When I ran Lucky's query with the table without any indexes it took about 102 seconds with 6.6 million logical IOs. I then added an index based on R_ID only and repeated the run. Again, it took about 102 seconds with 6.6 million logical IOs. I then looked at the SHOWPLAN and verified that the plan was still using a TABLE scan and was not using the index. This is expected because use of the index would require a prohibitive amount of bookmark lookups. I lastly dropped that index and added a cover index based on both R_ID and TEXT_DATA and repeated the run. This time it took 17 seconds with 3.1 million logical IOs.

Next, I repeated this using a query based on ROW_NUMBER. I first ran the query without any indexes. It took 7263 ms with 7709 logical IOs. I added an index based on R_ID only and it again took about 7600 ms with 7709 logical IOs. I then dropped that index and added an index based on both R_ID and TEXT_DATA. This query then took 2406 ms with 7706 logical IOs. This is the script I used for testing:

set nocount on
use tempdb
drop table t1
go

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(5), text_data NVARCHAR(50));
--exec sp_help t1

insert into t1
select convert (nvarchar (5), i.iter),
'Text for ' + convert (nvarchar(5), i.iter)
+ '-' + convert (nchar(2), j.iter)
from mugambo.dbo.small_iterator (nolock) i
inner join mugambo.dbo.small_iterator (nolock) j
on j.iter <= 30
--select * from t1

--drop index t1.t1_ndx
--create index t1_ndx on t1(r_id)
create index t1_ndx on t1(r_id, text_data)
update statistics t1
go

declare @.begDt datetime set @.begDt = getdate()

/*
--insert into @.receiver
-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)
*/

set statistics io on

select r_id,
text_data
from ( select r_id,
row_number () over
(partition by R_ID order by text_data)
as seq,
text_data
from t1
) x
where seq <= 3

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- r_id text_data
-- -- --
-- 1 Text for 1-1
-- 1 Text for 1-10
-- ...
-- 9999 Text for 9999-11

-- Table 't1'. Scan count 1, logical reads 7706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2220

|||

Hi,

keeping same table as Lucky_p has dipicted , just try this

select *from

(SELECT i.r_id, i.text_data,

DENSE_RANK() OVER (PARTITION BY i.r_id order by text_data) as RANK

FROM t1 i) A where rank<=3

Madhu

|||

That's all nice and i agree my solution is not fast when the table is large.

Do you have a fast solution for SQL 2000 too? All your solutions are for SQL 2005.

|||

Lucky:

I cannot come up with a method for SQL Server 2000 that is any faster than your version. Hopefully, one of the senior members can shed some light; I struck out.

Dave

|||

I found an alternative that might be of some use:

declare @.top3 table
( r_id nvarchar (5) not null,
seq integer not null,
text_data nvarchar (50) not null,

primary key (r_id, seq)
)

insert into @.top3
select r_id,
1,
min(text_data)
from t1
group by r_id

insert into @.top3
select r_id,
2,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a

insert into @.top3
select r_id,
3,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a
where seq = 2

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- Table '#33ADF722'. Scan count 0, logical reads 66286, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 1, logical reads 7640, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 97120, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98558, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 100528, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98552, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 2766

|||

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

|||

Lucky P wrote:

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

again with little modification u can fine tune this script.. eliminate Temp table

Select *from

(SELECT top 100 R_ID , text_data,

(SELECT COUNT(*) FROM T1 AS b

WHERE b.R_ID = a.R_ID

AND (b.text_data < a.text_data

OR (b.text_data = a.text_data))) AS rownum

FROM T1 AS a

ORDER BY R_ID, text_data) A

where rownum<4

Madhu

|||

I tested these other queries too with my "million-row" mockup and here is what I think:

If running SQL Server 2005 use either Madhu's or my first query|||

Your getting a partial result because of these line:

(SELECT top 100 R_ID , text_data,

If You change it to

(SELECT top 100 PERCENT R_ID , text_data,

You should get all the rows....

|||

Thanks for catching that lucky; I tested this one too and your original query is still much faster.

Dave

|||

ms sql server

data:

idno Email Category
1 a@.a.com c
2 b@.b.com c
3 b@.b.com c
4 b@.b.com c

only unique email address record are requierd.
1 a@.a.com c
2 b@.b.com c

what could be the query for such requirements.

How to get Top 1 in Join on Calendar table...?

I would only like to see the latest calendar Sale per #c1 record as (select
top
1 * from calen order by ondate desc)
It doesn't matter if it's type is RA or DS, just the latest ONE.
set nocount on
-- Hist
select id = 2 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '03/06/2006' ,Ac
tv =
'DS' ,Num = 500 ,Type = 'A' ,recid = '123457'
into #h
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Needs' ,ondate = '01/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'A' ,recid = '123456'
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '02/12/2006' ,Actv
=
'RA' ,Num = 2000 ,Type = 'S' ,recid = '123458'
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '09/23/2005' ,Actv
=
'RA' ,Num = 1500 ,Type = 'S' ,recid = '223458'
insert #h
select id = 3 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '12/12/2005' ,Ac
tv =
'DS' ,Num = 7500 ,Type = 'A' ,recid = '123459'
insert #h
select id = 4 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '11/10/2005' ,Ac
tv =
'RA' ,Num = 1300 ,Type = 'A' ,recid = '223456'
-- select * from #h
-- Cal
select id = 2 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '05/06/2006' ,Ac
tv =
'DS' ,Num = 2500 ,Type = 'S' ,recid = '123457'
into #ca
insert #ca
select id = 6 ,userid = 'CUPPSK1' ,Ref = 'Sale' ,ondate = '04/26/2006' ,Actv
=
'DS' ,Num = 2500 ,Type = 'S' ,recid = '123456'
insert #ca
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '09/26/2006' ,Actv
=
'RS' ,Num = 2500 ,Type = 'S' ,recid = '123458'
insert #ca
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '07/02/2006' ,Actv
=
'RA' ,Num = 1200 ,Type = 'S' ,recid = '223459'
insert #ca
select id = 3 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '06/12/2006' ,Act
v =
'DS' ,Num = 2500 ,Type = 'S' ,recid = '123459'
insert #ca
select id = 4 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '06/12/2006' ,Act
v =
'RA' ,Num = 2500 ,Type = 'S' ,recid = '223456'
insert #ca
select id = 5 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '03/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'S' ,recid = '123458'
-- select * from #ca
-- c1
select id = 2 ,Co = 'Vesto Mays' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum'
,recid = '654322'
into #c1
insert #c1
select id = 1 ,Co = 'Rogers' ,MC = 'Baton' ,Key4 = 'Chaz Arme' ,recid = '654
321'
insert #c1
select id = 3 ,Co = 'Fishy' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum' ,recid
=
'654323'
insert #c1
select id = 4 ,Co = 'Carmers' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum' ,rec
id =
'654324'
insert #c1
select id = 5 ,Co = 'Keys' ,MC = 'Baton' ,Key4 = 'Chaz Arme' ,recid = '65432
5'
insert #c1
select id = 6 ,Co = 'Smartie' ,MC = 'Baton' ,Key4 = 'Kelsy Cupps' ,recid =
'654326'
-- select * from #c1
-- c2
select id = 1 ,uawrv = 3000 ,udsawrv = 20000 ,unewcxd = '07/01/2006' ,udscxd
=
null ,recid = '9876543'
into #c2
insert #c2
select id = 2 ,uawrv = 10000 ,udsawrv = 7000 ,unewcxd = '04/01/2006' ,udscxd
=
null ,recid = '9876543'
insert #c2
select id = 3 ,uawrv = 15000 ,udsawrv = 5000 ,unewcxd = '12/01/2006' ,udscxd
=
null ,recid = '9876543'
insert #c2
select id = 4 ,uawrv = 23000 ,udsawrv = 500 ,unewcxd = '10/12/2006' ,udscxd
=
null ,recid = '9876543'
insert #c2
select id = 5 ,uawrv = 4000 ,udsawrv = 120 ,unewcxd = '02/03/2007' ,udscxd =
null ,recid = '9876543'
insert #c2
select id = 6 ,uawrv = 2500 ,udsawrv = 12000 ,unewcxd = '12/09/2009' ,udscxd
=
null ,recid = '9876543'
-- select * from #c2
-- staff
select id = 2 ,StaffName = 'Lester Mothartes' ,userid = 'MOTHARL1' ,MC = 'Lo
s
Angeles' ,terrid = 2001 ,awrv = 300 ,dsawrv = 2000 ,recid = '654322'
into #s
insert #s
select id = 1 ,StaffName = 'Steve Arme' ,userid = 'ARMES1' ,MC = 'Baton'
,terrid = 2004 ,awrv = 300 ,dsawrv = 2000 ,recid = '654321'
insert #s
select id = 3 ,StaffName = 'Lora Candum' ,userid = 'CANDUML1' ,MC = 'Los
Angeles' ,terrid = 2009 ,awrv = 300 ,dsawrv = 2000 ,recid = '654323'
insert #s
select id = 5 ,StaffName = 'Chaz Arme' ,userid = 'ARMEC1' ,MC = 'Baton' ,te
rrid
= 2005 ,awrv = 300 ,dsawrv = 2000 ,recid = '654325'
insert #s
select id = 6 ,StaffName = 'Kelsy Cupps' ,userid = 'CUPPSK1' ,MC = 'Baton'
,terrid = 2003 ,awrv = 300 ,dsawrv = 2000 ,recid = '654326'
-- select * from #s
declare @.endDate int
set @.endDate = 365
-- ========================================
=====================
select distinct id = c1.id
,Company = c1.co
,MarketCenter = c1.MC
,AE = c1.key4
,AWRV = c2.uawrv
,DSAWRV = c2.udsawrv
,CXD = c2.unewcxd
,DSCXD = c2.udscxd
,caRef
,caRecID
,caOnDate
,haRecType
,haRecID
,haRef
,haLastDate
,hsRecID
,hsRef
,hsLastDate
from #c1 c1 with(nolock)
join #c2 c2 with(nolock) on c1.id = c2.id
-- staff
left join (select staffname ,awrv ,dsawrv ,terrid ,userid from #s with(noloc
k))
s on c1.key4 = s.staffname
-- cal
-- ********** how do I only get the latest **************
join (select id ,caRef = ref ,caRecID = recid ,caOnDate = ondate ,userid fro
m
#ca with(nolock)
where (left(type,1)+left(Actv,1) in('SR','DS') and ondate between getdate()
and getdate()+@.enddate)
) ca on c1.id = ca.id and ca.userid = s.userid
-- ********************** <> **********************
-- hist Appt
left join (select id ,haRef = ref ,haRecID = recid ,haLastDate = ondate
,haRecType = Type from #h with(nolock)
where (left(type,1)+left(ref,5) = 'ANeeds' )) ha on c1.id = ha.id
-- hist Sale
left join (select id ,hsRef = ref ,hsRecID = recid ,hsLastDate = ondate
,hsRecType = Type from #h with(nolock)
where type ='S'
and ondate in(select top 1 ondate from #h with(nolock) where type = 'S' orde
r
by ondate asc)
) hs on c1.id = hs.id
group by c1.id ,c1.co ,c1.MC ,c1.key4 ,c2.uawrv ,c2.udsawrv ,c2.unewcxd
,c2.udscxd
,caRecID ,caOnDate ,haRecType ,haRecID ,haRef
,haLastDate ,hsRecID ,hsRef ,hsLastDate ,caRef
order by id
-- ========================================
=====================
-- clean up
drop table #h
drop table #ca
drop table #c1
drop table #c2
drop table #s
TIA
JeffP....JDP@.Work (JPGMTNoSpam@.sbcglobal.net) writes:
> I would only like to see the latest calendar Sale per #c1 record as
> (select top 1 * from calen order by ondate desc)
> It doesn't matter if it's type is RA or DS, just the latest ONE.
I was not able to get your repro to run, since you use case inconsistently,
and I am running a case-sensitive server.
It was also difficult to under stand the keys, since you did not post
CREATE TABLE statements, only SELECT INTO.
But as I understood it:

> -- ********** how do I only get the latest **************
> join (select id ,caRef = ref ,caRecID = recid ,caOnDate = ondate ,userid
> from
> #ca with(nolock)
> where (left(type,1)+left(Actv,1) in('SR','DS') and ondate between
> getdate()
> and getdate()+@.enddate)
> ) ca on c1.id = ca.id and ca.userid = s.userid
> -- ********************** <> **********************
This was the problematic part. Try replacing the derived table with
this query:
select a.id, caRef = a.ref ,caRecID = a.recid ,caOnDate = a.ondate,
a.userid
from #ca a
JOIN (SELECT id, userid, ondate = MAX(ondate)
FROM #ca
where (left(type,1)+left(Actv,1) in('SR','DS')
and ondate between convert(char(8, getdate(), 112) and
dateadd (DAY, @.enddate, convert(char(8), getdate())
I also rewrite the condition ondate, as it seemed like you like
to incldue today. and assuming that ondate is date-only, comparing
to getdate() which has both date and time is not good.
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|||Erland, sorry to say that this doesn't help... also sorry to hear that you w
ork
on a case sensetive environment
Your supplemental query failed due to a group by clause as well as the "on"
portion of the join.
When I cleaned these items up, I still got a row for each condition just lik
e in
my original but with an additional join.
I'm not sure how to phrase my question, I only want the one row as in "top 1
"
which I had used with an order by clause desc which essentially was the same
as
using max(ondate) without needing a group by.
However when using top 1 in a sub query it appears that the top 1 was is nev
er
found, I'm sure there is a good reason for this, so it doesn't matter.
Still searching...
JeffP...
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns979B6E52A678Yazorman@.127.0.0.1...
> JDP@.Work (JPGMTNoSpam@.sbcglobal.net) writes:
> I was not able to get your repro to run, since you use case inconsistently
,
> and I am running a case-sensitive server.
> It was also difficult to under stand the keys, since you did not post
> CREATE TABLE statements, only SELECT INTO.
> But as I understood it:
>
> This was the problematic part. Try replacing the derived table with
> this query:
> select a.id, caRef = a.ref ,caRecID = a.recid ,caOnDate = a.ondate,
> a.userid
> from #ca a
> JOIN (SELECT id, userid, ondate = MAX(ondate)
> FROM #ca
> where (left(type,1)+left(Actv,1) in('SR','DS')
> and ondate between convert(char(8, getdate(), 112) and
> dateadd (DAY, @.enddate, convert(char(8), getdate()
)
>
> I also rewrite the condition ondate, as it seemed like you like
> to incldue today. and assuming that ondate is date-only, comparing
> to getdate() which has both date and time is not good.
> --
> 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|||JDP@.Work (JPGMTNoSpam@.sbcglobal.net) writes:
> Erland, sorry to say that this doesn't help... also sorry to hear that
> you work on a case sensetive environment
> Your supplemental query failed due to a group by clause as well as the
> "on" portion of the join.
> When I cleaned these items up, I still got a row for each condition just
> like in my original but with an additional join.
> I'm not sure how to phrase my question, I only want the one row as in
> "top 1" which I had used with an order by clause desc which essentially
> was the same as using max(ondate) without needing a group by.
> However when using top 1 in a sub query it appears that the top 1 was is
> never found, I'm sure there is a good reason for this, so it doesn't
> matter.
It probably helps if you rewrite your repro to use CREATE TABLE with
definition of keys included. It also helps if you include the expected
output. And of course, it's a good idea to clean up inconsistent use of
case. It is a good recommendation to do all development with a case-
sensitive collation, in case this would be a requirement for production.
I took a guess on what the keys would be, but as I could not get the
script to work, I could not test it. And since there was no expected
result, I would not have been able to verify it anyway. I was hoping,
though, that you could use my suggestion as a starting point.
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|||Erland, thank you for your suggestions...
What's happening is that in my test, I am getting expected results but in li
ve I
do not as there are more aspects.
The Fkeys are the ID.
What I want is one row for each record, with the greatest appropriate recid
for
the calendar, history records.
here is my latest, I don't expect in my world to every be in a case sensetiv
e
environment, I just won't do it.
In the following example yields...notice that for record id = 1 whe have two
nearly identical sales, however one has a greater cal ondate, which is now
returned by the final qry.
However in production with real tables, additional fields I still am getting
a
few rows, if you wish to see my real qry send me a pm.
JeffP...
id userid Ref ondate Actv Num Type recid
-- -- -- -- -- -- -- --
1 ARMEC1 SaleR 09/26/2006 RA 2500 S 123458
1 ARMEC1 SaleR 07/02/2006 RA 1200 S 223459
caRecID caRef caOnDate userid id
-- -- -- -- --
123458 SaleR 09/26/2006 ARMEC1 1
id Company MarketCenter AE AWRV DSAWRV CXD
DSCXD caActv caRef caRecID caOnDate hnfRecID hnRef hnLastDate hpRecID
hpRef hpLastDate
-- -- -- -- -- -- --
--
-- -- -- -- -- -- -- -- -- --
--
-- --
1 Rogers Baton Chaz Arme 3000 20000
07/01/2006 01/01/1900 RA SaleR 123458 09/26/2006 123456 Needs 01/26/2
006
NULL NULL NULL
2 Vesto Mays Los Angeles Lora Candum 10000 7000
04/01/2006 01/01/1900 DS SaleD 123457 05/06/2006 123457 Needs 03/06/2
006
NULL NULL NULL
4 Carmers Los Angeles Lora Candum 23000 500
10/12/2006 01/01/1900 RA RA 223456 06/12/2006 223456 Needs 11/10/2
005
NULL NULL NULL
6 Smartie Baton Kelsy Cupps 2500 12000
01/01/1900 01/01/2007 DS Sale 123456 04/26/2006 223457 Needs 11/01/2
005
223459 Propo 11/10/2005
-- begin
set nocount on
-- Hist
select id = 2 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '03/06/2006' ,Ac
tv =
'DS' ,Num = 500 ,Type = 'A' ,recid = '123457'
into #h
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Needs' ,ondate = '01/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'A' ,recid = '123456'
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '02/12/2006' ,Actv
=
'RA' ,Num = 2000 ,Type = 'S' ,recid = '123458'
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'First' ,ondate = '09/23/2005' ,Actv
=
'RA' ,Num = 1500 ,Type = 'A' ,recid = '223458'
insert #h
select id = 3 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '12/12/2005' ,Ac
tv =
'DS' ,Num = 7500 ,Type = 'A' ,recid = '123459'
insert #h
select id = 4 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '11/10/2005' ,Ac
tv =
'RA' ,Num = 1300 ,Type = 'A' ,recid = '223456'
insert #h
select id = 6 ,userid = 'CUPPSK1' ,Ref = 'Needs' ,ondate = '11/01/2005' ,Act
v =
'RA' ,Num = 1300 ,Type = 'A' ,recid = '223457'
insert #h
select id = 6 ,userid = 'CUPPSK1' ,Ref = 'Propo' ,ondate = '11/10/2005' ,Act
v =
'RA' ,Num = 1300 ,Type = 'A' ,recid = '223459'
-- select * from #h
-- Cal
select id = 2 ,userid = 'CANDUML1' ,Ref = 'SaleD' ,ondate = '05/06/2006' ,A
ctv
= 'DS' ,Num = 2500 ,Type = 'S' ,recid = '123457'
into #ca
insert #ca
select id = 6 ,userid = 'CUPPSK1' ,Ref = 'Sale' ,ondate = '04/26/2006' ,Actv
=
'DS' ,Num = 2500 ,Type = 'S' ,recid = '123456'
insert #ca
select id = 1 ,userid = 'ARMEC1' ,Ref = 'SaleR' ,ondate = '09/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'S' ,recid = '123458'
insert #ca
select id = 1 ,userid = 'ARMEC1' ,Ref = 'SaleR' ,ondate = '07/02/2006' ,Actv
=
'RA' ,Num = 1200 ,Type = 'S' ,recid = '223459'
-- insert #ca
-- select id = 3 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '06/12/2006' ,
Actv
= 'DS' ,Num = 2500 ,Type = 'S' ,recid = '123459'
insert #ca
select id = 4 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '06/12/2006' ,Act
v =
'RA' ,Num = 2500 ,Type = 'S' ,recid = '223456'
insert #ca
select id = 5 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '03/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'S' ,recid = '123458'
-- select * from #ca
-- c1
select id = 2 ,Co = 'Vesto Mays' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum'
,recid = '654322'
into #c1
insert #c1
select id = 1 ,Co = 'Rogers' ,MC = 'Baton' ,Key4 = 'Chaz Arme' ,recid = '654
321'
insert #c1
select id = 3 ,Co = 'Fishy' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum' ,recid
=
'654323'
insert #c1
select id = 4 ,Co = 'Carmers' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum' ,rec
id =
'654324'
insert #c1
select id = 5 ,Co = 'Keys' ,MC = 'Baton' ,Key4 = 'Chaz Arme' ,recid = '65432
5'
insert #c1
select id = 6 ,Co = 'Smartie' ,MC = 'Baton' ,Key4 = 'Kelsy Cupps' ,recid =
'654326'
-- select * from #c1
-- c2
select id = 1 ,uawrv = 3000 ,udsawrv = 20000 ,unewcxd = '07/01/2006' ,udscxd
=
'01/01/1900' ,recid = '9876543'
into #c2
insert #c2
select id = 2 ,uawrv = 10000 ,udsawrv = 7000 ,unewcxd = '04/01/2006' ,udscxd
=
'01/01/1900' ,recid = '9876543'
insert #c2
select id = 3 ,uawrv = 15000 ,udsawrv = 5000 ,unewcxd = '12/01/2006' ,udscxd
=
'01/01/1900' ,recid = '9876543'
insert #c2
select id = 4 ,uawrv = 23000 ,udsawrv = 500 ,unewcxd = '10/12/2006' ,udscxd
=
'01/01/1900' ,recid = '9876543'
insert #c2
select id = 5 ,uawrv = 4000 ,udsawrv = 120 ,unewcxd = '02/03/2007' ,udscxd =
'01/01/1900' ,recid = '9876543'
insert #c2
select id = 6 ,uawrv = 2500 ,udsawrv = 12000 ,unewcxd = '01/01/1900' ,udscx
d =
'01/01/2007' ,recid = '9876543'
-- select * from #c2
-- staff
select id = 2 ,StaffName = 'Lester Mothartes' ,userid = 'MOTHARL1' ,MC = 'Lo
s
Angeles' ,terrid = 2001 ,awrv = 300 ,dsawrv = 2000 ,recid = '654322'
into #s
insert #s
select id = 1 ,StaffName = 'Steve Arme' ,userid = 'ARMES1' ,MC = 'Baton'
,terrid = 2004 ,awrv = 300 ,dsawrv = 2000 ,recid = '654321'
insert #s
select id = 3 ,StaffName = 'Lora Candum' ,userid = 'CANDUML1' ,MC = 'Los
Angeles' ,terrid = 2009 ,awrv = 300 ,dsawrv = 2000 ,recid = '654323'
insert #s
select id = 5 ,StaffName = 'Chaz Arme' ,userid = 'ARMEC1' ,MC = 'Baton' ,te
rrid
= 2005 ,awrv = 300 ,dsawrv = 2000 ,recid = '654325'
insert #s
select id = 6 ,StaffName = 'Kelsy Cupps' ,userid = 'CUPPSK1' ,MC = 'Baton'
,terrid = 2003 ,awrv = 300 ,dsawrv = 2000 ,recid = '654326'
-- select * from #s
declare @.endDate int
set @.endDate = 365
-- ========================================
=====================
select distinct id = c1.id
,Company = c1.co
,MarketCenter = c1.MC
,AE = c1.key4
,AWRV = c2.uawrv
,DSAWRV = c2.udsawrv
,CXD = c2.unewcxd
,DSCXD = c2.udscxd
,caActv = (select case when cast(isnull(cab.caOndate,'01/01/1900')as datetime)d">
>= cast(isnull(cad.caOndate,'01/01/1900')as datetime)
and cast(isnull(cab.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cab.Actv
when cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(cab.caOndate,'01/01/1900')as datetime)
and cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cad.Actv else car.Ac
tv
end)
,caRef = (select case when cast(isnull(cab.caOndate,'01/01/1900')as datetime
) >=
cast(isnull(cad.caOndate,'01/01/1900')as datetime)
and cast(isnull(cab.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cab.caRef
when cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(cab.caOndate,'01/01/1900')as datetime)
and cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cad.caRef else car.
Actv
end)
,caRecID = (select case when cast(isnull(cab.caOndate,'01/01/1900')as datetime)ed">
>= cast(isnull(cad.caOndate,'01/01/1900')as datetime)
and cast(isnull(cab.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cab.caRecID
when cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(cab.caOndate,'01/01/1900')as datetime)
and cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cad.caRecID else
car.caRecID end)
,caOnDate = (select case when cast(isnull(cab.caOndate,'01/01/1900')as datetime)red">
>= cast(isnull(cad.caOndate,'01/01/1900')as datetime)
and cast(isnull(cab.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cab.caOndate
when cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(cab.caOndate,'01/01/1900')as datetime)
and cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cad.caOndate else
car.caOndate end)
,hnfRecID = (select case when cast(isnull(han.LastDate,'01/01/1900')as
datetime) > cast(isnull(haf.LastDate,'01/01/1900')as datetime)
then han.Recid else haf.Recid end)
,hnRef = (select case when cast(isnull(han.LastDate,'01/01/1900')as datetime
) >
cast(isnull(haf.LastDate,'01/01/1900')as datetime)
then han.Ref else haf.Ref end)
,hnLastDate = (select case when cast(isnull(han.LastDate,'01/01/1900')as
datetime) > cast(isnull(haf.LastDate,'01/01/1900')as datetime)
then han.LastDate else haf.LastDate end)
,hpRecID = (select case when cast(isnull(happ.LastDate,'01/01/1900')as datetime)red">
> cast(isnull(haps.LastDate,'01/01/1900')as datetime)
then happ.recid else haps.recid end)
,hpRef = (select case when cast(isnull(happ.LastDate,'01/01/1900')as datetime)ed">
> cast(isnull(haps.LastDate,'01/01/1900')as datetime)
then happ.ref else haps.ref end)
,hpLastDate = (select case when cast(isnull(happ.LastDate,'01/01/1900')as
datetime) > cast(isnull(haps.LastDate,'01/01/1900')as datetime)
then happ.lastdate else haps.lastdate end)
into #main
from #c1 c1 with(nolock)
join #c2 c2 with(nolock) on c1.id = c2.id
-- staff
left join(select staffname ,awrv ,dsawrv ,terrid ,userid from #s with(nolock
))
s on c1.key4 = s.staffname
-- cal
-- how do I only get the latest
left join (select caRecID = recid ,caRef = ref ,caOnDate = ondate ,userid ,i
d
,Actv from #ca with(nolock)
where (left(type,1)+left(Actv,1) in('SD') and ondate between getdate() and
getdate()+@.enddate)
group by id ,ref ,recid ,ondate ,userid ,Actv ) cad on c1.id = cad.id and
cad.userid = s.userid
left join (select caRecID = recid ,caRef = ref ,caOnDate = max(ondate) ,user
id
,id ,Actv from #ca with(nolock)
where (left(type,1)+left(Actv,1) in('SR') and ondate between getdate() and
getdate()+@.enddate)
group by id ,ref ,recid ,ondate ,userid ,Actv) car on c1.id = car.id and
car.userid = s.userid
left join (select top 1 caRecID = recid ,caRef = ref ,caOnDate = max(ondate)
,userid ,id ,Actv from #ca with(nolock)
where (left(type,1)+left(Actv,1) in('SD','SR') and ondate between getdate()
and getdate()+@.enddate)
group by id ,ref ,recid ,ondate ,userid ,Actv ) cab on c1.id = cab.id and
cab.userid = s.userid
-- hist Appt
left join (select id ,Ref = ref ,RecID ,LastDate = ondate ,Type ,userid from
#h with(nolock)
where (left(type,1)+left(ref,5) = 'AFirst' )) haf on c1.id = haf.id and
haf.userid = s.userid
left join (select id ,Ref = ref ,RecID ,LastDate = ondate ,Type ,userid from
#h with(nolock)
where (left(type,1)+left(ref,5) = 'ANeeds' )) han on c1.id = han.id and
han.userid = s.userid
-- hist proposal
left join (select id ,Ref = ref ,RecID ,LastDate = ondate ,Type ,userid from
#h with(nolock)
where (left(type,1)+left(ref,5) = 'APropo' )) happ on c1.id = happ.id and
happ.userid = s.userid
left join (select id ,Ref = ref ,RecID ,LastDate = ondate ,Type ,userid from
#h with(nolock)
where (left(type,1)+left(ref,5) = 'APrese' )) haps on c1.id = haps.id and
haps.userid = s.userid
order by c1.id
-- ========================================
=====================
select * from #ca where id = 1
-- select * from #h where id = 6
select top 1 caRecID = recid ,caRef = ref ,caOnDate = max(ondate) ,userid ,i
d
from #ca with(nolock)
where (left(type,1)+left(Actv,1) in('SR','SD') and ondate between getdate()
and
getdate()+@.enddate)
group by id ,ref ,recid ,ondate ,userid
delete #main where carecid is null
select * from #main
-- clean up
drop table #h
drop table #ca
drop table #c1
drop table #c2
drop table #s
drop table #main
-- end
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns979B5FA7E9005Yazorman@.127.0.0.1...
> JDP@.Work (JPGMTNoSpam@.sbcglobal.net) writes:
>
> It probably helps if you rewrite your repro to use CREATE TABLE with
> definition of keys included. It also helps if you include the expected
> output. And of course, it's a good idea to clean up inconsistent use of
> case. It is a good recommendation to do all development with a case-
> sensitive collation, in case this would be a requirement for production.
> I took a guess on what the keys would be, but as I could not get the
> script to work, I could not test it. And since there was no expected
> result, I would not have been able to verify it anyway. I was hoping,
> though, that you could use my suggestion as a starting point.
> --
> 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

How to get todays date in format YY/MM/DD and to compare it to another date passed into th

I need to do the following and am hoping someone can help me out.

I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to the
stored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the date
passed into the stored proc needs to be compared to todays date. Todays date must be determined in
the SQL.

So basically here is my pseudo code for what I am trying to accomplish. Basically I just am after
the comparison of the two values:

If @.BeginDate < TodaysDate

The difficult part is how to obtain the value for "TodaysDate"

Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in.

Can someone please code this out for me in Microsoft SQL. I would be forever grateful.

I figured out what I needed to know, but will have further questions and will need help. Thanks to all.

how to get Time time difference in sql?

Could any one please tell me how to find time difference between two dates?

i have two fields in database as datetime data type.i need to get time difference between this two fields.how to do that?

i use this one

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

but it giving me results as

1.00000000

1.250000

3.00000

i jus want it to be

1

1.25

3

How to do this?

Thanks for any help.

To find the time difference in days, hours, minutes. Try it, you can replace the dates with you column datetime.

selectDATEDIFF(day,2007-11-30,2007-11-20)AS NumberOfDays,DATEDIFF(hour,2007-11-30,2007-11-20)AS NumberOfHours,DATEDIFF(minute,2007-11-30,2007-11-20)AS NumberOfMinutesFROM test_table

|||

hi, thanks for the reply i dint useDATEDIFF(hour,2007-11-30,2007-11-20) this option jus because

i have one data as 10:45 and another as 12:00 it gives me time diff as 2 in hrs but actual diff is only 1:15 min thats y im convertin to min first and dividing it by 60.

but my outputs are like 1.02345,2.4567

i jus need to be like 1, 2.45

|||

Tweety@.net:

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

simple...

just write this query as...

SELECT outdate, ROUND(cast((datediff(mi, outtime, intime) / 60.0) as FLOAT),2) AS TimeUtilized FROM breaktime

and it should work.

hope this helps./.

|||

Try casting the result to decimal data type. The syntax is cast ( <number> as decimal ( 5 , 2 ) ).

sql

How to get Time in SmallDateTime?

Hi,
I just want to get the value from a smalldatetime into time only in a SQL Statement
10/10/2005 10:00:00 AM -- > 10:00:00 AM
What would be the best approached?
Thanks,
VinceThis will truncate all datetime values to Jan 1, 1900, leaving only the time portion. To format the resulting value, look up the CAST and CONVERT functions.
declare @.MyTime smalldatetime
set @.MyTime = getdate()
select dateadd(d, -datediff(d, 0, @.MyTime), @.MyTime)

How to get thru security of Sql server http endpoint?

I have write the coding to create http endpoint in the sql server 2005, but if i type my pc ownself ip address (ex, 192.168.0.110) to connect to the sql server http endpoint, it prompt me for Username and Password. I didn't set any username and password before, what should i type ? Or what should i do to pass thru this authentication?

Thanks in advance.

Cheers,

Winson

Hi Winson,

For security reasons, the SQL Server 2005 http endpoints for Native XML Web Services require the client application to send user credentials. By default, the user account who created the HTTP endpoint will have access to connect to the endpoint. Users of the sysadmin role will also have access to the endpoint. All other SQL login accounts, must be granted explicit connect permissions. Please refer to Books Online's (BOL) GRANT Endpoint permission topic available at http://msdn2.microsoft.com/en-us/library/ms187811.aspx for additional information. For information regarding how to specify user credentials in the client application in .Net Frameworks, please refer to the following BOL topic http://msdn2.microsoft.com/en-us/library/ms175929.aspx.

Jimmy

|||

Hello Jimmy,

Thanks for the help, actually the situation is like this. I using nusoap (php) to connect to the Sql server http endpoint. but i fail to pass thru the security of sql sever http endpoint over the Internet. What do you think? Do you have any idea regarding this this of connection? nusoap (php) <> sql server http endpoint?

Thanks in advanced.

Cheers,

Winson

how to get this resultset...

Hi,
I have 3 tables as follow :
Kanji :
kanji_id
...
References :
ref_id
...
KanjiRefs
kref_idkanji
kref_idref
kref_value
...
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks!
ibiza
Try the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan
|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvan

how to get this resultset...

Hi,

I have 3 tables as follow :

Kanji :
kanji_id
...

References :
ref_id
...

KanjiRefs
kref_idkanji
kref_idref
kref_value
...

So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...

I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?

2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL

I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3

however, this obviously does not include kanjis having no reference
#3...

any help would be greatly appreciated, thanks! :)
ibizaTry the following (untested):

SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)

SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji

Razvan|||wow! It all works :P

many thanks!

Razvan Socol wrote:

Quote:

Originally Posted by

Try the following (untested):
>
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
>
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
>
Razvan

how to get this resultset...

Hi,
I have 3 tables as follow :
Kanji :
kanji_id
...
References :
ref_id
...
KanjiRefs
kref_idkanji
kref_idref
kref_value
...
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks! :)
ibizaTry the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvansql

how to get this resultset...

Hi,
I have 3 tables as follow :
Kanji :
kanji_id
...
References :
ref_id
...
KanjiRefs
kref_idkanji
kref_idref
kref_value
...
So, there is a many-to-many relationship between Kanjis and References
(one kanji may have more than one reference type, and a reference type
may be set to more than one kanji).
For example, one kanji may have the value 'abc' for reference type #1,
and the value 'def' for reference type #2, another kanji may also have
the reference type #1, but have instead the value '123' and so on...
I have two questions :
1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)
within their list of references?
2) How to get the value of all the kanjis that have the reference #3,
but still get other kanjis that do not have the reference #3...for
example, if I had 3 kanjis, with the two first having values 'abc' and
'def' for reference #3, and the last one having no reference #3, I'd
like to get that resultset :
kanji_id kref_value
1 'abc'
2 'def'
3 NULL
I manage to get all the kanjis that have reference # 3 with the
following query :
SELECT kanji_id, kref_value
FROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_id
WHERE kref_idref = 3
however, this obviously does not include kanjis having no reference
#3...
any help would be greatly appreciated, thanks!
ibizaTry the following (untested):
SELECT kanji_id FROM Kanji
WHERE kanji_id NOT IN (
SELECT kref_idkanji FROM KanjiRefs
WHERE kref_idref=3
)
SELECT kanji_id, (
SELECT kref_value FROM KanjiRefs
WHERE kref_idref=3
AND kref_idkanji=kanji_id
) as kref_value
FROM Kanji
Razvan|||wow! It all works :P
many thanks!
Razvan Socol wrote:
> Try the following (untested):
> SELECT kanji_id FROM Kanji
> WHERE kanji_id NOT IN (
> SELECT kref_idkanji FROM KanjiRefs
> WHERE kref_idref=3
> )
> SELECT kanji_id, (
> SELECT kref_value FROM KanjiRefs
> WHERE kref_idref=3
> AND kref_idkanji=kanji_id
> ) as kref_value
> FROM Kanji
> Razvan

How to get this relation(Need Sql Query)?

Hi friends

I have one Table called tblCategory.

I have three Column CatID, CatName, ParentID

I have many records in this Table

CatID CatName ParentID

1 Cat1 0

2 Cat2 1

3 Cat3 1

4 Cat4 2

5 Cat5 2

6 Cat6 0

7 Cat7 6

8 Cat8 6

9 Cat9 7

10 Cat10 7

11 Cat11 8

Here I have Main Category which has ParentID 0 [ Cat1 and Cat6 ]

I Have Sub categories of Cat1 Which has ParentID 1(CatID 1 of Cat1) [ Cat2 and Cat 3 ]

Cat 2 has also sub category with ParentID 2 (CatID 2 of Cat2) [ Cat4 and Cat5]

I want result looks like as

Cat1 Cat6 ->>>>>ParentID 0
- Cat2 -Cat7 ->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
- - Cat4 - -Cat9 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- - Cat5 - -Cat10 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- Cat3 -Cat8 -->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
--No record --Cat11

Can anybody give me solution?

Thanks

See if this article on working out JOIN syntax will help:http://www.mikesdotnetting.com/Article.aspx?ArticleID=72

|||

There are two tables but in my case I have only one table.

|||

impathan:

There are two tables but in my case I have only one table.

So you have. Sorry, I saw the word Relation in the title of your post and thought you were after somerthing else.

Is what you are after something like the threaded view of a discussion board or similar? If so, one solution might be a recursive function. If not, could you explain the logic beind the diagram you have supplied? I can't, for example see the basis on which Cat3 and Cat8 on the penultimate line are related.

|||

impathan:

There are two tables but in my case I have only one table.

You can query a table twice in the same statement and join it to itself. :) You just need to give each usage of the table a different table alias.

how to get this output ?

Hi Everybody,
I have a Table Call Referrals like this
EmpId Cl_Date RefrlNo OptNo
1 NULL 2565 2222
1 01/01/2007 2565 2223
2 01/02/2007 2567 2230
3 01/02/2007 2568 2231
3 01/03/2007 2568 2232
4 NULL 2569 2245
4 NULL 2570 2246

From this table i need to get the Emp Id, Cl_date RefrlNO,OptNo
where EmpId must have repeated more than one time as well as it must have at least one Cl_Date + at least one NULL Cl_Date

after all these requirement satisfy , i need to pick again a row data where Cl_Date Is null ?

So the out put should be like this
EmpId Cl_Date RefrlNo OptNo
1 NULL 2565 2222

how do i do this task ?

any idea ?
regards
suis

Code Snippet

createtable Referrals

(

EmpId int,

Cl_Date datetime,

RefrlNo int,

OptNo int

)

GO

insertinto Referrals values(1,NULL, 2565, 2222)

insertinto Referrals values(1,'01/01/2007', 2565, 2223)

insertinto Referrals values(2,'01/02/2007', 2567, 2230)

insertinto Referrals values(3,'01/02/2007', 2568, 2231)

insertinto Referrals values(3,'01/03/2007', 2568, 2232)

insertinto Referrals values(4,NULL,2569,2245)

insertinto Referrals values(4,NULL,2570,2246)

SELECT EmpId, Cl_date, RefrlNo, OptNo FROM Referrals

WHERE

EmpID IN(select EmpId from Referrals where Cl_date isNULL)

AND

EmpID IN(select EmpId from Referrals where Cl_date isNOTNULL)

AND

Cl_date ISNULL

If for EmpId we have line where Cl_date is NULL (see first IN clause) and line where Cl_Date is not NULL (see second IN clause), its that we have at least two lines with same EmpID

How to get this output

hi their
i create a temp table called tempEmpTran and it content a fileds like
Refrlno Description opt-refno

1474 New 5
1474 followup 3
1474 followup 3
1470 new 7

No my problem is i need to eleminate the refrlno which Description <> 'followup',but again the problem is if i see any referrals which repeating more than once then i need to check if any of that Rrefrlno got any Description which equal to the followup, if yes i need to eliminate the whole Referealno from the list,

Refrlno Description opt-refno
1474 New 5 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1470 new 7 -should include,cos its got description 'new'

so once all these condition satisfied then the output should looks like this

Refrlno Description opt-refno
1470 new 7

Any Idea ?
regards
Nirangatry this:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Formatting seems to be screwed on that post, heres the query again:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Hi sam,
Many thanks to u r fast respone,but in that query there is not line which eleminate the Description='Followup' ?
any idea ?
regards
Niranga|||Have you ran it?

It only returns records who have a single refrlno... so there shouldn't be any need to filter based on your sample data.

Have a play and try to understand what it does. Then you can expand on it |||

Hey, you could do this 2 ways:

Select *

from tempEmpTran

Where Refrlno not in (Select Refrlno from tempEmpTran where Description = 'Followup')

Select *

from tempEmpTran a

Where not exists (Select Refrlno from tempEmpTran b where Description = 'Followup' and a.Refrlno = b.Refrlno)

BobP

|||Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga|||

Niranga wrote:

Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga

Sorry about that, didn't bother creating the tables and testing. You just need to add an alias to the column name
count(*) becomes
count(*) as [Cnt]|||hi,

here's another alternative.
SELECT *
INTO #tempEmpTran
FROM (
SELECT 1474 AS Refrlno
, 'New' AS Description
, 5 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1470 AS Refrlno
, 'New' AS Description
, 7 AS [opt-refno]
) a

SELECT a.*
FROM #tempEmpTran a INNER JOIN
(
SELECT Refrlno
, IsNew = SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END))
FROM #tempEmpTran
GROUP BY
Refrlno
HAVING SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END)) = 0
) b ON a.RefrlNo = b.RefrlNo

DROP TABLE #tempEmpTran|||

here you go..

Code Snippet

Create Table #data (

[Refrlno] int ,

[Description] Varchar(100) ,

[opt-refno] int

);

Insert Into #data Values('1474','New','5');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1470','new','7');

Select

*

from

#Data Main

Where

Not Exists

(

Select

Sub.[Refrlno]

From

#Data Sub

Where

Main.[Refrlno]=Sub.[Refrlno]

And Sub.[Description] = 'followup'

)

How to get this output

Hi evrybody
i have a question like this
i need to display the row data like this
If i use this sql
select datediff(yyyy,'01/01/2001','01/01/2004')
i get the output = 4

problem is sometime first parameter will be null
like this
select datediff(yyyy,NULL,'01/01/2004'),'N/A')
in that case my result looks like NULL

But what i need to see is if the result is null then i need to display like this N/A

How to do this task ?
regards
suis

Hey,

You can use the isnull method, so that if a value is null, it will return another value, like 'N/A' in this case. Though I don't know how that could work in a datediff method. Rather, you may want to wrap the isnull around the entire datediff call, so that if it returns null, you can return 'N/A'.

|||Hi i already used isnull
like this way
select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')
but it giving me error !

"Conversion failed when converting the varchar value 'N/A' to data type int."
regards
suis|||

Try changing this:

select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')

to

select isnull(convert(varchar(4), datediff(yyyy,NULL,'01/01/2004')),'N/A')

|||Hi Kent
Thanks for ur comments,
its worked out
regards
suis

sql

How to get this out put ?

Hi everybody
I have a requirement like this
I have a table call Child which looks like following

ChildId ChildAgeByYear ReferralSoure NoOfReferral
Ch01 02 Self 2
Ch01 02 Open 1
Ch03 02 Self 1

Now my problem is I need to display the above Child table information like this

AgeGroup ReferralSoure NoOfReferral
0 0
1 0
2 Self 3
Open 1
3 0
4 0

I need to count the child age group by child age like, 1, 2, 3, 4, and their ReferralSource and NoOfReferral for each child depends on the ReferralSource

Any Idea ?

Regards
Suis

Perhaps something like this:

declare @.child table
( ChildId varchar(5),
ChildAgeByYear integer,
ReferralSource varchar(12),
NoOfReferral integer
)
insert into @.child
select 'Ch01', 2, 'Self', 2 union all
select 'Ch01', 2, 'Open', 1 union all
select 'Ch03', 2, 'Self', 1
--select * from @.child

select AgeGroup,
isnull(ReferralSource, '') as ReferralSource,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) as NoOfReferral
from ( select 0 as AgeGroup union all select 1 union all
select 2 union all select 3 union all select 4
) as n
left join @.child c
on n.AgeGroup = c.ChildAgeByYear
group by AgeGroup,
ReferralSource
order by AgeGroup,
sum( case when NoOfReferral is null then 0
else NoOfReferral end
) desc

/*
AgeGroup ReferralSource NoOfReferral
-- --
0 0
1 0
2 Self 3
2 Open 1
3 0
4 0
*/

|||Thank you very much
4 your quick response,i will let u now the outcome once i test this
best regards
suis

How to get this out put ?

Hi everybody I have a table call Child
and the information like this

ChildId Department Born_Dttm
01 ICU 01/01/2007
02 NormatWard 01/01/2006
03 ICU 01/01/2005

I need to get this out put like i need to count how many child attached to a particular department depending on child age ?

Department < 5 Months > 5Months 1 Year 2Year
ICU 1 0 0 1
NormatWard 0 0 1 0

Age calculation should be up to todays date ?

Any Idea ?
regards
suis

Here are a couple of resources detailing how to do this.

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

|||Hi Arni
thank you for u r quick response and many thanks to u r link
actually i have learned some thing out of that link and i could produce some SQL
Bellow is my SQL

DECLARE @.Child TABLE

(

[ChildId] varchar(2),

[Department] varchar(10),

[Born_Dttm] varchar(12)

)

INSERT INTO @.Child([ChildId], [Department], [Born_Dttm])

SELECT '01', 'ICU', '01/01/2007' UNION ALL

SELECT '02', 'NormalWard', '01/01/2006' UNION ALL

SELECT '03', 'ICU', '01/01/2005'

select [Department] ,

(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch1 where datediff(mm,[Born_Dttm],getdate())/12 < 1 AND ch1.[Department]=ch.[Department] ) as ZeroToOneYear,
(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch2 where datediff(mm,[Born_Dttm],getdate())/12 >= 1 AND datediff(mm,[Born_Dttm],getdate())/12 < 2 AND ch2.[Department]=ch.[Department] ) as OneTOTwoYear,
(select count(datediff(mm,[Born_Dttm],getdate())/12) from @.Child ch2 where datediff(mm,[Born_Dttm],getdate())/12 >= 2 AND datediff(mm,[Born_Dttm],getdate())/12 < 3 AND ch2.[Department]=ch.[Department] ) as TwoTOThreeYear
from @.Child ch
GROUP BY ch.[Department]

Now the problem is i can't get the Months calculation ?
can u help me to get months calculation
like 0 to 5 months ? and 5 to 1 year ?
Any Idea ?
regards
suis|||

This might work more efficiently. (Note the ZeroToOneYear is double counting the Months columns.)

Code Snippet

DECLARE @.Child TABLE
( [ChildId] varchar(2),
[Department] varchar(10),
[Born_Dttm] varchar(12)
)

INSERT INTO @.Child VALUES ( '01', 'ICU', '01/01/2007' )
INSERT INTO @.Child VALUES ( '02', 'NormalWard', '01/01/2006' )
INSERT INTO @.Child VALUES ( '03', 'ICU', '01/01/2005' )

SELECT
[Department],
[Under 5 Months] = sum( CASE WHEN datediff( month, Born_Dttm, getdate() ) < 5 THEN 1 ELSE 0 END ),
[5 MonthsToOneYear] = sum( CASE WHEN datediff( month, Born_Dttm, getdate() ) BETWEEN 5 AND 12 THEN 1 ELSE 0 END ),
[ZeroToOneYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 0 THEN 1 ELSE 0 END ),
[OneToTwoYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 1 THEN 1 ELSE 0 END ),
[TwoTOThreeYear] = sum( CASE datediff( year, Born_Dttm, getdate() ) WHEN 2 THEN 1 ELSE 0 END )
from @.Child ch
GROUP BY ch.[Department]

Department Under 5 Months 5 MonthsToOneYear ZeroToOneYear OneToTwoYear TwoTOThreeYear
- -- -- - --
ICU 1 0 1 0 1
NormalWard 0 0 0 1 0

|||Try this:

select [Department],

sum(case when age_mos < 5 then 1 else 0 end) as '< 5 months',

sum(case when age_mos > 4 and age_mos < 12 then 1 else 0 end) as '> 5 months',

sum(case when age_mos > 11 and age_mos < 24 then 1 else 0 end) as '1 year',

sum(case when age_mos > 23 then 1 else 0 end) as '2 year'

from

(select [Department], datediff(mm, [Born_Dttm], getdate()) as age_mos

from @.child) as t

group by [Department]

|||Thanks everybody for this valuable comments
now i could manage to sort out with the help of this forum
this is great help for me,
and this is the place to learn ........

many thanks.......to MSDN forum members ..........

regards
suis

How to get this out put !

Hi i have a table call Employee
Emp_Id Area_Code Temp_Tran_Id
02 2CL NULL
02 2CL 01235
03 3NY NULL
03 2CL 1452
08 2CL NULL

I need to get this out put from that table data !

Emp_Id Area_Code Temp_Tran_Id
02 2CL 01235
03 3NY NULL
03 2CL 1452
08 2CL NULL

These are the rules i must follow when getting that out put

Emp_Id and the Area_Code Cannot be repeated same time ,

if it's repeated i need to get the record where Temp_Tran_Id is not null

Any Idea ?

Code Snippet

select Emp_Id, Area_Code, max(Temp_Tran_Id) as TT_Id
from Employee
group by Emp_Id, Area_Code

But what you have to do when there's several records with the same Emp_Id and Area_Code and in some of them Temp_Tran_Id is not null?|||

Perhaps something like this:

declare @.employee table
( Emp_id varchar(5),
Area_Code char(3),
Temp_Tran_Id varchar(12)
)

insert into @.employee
select '02', '2CL', null union all
select '02', '2CL', '01235' union all
select '03', '3NY', null union all
select '03', '2CL', '1452' union all
select '08', '2CL', null

select Emp_id,
Area_code,
max (Temp_Tran_Id) as Temp_Tran_Id
from @.employee
group by Emp_Id,
Area_Code
order by Emp_Id,
max (Temp_Tran_Id), ''

/*
Emp_id Area_code Temp_Tran_Id
02 2CL 01235
03 3NY NULL
03 2CL 1452
08 2CL NULL
*/

|||Thanks very much itsw working perfectly now!
thanks every body
cheers