Help with counting Query

I am trying to find a query to count rows with multiple conditions.
I have files which are suffixed with -R and then a number from 1 to 3 and
then two digits 00-14.
the field is char
i.e.:
-R101
-R102
-R114
-R201
-R302
I need to determine how many 101, how many 102, etc through 114 and then the
same for
the 201-214 series and then
the 301-314 series.
I will be sending the query from Visual Basic using ADODC so I am not sure
how the data will be returned. If it was written to a temp table that would
be great.
Thanks,
Bob Hiller
Lifts for the Disabled LLCselect right(columnName, 3), count(columnName)
from tableName
group by right(columnName, 3)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>I am trying to find a query to count rows with multiple conditions.
> I have files which are suffixed with -R and then a number from 1 to 3 and
> then two digits 00-14.
> the field is char
> i.e.:
> -R101
> -R102
> -R114
> -R201
> -R302
> I need to determine how many 101, how many 102, etc through 114 and then
> the same for
> the 201-214 series and then
> the 301-314 series.
> I will be sending the query from Visual Basic using ADODC so I am not sure
> how the data will be returned. If it was written to a temp table that
> would be great.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
>|||Thank you,
That worked great but I did not ask the full question. Maybe you can help
again.
if these strings are in a column
12345678-R101
12345678-R201
12345678-R301
98564512-R112
18752381-R101
18752381-R201
18752381-R110
18752381-R111
18752381-R211
If there is a -R2 there will always be a -R1. Likewise if there is a -R3
there will always be a -R2.
In the above example I need to return
12345678-R301
98564512-R112
18752381-R201
18752381-R110
18752381-R211
I hope I have explained this well enough.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
> select right(columnName, 3), count(columnName)
> from tableName
> group by right(columnName, 3)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>|||select left(columnName, charindex('-R',columnName)+1),
MAX(substring(columnName,charindex('-R',columnName)+2,10))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Thank you,
> That worked great but I did not ask the full question. Maybe you can help
> again.
> if these strings are in a column
> 12345678-R101
> 12345678-R201
> 12345678-R301
> 98564512-R112
> 18752381-R101
> 18752381-R201
> 18752381-R110
> 18752381-R111
> 18752381-R211
> If there is a -R2 there will always be a -R1. Likewise if there is a -R3
> there will always be a -R2.
> In the above example I need to return
> 12345678-R301
> 98564512-R112
> 18752381-R201
> 18752381-R110
> 18752381-R211
> I hope I have explained this well enough.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
>|||OOPS... You need to convert the count values to int...
select left(columnName, charindex('-R',columnName)+1),
MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(substring(columnName,charindex('-R',columnName)+2,10))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||I will do some more checking but thus far your very appreciated suggestion
is producing some very strange results.
For one thing it is returning 2 expressions. I would expect only one.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I can live with the 2 returned expressions, they will work fine.
Here is what is returned when I run the sample:
12345678-R301
98564512-R112
18752381-R211
I am missing:
18752381-R201
18752381-R110
Think of the first number after the -R as a counter for the last 2 numbers
that represent a group.
When these are in the table
18752381-R101 01 is the group and 1 is the counter
18752381-R201 01 is the group and 2 is the counter
we want to return the largest counter for group 01 for the number to the
left of -R
return (18752381-R201)
18752381-R110 10 is the group and 1 is the counter
return (18752381-R110) it is the only group 10 for the number to the left
of -R
18752381-R111 11 is the group and 1 is the counter
18752381-R211 11 is the group and 2 is the counter
return (18752381-R211)
Thank in advance,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||select max(a)
from(select '12345678-R101'
union all select '12345678-R201'
union all select '12345678-R301'
union all select '98564512-R112'
union all select '18752381-R101'
union all select '18752381-R201'
union all select '18752381-R110'
union all select '18752381-R111'
union all select '18752381-R211')x(a)
group by left(a,8),right(a,2)
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Ok, I can live with the 2 returned expressions, they will work fine.
> Here is what is returned when I run the sample:
> 12345678-R301
> 98564512-R112
> 18752381-R211
> I am missing:
> 18752381-R201
> 18752381-R110
> Think of the first number after the -R as a counter for the last 2 numbers
> that represent a group.
> When these are in the table
> 18752381-R101 01 is the group and 1 is the counter
> 18752381-R201 01 is the group and 2 is the counter
> we want to return the largest counter for group 01 for the number to the
> left of -R
> return (18752381-R201)
> 18752381-R110 10 is the group and 1 is the counter
> return (18752381-R110) it is the only group 10 for the number to the
> left of -R
> 18752381-R111 11 is the group and 1 is the counter
> 18752381-R211 11 is the group and 2 is the counter
> return (18752381-R211)
> Thank in advance,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
>|||oj,
Thanks for the suggestion but the values where just given as samples. There
are thousands of rows that I have to search through. I don't think this
approach will work.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"oj" <nospam_ojngo@.home.com> wrote in message
news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
> select max(a)
> from(select '12345678-R101'
> union all select '12345678-R201'
> union all select '12345678-R301'
> union all select '98564512-R112'
> union all select '18752381-R101'
> union all select '18752381-R201'
> union all select '18752381-R110'
> union all select '18752381-R111'
> union all select '18752381-R211')x(a)
> group by left(a,8),right(a,2)
>
> --
> -oj
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
>|||Bob,
You need to adapt the technique to your data.
e.g.
select max(your_col)
from tb
group by left(your_col,8),right(your_col,2)
If it does not give you the desired result, you'd want to post ddl + sample
data + expected result here so we can help.
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uRMPBI8TGHA.5836@.TK2MSFTNGP10.phx.gbl...
> oj,
> Thanks for the suggestion but the values where just given as samples.
> There are thousands of rows that I have to search through. I don't think
> this approach will work.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
>

Help with COUNT(*)

Dear SQL,

I want to count the number of records, so I tried this:

SELECT COUNT(*) AS RecordCount
FROM Categories
WHERE Active = 1
ORDER BY Show_Order ASC

But it gives me an error:
error 8126: Column name 'Categories.Show_Order' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.

How can I make it work ? (I must ORDER it...)SELECT COUNT(*) will return a single row with a single column containing an integer value. What are you expecting an ORDER BY to sort??

Terri|||As the error says, you cant do a count without a group. Here is my suggestion:

SELECT COUNT(fieldname) AS RecordCount FROM Categories WHERE active = 1 GROUP BY fieldname ORDER BY show_order

Instead of counting all columns just use one field. A Count requires a grouping even if there isnt anything to group. For instance you have a field called id that is a primary key. Group by. Order is ALWAYS at the end and the default is ASC so no need for ASC.|||As the error says, you cant do a count without a group.
That's not accurate. You can certainly do a COUNT without explicitly giving a grouping.

What the error is saying is that if you want use an ORDER BY clause, the expression being ordered by must exist in the resultset. With this in mind, the example you've given will not work because show_order does not exist in the resultset.

And, in order to add an expression to the resultset using an aggregate function such as COUNT, you need a GROUP BY clause.

In this case, this is likely what is needed but I am not sure because more information is needed from the original poster:

SELECT Show_Order, COUNT(*) AS RecordCount
FROM Categories
WHERE Active = 1
GROUP BY Show_Order
ORDER BY Show_Order ASC

And also, I think that explicitly indicating the sort direction ("ASC") is good practice because you never know when default behaviors might change. But that's a personal preference. :-)

Terri|||Hello again & thank god 4 this forum :-)

I have now succeeded in returning the number of records
but as Terri said - when I use GROUP BY it seems to limit my recordset to only one record,

I need this SP to be very efficient, so I like to SELECT only once, as U can see on the SP (below),
right now I use another SELECT at the end to determine the number of records to return...

Please let me know how to improve it or how to COUNT the records on the first selection
Thanks in advanced, Yovav.

