Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts

Wednesday, March 28, 2012

help on SELECT

Help on creating correct select query on the following table where customer = multi-race

( that is, customers that have value ‘1’ on more than one race category)

Thanks!

CustomerID

Black

AmIndian

Asian

White

PacIslander

Hispanic

NoRaceDisc

32501

1

1

32677

1

35062

1

1

35261

1

36490

1

41026

1

41412

1

42488

1

1

1

45471

1

47083

1

1

50066

1

Okay, first off, you should probably change your table structure if you get the chance. The way you designed things, you actually have to go through a schema change if you ever want to add a new race. I suggest you go to a table structure that has a table for race types, and another table which contains your customer_id and the race_id. In this case, your query would look something like this:

select customer_id, count(*)
from customer_races
group by customer_id
having count(*) > 1

In your current table structure, it gets a lot more complicated. If you cannot change the table structure, I'd suggest you follow a method similar to the one I outlined above. Create a temp table / table variable with the following structure...

declare table @.customer_race_count (
customer_id int, race_count int)

Then, you'll have to construct a series of statements like this...

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where black is not null

insert into @.customer_race_count (customer_id, race_count)
select customer_id, 1 AS race_count
from customer
where white is not null

then do something similar to this...

select customer_id, count(*)
from race_count
group by customer_id
having count(*) > 1

I hope that helps!

|||oh yeah, if you're using sql 2005, you might be able to take advantage of pivot / unpivot, but I've been working all day and no longer have the brain power to conjure some sample code for that.|||

well, if the race category is a numerical value (ie int) of some sort, this would work:

select CustomerID from MyTable
where (coalesce(Black,0) + coalesce(AmIndian,0) + coalesce(Asian,0) + coalesce(White,0) + (PacIslander,0) + (Hispanic,0)) > 1

If this is 2005, you may want to look at PIVOT

|||

First off, I agree completely with the person who said change the table structure. This should be a very easy query, but it isn't like you have it.

Second, if not numbers, or values aren't actually null, just change to something like

case when Black = '1' then 1 else 0 end +
case when AmIndian = '1' then 1 else 0 end + ...

and you can handle any datatype

|||

Thanks to all that responded.
I did change the structure. Created RaceTypeID 1(White),2(Black),3(Asian),4(AmIndian),and 5(Hispanic). a customer can supply more than 1 race type id so in the race table there can be multiple instances of rows with the same customerid but different race type id.

now i run a query as follows:

SELECT DISTINCT
dbo.Customers.CustomerID
,CASE WHEN EXISTS
(SELECT DISTINCT
dbo.Customers.CustomerID,
COUNT(*)
FROM dbo.Customers
INNER JOIN dbo.Race ON dbo.Customers.CustomerID = dbo.Race.CustomerID
GROUP BY dbo.Customers.CustomerID
HAVING COUNT (*) > 1) THEN 'yes' ELSE 'no' END as MultiRace
FROM dbo.Customers
WHERE
dbo.Customers.LastName NOT LIKE 'test'
AND dbo.Customers.LastName NOT LIKE 'training%'

in my result set i am getting 'yes' on all customers though only 7 are actually muti-race. Help please?

|||

Try this...

select
Customers.CustomerId,
CASE WHEN d_Race.CustomerID IS NOT NULL THEN 'YES'
ELSE 'NO'
END As MultiRace
FROM Customers
LEFT OUTER JOIN
(select customerid, count(*)
from race
group by customerid
having count(*) > 1) AS d_Race
ON d_Race.Customer_Id = Customers.Customer_Id

|||thank you very much...i only had to assign a column name for the count (*) and i finally was able to get the result i wanted. thanks a lot for all your help and to others who pitched in as well.|||whoops, yeah, you're right. I didn't alias that column. That's what happens when you develop pseudo-code. :) Glad I was able to steer you in the right direction.

Monday, March 26, 2012

Help on Oracle RDB Migration to SQL Server.

Hi,
We need help on following things,

1. Inputs on creating comments on the columns & Tables of a SQL
Database & generating the sql script of that.

2. Is it possible to call a .exe file in SQL server like following
code in ORACLE

create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
language SQL;
external
name "CERT_VERIFY"
location 'HOST_IMG:TEST_CALCS.EXE'
with ALL logical_name translation
language C
GENERAL parameter style

3. We are using Rules for restricting data(now), We need inputs
whether to use Check constraints or Rules.