/*================================================================================*/
/* Get categories (All / Titles / Subtitles) */
/*================================================================================*/
CREATE PROCEDURE Admin_Categories_Get
/*
' Usage example:
' ~~~~~~~~~~~
AdoCmd.CommandType = adCmdStoredProc
AdoCmd.CommandText = "Admin_Categories_Get"
' Return parameter comes first and can be used after recordset is closed
AdoCmd.Parameters.Append AdoCmd.CreateParameter("RETURN", adInteger, adParamReturnValue, 4)
' 0 (All), 1 (All active) 2 (All active compact), 10 (All titles), 11 (All active titles), 20 (All subtitles), 21 (All active subtitles)
AdoCmd.Parameters.Append AdoCmd.CreateParameter("@.ShowType", adTinyInt, adParamInput, 1, 0)
Set CategoriesRS = AdoCmd.Execute
CategoriesRS.Close
Response.Write("Return value = " &CStr(AdoCmd.Parameters.Item("RETURN").Value))
*/
@.ShowType tinyint
AS
DECLARE @.RecordCount int
IF @.ShowType = 0 -- (All)
SELECT *
FROM Categories
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 1 -- (All active)
SELECT *
FROM Categories
WHERE Active = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 2 -- (All active compact)
SELECT Category_ID, Title, Name_Eng, Name_Heb
FROM Categories
WHERE Active = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 10 -- (All titles)
SELECT *
FROM Categories
WHERE Title = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 11 -- (All active titles)
SELECT *
FROM Categories
WHERE Active = 1 /* True */ AND Title = 1 /* True */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 20 -- (All subtitles)
SELECT *
FROM Categories
WHERE Title = 0 /* False */
ORDER BY Show_Order ASC
ELSE
IF @.ShowType = 21 -- (All active subtitles)
SELECT *
FROM Categories
WHERE Active = 1 /* True */ AND Title = 0 /* False */
ORDER BY Show_Order ASC
-- Count *ALL* records on table Categories
SELECT @.RecordCount = COUNT(*) FROM Categories
RETURN @.RecordCount
GO

|||...

oh dear goodness.

In the spirit of the holidays..

Why don't you just send in Active and Title as parameters? It appears to me that they're bits, and two bits are smaller in size than one integer.|||it wont help, coz sometimes I need to do things according to the ShowType
+
my main problem was how to return the COUNT of records together with the recordset...

Help with count and group by

tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc... a
nd the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--
--Without clear DDLs ( www.aspfaq.com/5006 ), it is hard to write up a clean
query.
As for a general solution, join the table, derive the age based on the date
of birth column and group by that value with aggregate function COUNT on the
SELECT list. Search the archives of this newsgroup for examples of finding
age from date of birth.
Anith|||try this:
--
this is untested as ddl is not given.
--
SELECT COUNT(DISTINCT VisitID) as nofovisits,
DATEDIFF(YEARS,BIRTHDAY,GETDATE()),COUNT
(distinct ChildID ) FROM tbVisitLog
INNER JOIN tbChild on tbVisitLog.ChildID = tbChild.ChildID
GROUP BY DATEDIFF(YEARS,BIRTHDAY,GETDATE())
--
Regards
R.D
--Knowledge gets doubled when shared
"Sonny Sablan" wrote:

> tbChild
> ChildID | ChildName | Birthday
> tbVisitLog
> VisitID | ChildID |
> I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
and the total number of visits...
> Example:
> Age | # of Children | # of Visits
> 2 300 350
> 3 500 750
> Sonny
>
> --
> --|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
I see from the names of the pseudo-code tables that (1) you have only
one child, not children (2) They have tuberculosis as shown by the
"tb-" prefix. (3) that these are not tables since they have no keys.
Do know the ISO-11179 naming standards and wehat DDL is?
The specification did not include the dates of the visits, so we cannot
determine what happens as the same child gets older and has visits at
age (n), age (n+1), then skips a year to visit at age (n+3), etc.
Also, a birthdate is a fixed date while a birthday is a month-day pair
that represents a set of event in a lifetime.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Do know the ISO-11179 naming standards and wehat DDL is?
DDL? That sounds a bit like DDT. It's probably poisonous.
Seriously, if you are actually interested in helping people in these
newsgroup, then don't use cryptic stuff like DDL, say CREATE TABLE
statements, so they know what they are talking about.
As for 11179, I would expect not very many care about it, least of all
people who have started to work with SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Sonny,
Its going to be something along these lines i think, hopefully it will give
you a start...
-- gets child ages...
select distinct dateadiff( year, birthday, getdate() )
from tblChild
-- get counts by years...
select ages.age,
count_children = ( select count(*)
from tblChild c
where dateadiff( year, c.birthday,
getdate() ) = ages.age ),
count_visits = ( select count(distinct v.VisitID)
from tblChild c
inner join tblVisitLog v on v.Child
= c.Child
where dateadiff( year, c.birthday,
getdate() ) = ages.age )
from (
select distinct age = dateadiff( year, birthday, getdate() )
from tblChild ) as ages
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:OkVJsQ41FHA.2076@.TK2MSFTNGP14.phx.gbl...
tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
and the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--|||> I see from the names of the pseudo-code tables that (1) you have only
> one child, not children (2)
A set should be singular, that is more logical in the real world. Having a
single row in a table Children makes no sense.

> They have tuberculosis as shown by the
> "tb-" prefix. (3) that these are not tables since they have no keys.
This is a practice often used in large systems, it is good to group object
names.
tb[a table]Child[of one or more Child's]
As usual your arrogance is unhelpful, this is a community of both novice and
experts, if you cannot handle that then i would suggest you consider your
contribution [or lack of it].
I notice your book targets people that are both novice and expert, that
makes sense from a commercial perspective - its a pitty you don't adopt that
pose here too; perhaps then you wouldn't have the reputation you have at the
moment which is that of an arrogant tosser.
Only you could interpret birthday in that way, if you read the post you
would understand otherwise.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130077723.236813.131320@.g49g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> I see from the names of the pseudo-code tables that (1) you have only
> one child, not children (2) They have tuberculosis as shown by the
> "tb-" prefix. (3) that these are not tables since they have no keys.
> Do know the ISO-11179 naming standards and wehat DDL is?
> The specification did not include the dates of the visits, so we cannot
> determine what happens as the same child gets older and has visits at
> age (n), age (n+1), then skips a year to visit at age (n+3), etc.
> Also, a birthdate is a fixed date while a birthday is a month-day pair
> that represents a set of event in a lifetime.
>|||This was a big help...
Thank you
--
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uH4Nul$1FHA.3524@.tk2msftngp13.phx.gbl...
> Hi Sonny,
> Its going to be something along these lines i think, hopefully it will
give
> you a start...
> -- gets child ages...
> select distinct dateadiff( year, birthday, getdate() )
> from tblChild
> -- get counts by years...
> select ages.age,
> count_children = ( select count(*)
> from tblChild c
> where dateadiff( year, c.birthday,
> getdate() ) = ages.age ),
> count_visits = ( select count(distinct v.VisitID)
> from tblChild c
> inner join tblVisitLog v on
v.Child
> = c.Child
> where dateadiff( year, c.birthday,
> getdate() ) = ages.age )
> from (
> select distinct age = dateadiff( year, birthday, getdate() )
> from tblChild ) as ages
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:OkVJsQ41FHA.2076@.TK2MSFTNGP14.phx.gbl...
> tbChild
> ChildID | ChildName | Birthday
> tbVisitLog
> VisitID | ChildID |
> I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
> and the total number of visits...
> Example:
> Age | # of Children | # of Visits
> 2 300 350
> 3 500 750
> Sonny
>
> --
> --
>|||I used this query...
SELECT TOP 100 PERCENT tbVisitLog.CenterID,
DATEDIFF(YYYY, tbChild.Birthday, GETDATE()) AS Age,
COUNT(DISTINCT tbVisitLog.ChildID) AS ChildCount,
COUNT(tbVisitLog.ChildID) AS VisitCount
FROM tbVisitLog INNER JOIN
tbChild ON tbVisitLog.ChildID = tbChild.ChildID
GROUP BY DATEDIFF(YYYY, tbChild.Birthday, GETDATE()), tbVisitLog.CenterID
Thanks for the help.
Sonny
--
--
"Sonny Sablan" <sonny@.sablan.org> wrote in message news:OkVJsQ41FHA.2076@.TK2
MSFTNGP14.phx.gbl...
tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc... a
nd the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--
--|||>> A set should be singular, that is more logical in the real world.<<
No, collective nouns -- Forest, not Trees, not Tree. Children, not
Child, not "sons and daughters", etc. A set is a collection and
should be named as such.
And I thought that the "tb-" thing was funny. I also do "vw-" means
"VolksWagen" and get a laugh out of that one.
That is common US English usage. It makes a really big difference. I
had an old Cobol system that only had the birthday, but had pruned the
year out of the birthdate to get it. It was damn useless for anything
but sending a card to someone -- even long after they were dead.
Does the UK or other dialects make them synonyms?

Help with Count

Hi all!

I have a table that has Order Number and an Account Number, I want to count all the account numbers. However there might be more then one order number with the same account number...

See example:

Table:

[Order Number] [Account Number]
12312 1234
13231 2342
14352 1311
23423 1313
11422 1234

Output should be: 4

I want to count([account number]) and get 4, notice there are 5 rows, the 1st and last have the same account number.

How do I write this query?

Thanks,

KenFound the anwser I was looking for!

select count(distinct [account number]) from table|||select count(distinct [Account Number])
from tbl|||Good timing! I found the answer just as you posted!

Thanks for the reply!

Ken

Help with complex query

Hi Everyone,

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

--

Agent Name: John Doe

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

RowID Work Minutes in Dept Total Work Minutes Dept

1 26355 52920 Service

2 9000 52920 Parts

3 17565 52920 Dispatch

Service = 26355 / 52920 = 0.499 = 50%

Parts = 9000 / 52920 = 0.17 = 17%

Dispatch= 17565 / 52920 = 0.33 = 33%

--

How can I accomplish this?

I am using SQL Server 2005 Express

Thank You

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

Code Snippet

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

insert into #t1

select 1, 26355, 52920, 'Service'

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

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

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

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

from #t1

|||

DaleJ,

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

|||

SamCosta wrote:

DaleJ,

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

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

And some additional sample data.

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

|||

Code Snippet

SELECT E.EMPID,

E.DEPTID,

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

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

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

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

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

GROUP BY E.ID, E.DEPTID

This query outputs:

EmpID DeptID AvgPLevel DeptWorkMI

28899 Service 2 17244

28899 Parts 3 9000

28899 Dispatch 1 27836

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

Result:

EmpID AvgPLevel

28899 2

How to calculate total productivity level:

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

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

Thank You

|||

See if this does what you need:

Code Snippet

;WITH base

AS

(

SELECT E.EMPID,

E.DEPTID,

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

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

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

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

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

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

GROUP BY E.ID, E.DEPTID

),

Totals

AS

(

SELECT EmpID, SUM(DeptWorkMi) AS TotalMinutes

FROM base

GROUP BY EmpID

)

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

FROM base b

INNER JOIN Totals AS t

ON b.EmpID = t.EmpID

GROUP BY b.EmpID

|||

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

Help with Case

I have the following data with following conditions:

Old Type New Type

Number: 01 in table1 with Connection : 'J' in table2 'GE'

Number: 01 in table1 with Connection : 'K' in table2 'GPE'

Number: 01 in table1 with Connection: 'L' In table2 'GPA'

Number: 02 in table1 with Connection: 'I' in table2 'I02'

I used queries like:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

ELSE CASE WHEN t2.Connection = 'K' THEN 'GPE'

ELSE CASE WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END END END END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID).

I'll take an example:

An ID can have multiple connections attached to it. Let Say:

Table1 ID : 0001

Number: 01

Table2 ID: 0001

Connection: J, K, S, O, P.

Now, when i do the above query, i would get a result of 3 rows new case types for ID 0001.

I would like to just get one, and the first priority will be put on the condition of (GE).

everytime I see a Number '01' and Connection 'J', I would ignore the other connection. If i do not see Number = '01' and Connection: 'J'. i would go for the rest of the conditions.

Hope you guys see what problem i am having.

Thanks,

Jul.

Try this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE WHEN t2.Connection = 'J' THEN 'GE'

WHEN t2.Connection = 'K' THEN 'GPE'

WHEN t2.Connection = 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

Since I do not like repeating the condition, I would do this:

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN Table2 AS t2

ON (t1.ID = t2.ID)

|||Still, i get multiple results.....Thanks though.|||So you want only one row returned?

SELECT CASE WHEN t1.Number = '01' THEN

CASE t2.Connection WHEN 'J' THEN 'GE'

WHEN 'K' THEN 'GPE'

WHEN 'L' THEN 'GPA'

ELSE 'NOTHING' END

END AS NewType

FROM Table1 AS t1 LEFT JOIN (

select id, min(Connection) [Connection]

from Table2

group by id) AS t2

ON (t1.ID = t2.ID)

|||It works....Thanks sooo much.....

Help with calculating duration time

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?Blind code, but give it a try: create table #Blocks
(Machine_No int,
Start_Time datetime,
End_Time datetime)

insert into #Blocks
(Machine_No,
Start_Time,
End_Time)
select Machine_No,
Start_Time,
max(End_Time)
from Tickets
inner join --FirstTickets
(select Machine_No
min(Start_Time) Start_Time
from Tickets) FirstTickets
on Tickets.Machine_No = FirstTickets.Machine_No
and Tickets.Start_Time = FirstTickets.StartTime

while @.@.RowCount > 0
begin
while @.@.RowCount > 0 --I don't think this will be reset by previous check...
update #Blocks
set End_Time = max(End_Time)
from #Blocks
inner join Tickets
on #Blocks.Machine_No = Tickets.Machine_No
and Tickets.Start_Time between #Blocks.Start_Time and #Blocks.End_Time
and Tickets.End_Time > #Blocks.End_Time