Thanks & Regards,
Chandra MohanOn Thu, 07 Aug 2003 04:45:54 -0700, Chandra Mohan wrote:

> Hi,
> We need help on following things,
> 1. Inputs on creating comments on the columns & Tables of a SQL
> Database & generating the sql script of that.

There is no direct support for Rdb-style comments in SQL Server. There is
an Extended Property facility, and SQL Enterprise Manager uses this to
allow you to annotate objects with comments. You could do the same thing
yourself with the system stored procedures for extended properties. Or
(and I've never looked at this) you could see if DMO has a way to
programmatically manipulate the same comments as SQL Enterprise Manager
uses. Then you could script calls to DMO.

> 2. Is it possible to call a .exe file in SQL server like following
> code in ORACLE
> create procedure CERT_VERIFY_PROCEDURE ( in :X Y by value )
> language SQL;
> external
> name "CERT_VERIFY"
> location 'HOST_IMG:TEST_CALCS.EXE'
> with ALL logical_name translation
> language C
> GENERAL parameter style

Unfortunately not something similar to Rdb. Keep in mind that Rdb (on
VMS) runs as a run-time library in the user process. Thus, subject to a
little bit of security work to make sure you drop into user mode, its
pretty easy to run external logic. SQL Server runs as a central server
process, so it is far more difficult to safely run external logic.

Currently SQL Server has three mechanism for running external logic.
XP_CMDSHELL allows you to directly send a command or script to a command
shell. You could wrap a call to XP_CMDSHELL in a stored procedure to
simulate something like what you can code in Rdb, but its quite different.
Anyway, take a look and pay attention to the security requirements.
Second, you can write extended stored procedures to call code written in
C. Third, you can use the OLE Automation stored procedures to call OLE
Automation objects. This is probably the closest thing to the Rdb
capability since much software on Windows exposes its functionality via
OLE Automation.

> 3. We are using Rules for restricting data(now), We need inputs
> whether to use Check constraints or Rules.

Use Check constraints.

Hal

Friday, March 23, 2012

Help on creating SSIS

Hello ,

I am getting the given error message while creting SSIS. Please help me to solve the error....

Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..

regards,

Malhar , THanks

Where do you see the error message?|||

Hi,

Error is coming in Error List...

When i open new project->Integration Service Project.. I see the Package.dtsx with the following error

Plz help me out if u can..

Thanx n Regards,

Malhar

|||

Hi,

Error is coming in Error List...

When i open new project->Integration Service Project.. I see the Package.dtsx with the following error

Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..

Plz help me out if u can..

Thanx n Regards,

Malhar

|||If all you're doing is creating a brand new project and this error appears before you've done anything then it sounds like you've got a corrupt installation. Unfortunatly I'm not able to offer any meaningful help other than trying to reinstall the SQL 2005 Client Tools|||

Hi,

Thanx for ur reply.

Yup i m creating brand new project and as soon as i create new project this error comes.

Well, whatever solution u have mentioned, is the only solution? and after reinstalling sql server 2005, my problem wud b solved?

Thanx n Regards,

Malhar

|||

hi,

Thanx for ur reply.

Yup i m creating brand new project and as soon as i create new project, that error comes.

Well, Whatever solution u have mentioned, is the only solution? My problen will get solved if i wud reinstall MS Sql Server 2005?

or is there any other solution available? Whts the meaning of that error : Failed on the unmanaged server..

Thanx and Regards,

Malhar

|||

malhar wrote:

Hi,

Error is coming in Error List...

When i open new project->Integration Service Project.. I see the Package.dtsx with the following error

Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..

Plz help me out if u can..

Thanx n Regards,

Malhar

I'm not sure what's causing your issue, but it's possible that your SSIS installation has become corrupted. The only solution I can think of is to do a fresh install.|||

This looks like it may be a problem related to InstallShield. I just received the same error opening an existing SSIS project. Here is a link to what may be a solution

http://support.installshield.com/kb/view.asp?articleid=q106194

Help on creating SSIS

Hello ,

I am getting the given error message while creting SSIS. Please help me to solve the error....

Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..

regards,

Malhar , THanks

Where do you see the error message?|||

Hi,

Error is coming in Error List...

When i open new project->Integration Service Project.. I see the Package.dtsx with the following error

Plz help me out if u can..

Thanx n Regards,

Malhar

|||

Hi,

Error is coming in Error List...

When i open new project->Integration Service Project.. I see the Package.dtsx with the following error

Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..

Plz help me out if u can..

Thanx n Regards,

Malhar

|||If all you're doing is creating a brand new project and this error appears before you've done anything then it sounds like you've got a corrupt installation. Unfortunatly I'm not able to offer any meaningful help other than trying to reinstall the SQL 2005 Client Tools|||

Hi,

Thanx for ur reply.

Yup i m creating brand new project and as soon as i create new project this error comes.

Well, whatever solution u have mentioned, is the only solution? and after reinstalling sql server 2005, my problem wud b solved?

Thanx n Regards,

Malhar

|||

hi,

Thanx for ur reply.

Yup i m creating brand new project and as soon as i create new project, that error comes.

Well, Whatever solution u have mentioned, is the only solution? My problen will get solved if i wud reinstall MS Sql Server 2005?

or is there any other solution available? Whts the meaning of that error : Failed on the unmanaged server..

Thanx and Regards,

Malhar

|||

malhar wrote:

Hi,

Error is coming in Error List...

When i open new project->Integration Service Project.. I see the Package.dtsx with the following error

Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..

Plz help me out if u can..

Thanx n Regards,

Malhar

I'm not sure what's causing your issue, but it's possible that your SSIS installation has become corrupted. The only solution I can think of is to do a fresh install.|||

This looks like it may be a problem related to InstallShield. I just received the same error opening an existing SSIS project. Here is a link to what may be a solution

http://support.installshield.com/kb/view.asp?articleid=q106194

Help on creating letter in RS

Hi,
I am trying to create report using Reporting Services 2000. My dataset returns 2 records. I want to generate two reports (one for each record) in a letter format. Can anybody tell me how do I design the report that to generate seperate letter for each record? Thanks.

Am I correct in assuming that you want a seperate page for each record?

Wednesday, March 21, 2012

Help on creating a user function.

When I declare a cursor,I use a variable to replace the sql statement:
DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
@.sqlPlan
But it is not true.Who can correct for me.

Another question is :
How to execute a sql statement state by a variable "@.sqlPlan" and
insert the result to a table "@.FeatRequestStatus"?

I am a new hand of sql programming.Thank you very much for your helpWhen I use:
insert @.FeatRequestStatus
exec @.sqlPlan
It says "execute can be used as a source when insert into a table viarable"
"Kevin" <hua@.lucent.com> wrote in message
news:dc2mgs$16f@.netnews.proxy.lucent.com...
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.
> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?
> I am a new hand of sql programming.Thank you very much for your help|||Kevin (hua@.lucent.com) writes:
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.

You need to say:

EXEC ('DECLARE rs CURSOR GLOBAL FAST_FORWARD ' + @.sqlPlan)

Note that I changed LOCAL to GLOBAL here. This is necessary, since the
cursor is accessed from a different scope than it is created.

> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?

INSERT EXEC does not work with table variables, as you have experienced.
Use a temp table instead.

And if @.sqlPlan is an SQL statement, the syntax is

EXEC(@.sqlPlan)

The syntax you had on your other post:

EXEC @.sqlPlan

means "execute the stored procedure of which the name is in @.sqlPlan".

> I am a new hand of sql programming.Thank you very much for your help

In such case, I should maybe point out, that cursors is something
to be used sparingly. There are situations where cursors can be
motivated, but they often come with a price of severly reduced
performance. Work set-based if you can.

Dynamic SQL is not really anything for beginners - it's definitely an
advanced feature. Dynamic SQL makes things a lot more complex, and
avoid if you can. I have a longer article on dynamic SQL on my web
site that you could find useful:
http://www.sommarskog.se/dynamic_sql.html

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Help Needed: For Creating Synchronous Transform Component

Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.

So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.

Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.


Thanks Steve

There are a whole host of samples now out, as well as those that shipped in the box, one of them will help I'm sure-

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20SSIS%20Sample%20Component&DisplayLang=en

(Or call jamie and he'll tell you, even if he does ask me!)

|||

Just been talking with Steve about this offline.

Turns out that, based on his requirement, he needed an asynch component. However, the same number of rows will be coming in as are going out, its just that the "shape" of the output (i.e. the metadata) needs to change.

Steve doesn't need to cache all the data in memory which is usually what asynch components do. They don't have to though. ProcessInput() can push rows to the output as soon as they are encountered without storing internally - thus giving the "illusion" of it being a synch component.

-Jamie

|||

I would first like to clear up some confusion on asynchronous outputs (I have tried before but it persists :)). Having an async output does not mean that the component waits until all the data has been seen to output any data. All it means is that the data coming out of the component is a copy in a new buffer. The Union All transform has an async output but it clearly does not wait until it receives all the data from all its inputs before outputing any data. In fact, most of the stock components with async outputs start generating output data well before all the input data has been seen.

In specific anwer to your question you can do one of 2 things. The first is to add additional output columns to the match output and keep it in sync with the input. The 2nd is to have your match output be an async output. Normally the 1st is better if you can do it because you don't have a memory copy. However, since you stated you don't want to keep any data from the input data on a match then in your case making the output async is actually the better alternative because you will actually wind up saving memory by doing so (due to the internals of the buffering system that have to do with sync outputs with additional columns causing buffer row widening).

As for sample, as Darren pointed out there are plenty to choose from. Although there probably are none that have a component with one async and one sync output there will be very little difference with just looking at both a sync and async sample and mixing and matching the appropriate code that is needed.

Thanks,

Matt

|||

I had a look and could not see a sample with two synch outputs which is what I thought was required at first. Steve now has a simple example I wrote for him earlier, but for reference the only (public) asynch sample I know of is the RemoveDuplicates component which ships in the box. This does cache data, which is a good example of this style of transform, but since all he work is done in ProcessInput, although it would spane several invocations of this method, it is fairly easy to see how you can change this blocking nature, and start passing rows straight to the output buffer as soon as received from the input.

|||

Interesting thread. I feel a blog post coming on but in this case I'm gonna leave it to Steve as he is keen to share what he's learnt here. Keep a look out!

-Jamie

|||

I was hoping for a little bit more information about Asynchronous output, especially where the input is sent to the output right away instead of using blocking.

Is there any more information on developing Async components besides what has already listed? I've already looked at Remove Duplicates from the samples but find it a litte confusing without a document to go with it explaining the process and why certain techniques were used.

I'm trying to create an Async component with 1 input, 3 outputs. I basically want some processing to be done that will determine which of the three outputs to go to. I'm not concerned about the decision making part (which output to go to) but more so on how to move the data to the outputs right way.

|||

If all the rows that come in, and no more than those that come in, end up in one of the outputs, then this would be best done as a synch component.

You would create your 3 outputs in ProvideComponentProperties, setting the SynchronousInputID property to that of yuor input. Since you have several outputs you also need to set the ExclusionGroup property for each output, each should have a unique value to differentiate them. You then use the DirectRow method of the PipelineBuffer to send each row to the output you decide.

Lookup the ExclusionGroup property help topic for a simple example.

Help Needed: For Creating Synchronous Transform Component

Hi
I am currently trying to write a custom transform componet in c# that will take a row of data, perform a look-up via an external system,
then if there is a match then send the data from the extranel system down macth ouptut (which will have different columns to the input) and drop the data that
was read, else send the data down the unmacthed output which will be the same as the input.

So I would like to write a synchrons transform becuase I don't need read all the rows from the input buffer before I started processing, also I wish have millions of rows
load in memory.

Can this be done? also does any have explame code of how to do this? becuse I can't see how to send data down the match output buffer,
as this will have the lookup results data which will have diffent columns to the input data and how disgard the input data as well.


Thanks Steve

There are a whole host of samples now out, as well as those that shipped in the box, one of them will help I'm sure-

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=SQL%20Server%20SSIS%20Sample%20Component&DisplayLang=en

(Or call jamie and he'll tell you, even if he does ask me!)

|||

Just been talking with Steve about this offline.

Turns out that, based on his requirement, he needed an asynch component. However, the same number of rows will be coming in as are going out, its just that the "shape" of the output (i.e. the metadata) needs to change.

Steve doesn't need to cache all the data in memory which is usually what asynch components do. They don't have to though. ProcessInput() can push rows to the output as soon as they are encountered without storing internally - thus giving the "illusion" of it being a synch component.

-Jamie

|||

I would first like to clear up some confusion on asynchronous outputs (I have tried before but it persists :)). Having an async output does not mean that the component waits until all the data has been seen to output any data. All it means is that the data coming out of the component is a copy in a new buffer. The Union All transform has an async output but it clearly does not wait until it receives all the data from all its inputs before outputing any data. In fact, most of the stock components with async outputs start generating output data well before all the input data has been seen.

In specific anwer to your question you can do one of 2 things. The first is to add additional output columns to the match output and keep it in sync with the input. The 2nd is to have your match output be an async output. Normally the 1st is better if you can do it because you don't have a memory copy. However, since you stated you don't want to keep any data from the input data on a match then in your case making the output async is actually the better alternative because you will actually wind up saving memory by doing so (due to the internals of the buffering system that have to do with sync outputs with additional columns causing buffer row widening).

As for sample, as Darren pointed out there are plenty to choose from. Although there probably are none that have a component with one async and one sync output there will be very little difference with just looking at both a sync and async sample and mixing and matching the appropriate code that is needed.

Thanks,

Matt

|||

I had a look and could not see a sample with two synch outputs which is what I thought was required at first. Steve now has a simple example I wrote for him earlier, but for reference the only (public) asynch sample I know of is the RemoveDuplicates component which ships in the box. This does cache data, which is a good example of this style of transform, but since all he work is done in ProcessInput, although it would spane several invocations of this method, it is fairly easy to see how you can change this blocking nature, and start passing rows straight to the output buffer as soon as received from the input.

|||

Interesting thread. I feel a blog post coming on but in this case I'm gonna leave it to Steve as he is keen to share what he's learnt here. Keep a look out!

-Jamie

|||

I was hoping for a little bit more information about Asynchronous output, especially where the input is sent to the output right away instead of using blocking.

Is there any more information on developing Async components besides what has already listed? I've already looked at Remove Duplicates from the samples but find it a litte confusing without a document to go with it explaining the process and why certain techniques were used.

I'm trying to create an Async component with 1 input, 3 outputs. I basically want some processing to be done that will determine which of the three outputs to go to. I'm not concerned about the decision making part (which output to go to) but more so on how to move the data to the outputs right way.

|||

If all the rows that come in, and no more than those that come in, end up in one of the outputs, then this would be best done as a synch component.

You would create your 3 outputs in ProvideComponentProperties, setting the SynchronousInputID property to that of yuor input. Since you have several outputs you also need to set the ExclusionGroup property for each output, each should have a unique value to differentiate them. You then use the DirectRow method of the PipelineBuffer to send each row to the output you decide.

Lookup the ExclusionGroup property help topic for a simple example.

sql

Monday, March 19, 2012

Help needed with Primary Key and Identity

I have some code in my ASP.NET page which uses a SQL 2000 Database that was created before creating the ASP Page. The problem I'm having is using an insert statement such as the following example from the DATAGRID example on the Matrix Product. I want the option to create new rows but my Primary Key doesn't allow Nulls and when I hard code a number in the first field of my table for my ID...it's not automatically generated. I've looked through this forum but I'm having some problems understanding what others have done with Identity or GUID's...etc...:

Sub AddNew_Click(Sender As Object, E As EventArgs)

' add a new row to the end of the data, and set editing mode 'on'

CheckIsEditing("")

If Not isEditing = True Then

' set the flag so we know to do an insert at Update time
AddingNew = True

' add new row to the end of the dataset after binding

' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

' add a new blank row to the end of the data
Dim rowValues As Object() = {"", "", ""}
ds.Tables(0).Rows.Add(rowValues)

' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count

If recordCount > 1 Then

recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize

End If

' databind
DataGrid1.DataSource = ds
DataGrid1.DataBind()

End If

End Subds.Tables(0).Columns("YourPrimaryKey").IncrementSeed = 1
ds.Tables(0).Columns("YourPrimaryKey")... other properties you need to set to make it an identity.|||Right now I'm using an "ID" field as the primary key and it is setup in SQL as an identity but when I try to add a row...it says..

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'CustomerInfo' when IDENTITY_INSERT is set to OFF. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.OrderEdit_aspx.DataGrid_Update(Object Sender, DataGridCommandEventArgs E

Would I need to remove the identity setting in SQL and create the identity through my code or is there a way to specify that my "ID" field is a primary key and it needs to be incremented by 1 whenever a new row is added?

Thanks for the help!!

Friday, March 9, 2012

Help needed for creating view

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required......

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.

ThanksQuestion: If anything in an itemgroup does not say complete, then it's incomplete?

Sounds simple enough...|||Is that an anwer or a question?|||Well it was a question...but...how's about

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(item int, itemgroup int, status varchar(25))
GO

INSERT INTO myTable99(item, itemgroup, status)
SELECT 1, 1, 'complete' UNION ALL
SELECT 2, 1, 'Xyz' UNION ALL
SELECT 3, 2, 'complete' UNION ALL
SELECT 4, 2, 'complete' UNION ALL
SELECT 5, 2, 'complete'
GO

CREATE VIEW myView99
AS
SELECT DISTINCT l.itemgroup
, CASE WHEN Status_COUNT IS NULL THEN 'Complete' ELSE 'Incomplete' END AS Status
FROM myTable99 l
LEFT JOIN ( SELECT itemgroup, COUNT(*) AS Status_COUNT
FROM myTable99
WHERE status <> 'Complete'
GROUP BY itemgroup) AS r
ON l.itemgroup = r.itemgroup
GO

SELECT * FROM myView99
GO|||Oh, oh! Can I play too?SELECT DISTINCT a.itemgroup
, CASE
WHEN EXISTS (SELECT *
FROM myTable AS b
WHERE b.itemgroup = a.itemgroup
AND b.status <> 'complete') THEN 'incomplete'
ELSE 'complete'
END AS groupStatus
FROM myTable AS a-PatP|||I like that one better....|||Thanks Guys...Both of them are much better than the function I have

Help needed creating select statement

Hi,

I have a need to create a table detailing the ID of all contacts and the
last time they were contacted. This information is stored in 2 tables,
'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
in the 'activity' table).

I guess I need some sort if iteration to go through each contact and find
find the last activity that took place against each of them (there many be
more than 1 activity against each contact) and then place the output values
into the new table.

Can anyone show me how to go about this?

Thanks!This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.

SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)