insert into #Blocks
(Machine_No,
Start_Time,
End_Time)
select Machine_No,
Start_Time,
max(End_Time)
from Tickets
inner join --FirstTickets
(select Machine_No
min(Start_Time) Start_Time
from Tickets
where Start_Time >
(select max(End_Time)
from #Blocks
where #Blocks.Machine_No = Tickets.Machine_No) NextTickets
on Tickets.Machine_No = NextTickets.Machine_No
and Tickets.Start_Time = NextTickets.StartTime
end

select Machine_No,
sum(datediff(s, Start_Time, End_Time)/3600.00 as Hours
from #Blocks
group by Machine_No
If you can deal with discrete time segments (say, 1 minute increments) and place an upper limit on the date range, you may be able to do this with a simpler query using a table of integers.|||I really think I can use this (with minor modifications). How can I make this work so that it only compares the records within the same day and not all the records? Thank you.|||This formula will concatenate datetime to whole dates:

select dateadd(d, datediff(d, 0, [Yourdate]), 0) as WholeDate|||How can I modify the following solution so that it only captures the duration time for range of 7 am - 11 pm spanning multiple days? Right now, it works only for 24 hr period. Here is my code:

SELECT dbo.tbl_installed_ATMs.Term_ID, FirstTickets.Object_Key, FirstTickets.Start_Time, MAX(dbo.tbl_install_tickets.END_TIME) AS End_Time,
CONVERT(varchar(12), dbo.tbl_install_tickets.START_TIME, 110) AS Start_Date, CONVERT(varchar(12), dbo.tbl_install_tickets.END_TIME, 110)
AS End_Date, dbo.tbl_status_code_key.DESCRIPTION
INTO dbo.Blocks
FROM dbo.tbl_install_tickets INNER JOIN
(SELECT Object_Key, MIN(Start_Time) Start_Time
FROM tbl_install_tickets
GROUP BY Object_Key) FirstTickets ON dbo.tbl_install_tickets.OBJECT_KEY = FirstTickets.Object_Key AND
dbo.tbl_install_tickets.START_TIME = FirstTickets.Start_Time INNER JOIN
dbo.tbl_status_code_key ON dbo.tbl_install_tickets.STATUS_CODE_KEY = dbo.tbl_status_code_key.LINK INNER JOIN
dbo.tbl_installed_ATMs ON FirstTickets.Object_Key = dbo.tbl_installed_ATMs.Object_Key
GROUP BY FirstTickets.Object_Key, FirstTickets.Start_Time, CONVERT(varchar(12), dbo.tbl_install_tickets.START_TIME, 110), CONVERT(varchar(12),
dbo.tbl_install_tickets.END_TIME, 110), dbo.tbl_status_code_key.DESCRIPTION, dbo.tbl_installed_ATMs.Term_ID

while @.@.RowCount > 0
begin
while @.@.RowCount > 0 --I don't think this will be reset by previous check...
update Blocks
set End_Time = (Select max(Blocks.End_Time)
from Blocks
inner join tbl_install_tickets
On Blocks.Object_Key = tbl_install_Tickets.Object_Key
and tbl_install_Tickets.Start_Time between Blocks.Start_Time and Blocks.End_Time
and tbl_install_Tickets.End_Time > Blocks.End_Time
)
from Blocks
end

SELECT Term_ID, CONVERT(varchar(12), Start_Time, 110) AS Date, DESCRIPTION AS Ticket_type, SUM(DATEDIFF(s, Start_Time, End_Time) / 3600.00)
AS Duration
FROM dbo.Blocks
GROUP BY Term_ID, CONVERT(varchar(12), Start_Time, 110), DESCRIPTION
GO

help with an sql statement please

Hi All

I need to get the maximum Decision number from my decision table where Enquiry Number = 1 but im not having much luck, can anyone help please (Niether are auto number field or primary key)

thanks

Gibbo

e.g. Select Maximum Decision_No from Decisions Where Enquiry_No = 1

Code Snippet

SELECT max( Decision_No )
FROM Decisions
WHERE Enquiry_No = 1

Help with an SQL statement

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

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

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

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

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

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

My raw data looks like this

Date Type
20040101 Meal
20040101 Meal
20040101 Meal

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

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

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

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

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

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

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

Help with an SQL statement

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

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

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

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

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

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

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

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

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

-PatP

Help with Agent Jobs

Hi
I am suddenly receiving a number fo failed jobs, upon looking at the
history, there is very little to go on, any help on where i can look to
solve this would be great. The only error i receive is below, I am the
owner.
The job failed. The Job was invoked by User EFMG\InglisG. The last step to
run was step 1 (Update Clients).
Hi,
In the Job History screen click the "Job step details" and look into the
details messages of each step id.
You will get more details there to troubleshoot.
Thanks
Hari
MCDBA
"Gordon" <inglisg@.edfd.com> wrote in message
news:OLfX#s4IEHA.228@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am suddenly receiving a number fo failed jobs, upon looking at the
> history, there is very little to go on, any help on where i can look to
> solve this would be great. The only error i receive is below, I am the
> owner.
> The job failed. The Job was invoked by User EFMG\InglisG. The last step
to
> run was step 1 (Update Clients).
>
|||Gordon
Most likely it is because your ID was not logged into the server and did not have access at the time. If you would make the owner of the jobs SA then most likely your problems will go away. If your SQL instance is running as a network ID and it has admi
n access you could also make it the owner of the job.
Hope this helps
Jeff
MCDBA, MCSE+I

Help with Agent Jobs

Hi
I am suddenly receiving a number fo failed jobs, upon looking at the
history, there is very little to go on, any help on where i can look to
solve this would be great. The only error i receive is below, I am the
owner.
The job failed. The Job was invoked by User EFMG\InglisG. The last step to
run was step 1 (Update Clients).Hi,
In the Job History screen click the "Job step details" and look into the
details messages of each step id.
You will get more details there to troubleshoot.
Thanks
Hari
MCDBA
"Gordon" <inglisg@.edfd.com> wrote in message
news:OLfX#s4IEHA.228@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am suddenly receiving a number fo failed jobs, upon looking at the
> history, there is very little to go on, any help on where i can look to
> solve this would be great. The only error i receive is below, I am the
> owner.
> The job failed. The Job was invoked by User EFMG\InglisG. The last step
to
> run was step 1 (Update Clients).
>|||Gordon
Most likely it is because your ID was not logged into the server and did not
have access at the time. If you would make the owner of the jobs SA then m
ost likely your problems will go away. If your SQL instance is running as a
network ID and it has admi
n access you could also make it the owner of the job.
Hope this helps
Jeff
MCDBA, MCSE+Isql

Help with Agent Jobs

Hi
I am suddenly receiving a number fo failed jobs, upon looking at the
history, there is very little to go on, any help on where i can look to
solve this would be great. The only error i receive is below, I am the
owner.
The job failed. The Job was invoked by User EFMG\InglisG. The last step to
run was step 1 (Update Clients).Hi,
In the Job History screen click the "Job step details" and look into the
details messages of each step id.
You will get more details there to troubleshoot.
Thanks
Hari
MCDBA
"Gordon" <inglisg@.edfd.com> wrote in message
news:OLfX#s4IEHA.228@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am suddenly receiving a number fo failed jobs, upon looking at the
> history, there is very little to go on, any help on where i can look to
> solve this would be great. The only error i receive is below, I am the
> owner.
> The job failed. The Job was invoked by User EFMG\InglisG. The last step
to
> run was step 1 (Update Clients).
>

Help with a special stored procedure

Hi

I have a table there have a primary key, this value have i also in another
table as a forreignkey, so good so far.

Here it is
If the number in may primarykey in table 1 , dosn't exits in table 2 then
delete records in table 1

I have made this in a ASP page with a view there list all records where the forreignkey in table 2 are NULL and then delte all records in table one

Can i made this as a stored procedure ?

regards

alvin

You really want a trigger on table 1 to check table 2 before insert.

Research INSTEAD OF Insert Triggers.

What this will do is instead of inserting a new record into table 1, it will check table 2 for a match. If there is no match, no record is inserted into table 1.

It's better to catch the record before it goes in, than to insert the record and then have to remove it later.

Adamus

|||

Hi

I don't believe you understand

When i made a post in table one i also make a post in table 2

All works fine

But tabel 2 i also connected with table 3 and here i have in my diagram a cascade delete

So when i delete a record in table 3 it's delete a record or more in table 2
then i have my record in table 1, this i can't delete when i delete the record in table 3
so what i want is to delete all record in table 1 if the record in table 2 are deleted

hop you understand ?

Alvin

|||

Ok table 3 is new...but the answer is still the same...triggers not sp's

When you delete from any table, also delete from other tables...correct?

So you want INSTEAD OF DELETE

Adamus

|||

I try to explain and maybe you can help

In table 1 i have a ID theis ID can bee many times in table 2
Table 2 have also a int there connect to table3

all works fine

When a date field in table3 is over current date then i delete the record in table
And when it does this it also delete the record in table2.

after sometime all the records in table2 there have the ID from table 1 is been deleted
and when there are no more record in table2 there in my feild have the same numbers
as the ID in table 1 then i wnt to delete the record in table 1

Like:

Delete all records in table 1 if table1.ID <> from table2.field

In vbscript i can make a loop to check if the Id from table1 are in table2
if the catch this number = do nothing
if the find the number = delte record

i Can't explain it better. Sorry

Alvin

|||

It looks to me like you're wanting something like this:

create procedure delete_records_from_table1

as

delete from table1

from table1

left join table2

on table1.ID = table2.ID

where table2.ID is null

The left join will include everything in table1, regardless of whether there's a matching record in table2, and then the where clause excludes all of the records where there is a match in table2. This just leaves those records where there's no match in table2, and these can be deleted.

Is this the sort of thing you were looking for?

Iain

|||

Yes yes yes

Thanks

Alvin

sql

Help with a query

Hi all,
I have a table that describes bus routes. The table includes the following
columns:
ID - just an autonumber
Line - The line number / bus number
Way - A way number. Each line/bus can have multiple ways it travels on
OrderNo - The order of how the stations will be visited (starting from
1,2,3, etc) for every way
Distance - Distance in meters between the start and end station
StartStation - The start station number
EndStation - The end station number
An example recordset could look this:
ID Line Way Order Distance Start End
1 1 1 1 100 A B
2 1 1 2 150 B C
3 1 1 3 250 C D
4 1 2 1 150 A B
5 1 2 2 110 B C
6 1 2 3 200 C D
7 1 2 4 200 D E
8 2 1 1 100 Y Z
9 2 1 2 100 Z A
10 2 1 3 100 A B
11 2 1 4 150 B X
12 2 1 5 150 X W
13 2 1 6 150 W C
14 2 1 7 250 C D
and so on...
Imagine that I want to go from A to C, so my start station will be A and end
station will be C. From the example above I know I have 3 possibilities, and
these are:
Line 1, Way 1: A-B-C (ids: 1-2)
Line 1, Way 2: A-B-C (ids: 4-5)
Line 2, Way 1: A-B-X-W-C (ids: 10-11-12-13)
Basically I need a query that returns a recordset with the above results.
Is it possible to build an SQL statement of this kind?
Thanks,
IvanWith the arsenal available today, you are not likely to easily solve this
with T-SQL alone, although you can potentially kludge this with a view or
two. It is more likely you will have "client" code, meaning your app that
consumes the results of this query.
In SQL Server 2005, you have additional options, including .NET code in the
database, to produce the results you want without much pain.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Ivan Debono" wrote:

> Hi all,
> I have a table that describes bus routes. The table includes the following
> columns:
> ID - just an autonumber
> Line - The line number / bus number
> Way - A way number. Each line/bus can have multiple ways it travels on
> OrderNo - The order of how the stations will be visited (starting from
> 1,2,3, etc) for every way
> Distance - Distance in meters between the start and end station
> StartStation - The start station number
> EndStation - The end station number
> An example recordset could look this:
> ID Line Way Order Distance Start End
> 1 1 1 1 100 A B
> 2 1 1 2 150 B C
> 3 1 1 3 250 C D
> 4 1 2 1 150 A B
> 5 1 2 2 110 B C
> 6 1 2 3 200 C D
> 7 1 2 4 200 D E
> 8 2 1 1 100 Y Z
> 9 2 1 2 100 Z A
> 10 2 1 3 100 A B
> 11 2 1 4 150 B X
> 12 2 1 5 150 X W
> 13 2 1 6 150 W C
> 14 2 1 7 250 C D
> and so on...
> Imagine that I want to go from A to C, so my start station will be A and e
nd
> station will be C. From the example above I know I have 3 possibilities, a
nd
> these are:
> Line 1, Way 1: A-B-C (ids: 1-2)
> Line 1, Way 2: A-B-C (ids: 4-5)
> Line 2, Way 1: A-B-X-W-C (ids: 10-11-12-13)
> Basically I need a query that returns a recordset with the above results.
> Is it possible to build an SQL statement of this kind?
> Thanks,
> Ivan
>
>|||Actually, there is a neat solution in SQL-2005 that uses a recursive
CTE to implement Dijkstra's algorithm for the shortest path. There is
no need to add .NET code to the schema.|||I got an email asking me how to find paths in a graph using SQL. The
author of the email had seen my chapter on graphs in SQL FOR SMARTIES
2nd, and read that I was not happy with my own answers. What he wanted
was a list of paths from any two nodes in a directed graph, and I would
assume that he wanted the cheapest path.
After thinking about this for awhile, the best way is probably to do
the Floyd-Warshall or Johnson algorithm in a procedural language and
load a table with the results. But I want to do this in pure SQL as an
exercise.
Let's start with a simple graph and represent it as an adjacency list
with weights on the edges.
CREATE TABLE Graph
(source CHAR(2) NOT NULL,
destination CHAR(2) NOT NULL,
cost INTEGER NOT NULL,
PRIMARY KEY (source, destination));
I got data for this table from the book Introduction to Algorithms by
Cormen, Leiserson and Rivest (ISBN 0-262-03141-8), page 518. This book
is very popular in college courses in the United States. I made one
decision that will be important later; I added self-traversal edges
(i.e. the node is both the source and the destination) with weights of
zero.
INSERT INTO Graph VALUES ('s', 's', 0);
INSERT INTO Graph VALUES ('s', 'u', 3);
INSERT INTO Graph VALUES ('s', 'x', 5);
INSERT INTO Graph VALUES ('u', 'u', 0);
INSERT INTO Graph VALUES ('u', 'v', 6);
INSERT INTO Graph VALUES ('u', 'x', 2);
INSERT INTO Graph VALUES ('v', 'v', 0);
INSERT INTO Graph VALUES ('v', 'y', 2);
INSERT INTO Graph VALUES ('x', 'u', 1);
INSERT INTO Graph VALUES ('x', 'v', 4);
INSERT INTO Graph VALUES ('x', 'x', 0);
INSERT INTO Graph VALUES ('x', 'y', 6);
INSERT INTO Graph VALUES ('y', 's', 3);
INSERT INTO Graph VALUES ('y', 'v', 7);
INSERT INTO Graph VALUES ('y', 'y', 0);
I am not happy about this approach, because I have to decide the
maximum number of edges in path before I start looking for an answer.
But this will work and I know that a path will have no more than the
total number of nodes in the graph. Let's create a table to hold the
paths:
CREATE TABLE Paths
(step1 CHAR(2) NOT NULL,
step2 CHAR(2) NOT NULL,
step3 CHAR(2) NOT NULL,
step4 CHAR(2) NOT NULL,
step5 CHAR(2) NOT NULL,
total_cost INTEGER NOT NULL,
path_length INTEGER NOT NULL,
PRIMARY KEY (step1, step2, step3, step4, step5));
The step1 node is where I begin the path. The other columns are the
second step, third step, fourth step, and so forth. The last step
column is the end of the journey. The total_cost column is the total
cost, based on the sum of the weights of the edges, on this path. The
path length column is harder to explain, but for now, let's just say
that it is a count of the nodes visited in the path.
To keep things easier, let's look at all the paths from "s" to "y" in
the graph. The INSERT INTO statement for construction that set looks
like this:
INSERT INTO Paths
SELECT G1.source, -- it is 's' in this example
G2.source,
G3.source,
G4.source,
G4.destination, -- it is 'y' in this example
(G1.cost + G2.cost + G3.cost + G4.cost),
(CASE WHEN G1.source NOT IN (G2.source, G3.source, G4.source)
THEN 1 ELSE 0 END
+ CASE WHEN G2.source NOT IN (G1.source, G3.source, G4.source)
THEN 1 ELSE 0 END
+ CASE WHEN G3.source NOT IN (G1.source, G2.source, G4.source)
THEN 1 ELSE 0 END
+ CASE WHEN G4.source NOT IN (G1.source, G2.source, G3.source)
THEN 1 ELSE 0 END)
FROM Graph AS G1, Graph AS G2, Graph AS G3, Graph AS G4
WHERE G1.source = 's'
AND G1.destination = G2.source
AND G2.destination = G3.source
AND G3.destination = G4.source
AND G4.destination = 'y';
I put in "s" and "y" as the source and destination of the path, and
made sure that the destination of one step in the path was the source
of the next step in the path. This is a combinatorial explosion, but it
is easy to read and understand.
The sum of the weights is the cost of the path, which is easy to
understand. The path_length calculation is a bit harder. This sum of
CASE expressions looks at each node in the path. If it is unique within
the row, it is assigned a value of one, if it is not unique within the
row, it is assigned a value of zero.
All paths will have five steps in them because that is the way to table
is declared. But what if a path exists between the two nodes which is
shorter than five steps? That is where the self-traversal rows are
used! Consecutive pairs of steps in the same row can be repetitions of
the same node.
Here is what the rows of the Paths table look like after this INSERT
INTO statement, ordered by descending path_length, and then by
ascending cost.
Paths step1 step2 step3 step4 step5 total_cost path_length
========================================
==============
s s x x y 11 0
s s s x y 11 1
s x x x y 11 1
s x u x y 14 2
s s u v y 11 2
s s u x y 11 2
s s x v y 11 2
s s x y y 11 2
s u u v y 11 2
s u u x y 11 2
s u v v y 11 2
s u x x y 11 2
s x v v y 11 2
s x x v y 11 2
s x x y y 11 2
s x y y y 11 2
s x y v y 20 4
s x u v y 14 4
s u v y y 11 4
s u x v y 11 4
s u x y y 11 4
s x v y y 11 4
Clearly, all pairs of nodes could be picked from the original Graph
table and the same INSERT INTO run on them with a minor change in the
WHERE clause. However, this example is big enough for a short magazine
article. And it is too big for most applications. It is safe to assume
that people really want the cheapest path. In this example, the
total_cost column defines the cost of an path, so we can eliminate some
of the paths from the Paths table with this statement.
DELETE FROM Paths
WHERE total_cost
> (SELECT MIN(total_cost)
FROM Paths);
Again, if you had all the paths for all possible pairs of nodes, the
subquery expression would have a WHERE clause to correlate it to the
subset of paths for each possible pair.
In this example, it got rid of 3 out of 22 possible paths. It is
helpful and in some situations we might like having all the options.
But these are not distinct options.
As one of many examples, the paths
(s, x, v, v, y, 11, 2)
and
(s, x, x, v, y, 11, 2)
are both really the same path, (s, x, v, y). Before we decide to write
a statement to handle these equivalent rows, let's consider another
cost factor. People do not like to change airplanes or trains. If they
can go from Amsterdam to New York City on one plane without changing
planes for the same cost, they are happy. This is where that
path_length column comes in. It is a quick way to remove the paths that
have more edges than they need to get the job done.
DELETE FROM Paths
WHERE path_length
> (SELECT MIN(path_length)
FROM Paths);
In this case, that last DELETE FROM statement will reduce the table to
one row: (s, s, x, x, y, 11, 0) which reduces to (s, x, y). This single
remaining row is very convenient for my article, but if you look at the
table, you will see that there was also a subset of equivalent rows
that had higher path_length numbers.
(s, s, s, x, y, 11, 1)
(s, x, x, x, y, 11, 1)
(s, x, x, y, y, 11, 2)
(s, x, y, y, y, 11, 2)
Your task is to write code to handle equivalent rows. Hint: the
duplicate nodes will always be contigous across the row.|||Actually, I managed to find a way using a view with 2 inner joins.
It does find the path (not the shortest) from source to target, but the
problem is that it does not find paths when changes occur (that is, a change
in line). Therefore it finds only paths on the same lines.
This is the SQL statement:
select d2.* from descriptions_search d1
inner join descriptions_search d2
on d1.desline = d2.desline
and d1.deswayno = d2.deswayno
and d1.desdirection = d2.desdirection
and d2.desorder>= d1.desorder
inner join descriptions_search d3
on d1.desline = d3.desline
and d1.deswayno = d3.deswayno
and d1.desdirection = d3.desdirection
and d2.desorder<= d3.desorder
where d1.startstation = 1301 and d3.endstation = 1313
order by d2.desline, d2.deswayno, d2.desdirection, d2.desorder
1301 and 1313 and example bus stops. descriptions_search is a view based on
the graph table with further joins to get station numbers instead of id's
I wouldn't know how to further develop the view to find paths with changes.
Any ideas?
Ivan
"--CELKO--" <jcelko212@.earthlink.net> schrieb im Newsbeitrag
news:1117822495.714286.127820@.g47g2000cwa.googlegroups.com...
> I got an email asking me how to find paths in a graph using SQL. The
> author of the email had seen my chapter on graphs in SQL FOR SMARTIES
> 2nd, and read that I was not happy with my own answers. What he wanted
> was a list of paths from any two nodes in a directed graph, and I would
> assume that he wanted the cheapest path.
> After thinking about this for awhile, the best way is probably to do
> the Floyd-Warshall or Johnson algorithm in a procedural language and
> load a table with the results. But I want to do this in pure SQL as an
> exercise.
> Let's start with a simple graph and represent it as an adjacency list
> with weights on the edges.
> CREATE TABLE Graph
> (source CHAR(2) NOT NULL,
> destination CHAR(2) NOT NULL,
> cost INTEGER NOT NULL,
> PRIMARY KEY (source, destination));
>
> I got data for this table from the book Introduction to Algorithms by
> Cormen, Leiserson and Rivest (ISBN 0-262-03141-8), page 518. This book
> is very popular in college courses in the United States. I made one
> decision that will be important later; I added self-traversal edges
> (i.e. the node is both the source and the destination) with weights of
> zero.
> INSERT INTO Graph VALUES ('s', 's', 0);
> INSERT INTO Graph VALUES ('s', 'u', 3);
> INSERT INTO Graph VALUES ('s', 'x', 5);
> INSERT INTO Graph VALUES ('u', 'u', 0);
> INSERT INTO Graph VALUES ('u', 'v', 6);
> INSERT INTO Graph VALUES ('u', 'x', 2);
> INSERT INTO Graph VALUES ('v', 'v', 0);
> INSERT INTO Graph VALUES ('v', 'y', 2);
> INSERT INTO Graph VALUES ('x', 'u', 1);
> INSERT INTO Graph VALUES ('x', 'v', 4);
> INSERT INTO Graph VALUES ('x', 'x', 0);
> INSERT INTO Graph VALUES ('x', 'y', 6);
> INSERT INTO Graph VALUES ('y', 's', 3);
> INSERT INTO Graph VALUES ('y', 'v', 7);
> INSERT INTO Graph VALUES ('y', 'y', 0);
> I am not happy about this approach, because I have to decide the
> maximum number of edges in path before I start looking for an answer.
> But this will work and I know that a path will have no more than the
> total number of nodes in the graph. Let's create a table to hold the
> paths:
>
> CREATE TABLE Paths
> (step1 CHAR(2) NOT NULL,
> step2 CHAR(2) NOT NULL,
> step3 CHAR(2) NOT NULL,
> step4 CHAR(2) NOT NULL,
> step5 CHAR(2) NOT NULL,
> total_cost INTEGER NOT NULL,
> path_length INTEGER NOT NULL,
> PRIMARY KEY (step1, step2, step3, step4, step5));
>
> The step1 node is where I begin the path. The other columns are the
> second step, third step, fourth step, and so forth. The last step
> column is the end of the journey. The total_cost column is the total
> cost, based on the sum of the weights of the edges, on this path. The
> path length column is harder to explain, but for now, let's just say
> that it is a count of the nodes visited in the path.
> To keep things easier, let's look at all the paths from "s" to "y" in
> the graph. The INSERT INTO statement for construction that set looks
> like this:
> INSERT INTO Paths
> SELECT G1.source, -- it is 's' in this example
> G2.source,
> G3.source,
> G4.source,
> G4.destination, -- it is 'y' in this example
> (G1.cost + G2.cost + G3.cost + G4.cost),
> (CASE WHEN G1.source NOT IN (G2.source, G3.source, G4.source)
> THEN 1 ELSE 0 END
> + CASE WHEN G2.source NOT IN (G1.source, G3.source, G4.source)
> THEN 1 ELSE 0 END
> + CASE WHEN G3.source NOT IN (G1.source, G2.source, G4.source)
> THEN 1 ELSE 0 END
> + CASE WHEN G4.source NOT IN (G1.source, G2.source, G3.source)
> THEN 1 ELSE 0 END)
> FROM Graph AS G1, Graph AS G2, Graph AS G3, Graph AS G4
> WHERE G1.source = 's'
> AND G1.destination = G2.source
> AND G2.destination = G3.source
> AND G3.destination = G4.source
> AND G4.destination = 'y';
>
> I put in "s" and "y" as the source and destination of the path, and
> made sure that the destination of one step in the path was the source
> of the next step in the path. This is a combinatorial explosion, but it
> is easy to read and understand.
> The sum of the weights is the cost of the path, which is easy to
> understand. The path_length calculation is a bit harder. This sum of
> CASE expressions looks at each node in the path. If it is unique within
> the row, it is assigned a value of one, if it is not unique within the
> row, it is assigned a value of zero.
> All paths will have five steps in them because that is the way to table
> is declared. But what if a path exists between the two nodes which is
> shorter than five steps? That is where the self-traversal rows are
> used! Consecutive pairs of steps in the same row can be repetitions of
> the same node.
> Here is what the rows of the Paths table look like after this INSERT
> INTO statement, ordered by descending path_length, and then by
> ascending cost.
> Paths step1 step2 step3 step4 step5 total_cost path_length
> ========================================
==============
> s s x x y 11 0
> s s s x y 11 1
> s x x x y 11 1
> s x u x y 14 2
> s s u v y 11 2
> s s u x y 11 2
> s s x v y 11 2
> s s x y y 11 2
> s u u v y 11 2
> s u u x y 11 2
> s u v v y 11 2
> s u x x y 11 2
> s x v v y 11 2
> s x x v y 11 2
> s x x y y 11 2
> s x y y y 11 2
> s x y v y 20 4
> s x u v y 14 4
> s u v y y 11 4
> s u x v y 11 4
> s u x y y 11 4
> s x v y y 11 4
> Clearly, all pairs of nodes could be picked from the original Graph
> table and the same INSERT INTO run on them with a minor change in the
> WHERE clause. However, this example is big enough for a short magazine
> article. And it is too big for most applications. It is safe to assume
> that people really want the cheapest path. In this example, the
> total_cost column defines the cost of an path, so we can eliminate some
> of the paths from the Paths table with this statement.
> DELETE FROM Paths
> WHERE total_cost
> FROM Paths);
> Again, if you had all the paths for all possible pairs of nodes, the
> subquery expression would have a WHERE clause to correlate it to the
> subset of paths for each possible pair.
> In this example, it got rid of 3 out of 22 possible paths. It is
> helpful and in some situations we might like having all the options.
> But these are not distinct options.
> As one of many examples, the paths
> (s, x, v, v, y, 11, 2)
> and
> (s, x, x, v, y, 11, 2)
> are both really the same path, (s, x, v, y). Before we decide to write
> a statement to handle these equivalent rows, let's consider another
> cost factor. People do not like to change airplanes or trains. If they
> can go from Amsterdam to New York City on one plane without changing
> planes for the same cost, they are happy. This is where that
> path_length column comes in. It is a quick way to remove the paths that
> have more edges than they need to get the job done.
> DELETE FROM Paths
> WHERE path_length
> FROM Paths);
> In this case, that last DELETE FROM statement will reduce the table to
> one row: (s, s, x, x, y, 11, 0) which reduces to (s, x, y). This single
> remaining row is very convenient for my article, but if you look at the
> table, you will see that there was also a subset of equivalent rows
> that had higher path_length numbers.
> (s, s, s, x, y, 11, 1)
> (s, x, x, x, y, 11, 1)
> (s, x, x, y, y, 11, 2)
> (s, x, y, y, y, 11, 2)
> Your task is to write code to handle equivalent rows. Hint: the
> duplicate nodes will always be contigous across the row.
>|||if you have SQL Server 2005 with CTE, you can use this approach:
http://www.sqlservercentral.com/col...lserver2005.asp|||unfortunately not :(
"--CELKO--" <jcelko212@.earthlink.net> schrieb im Newsbeitrag
news:1118080444.728097.178340@.g14g2000cwa.googlegroups.com...
> if you have SQL Server 2005 with CTE, you can use this approach:
>
http://www.sqlservercentral.com/col...>
5.asp

>

Help with a matrix or pivot table?

I'm trying to create a table that is a combination of two tables, and the number of columns is dynamic. So I have 2 tables, Students and Assignments. I'd like to get a result with the students on the left and the assignments across the top. I'm not sure where to start, any help would be great. Thanks

What your describing is not (1) a table, (2) a view nor (3) a table valued function because none of these can contain a dynamic number of columns. Where you need to start is by describing more clearly your needs.

If you are using SQL Server 2005 you probably use the ROW_NUMBER() function and PIVOT to slot different classes into ordinally assigned columns. This might be done with either a view or a TVF; however, if you truly need a dynamic number of columns, first rethink this at least a little. Once you decide you need a dynamic number of columns, you will need to settle with dynamic SQL.

|||

I'll try to be more clear. I need to display a table that has a row for each student. For each student row I'd like to have a column for each assignment completed. The tables look like this. The number of assignments could be 0 to 100, so the number of columns is dynamic.

tblStudent

StudentId

Name

tblAssignment

AssignmentId

StudentId

Score

Example

Assignment 1 | Assignment 2 | Assignment 3

Student X 10 20 30

Student Y 5 15 40

Help using GetColumnInfo()...

Hi, I'm trying to use the following piece of code to obtain the column names from the table in SQL Server, but somehow I'm getting the wrong number of columns in ul_numColumns (see code), and only the first column name in pstr_stringBuffer. Presumably I should be getting a whole string with all the column names there, but I don't. Any suggestions for what I may be doing wrong?..

Thanks in advance

int GetColumnNames(void)

{

try

{

pCommand.CreateInstance(__uuidof (Command));

pCommand->ActiveConnection = pConn;

pCommand->CommandText = "SELECT * FROM t25_pallet_status"; // SQL Syntax...

pRecordset.CreateInstance (__uuidof (Recordset));

pRecordset->CursorLocation = adUseClient;

pRecordset->Open( (IDispatch *) pCommand, vtMissing, adOpenStatic,

adLockReadOnly, adCmdUnknown);

// Get ADORecordsetConstruction interface from the the ADO Recordset

ADORecordsetConstruction *p_adoRecordsetConstruct;

pRecordset->QueryInterface(__uuidof(ADORecordsetConstruction),

(void **)&p_adoRecordsetConstruct);

// From it, we can get the OLEDB <code>IRowset

IRowset *p_rowset;

p_adoRecordsetConstruct->get_Rowset((IUnknown **)&p_rowset);

p_adoRecordsetConstruct->Release(); // don't need it anymore

// The IColumnsInfo that contains ordinals

CComPtr<IColumnsInfo> spColumns;

// Get the the IColumnsInfo from IRowset interface

p_rowset->QueryInterface(&spColumns);

// At this point, we may now release p_rowset

p_rowset->Release();

// IColumnsInfo will give us the DBCOLUMNINFO structure

ULONG ul_numColumns;

DBCOLUMNINFO *p_columnInfo = NULL;

OLECHAR *pstr_stringBuffer = NULL;

// Now get the DBCOLUMNINFO data

spColumns->GetColumnInfo(&ul_numColumns, &p_columnInfo, &pstr_stringBuffer);

// Clean up

CoTaskMemFree(p_columnInfo);

CoTaskMemFree(pstr_stringBuffer);

}

catch(_com_error & ce)

{

PrintComError(ce);

return 0;

}

}

You can do the following to get all the column names

for(ULONG j=0; j<ul_numColumns; j++)

printf("%S\t", p_columnInfo[j].pwszName);

Hope this helps

|||

Thanks a lot Raj,

I kind of sussed it out for my self a short while after I posted the message. I didn't really understand how GetColumnInfo() worked, and I was expecting pstr_stringBuffer to return a string with ALL of the column names (separated by a common delimitter or something). It became apparent to me that I had to loop through and read from a different variable (pwszName) in order to get those names.

I have to say there's very little on examples out there to use functions like this one. Can anyone recomend me a good website where I can get sample code from in C or C++ for the Visual Studio 2003 environment?

Cheers

Help to deleting n numbers of records

Hi
One of our users have by mistake created a number of dublicate records in
the database. Now I need to delete the "faulty" records from the table and
just keep one of them. The records are linked to a customer table, so I have
a Recordid column in the table that I can use to group on. E.g. if I in the
table have 10 records with RecordID 1, I need to delete 9 of them and keep
1, 8 records with RecordID 2 I need to delete 7 and keep 1 etc.
I'll have to search the customer table to find the recordID's that is used
to link to the child table, so my plan is to use a cursor to find these and
put them into a variable. I'll then use this to find the record(s) in the
child table, get the number of records and then either delete them one by
one until I've only 1 pr. RecordID left or use SELECT Top x based on the
number of records found with each recordid and then delete all but 1 record.
It's not a huge number of records I need to delete so from a practical
and/or performance point of wiev it's not critical how I do it. It's more
that I'm currious to hear if my approach is the best one or if any of you
have any other ideas?
TIA
Regards
SteenTo answer this properly we'll need to know the keys and constraints in
your tables. Please post DDL (CREATE TABLE) and some sample data
(INSERTs) if you want help with the actual code.
You missed out one critical step from your solution: Add a new unique
constraint so that this can't happen again.
David Portas
SQL Server MVP
--|||Hi
First of all, it's a vendor application, so I can't change anything in the
database or application. I this case it's not a problem though, since it's
ok to create several records as they did, but in this case it's just because
they had some problems with a printer and therefore thay ran a wizard
several times which generated a number of records that where baiscally same.
It's these records thay now want to get deleted.
The 2 tables that's involved is called Lejer and Note.
CREATE TABLE [Lejer] (
[EjendomNr] [EjendomNr] NOT NULL ,
[LejemaalNr] [LejemaalNr] NOT NULL ,
[LejerNr] [LejerNr] NOT NULL ,
[LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
....and about 200 more column definitions
CREATE TABLE [Note] (
[NoteID] [RecordID] NOT NULL ,
[Tabelnavn] [TabelNavn] NOT NULL ,
[RecordID] [RecordID] NOT NULL ,
[Dato] [Dato] NOT NULL ,
[NoteType] [KodeId] NOT NULL ,
....and some more column definitions.
The fields that links the tables are Lejer.LejerID and Note.RecordID.
Below is ans example of sample data
Lejer:
Ejendomnr Lejemaalnr Lejernr LejerID
1 1 1 1000
1 2 2 1001
1 3 3 1002
2 1 1 1003
2 2 2 1004
3 1 1 1005
3 2 2 1006
Note
NoteID RecordID NoteType
1 1000 29000
2 1000 29000
3 1000 29000
4 1000 29000
5 1001 29000
6 1001 29000
7 1001 29000
8 1002 29000
9 1002 29000
10 1002 29000
11 1003 29000
12 1003 29000
I'd like to find the notes where the type is e.g. 29000 and are linked a
record in the Lejer table with the Ejendomnr of e.g. 1.
and then delete all notes but 1.
In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note records with NoteID 1 to 10. Out of these I'd like to delete 3 of the
ones with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with
RecordID 1002.
As mentioned in my original post, I could get all the RecordID's into a
cursor and then e.g. count the number of occurences for each of them and
then do a delete n-1 times. I just don't know if that's the smartest way to
do it or if there's a better approach?
Regards
Steen
David Portas wrote:
> To answer this properly we'll need to know the keys and constraints in
> your tables. Please post DDL (CREATE TABLE) and some sample data
> (INSERTs) if you want help with the actual code.
> You missed out one critical step from your solution: Add a new unique
> constraint so that this can't happen again.
> --
> David Portas
> SQL Server MVP|||How about
Delete Note
Where NoteType = '29000'
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Here I'm assuming that a given Note.RecordId must exist in Lejer.LejerId.
However, it is possible that this is not the case and you want to ensure tha
t
the record does exist in the Lejer table then you could add an Exists clause
like so:
Delete Note
Where NoteType = '29000'
And Exists(
Select *
From Lejer As L1
Where L1.LegerId = Note.RecordId
)
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Obviously, you should execute this code carefully to ensure that it is produ
ce
the results you want before you commit against production data.
HTH
Thomas
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Hi
> First of all, it's a vendor application, so I can't change anything in the
> database or application. I this case it's not a problem though, since it's
ok
> to create several records as they did, but in this case it's just because
they
> had some problems with a printer and therefore thay ran a wizard several t
imes
> which generated a number of records that where baiscally same. It's these
> records thay now want to get deleted.
> The 2 tables that's involved is called Lejer and Note.
>
> CREATE TABLE [Lejer] (
> [EjendomNr] [EjendomNr] NOT NULL ,
> [LejemaalNr] [LejemaalNr] NOT NULL ,
> [LejerNr] [LejerNr] NOT NULL ,
> [LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> .....and about 200 more column definitions
>
> CREATE TABLE [Note] (
> [NoteID] [RecordID] NOT NULL ,
> [Tabelnavn] [TabelNavn] NOT NULL ,
> [RecordID] [RecordID] NOT NULL ,
> [Dato] [Dato] NOT NULL ,
> [NoteType] [KodeId] NOT NULL ,
> ....and some more column definitions.
> The fields that links the tables are Lejer.LejerID and Note.RecordID.
>
> Below is ans example of sample data
> Lejer:
> Ejendomnr Lejemaalnr Lejernr LejerID
> 1 1 1 1000
> 1 2 2 1001
> 1 3 3 1002
> 2 1 1 1003
> 2 2 2 1004
> 3 1 1 1005
> 3 2 2 1006
> Note
> NoteID RecordID NoteType
> 1 1000 29000
> 2 1000 29000
> 3 1000 29000
> 4 1000 29000
> 5 1001 29000
> 6 1001 29000
> 7 1001 29000
> 8 1002 29000
> 9 1002 29000
> 10 1002 29000
> 11 1003 29000
> 12 1003 29000
>
> I'd like to find the notes where the type is e.g. 29000 and are linked a
> record in the Lejer table with the Ejendomnr of e.g. 1.
> and then delete all notes but 1.
> In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note
> records with NoteID 1 to 10. Out of these I'd like to delete 3 of the ones
> with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with Record
ID
> 1002.
> As mentioned in my original post, I could get all the RecordID's into a cu
rsor
> and then e.g. count the number of occurences for each of them and then do
a
> delete n-1 times. I just don't know if that's the smartest way to do it or
if
> there's a better approach?
> Regards
> Steen
>
> David Portas wrote:
>|||Hi Thomas
Thanks for you input. That was another way of doing it than I had in my
mind. I'll try it out in my test db.
Regards
Steen
Thomas Coleman wrote:
> How about
> Delete Note
> Where NoteType = '29000'
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Here I'm assuming that a given Note.RecordId must exist in
> Lejer.LejerId. However, it is possible that this is not the case and
> you want to ensure that the record does exist in the Lejer table then
> you could add an Exists clause like so:
> Delete Note
> Where NoteType = '29000'
> And Exists(
> Select *
> From Lejer As L1
> Where L1.LegerId = Note.RecordId
> )
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Obviously, you should execute this code carefully to ensure that it
> is produce the results you want before you commit against production
> data.
> HTH
>
> Thomas
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...

Help suppress

I am trying to suppress some details, what I have is 3 columns 1st is order number, 2nd is product, 3rd stock status ie allocated/back ordered. It prints a line per product and the status. What I am trying to do is if the order has one line on back order then I want to suppress the whole order.

Thanks

RichardHi,
Can you post the expected result with some sample data?

Madhivanan|||What version on Crystal are you using? I use 8.5, so you may need to modify the following to work with your version.

You can use a conditional suppress. I will assume that you have each order appearing in the Details section. Go into the Format of the details section. Find where you can check to Suppress that section. You should see a button that looks like 'x-2'. Click the button and enter a formula. The Detail section will be supressed each time the formula evaluates to True.

Example, put this in the Formula box when you click 'x-2':
{StockStatus} = "BackOrdered"

Help required in import Data into sql 2005 from excel 4.0

Hi
I have to import data from a number of excel files to corresponding tables in SQL 2005. The excel files are created using excel 4.0. I have created an excel connection manager and provided it with the path of the excel sheet.Next i have added an excel source from the toolbox to the dataflow. I have set the connection manger, data access mode, and the name of the excel sheet (the wizard detects the sheet correctly) in the dialog window i get when i double click the excel source. Every thing goes fine till here. Now when i select the 'columns' in this dialog window or the preview button, i get this error

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Excel Source [1]]: An OLE DB error has occurred. Error code: 0x80004005.
Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "test4$" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)


Any ideas about why is this happening?
UmerI have the exact error. Please someone help.