Roy Harvey
Beacon Falls, CT

On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@.ntlworld.com>
wrote:

Quote:

Originally Posted by

>Hi,
>
>I have a need to create a table detailing the ID of all contacts and the
>last time they were contacted. This information is stored in 2 tables,
>'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'
>in the 'activity' table).
>
>I guess I need some sort if iteration to go through each contact and find
>find the last activity that took place against each of them (there many be
>more than 1 activity against each contact) and then place the output values
>into the new table.
>
>Can anyone show me how to go about this?
>
>Thanks!
>

|||Hi Roy,

Many thanks. I've managed to use your example to get exactly what I need.
Cheers!

"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:7240031r1ken2gmb5a3qe8gfost4nvma25@.4ax.com...

Quote:

Originally Posted by

This sounds like something that can be handled by a view, rather than
creating a table that has to be maintained. Either way the general
approach is something like that below. Note that it is all based on
assumptions, but hopefully it will be enough to give you the idea.
>
SELECT *
FROM Contact as C
JOIN Activity as A
ON C.ID = A.main_contact
WHERE A.ActivityDate =
(SELECT MAX(X.ActivityDate) FROM Activity as X
WHERE A.main_contact = X.mainContact)
>
Roy Harvey
Beacon Falls, CT
>
On Tue, 20 Mar 2007 15:02:06 -0000, "Mintyman" <mintyman@.ntlworld.com>
wrote:
>

Quote:

Originally Posted by

>>Hi,
>>
>>I have a need to create a table detailing the ID of all contacts and the
>>last time they were contacted. This information is stored in 2 tables,
>>'contact' and 'activity' (ID in the 'contact' table links to
>>'main_contact'
>>in the 'activity' table).
>>
>>I guess I need some sort if iteration to go through each contact and find
>>find the last activity that took place against each of them (there many be
>>more than 1 activity against each contact) and then place the output
>>values
>>into the new table.
>>
>>Can anyone show me how to go about this?
>>
>>Thanks!
>>

Friday, February 24, 2012

Help me PLZ

Hi...

I have a problem ..I am creating an auction site the problem is when the user enter the min bid (the type of it is money) the compiler display error

cmd.Parameters.Add("@.minbid", Data.SqlDbType.money)

when I but the type of the min bid integer its work

cmd.Parameters.Add("@.minbid", Data.SqlDbType.Int)

use Decimal type and set the precision appropriately.

Also few things:

(1) Please do not multi-post.

(2) Post all therelevant code.

(3) Post the EXACT error message as it appears rather than simply saying compilor displays error or there is a problem. In order for some one to visualize the problem and help you we need as much information as we can. You have to help us first in order for us to help you.