Help with SQL query please

I have a table that have 4 fields of interest, A, B, C and D (plus others
that are not of interest).
I am not interested in completely unique rows (based on the fields A,B,C &
D).
What I am interested in rows where the A, B & C fields are identical, but
within these identical "groups" the D field is different.
So, I though I'd write an SQL statement that uses a sub query.
The inner query would bring back all rows that are not unique (by using a
count > 1 statement) and the outer query would then get the details
The SQL I came up with is as follows.
The query is as follows:
---
select A, B, C, D
from X
where exists
(select A, B, C, count(D)
from X
group by A, B, C
having count(D) > 1)
order by A, B, C
---
However...when running the sub query in isolation and ordering the results,
I found that the lowest value of "A" returned was 3. But, when I run the
query as a whole, the first set of results returned have data for rows that
contain A values of 1 & 2.
Puzzled over this, but now need some help.
Thanks in advance if you can provide this...
GriffPlease ALWAYS post DDL with questions like this so that we don't have
to guess what your table looks like. It also helps to include some
sample data and show your required end result.
The problems with the query you posted seem to be twofold. First the
subquery isn't correlated. Second COUNT(D) will count >1 if there is
more than one row even if D is the same (non-null) in each case. That
would make sense if (A,B,C,D) is a key of this table but you didn't
actually specify a key (did I mention about the importance of including
DDL?).
Assuming (A,B,C,D) is not nullable (yes, the DDL would have told us
that too) you can do it with a correlated subquery:
SELECT x.a, x.b, x.c, x.d
FROM x
WHERE EXISTS
(SELECT *
FROM x AS z
WHERE x.a = z.a
AND x.b = z.b
AND x.c = x.c
AND x.d <> z.d)
ORDER BY x.a, x.b, x.c, x.d ;
or with a derived table:
SELECT x.a, x.b, x.c, x.d
FROM
(SELECT a, b, c
FROM x
GROUP BY a, b, c
HAVING MIN(d)<MAX(d)) AS z
JOIN x
ON x.a = z.a
AND x.b = z.b
AND x.c = x.c
ORDER BY x.a, x.b, x.c, x.d ;
(both untested)
Essential reading on the best way to post a problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

Help with SQL query is required please

Hi

I have the following table:

date number called answered
2006-08-10 9051231234 0
2006-08-10 9051231235 1
2006-08-11 9051231231 0
2006-08-11 9051231211 0
2006-08-06 9051231222 1
2006-08-06 9051231233 0
2006-08-06 9051231233 0

need to get the report on how many calls have been placed for particular day and how many were answered, something like this:

2006-08-06 3 1
2006-08-10 2 0
2006-08-11 2 1

How to do this in one query or without creating an interim table.
Thanks.Can you post the URL or a copy of the assignment exactly as it came from the teacher? There are usually some subtle quirks in how the assignments are worded that will influcence how you need to solve the problem to get a good grade.

It would also help to know exactly what hardware/software they'll test this on, since that can influence the choices we make too.

-PatP|||pat, you cynic

905 area code is just outside toronto, and i happen to know there are no sql classes going on right now

this has to be a real world problem, not a homework assignment!! :)

xirurg, would you please show the query that you've managed to work out by yourself so far? use correct table and column names, please|||This should be relatively simple, viz. grouping by date, counting to find the second column, and summing to get the third one.|||SQL classes? Yes I think I'd need them :-). Yes it's the real issue.
Software is mySQL. Here are the fields which matter (there are more in the table but they are irrelevant for this task):
+----+-----+--+--+-------+---+
| Field | Type | Null | Key | Default | Extra |
+----+-----+--+--+-------+---+
| calldate | datetime | | MUL | 0000-00-00 00:00:00 | |
| dcontext | varchar(80) | | | | |
| disposition | varchar(45) | | | | |

dcontext has values outbond or incoming - I'm interesting in outbound only. Disposition has values ANSWERED, NO ANSWER, FAILED. I'm interesed in ANSWERED only.
Report should look like:
calldate, number of outbound calls, number of answered calls.

I can get those numbers by using 2 separate queries:
select left(calldate,11),count(*) from cdr where dcontext<>"incoming" group by left(calldate,11);
select left(calldate,11),count(*) from cdr where disposition="ANSWERED" and dcontext<>"incoming" group by left(calldate,11);

but then I have to combine results either in temp table or inside programming code (Perl in this case) so I was thinking there is a way to do it in one query. I tried to use UNION SELECT and CUBE grouping, CASE as well - no luck

Xirurg

pat, you cynic

905 area code is just outside toronto, and i happen to know there are no sql classes going on right now

this has to be a real world problem, not a homework assignment!! :)

xirurg, would you please show the query that you've managed to work out by yourself so far? use correct table and column names, please|||dcontext has values outbond or incoming - I'm interesting in outbound only. Disposition has values ANSWERED, NO ANSWER, FAILED. I'm interesed in ANSWERED only.
Try this:SELECT LEFT(calldate,11) AS calldate,
COUNT(*) AS number_of_outbound_calls,
SUM(CASE disposition WHEN 'ANSWERED' THEN 1 ELSE 0 END)
AS number_of_answered_calls
FROM cdr
WHERE dcontext<>'incoming'
GROUP BY LEFT(calldate,11)Instead of "LEFT(calldate,11)" also try "CAST(calldate AS date)" -- should be better in terms of performance, in case mySQL supports this.|||Thanks Peter

Help with SQL query from ASP

Hi,
Not sure if I have posted this is the right place, if anyone could
point me in the direction if I'm wrong I'd be grateful.
Anyway, heres my query, I have a SQL query which is searching records
where a date has past, and it works fine in SQL query analyser. Heres
an example of the query...
SELECT
PROD_NO,
B.BRAND,
PRODUCT_NAME,
D.DATE_SENT,
D.DATE_RETURN
FROM dbo.PRODUCTS AS P
INNER JOIN BRANDS AS B
ON P.BRAND_ID = B.BRAND_ID
INNER DATES AS D
ON D.UID = P.UID
WHERE STATUS_ID = '3'
AND DATE_RETURN < '2005-04-25 00:00:00'
This works fine in query analyser, but when I pass the same script to
the server from an ASP page (where I am replacing the date_return value
with an ASP variable representing todays date as above) I get the
following message...
"The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value."
Does anyone have any ideas? Maybe I'm going the wrong way about this?
Thanks
STR8Badly formatted datetime string. I suggest you start by reading relevant sec
tions here:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<straighteight@.gmail.com> wrote in message
news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
> Hi,
> Not sure if I have posted this is the right place, if anyone could
> point me in the direction if I'm wrong I'd be grateful.
> Anyway, heres my query, I have a SQL query which is searching records
> where a date has past, and it works fine in SQL query analyser. Heres
> an example of the query...
> SELECT
> PROD_NO,
> B.BRAND,
> PRODUCT_NAME,
> D.DATE_SENT,
> D.DATE_RETURN
> FROM dbo.PRODUCTS AS P
> INNER JOIN BRANDS AS B
> ON P.BRAND_ID = B.BRAND_ID
> INNER DATES AS D
> ON D.UID = P.UID
> WHERE STATUS_ID = '3'
> AND DATE_RETURN < '2005-04-25 00:00:00'
> This works fine in query analyser, but when I pass the same script to
> the server from an ASP page (where I am replacing the date_return value
> with an ASP variable representing todays date as above) I get the
> following message...
> "The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value."
> Does anyone have any ideas? Maybe I'm going the wrong way about this?
> Thanks
> STR8
>|||To add to Tibor's response, you can also use a parameterized query so that
you don't need to bother with formatting SQL string literals in your code.
Hope this helps.
Dan Guzman
SQL Server MVP
<straighteight@.gmail.com> wrote in message
news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
> Hi,
> Not sure if I have posted this is the right place, if anyone could
> point me in the direction if I'm wrong I'd be grateful.
> Anyway, heres my query, I have a SQL query which is searching records
> where a date has past, and it works fine in SQL query analyser. Heres
> an example of the query...
> SELECT
> PROD_NO,
> B.BRAND,
> PRODUCT_NAME,
> D.DATE_SENT,
> D.DATE_RETURN
> FROM dbo.PRODUCTS AS P
> INNER JOIN BRANDS AS B
> ON P.BRAND_ID = B.BRAND_ID
> INNER DATES AS D
> ON D.UID = P.UID
> WHERE STATUS_ID = '3'
> AND DATE_RETURN < '2005-04-25 00:00:00'
> This works fine in query analyser, but when I pass the same script to
> the server from an ASP page (where I am replacing the date_return value
> with an ASP variable representing todays date as above) I get the
> following message...
> "The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value."
> Does anyone have any ideas? Maybe I'm going the wrong way about this?
> Thanks
> STR8
>|||Good point, Dan. I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eb2f76lSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> To add to Tibor's response, you can also use a parameterized query so that
you don't need to
> bother with formatting SQL string literals in your code.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <straighteight@.gmail.com> wrote in message
> news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
>|||Thanks guys, the link gave me a couple of hints which lead to me
sorting it out (ie. drop the dashes and it was fine!) As for the
"parameterized query", just a newbie to SQL here (month or so...) but
will certainly look into it, thanks for the advice! :)
Cheers

Help with SQL Query - "The multi-part identifier "alias field Name" could not

Hi Everyone
This is the query and I am getting follwoing error message

"The multi-part identifier "InvDate.Account Reference" could not be bound."

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC

By the way its SQL Server 2005 Environment.
Mitesh
Well how about getting rid of:
- (select * from customer) -- just use a simple join to customer
- get rid of the collate statements in your where clauses.

Also, you'll need to add INVDATE.[LASTORDERDATE] to your group by statement.

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]

FROM CUSTOMERCONTACTNOTES AS CCN,

CUSTOMER AS CC,

(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE

WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]

GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE]
ORDER BY CC.COMPANY ASC|||Thanks Phill,

Your solution was just spot on.

Just out of curosity, how do you read any SQL Query, for e.g. like mine and find what is wrong.

Mitesh|||Experience, I guess. When you work with it enough, you can just "read" SQL and understand what's going on.

I really don't think you need the "select distinct [account reference] from invoicedata" query in your where clause though. You already have a distinct list from the INVDATE query in your FROM statement. Your where clause should probably be:

WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]sql

help with sql query

I am trying to fill a dataset with customers in Table1, but i am searching by ItemID(probably from Table3). I can't figure out the sql query for this request.
Maybe something like:"select * from Table1 ...and now I think I should some how inner join Table1 and Table3 so i can select according to ItemID.

I have the three following tables:

Table1--It's primary key is the CustID identity field:

CustID CustName CustEmail ...

Table2--It's primary key is the ItemID identity field:

ItemID ItemName

Table3--ItemID and CustID function together for the primary key of the table:

ItemID CustID
1 1
2 1
2 2
3 2
1 3
3 3

ThanksTry this

select customers.* from table1, table2,table3 where
table1.custid=table3.custid and table3.itemid=table2.itemid

HTH|||Thanks!

Help with SQL Query

Hello all. I have a query which is very simple but I just cant think of a way to do it. I have 3 tables
enrollment course
-------- ----
enrollment_id course_id
course_id course_title
ecommerce_time
enrollment_status_id
A value of 1 for enrollment_status_id = 1 means that the status is complete. Now in this query I need to get a list of all courses, a count of all the enrollments for that course_id and a count of all completed courses(enrollment_status_id=1). And then the condition is a start date and an end date(inputs). The query must be filtered on the condition
ecommerce_time >= start_date and ecommerce_time >= end_date. How do I get the 2 different counts which have different conditions? Thank you.
I have this query but I want to exclude the results which have 0 enrollments from the result set.
Select course_id, course_title As course_title,
(Select count(enrollment_id) From enrollment Where enrollment.course_id = course.course_id
And (ecommerce_time >= '01/01/2000' And ecommerce_time <= '03/01/2005')) As total_enrollments,
(Select count(enrollment_results_id) From enrollment Where enrollment_results_id = '1'
And enrollment.course_id = course.course_id And (ecommerce_time >= '01/01/2000' And ecommerce_time <= '03/01/2005')) As total_completions
From course Where organization_id = '1'
Order By course_titlePersonally, this is how I would form that query:
SELECT
course_id,
course_title,
TE.total_enrollments,
E.total_completions
FROM
course
INNER JOIN
(SELECT course_id, COUNT(*)AS total_enrollments FROM enrollment
WHERE ecommerce_time >= '01/01/2000' AND ecommerce_time <= '03/01/2005
GROUP BY course_ID) AS TE ON course.course_id = TE.course_id
LEFT OUTER JOIN
(SELECT course_id, COUNT(*)AStotal_completionsFROM enrollment
WHERE enrollment_results_id = '1' AND
ecommerce_time >='01/01/2000' AND ecommerce_time <= '03/01/2005'
GROUP BYenrollment.course_id) AS E ON course.course_id =E.course_id
WHERE
organization_id = '1' AND
TE.total_enrollments > 0
ORDER BY
course_title

The main thing here is using a derived table and to INNER JOIN onit. I also moved your other query as a LEFT OUTER JOIN although Ithink the query would have worked without moving it. To me thisapproach is easier to read and understand. I am not 100% sureabout performance.

Help with SQL Query

Hi Guys,

I need "little help" (in my books) with my SQL Query.

I have a table which lists the total transactions entered by each staff member.
All the transactions are saved with the staffID of the staff member and the date of the transaction.

Date Amount Staff
1/1/2005 12 STaff1
1/1/2005 2 STaff2
1/1/2005 1 STaff3
1/1/2005 5 STaff1

Now the problem is that I need to select All the transactions from the table grouped on the basis of data and staff member,

so essentially its should look like

Date Total Staff1 Staff2 Staff3
1/1/2005 24 12 11 1

Moreover the number of staff members can be anything, so basically I can't even use sub queries.

I have been working on this one for a while now, and still don't know what to do... any help will be greatly appreciated.

ThanxYou will need a dynamic pivot (cross-tab) function - something like:

http://www.sqlteam.com/item.asp?ItemID=2955|||hi ehorn,

Thanks for your help... worked like a charm.. :)

Cheers!

Help with SQL query

Hi everyone.

I'm stuck on an SQL query, and hope one of you can help me. Have been trying to solve it all day long without any success. Doesn't even sound that difficult...

I have a table that has four columns. A combination of the first three columns is a foreign key for 'products'. The last column is a foreign key for 'stores'. This table keeps track of which products are assigned to which stores. Example of data -

Id_Prod_Grupo Id_Prod_Tipo Id_Prod_Pres Id_Cliente
---- ---- ---- ----
0 0 1 100
0 0 2 11476
0 0 3 12939
0 0 4 960
0 0 4 12941
0 0 5 1
0 0 5 10
0 0 5 960
0 0 5 15033
0 0 6 1
0 0 6 10
0 0 7 1
0 0 7 15033
0 0 7 92606

In the application, the user selects multiple stores, and the application has to display which all products are common to them. For example, if the user selects store 1 and 10, then application has to pick up products (0,0,5) and (0,0,6). Simple enough right?

Unfortunately, I can't form the query. Can someone please help me with this? If you could just give me a query that works for stores 1 and 10, I'm sure I can modify it myself in the application to make the whole process dynamic.

Thanks in advance folks!Table definitions and some sample data (INSERTs) would be helpful.|||drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)|||Originally posted by sbaru
Table definitions and some sample data (INSERTs) would be helpful.

Actually the table structure etc. are completely irrelevant. Forget the whole part about the foreign keys etc... The only table to be used here is the one I showed above..

Let me see if I can make it any more clear though.. One of the earliest solutions I tried was a query like this -

SELECT DISTINCT Id_Prod_Grupo, Id_Prod_Tipo, Id_Prod_Pres FROM CVR_PRODUCTOS_TIENDA
WHERE Id_Cliente = 1 or Id_Cliente = 10

However, this query simply returns all the products that belong to EITHER one of the stores. I want products which are _common_ to both these stores. Get it?|||Originally posted by snail
drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)

Worked beautifully Snail! Thanks a lot!!|||You didn't like my first solution (http://www.dbforums.com/showthread.php?postid=3656027#post3656027)?|||Originally posted by Pat Phelan
You didn't like my first solution (http://www.dbforums.com/showthread.php?postid=3656027#post3656027)?

Nice try - it works better than mine.|||Originally posted by snail
drop table #test
create table #test(f1 int,f2 int,f3 int,f4 int)
go
insert #test values(1,0,1,1)
insert #test values(1,1,1,2)
insert #test values(0,1,1,2)
insert #test values(1,1,1,3)
insert #test values(1,0,1,3)
insert #test values(0,1,1,1)
insert #test values(1,0,1,2)
go
select distinct t2.*
from #test t1
join #test t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3 and t2.f4<>t1.f4
where t1.f4 in(1,2) and t2.f4 in(1,2)

Damn.. found a problem. If I use this method to find common products for more than two stores, the queries returns even those products which are present in just two of the stores..|||Originally posted by anujjain
Damn.. found a problem. If I use this method to find common products for more than two stores, the queries returns even those products which are present in just two of the stores..

Use Pat Phelan solution - it works like a charm ...|||Pat Phelan, one small problem in the solution you gave me.. I need to select the fourth column (store) as well, and I can't seem to be able to modify your query to do it right.. I'm afraid I suck at SQL!

Help??

P.S. Thanks a lot for the help guys, you're the best :)|||Originally posted by anujjain
Pat Phelan, one small problem in the solution you gave me.. I need to select the fourth column (store) as well, and I can't seem to be able to modify your query to do it right.. I'm afraid I suck at SQL!

Help??

P.S. Thanks a lot for the help guys, you're the best :)

Try this is combination (I hope Pat Phelan will not be offended ;) )

select t1.* from #test t1
join (SELECT f1,f2,f3
FROM #test
WHERE f4 IN (1,2,3) -- store list goes here
GROUP BY f1, f2, f3
HAVING Count(DISTINCT f4) = 3 -- store count goes here
) as t2 on t2.f1=t1.f1 and t2.f2=t1.f2 and t2.f3=t1.f3|||Off we go, into... Nevermind!SELECT *
FROM phrog AS a
WHERE 2 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM phrog AS z
WHERE Id_Cliente IN (1, 10) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)

Edited to fix two logic errors (oops)!

-PatP|||Originally posted by snail
Try this is combination (I hope Pat Phelan will not be offended ;) ) Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.

-PatP|||Originally posted by Pat Phelan
Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.

-PatP Nice to meet you ;)|||Pat Phelan / Snake,

Both your solutions don't work for the following set of data -

Id_Prod_Grupo Id_Prod_Tipo Id_Prod_Pres Id_Cliente
---- ---- ---- ----
0 0 5 1
0 0 5 10
0 0 5 960
0 0 5 15033
0 0 8 1
0 0 8 10
0 0 8 960
0 0 8 92606

The whole table is returned when running the query for 3 stores (1, 10 and 960), whereas row 4 and 8 shouldn't be in the results...|||Did you change the 2 count to a 3 count in my query?

-PatP|||Yes, here is the exact query I ran...

SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE 3 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)|||Uff-da! Bone-head alert. I missed an important part. I showed the products that existed in those three stores, without regard to where those products were stored (doh!). For a band-aid fix, you can use:SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE Id_Cliente IN (1, 10, 960)
AND 3 = (SELECT Count(DISTINCT Id_Cliente) -- store count goes here
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres)while I think about a more elegant fix. Sorry!

-PatP|||Pat Phelan,

You da man! :) Works perfectly. Thanks a lot!|||Just because I like simple solutions, could you also try:SELECT *
FROM CVR_PRODUCTOS_TIENDA AS a
WHERE Id_Cliente IN (SELECT Id_Cliente
FROM CVR_PRODUCTOS_TIENDA AS z
WHERE Id_Cliente IN (1, 10, 960) -- store list goes here
AND z.Id_Prod_Grupo = a.Id_Prod_Grupo
AND z.Id_Prod_Tipo = a.Id_Prod_Tipo
AND z.Id_Prod_Pres = a.Id_Prod_Pres
GROUP BY z.Id_Cliente
HAVING Count(DISTINCT z.Id_Cliente) = 3)) -- store count goes hereThis shouldn't change the execution plan, but it does simplify the query because you only need to include the store list once and store count once in this query.

-PatP|||Originally posted by Pat
Nah, you've got to work REALLY hard to offend me. I'm pretty much rude, crude, lewd, and socially unacceptable in almost anybody's frame of reference.
Sounds very familiar...Wait a minute, that's my last annual review you're quoting! Where did you get it?|||Originally posted by rdjabarov
Sounds very familiar...Wait a minute, that's my last annual review you're quoting! Where did you get it? You be amazed at the stuff we've got posted on the walls around here! ;)

-PatPsql

help with sql query

hello all,
I need some urgent help with a query.
basically I have a simple table (sql server 2005 database) with this
kind of data
user date
john 1 apr
mark 31 mar
paul 2 apr
john 30 mar
john 14 apr
paul 4 apr
I need to build a query that, for a given user, retrieves the user
with the latest date only (the date field is formatted in the table as
datetime), for example john 14 apr...or paul 4 apr....
I'm kind of stuck...any help is greatly appreciated!
thanks in advance!
zz
Here is one way:
;WITH RankedUsers
AS
(SELECT [user], [date],
ROW_NUMBER() OVER(
PARTITION BY [user]
ORDER BY [date] DESC) AS seq
FROM Foo)
SELECT [user], [date]
FROM RankedUsers
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
Chuck
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>
|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help with SQL Query

I originally posted this in microsoft.public.sqlserver.datamining, which
apparently is a dead zone. My apologies for the redundancy:
I need help with a query, and unfortunately, my SQL skills aren't
particularly advanced. My sample data looks something like this:
Name City Date
Smith New York Jan. 23, 2004
Jones New York May 1, 2004
Brown New York Aug. 18, 2004
Johnson Chicago Feb. 23, 2004
Chrysler Chicago April 23, 2004
Ford Chicago Sept. 3, 2004
I'd like to run a query which will give me the latest or last entry
(date-wise) relative to city. The result would look something like this:
Name City Date
Brown New York Aug. 18, 2004
Ford Chicago Sept. 3, 2004
Is there an easy solution to this? Thanks for any and all input.
btw, is this the best ms newsgroup to post sql queries questions, or is
there a more appropriate one? Thanks again.
steve.
SELECT t1.Name, t1.City, t1.Date
FROM some_table t1
WHERE NOT EXISTS(SELECT NULL FROM some_table t2
WHERE t2.City = t1.City AND t2.Date > t1.Date)
The best newsgroup for query questions btw, is
microsoft.public.sqlserver.programming.
Jacco Schalkwijk
SQL Server MVP
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eT%23zMA$yEHA.3028@.TK2MSFTNGP10.phx.gbl...
>I originally posted this in microsoft.public.sqlserver.datamining, which
> apparently is a dead zone. My apologies for the redundancy:
> I need help with a query, and unfortunately, my SQL skills aren't
> particularly advanced. My sample data looks something like this:
> Name City Date
> Smith New York Jan. 23, 2004
> Jones New York May 1, 2004
> Brown New York Aug. 18, 2004
> Johnson Chicago Feb. 23, 2004
> Chrysler Chicago April 23, 2004
> Ford Chicago Sept. 3, 2004
> I'd like to run a query which will give me the latest or last entry
> (date-wise) relative to city. The result would look something like this:
> Name City Date
> Brown New York Aug. 18, 2004
> Ford Chicago Sept. 3, 2004
> Is there an easy solution to this? Thanks for any and all input.
> btw, is this the best ms newsgroup to post sql queries questions, or is
> there a more appropriate one? Thanks again.
> steve.
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ePk1xD$yEHA.1300@.TK2MSFTNGP14.phx.gbl...
> SELECT t1.Name, t1.City, t1.Date
> FROM some_table t1
> WHERE NOT EXISTS(SELECT NULL FROM some_table t2
> WHERE t2.City = t1.City AND t2.Date > t1.Date)
> The best newsgroup for query questions btw, is
> microsoft.public.sqlserver.programming.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Thanks for both.
steve.

Help with SQL Query

Hey all,
My abridged table structure is:
UNQ Number Student ID Date Attendance
1 1 1-feb-05 Y
2 2 2-feb-05 N
3 3 3-feb-05 Y
4 4 4-feb-05 C
5 1 .
6 2 .
7 3
8 4
9 1
10 2
11 3
12 4
13 1
14 2
15 3
16 4
and so on:
I need an output like (between two dates):
Student ID Present Absent Cancelled
1 10 4 3
These numbers are just examples.
I know I can write a query like:
select a.[student id],count(distinct a.[unique number]) from attendancetable
as a where a.[attendance]='Y' and a.[date]>='01-feb-05' and
a.[date]<='01-feb-05' group by a.[student id]
to get a result like:
Student ID Present
1 10
But, how can I get all the three information in one output?
Thank you.Try the following.
SELECT StudentID,
count(CASE Attendance WHEN 'Y' THEN 1 ELSE 0 END) AS 'Present',
count(CASE Attendance WHEN 'N' THEN 1 ELSE 0 END) AS 'Absent',
count(CASE Attendance WHEN C' THEN 1 ELSE 0 END) AS 'Cancelled'
FROM Attendance_Table
WHERE Date>= YourStartDate and Date <= YourEnddate
GROUP BY StudentID
GO
Gavin|||Thanks plenty!
A little modification:
SELECT distinct [Student ID],
count(CASE WHEN attendance='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN attendance='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when Attendance='C' THEN 1 ELSE null END) AS 'Cancelled'
FROM StudentScheduleAttendanceDetails
WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID] order
by [student id]
GO
did it. Thanks again!
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107658677.037863.312350@.f14g2000cwb.googlegroups.com...
> Try the following.
> SELECT StudentID,
> count(CASE Attendance WHEN 'Y' THEN 1 ELSE 0 END) AS 'Present',
> count(CASE Attendance WHEN 'N' THEN 1 ELSE 0 END) AS 'Absent',
> count(CASE Attendance WHEN C' THEN 1 ELSE 0 END) AS 'Cancelled'
> FROM Attendance_Table
> WHERE Date>= YourStartDate and Date <= YourEnddate
> GROUP BY StudentID
> GO
> --
> Gavin
>|||One other thing:
Is it possible to show Present / Total as a column ?
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08'
and a.[student id]<>0 and b.[student id]=a.[student id]
and a.[student id] like '%'
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
a.[student id]
somthing like:
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
(Present / Total) * 100 as "Present Ratio"
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08'
and a.[student id]<>0 and b.[student id]=a.[student id]
and a.[student id] like '%'
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
a.[student id]
So, I get something like
1011 A B a@.b.com 10 20 30 5 65 15.3
Can you please tell me if this can be done?
Thank you
Vince
"Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
news:uC63Lr$CFHA.2620@.tk2msftngp13.phx.gbl...
> Thanks plenty!
> A little modification:
> SELECT distinct [Student ID],
> count(CASE WHEN attendance='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN attendance='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when Attendance='C' THEN 1 ELSE null END) AS 'Cancelled'
> FROM StudentScheduleAttendanceDetails
> WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
order
> by [student id]
> GO
> did it. Thanks again!
> "celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
> news:1107658677.037863.312350@.f14g2000cwb.googlegroups.com...
>|||I think I have to only use a stored procedure, declare the variables, and
select the variable...
"Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
news:umGSdBADFHA.2600@.TK2MSFTNGP09.phx.gbl...
> One other thing:
> Is it possible to show Present / Total as a column ?
>
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
> count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
> count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08'
> and a.[student id]<>0 and b.[student id]=a.[student id]
> and a.[student id] like '%'
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
> a.[student id]
> somthing like:
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE when a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
> count(CASE when a.[Attendance] = '' then 1 else null END) as 'Future',
> count(CASE when a.[Attendance] like '%' then 1 else null END) as 'Total'
> (Present / Total) * 100 as "Present Ratio"
>
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08'
> and a.[student id]<>0 and b.[student id]=a.[student id]
> and a.[student id] like '%'
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail] order by
> a.[student id]
> So, I get something like
> 1011 A B a@.b.com 10 20 30 5 65 15.3
> Can you please tell me if this can be done?
> Thank you
> Vince
>
> "Vince" <nmvkPLEASERMVTHIS@.vsnl.net> wrote in message
> news:uC63Lr$CFHA.2620@.tk2msftngp13.phx.gbl...
> order
>|||Glad I could help. I am new to this group and have a few questions open
so feel I should at least answer what I can.
Sorry, I should have left the function as SUM. The following will work
as well.
SELECT distinct [Student ID],
sum(CASE WHEN attendance='Y' THEN 1 ELSE 0 END) AS 'Present',
sum(CASE WHEN attendance='N' THEN 1 ELSE 0 END) AS 'Absent',
sum(CASE when Attendance='C' THEN 1 ELSE 0 END) AS 'Cancelled'
FROM StudentScheduleAttendanceDetails
WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
order
by [student id]
GO|||Just guessing but this should work. But I have not tried doing
calculations on groups.
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS
'Cancelled',
count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
count(a.[Attendance]) as 'Total'),
count(CASE a.[attendance]
WHEN 'Y' THEN 1
WHEN 'N' THEN 1
WHEN 'C' THEN 1
ELSE null END) / count(a.[Attendance]) as 'Total')*100 as 'Present
Ratio'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08' and
a.[student id]<>0 and
b.[student id]=a.[student id]
/* and a.[student id] like '%' NOT SURE WHAT THIS LINE IS FOR' */
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
ORDER BY a.[student id]|||Thanks Gavin! Really appreciate your help.
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107664607.420207.136340@.g14g2000cwa.googlegroups.com...
> Glad I could help. I am new to this group and have a few questions open
> so feel I should at least answer what I can.
> Sorry, I should have left the function as SUM. The following will work
> as well.
> SELECT distinct [Student ID],
> sum(CASE WHEN attendance='Y' THEN 1 ELSE 0 END) AS 'Present',
> sum(CASE WHEN attendance='N' THEN 1 ELSE 0 END) AS 'Absent',
> sum(CASE when Attendance='C' THEN 1 ELSE 0 END) AS 'Cancelled'
> FROM StudentScheduleAttendanceDetails
> WHERE Date>= '01-jan-03' and Date <= '01-feb-06' GROUP BY [Student ID]
> order
> by [student id]
> GO
>|||Again, thank you! It worked, I just had to cast as float...That [student id]
like '%' was to be like '3%' (all student ids beginning with 3)..
SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS 'Cancelled',
count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
count(a.[Attendance]) as 'Total',
cast(cast(count(CASE when a.[attendance]='Y' THEN 1 else null end)as float)
/
count( a.[attendance])* 100 as decimal(4,0))
as 'Present Ratio (%)'
FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
WHERE Date>= '01-jan-01' and
Date <= '01-jan-08' and
a.[student id]<>0 and
b.[student id]=a.[student id]
GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
ORDER BY a.[student id]
Again, thanks for your help.
"celtic_kiwi" <gavin.jolly@.gmail.com> wrote in message
news:1107666089.497943.221340@.g14g2000cwa.googlegroups.com...
> Just guessing but this should work. But I have not tried doing
> calculations on groups.
> SELECT distinct a.[Student ID],b.[First Name],B.[Last Name],b.[E-Mail],
> count(CASE WHEN a.[attendance]='Y' THEN 1 ELSE null END) AS 'Present',
> count(CASE WHEN a.[attendance]='N' THEN 1 ELSE null END) AS 'Absent',
> count(CASE WHEN a.[Attendance]='C' THEN 1 ELSE null END) AS
> 'Cancelled',
> count(CASE WHEN a.[Attendance] = '' THEN 1 ELSE null END) as 'Future',
> count(a.[Attendance]) as 'Total'),
> count(CASE a.[attendance]
> WHEN 'Y' THEN 1
> WHEN 'N' THEN 1
> WHEN 'C' THEN 1
> ELSE null END) / count(a.[Attendance]) as 'Total')*100 as 'Present
> Ratio'
> FROM StudentScheduleAttendanceDetails as a,StudentPersonalDetails as B
> WHERE Date>= '01-jan-01' and
> Date <= '01-jan-08' and
> a.[student id]<>0 and
> b.[student id]=a.[student id]
> /* and a.[student id] like '%' NOT SURE WHAT THIS LINE IS FOR' */
> GROUP BY a.[Student ID],b.[first name],b.[last name],b.[e-mail]
> ORDER BY a.[student id]
>

Help with SQL Query

I originally posted this in microsoft.public.sqlserver.datamining, which
apparently is a dead zone. My apologies for the redundancy:
I need help with a query, and unfortunately, my SQL skills aren't
particularly advanced. My sample data looks something like this:
Name City Date
Smith New York Jan. 23, 2004
Jones New York May 1, 2004
Brown New York Aug. 18, 2004
Johnson Chicago Feb. 23, 2004
Chrysler Chicago April 23, 2004
Ford Chicago Sept. 3, 2004
I'd like to run a query which will give me the latest or last entry
(date-wise) relative to city. The result would look something like this:
Name City Date
Brown New York Aug. 18, 2004
Ford Chicago Sept. 3, 2004
Is there an easy solution to this? Thanks for any and all input.
btw, is this the best ms newsgroup to post sql queries questions, or is
there a more appropriate one? Thanks again.
steve.SELECT t1.Name, t1.City, t1.Date
FROM some_table t1
WHERE NOT EXISTS(SELECT NULL FROM some_table t2
WHERE t2.City = t1.City AND t2.Date > t1.Date)
The best newsgroup for query questions btw, is
microsoft.public.sqlserver.programming.
Jacco Schalkwijk
SQL Server MVP
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:eT%23zMA$yEHA.3028@.TK2MSFTNGP10.phx.gbl...
>I originally posted this in microsoft.public.sqlserver.datamining, which
> apparently is a dead zone. My apologies for the redundancy:
> I need help with a query, and unfortunately, my SQL skills aren't
> particularly advanced. My sample data looks something like this:
> Name City Date
> Smith New York Jan. 23, 2004
> Jones New York May 1, 2004
> Brown New York Aug. 18, 2004
> Johnson Chicago Feb. 23, 2004
> Chrysler Chicago April 23, 2004
> Ford Chicago Sept. 3, 2004
> I'd like to run a query which will give me the latest or last entry
> (date-wise) relative to city. The result would look something like this:
> Name City Date
> Brown New York Aug. 18, 2004
> Ford Chicago Sept. 3, 2004
> Is there an easy solution to this? Thanks for any and all input.
> btw, is this the best ms newsgroup to post sql queries questions, or is
> there a more appropriate one? Thanks again.
> steve.
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ePk1xD$yEHA.1300@.TK2MSFTNGP14.phx.gbl...
> SELECT t1.Name, t1.City, t1.Date
> FROM some_table t1
> WHERE NOT EXISTS(SELECT NULL FROM some_table t2
> WHERE t2.City = t1.City AND t2.Date > t1.Date)
> The best newsgroup for query questions btw, is
> microsoft.public.sqlserver.programming.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
Thanks for both.
steve.sql

Help With SQL Query

Hello,
With the following table how would I create a query that would return all
rows whos EndDate minus StartDate is more than 28 Days.
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
Table:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
ChuckSELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message news:O6E$smQ2GHA.5048@.T
K2MSFTNGP05.phx.gbl...
> Hello,
>
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
>
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
>
> Table:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
>
> The Query Should Return:
>
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
>
> Thanks,
>
> Chuck
>
>|||Thank You
Chuck
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uHoF7yQ2GHA.3656@.TK2MSFTNGP04.phx.gbl...
SELECT
ID
, CustomerID
, StartDate
, EndDate
FROM Customers
WHERE datediff( day, StartDate, EndDate ) > 28
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:O6E$smQ2GHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hello,
> With the following table how would I create a query that would return all
> rows whos EndDate minus StartDate is more than 28 Days.
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> StartDate - Date
> EndDate - Date
> Table:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 2 Mike1 8/25/06 9/15/06
> 3 Dinah 8/23/06 9/1/06
> 4 James 7/11/06 8/30/06
>
> The Query Should Return:
> ID CustomerID StartDate EndDate
> 1 Chuck1 9/1/06 9/30/06
> 4 James 7/11/06 8/30/06
>
> Thanks,
> Chuck
>

Help with SQL query

Hi, I have a table like the following fields:

TradeDate Item Price

Now, suppose I want to do an update like this: every price
corresponding to a date higher than '30 Sep 2005' will be reset to the
latest price in that item. Can I do something like the following

UPDATE Table t SET Price = (SELECT TOP 1 Price FROM Table q WHERE
q.Item = t.Item ORDER BY TradeDate DESC) WHERE t.TradeDate > '30 Sep
2005'

or is there a better way?

Thank you very much.

BrunoHi Bruno,

Perfect :-D Looks good to me.

HTH, Jens Suessmeyer

Help with SQL Query

I have 2 tables:

- Users, with fields id and fullName
- Accounts, with accntID, userID, accntName, Active

I need to write a query which lists all the users, along with the
number of accounts they have.

I am using the following query -

SELECT U.id, U.fullName, Count(A.accntID) AS CountOfaccntID
FROM users AS U LEFT JOIN accounts AS A ON U.id = A.userID
WHERE A.active=Yes
GROUP BY U.id, U.fullName;

My data is as follows:

Users
=====
1, User1
2, User2

Accounts
========
1,1,User1_Accnt1,true
2,1,User1_Accnt2,true
3,2,User2_Accnt1,false

The expected output is :

1, User1, 2
2, User2, 0

But I get,

1, User1, 2

What do I need to change in the query?> What do I need to change in the query?

Only one word: instead of "WHERE" use "AND".

Razvan|||(b_naick@.yahoo.ca) writes:
> I have 2 tables:
> - Users, with fields id and fullName
> - Accounts, with accntID, userID, accntName, Active
> I need to write a query which lists all the users, along with the
> number of accounts they have.
> I am using the following query -
> SELECT U.id, U.fullName, Count(A.accntID) AS CountOfaccntID
> FROM users AS U LEFT JOIN accounts AS A ON U.id = A.userID
> WHERE A.active=Yes
> GROUP BY U.id, U.fullName;

To explain Razvan's answer a little more, this is what is happening:

Logically, in an SQL query, you start with the table in the FROM
clause, and then you build a new table every time you tack on a
new table with a JOIN operator (this can be changed with parentheses).
Eventually, the resulting table is filtered by the WHERE clause.

So you start with Users, and left-join it to Accounts. You now have
a table which has all the original rows in Users. For the matching
columns in Accounts, the columns from Accounts have the value from
that table. From the non-matching rows, you have NULL. Then comes the
WHERE clause, which says "A.Active=Yes". Which means that all rows
with NULL in A.Active are filtered away. That is, all those rows
from Users with no matching accounts are no longer in the result set.

When you change WHERE to AND, the condition A.Active=Yes moves to
the JOIN operation. This means that only the rows from Accounts
with Active=Yes are brought in, and remaining rows have NULL in
all columns. In your original query, the rows with Active=No had
values in Accounts in that intermediate table (which is only locigal).

This is indeed a common error to make, and it took me sometime as well
to understand how the FROM-JOIN co-operates with WHERE, when I started
to use this syntax.

--
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

May this solve your problem

create Table Users(UserId int primary key,Username varchar(20))

insert into Users(Userid,UserName) values(1,'User1')
insert into Users(Userid,UserName) values(2,'User2')

Create Table Accounts(AccountNo int primary key,UserId int references
Users,AccountName varchar(20),Active varchar(20))

insert into Accounts VALUES(1,1,'User1_Accnt1','true')
insert into Accounts VALUES(2,1,'User1_Accnt2','true')
insert into Accounts VALUES(3,2,'User2_Accnt1','false')

select U.*,count(case when A.Active='true' then 1 else null end) from
Users U, Accounts A
where U.userid = A.userid
group by U.Userid,U.UserName

Drop Table Accounts
Drop Table Users
Please do post DDL , DML as it become easy for others to test their
queries

With warm regards
Jatinder Singh

b_naick@.yahoo.ca wrote:
> I have 2 tables:
> - Users, with fields id and fullName
> - Accounts, with accntID, userID, accntName, Active
> I need to write a query which lists all the users, along with the
> number of accounts they have.
> I am using the following query -
> SELECT U.id, U.fullName, Count(A.accntID) AS CountOfaccntID
> FROM users AS U LEFT JOIN accounts AS A ON U.id = A.userID
> WHERE A.active=Yes
> GROUP BY U.id, U.fullName;
>
> My data is as follows:
> Users
> =====
> 1, User1
> 2, User2
> Accounts
> ========
> 1,1,User1_Accnt1,true
> 2,1,User1_Accnt2,true
> 3,2,User2_Accnt1,false
> The expected output is :
> 1, User1, 2
> 2, User2, 0
> But I get,
> 1, User1, 2
> What do I need to change in the query?

Help with SQL Query

I need help building the following query..

My table has the following schema: eventID, typeID

Sample Rows:

1,1
1,2
1,3
2,1
3,2
3,2
4,3
4,4
5,2

I want to be able to query for all eventID's such that type = 2 and
type <> 1. So the result should be

3,2
4,2

The result should NOT include 1,2 because eventID 1 is also "related"
to typeID 1 and 3.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Your personal pseudo-code is wrong at many levels; did
you mean this?

CREATE TABLE EventSchedules
(event_id INTEGER NOT NULL
REFERENCES Events (event_id),
event_type INTEGER NOT NULL
CHECK (event_type > 0), --assumption
PRIMARY KEY (event_id, event_type)); --requirement!

INSERT INTO EventSchedules VALUES (1,1);
INSERT INTO EventSchedules VALUES (1,2);
INSERT INTO EventSchedules VALUES (1,3);
INSERT INTO EventSchedules VALUES (2,1);
INSERT INTO EventSchedules VALUES (3,2);
INSERT INTO EventSchedules VALUES (3,2);-- removed dup row!!
INSERT INTO EventSchedules VALUES (4,3);
INSERT INTO EventSchedules VALUES (4,4);
INSERT INTO EventSchedules VALUES (5,2);

A data element name like "type_id" makes no sense. Either it is an
identifier for a particular kind of entity or it is some kind of code
for an attribute. It cannot be both an attribute and an entity. You
might want to get a book on data modeling and the ISO-11179 Standards.

>> I want to be able to query for all event_id's such that event_type = 2 and event_type <> 1. <<

Here is one way.

SELECT event_id
FROM EventSchedules
GROUP BY event_id
HAVING MIN(event_type) > 1
AND MAX (CASE WHEN event_type <> 2 THEN 0 ELSE 2 END) = 2;

And I am sure that someone will come up with a self-join solution, too.|||b_naick@.yahoo.ca wrote:
> I need help building the following query..
> My table has the following schema: eventID, typeID
> Sample Rows:
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
> 3,2
> 4,2
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Shouldn't the last pair be 5,2, since there isn't any 4,2.

Try,

SELECT DISTINCT eventID, typeID
FROM t as t1
WHERE typeID = 2
AND eventID NOT IN (SELECT eventID FROM t WHERE typeID != 2)

Change table name "t" to your table's true name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpTZ5IechKqOuFEgEQKIrgCfeW81ytgRIXUnl//jAA0RU8zZwLQAoOPN
UBuYtqvs/JqhLjVuFIYYTzqF
=aqWw
--END PGP SIGNATURE--|||(b_naick@.yahoo.ca) writes:
> I need help building the following query..
> My table has the following schema: eventID, typeID
> Sample Rows:
> 1,1
> 1,2
> 1,3
> 2,1
> 3,2
> 3,2
> 4,3
> 4,4
> 5,2
> I want to be able to query for all eventID's such that type = 2 and
> type <> 1. So the result should be
> 3,2
> 4,2
> The result should NOT include 1,2 because eventID 1 is also "related"
> to typeID 1 and 3.

I assume that desired result is

3,2
5,2

Else there is something I don't understand at all.

This could be a good query:

SELECT *
FROM tbl a
WHERE a.type = 2
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.eventID = b.eventID
AND EXISTS (SELECT *
FROM tbl c
WHERE c.eventID = b.eventID
ABD c.type = 1))

Since you did not include CREATE TABLE and INSERT statements, I
have not tested this.

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

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

help with sql query

Dear all,

i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Hi,

Try something like the following:

(
SELECT employee_id AS ALLemployees
FROM employeesIBM
)
UNION
(
SELECT employee_id
FROM employeesSUN
)

Not perfect, but it should get you the result set you're after. Note that if there are duplicate rows between tables and you don't want to filter out the duplicates then I believe you need to use UNION ALL instead of UNION

Hope that helps a bit, but sorry if it doesn't
|||

That does solves my prob.

Thanks,

Kris

sql

Help with SQL query

Hi,

Let's say I have a Customer table and an Order table. The latter is linked to the former through foreign key CustomerID. Now, I want to create a SQL statement which, given a CustomerID, returns the corresponding row in Customer table PLUS a field indicating the total number of orders this particular customer has. How can I achieve this with a single SQL statement?

Thanks in advance

--USE Northwind

SELECT Customers.CustomerID, SUM(Orders.OrderID) AS TotalOrders

FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

GROUP BY Customers.CustomerID

|||Thanks, this is exactly what I want, except I need to change the aggregate function to Count instead of Sum.

Help with SQL Query

I'm really struggling with this and hoping someone can help me AND I need to restate that I am a SQL noob!

First attempt:
This query is returning all the data I need and then some! There are two fields I can use to try to filter on InventoryDate and RefCodeID. As you will see in the next example I've tried without success to get the info I need.

Code Snippet

SELECT B.BlockName AS Block,

I.Lot,

R.RefCodeName AS LotType,

I.SaleableFrontFootage As Frontage,

ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,

AI.HouseStyle AS Style,

RC.RefCodeName AS Status,

PUR.PurchaserName as Builder

FROM Inventory AS I

JOIN Block AS B

INNER JOIN Phase AS P

INNER JOIN ProjectSub AS PS

INNER JOIN Project AS PJ

ON PS.ProjectID = PJ.ProjectID

ON P.ProjectSubID = PS.ProjectSubID

INNER JOIN PhaseSetup AS PSetup

ON P.PhaseID = PSetup.PhaseID

ON B.PhaseID = P.PhaseID

ON I.BlockID = B.BlockID

LEFT JOIN InventoryDate AS IStatus

ON I.InventoryID = IStatus.InventoryID

AND IStatus.RefCodeID IN (71, 73) -- Open, Spec, Sale

LEFT JOIN RefCode AS R

ON I.LotTypeRefCodeID = R.RefCodeID

LEFT JOIN dbo.BuilderSaleByInventory(NULL) AS BSale

ON I.InventoryID = BSale.InventoryID

LEFT JOIN dbo.InventoryAddressByInventoryID(NULL) IA

ON I.InventoryID = IA.InventoryID

LEFT JOIN dbo.PurchaserByInventory(NULL) AS PUR

ON I.InventoryID = PUR.InventoryID

LEFT JOIN ArchitectureInformation as AI

ON I.InventoryID = AI.InventoryID

LEFT JOIN RefCode AS RC

ON IStatus.RefCodeID = RC.RefCodeID

WHERE PJ.ProjectName = 'Copperfield'

AND P.PhaseID IN (114, 119, 120)

AND Pur.PurchaserName NOT LIKE '%HRC%'

Second attempt:
I've tried adding a select statement in the select statement but it's not working for me, if you have any questions please ask, I'm not sure what Info you will need to help me solve this puzzle.

Code Snippet

SELECT P.PhaseName,

B.BlockName AS Block,

I.Lot,

R.RefCodeName AS LotType,

I.SaleableFrontFootage As Frontage,

ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,

AI.HouseStyle AS Style,

-- RC.RefCodeName AS Status,

(SELECT RC.RefCodeName AS Status

FROM InventoryDate AS IDate

JOIN RefCode AS RC

ON IDate.RefCodeID = RC.RefCodeID

WHERE IDate.InventoryID >= BSale.InventoryDate

AND IDate.InventoryDate = (SELECT MAX(InventoryDate)

FROM InventoryDate AS IDate2

WHERE IDate.InventoryID = IDate2.InventoryID)) AS 'Status',

PUR.PurchaserName as Builder

FROM Inventory AS I

JOIN Block AS B

INNER JOIN Phase AS P

INNER JOIN ProjectSub AS PS

INNER JOIN Project AS PJ

ON PS.ProjectID = PJ.ProjectID

ON P.ProjectSubID = PS.ProjectSubID

INNER JOIN PhaseSetup AS PSetup

ON P.PhaseID = PSetup.PhaseID

ON B.PhaseID = P.PhaseID

ON I.BlockID = B.BlockID

LEFT JOIN RefCode AS R

ON I.LotTypeRefCodeID = R.RefCodeID

LEFT JOIN dbo.InventoryAddressByInventoryID(NULL) IA

ON I.InventoryID = IA.InventoryID

LEFT JOIN dbo.PurchaserByInventory(NULL) AS PUR

ON I.InventoryID = PUR.InventoryID

LEFT JOIN ArchitectureInformation as AI

ON I.InventoryID = AI.InventoryID

LEFT JOIN InventoryDate AS BSale

ON I.InventoryID = BSale.InventoryID

AND BSale.RefCodeID IN (70, 71, 73)

WHERE PJ.ProjectName = 'Copperfield'

AND P.PhaseID IN (114, 119, 120)

AND Pur.PurchaserName NOT LIKE '%HRC%'

any and all help is appreciated

Thanks

Wade

Wade:

In addition to your queries what we also need is (1) sample source data and (2) what the target output should look like; It is rather like you have given us a gun without any munitions and without a target and said, "Shoot!"

You also need to give us the function definitions for the two table functions.

|||

Hi WadeG,

When I write code, I break it down to simpler levels. As I get the data I need, I add more of the code and until I have it the way I need it. The problem with this is the level of complication. Break it down, and either eliminate or add code that will better define your query. Not a solution, but a method that will lead to the solution.

dbmsql

|||

This query may fix your problem,

Code Snippet

SELECT B.BlockName AS Block,

I.Lot,

R.RefCodeName AS LotType,

I.SaleableFrontFootage As Frontage,

ISNULL(IA.Address + ', ','') + IA.CityName + ' ' + IA.ProvinceCode + ' ' + ISNULL(IA.PostalCode,'') AS Address,

AI.HouseStyle AS Style,

RC.RefCodeName AS Status,

PUR.PurchaserName as Builder

FROM Inventory AS I

JOIN Block AS B

INNER JOIN Phase AS P

INNER JOIN ProjectSub AS PS

INNER JOIN Project AS PJ

ON PS.ProjectID = PJ.ProjectID

ON P.ProjectSubID = PS.ProjectSubID

INNER JOIN PhaseSetup AS PSetup

ON P.PhaseID = PSetup.PhaseID

ON B.PhaseID = P.PhaseID

ON I.BlockID = B.BlockID

LEFT JOIN InventoryDate AS IStatus

ON I.InventoryID = IStatus.InventoryID

AND IStatus.RefCodeID IN (71, 73) -- Open, Spec, Sale

LEFT JOIN RefCode AS R

ON I.LotTypeRefCodeID = R.RefCodeID

LEFT JOIN dbo.BuilderSaleByInventory(NULL) AS BSale

ON I.InventoryID = BSale.InventoryID

LEFT JOIN dbo.InventoryAddressByInventoryID(NULL) IA

ON I.InventoryID = IA.InventoryID

LEFT JOIN dbo.PurchaserByInventory(NULL) AS PUR

ON I.InventoryID = PUR.InventoryID AND Pur.PurchaserName NOT LIKE '%HRC%'

LEFT JOIN ArchitectureInformation as AI

ON I.InventoryID = AI.InventoryID

LEFT JOIN RefCode AS RC

ON IStatus.RefCodeID = RC.RefCodeID

WHERE PJ.ProjectName = 'Copperfield' AND P.PhaseID IN (114, 119, 120)

|||

The problem (I think) is with this piece of code:

Code Snippet

(SELECT RC.RefCodeName AS Status

FROM InventoryDate as IDate

JOIN RefCode AS RC

ON IDate.RefCodeID = RC.RefCodeID AND IDate.RefCodeID IN (70, 71, 73)

WHERE IDate.InventoryID = I.InventoryID

AND IDate.InventoryDate = (SELECT MAX(InventoryDate)

FROM InventoryDate AS IDate2

WHERE IDate.InventoryID = IDate2.InventoryID)) AS 'Status',

What I need to do is be able to pick the latest (MAX) date transaction, I just can't seem to get it to work. There are several tables involved, I can try to post whatever info you need.

Bascally the query with out the above code (Just using RC.RafCodeName AS Status) works it's just returning too many results.

I hope that makes sense.

help with sql query

Lets assume i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

Lets once again assume,that there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Kris, you've specified returning only 2 of 10 rows from your employeeSUN table without specifying the defining criteria causing only those two rows to be returned. My initial thought is to define a UNION query to produce this result, but a little more information will be required.

select employee_id as ALLemployees from employeesIBM

union all

select employee_id as ALLemployees from employeesSUN

where <some condition exists>

|||

Hi Allen,

Consider that im not so thorough with sql , But sure your reply has helped me a lot.

Thanks

Kris

Help with SQL Query

Given the following two tables below, I need help in writing a query that would retreive only 3 or less distinct values of BDesc from tbB table for every row found in tbA.

for example:

I expect result to be:

Aid Bdesc
100 1st Desc for 100
100 2nd Desc for 100
100 3rd Desc for 100
200 1st Desc for 200
200 2nd Desc for 200
200 3rd Desc for 200
300 1st Desc for 300
300 2nd Desc for 300
300 3rd Desc for 300
400 1st Desc for 400
500 1st Desc for 500
500 3rd Desc for 500

The tables are:

use tempdb

go

set nocount on

if exists (select name from sysobjects where name = 'TbA')

Drop table TbA

Create Table TbA ( Aid int )

Insert into TbA values(100)

Insert into TbA values(200)

Insert into TbA values(300)

Insert into TbA values(400)

--select * from TbA

if exists (select name from sysobjects where name = 'TbB')

Drop table TbB

Create Table TbB ( Bid int , BDesc varchar(50) )

INSERT INTO TbB Values(100, '1st Desc for 100')

INSERT INTO TbB Values(100, '2nd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(100, '3rd Desc for 100')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(200, '2nd Desc for 200')

INSERT INTO TbB Values(200, '3rd Desc for 200')

INSERT INTO TbB Values(200, '4th Desc for 200')

INSERT INTO TbB Values(200, '1st Desc for 200')

INSERT INTO TbB Values(300, '1st Desc for 300')

INSERT INTO TbB Values(300, '2nd Desc for 300')

INSERT INTO TbB Values(300, '3rd Desc for 300')

INSERT INTO TbB Values(300, '4th Desc for 300')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(400, '1st Desc for 400')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '1st Desc for 500')

INSERT INTO TbB Values(500, '3rd Desc for 500')

--select * from TbB

Thanks for your help with this...

Here ya go

Code Snippet

selectdistinct TbB.*

from(selectdistinct aid from TbA)as TbA

innerjoin TbB

on TbA.Aid = TbB.Bid

and TbB.BDesc in

(selectdistincttop 3 BDesc from TbB where Bid = TbA.Aid orderby BDesc)

|||Thanks|||

How about if I wanted to get the result like this:

ColA ColB

100 1st Descfor 100, 2nd Descfor 100, 3rd Descfor 100

200 1st Descfor 200, 2nd Descfor 200, 3rd Descfor 200

300 1st Descfor 300, 2nd Descfor 300, 3rd Descfor 300

400 1st Descfor 400

500 1st Descfor 500, 3rd Descfor 500

|||hi, you can try using a udf

CREATE FUNCTION dbo.GetBDesc
(
@.AID int
)
RETURNS varchar(800)
AS
BEGIN
DECLARE @.BDesc varchar(100)
SET @.BDesc = ''
SELECT
@.BDesc = @.BDesc + BDesc + ','
FROM (SELECT DISTINCT TOP 3 * FROM TbB a WHERE a.BID = @.AID) b
WHERE BID = @.AID
ORDER BY
BDesc

IF @.BDesc <> '' SET @.BDesc = LEFT(@.BDesc, LEN(@.BDesc) - 1)

RETURN @.BDesc

END

GO

select *
, dbo.GetBDesc(AID)
from tba|||

If you use SQL Server 2005 you dont need a function...

Here the sample,

Code Snippet

Create Table #TableA(

Aid int );

Insert into #TableA values(100)

Insert into #TableA values(200)

Insert into #TableA values(300)

Insert into #TableA values(400)

Insert into #TableA values(500)

Create Table #TableB(

Bid int

,BDesc varchar(50)

)

INSERT INTO #TableB Values(100, '1st Desc for 100')

INSERT INTO #TableB Values(100, '2nd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(100, '3rd Desc for 100')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(200, '2nd Desc for 200')

INSERT INTO #TableB Values(200, '3rd Desc for 200')

INSERT INTO #TableB Values(200, '4th Desc for 200')

INSERT INTO #TableB Values(200, '1st Desc for 200')

INSERT INTO #TableB Values(300, '1st Desc for 300')

INSERT INTO #TableB Values(300, '2nd Desc for 300')

INSERT INTO #TableB Values(300, '3rd Desc for 300')

INSERT INTO #TableB Values(300, '4th Desc for 300')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(400, '1st Desc for 400')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '1st Desc for 500')

INSERT INTO #TableB Values(500, '3rd Desc for 500')

;With DistinctData

as

(

Select Distinct A.Aid,B.BDesc from #TableA A Join #TableB B On A.Aid =B.Bid

),

RowData

as

(

Select Aid,Bdesc,Row_Number() Over(Partition By Aid Order By BDesc) RowID From DistinctData

)

/*

Select

Aid,

BDesc

From

RowData

Where

RowID <=3

*/

Select Distinct

Aid

,Substring((Select ',' + BDesc as [text()] From RowData Sub Where Sub.Aid=Main.Aid And Sub.RowId<=3 For XML Path(''), Elements),2,8000) as Descs

From

RowData Main

|||MG,

How about these two queries (both require SQL 2005)

select a.aid, b.bdesc
from tbA a
cross apply
(select distinct top (3) bdesc from tbB b where b.bid = a.aid) b
;

select a.aid as ColA, stuff((select distinct top (3) ', ' + bdesc from tbB b where b.bid = a.aid order by 1 for xml path('')),1,2,'') as ColB
from tbA a
;

The second one puts them into a single column for you.

Rob|||I should have mentioned that this is for SQL 2000 and for an OLTP environment. The procedure processes approx. 20,000 rows and right now it's using cursor logic which is slowing things down, so I was looking for ways to use set based processing. The function idea is good, but again its going to be row by row processing.|||

The function approach should be a significant improvement over any cursor processing.

Is there something we're not understanding about what you want to accomplish?

|||

Hi Rhamille Golimlim,

There is an issue when using "order by" during an aggregate concatenation query.

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

http://support.microsoft.com/default.aspx/kb/287515

AMB

|||thanks for the tip hunchback. would it still show a different execution plan if we put the order by inside the subquery?|||

Hi Rhamille Golimlim,

If you put the "order by" clause inside the derived table, then how are you going to be sure that the result is sorted if the only way to asure a sorted resultset is using the "order by" clause in the statement that pull the data?. It is like sorting inside a view and not using "order by" clause when you pull from the view.

Concatenating row values in T-SQL

http://www.projectdmx.com/tsql/rowconcatenate.aspx

AMB

|||hi hunchback,

cool, would the xml path approach be the best work around for this scenario? or are there other alternatives or tsql hacks?

/rhamille

Help with SQL Query

Hello Everyone,

I need help writing this query. I have a table named "PMP" that stores up to 10 objective ID's:

Code Snippet

SELECT ObjectiveID1,ObjectiveID2,ObjectiveID3,ObjectiveID4,... ObjectiveID10

FROM PMP

WHERE PMPID = @.PMPID

I have a child table named "METRICS" that stores the details for each objective:

Code Snippet

SELECT ObjectiveID, TitleText, Description, KPIvalue

FROM METRICS

WHERE ObjectiveID IN (query the PMP table)

I need to write a query that will return all the Metrics that are used for a given PMPID. Any ideas how this can be done?

Thank You

My first suggestion is to correct a bad design mistake. You have a severly de-normalized table.

You most likely 'should' NOT have columns Objective1-Objective10.

You most likely would benefit from having a separate table for Objectives.

THEN issues such as this one would become MUCH easier to solve.

If you are using SQL 2005, you may find the UNPIVOT statement to be useful.

|||

Which version of SQL Server are you using?

AMB

|||

I am using SQL Server 2005 Express Edition

I understand why you would think this table is de-normalized, but it's the right structure for the solution. My organization allows a maximum of 10 objectives to be identified per request. I need to send my crystal report 1 record.

Can someone help me with the UNPIVOT query?

Thank You

|||

I figured it out:

SELECT * FROM COACHING.METRICS

WHERE OBJECTIVEID IN (

SELECT OBJECTIVEID

FROM

(SELECT RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID

FROM COACHING.PMPFORMS

WHERE PMPID = 271 ) OBJ

UNPIVOT

(OBJECTIVEID FOR PMPID IN (RESULT1_OBJECTIVEID,

RESULT2_OBJECTIVEID,

RESULT3_OBJECTIVEID,

RESULT4_OBJECTIVEID,

RESULT5_OBJECTIVEID,

RESULT6_OBJECTIVEID,

RESULT7_OBJECTIVEID,

RESULT8_OBJECTIVEID,

RESULT9_OBJECTIVEID,

RESULT10_OBJECTIVEID)) VALS)

|||

Thanks Arnie Rowland and hunchback for your reponses

|||I'm glad we could point you in a direction that worked.sql

help with sql query

Lets assume i have ' employee_Id ' column in table employeesIBM , and 'employee_Id' column in employeesSUN.

Now,

Lets once again assume,that there are 4 records in employeesIBM.

and 10 records in employeesSUN.

*******What i want to achieve*****

i want to write a query which will display one column 'ALLemployees'

displaying 4 records of employeesIBM and 2 records in employeesSUN.

something like this >>>

employee_Id ******from employeesIBM table

1

2

3

4

employee_Id ******from employeesSUN table

10

11

want to write a query which will display something like this

ALLemployees

1

2

3

4

10

11

Please help me out with this.

Kris

Made another post about this at:

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

PS. Prolly better if you only post in one place. For questions of this sort I think the Transact-SQL forum would be better suited.

Ta.

Help with SQL query

Please bare with me in trying to get my point across, I'm new to SQL and
would appreciate the help so much.
I have two table Table1 and Table2. There is a one to many relationship
between Table1 and Table2. Table2 has a list of widgets, I need to create a
query that would show the records from Table1 along with it's related
records and all non related records from Table2. So if I have 5 widgets and
only two have related records in Table1 results should be
Table1ID(2) fkTable2 Table2(widgetname1)
Table1ID(2) fkTable2 Table2(widgetname2)
null null Table2(widgetname3)
null null Table2(widgetname4)
null null Table2(widgetname5)
When I filter the query on another Table1ID, I would need the same sort of
result for each Table1ID.try this
SELECT TABLE1.COL1,TABLE1.COL2,TABLE2.COL1,TABLE2.COL2 FROM TABLE1 RIGHT
OUTER JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL1
--
Regards
R.D
--Knowledge gets doubled when shared
"Tim Harvey" wrote:

> Please bare with me in trying to get my point across, I'm new to SQL and
> would appreciate the help so much.
> I have two table Table1 and Table2. There is a one to many relationship
> between Table1 and Table2. Table2 has a list of widgets, I need to create
a
> query that would show the records from Table1 along with it's related
> records and all non related records from Table2. So if I have 5 widgets an
d
> only two have related records in Table1 results should be
> Table1ID(2) fkTable2 Table2(widgetname1)
> Table1ID(2) fkTable2 Table2(widgetname2)
> null null Table2(widgetname3)
> null null Table2(widgetname4)
> null null Table2(widgetname5)
> When I filter the query on another Table1ID, I would need the same sort of
> result for each Table1ID.
>
>
>

Help with SQL Query

I have a financial database which tracks charges and payments form clients.
I need to create a query which returns only those months when a client
hasn't paid in full and the amount still owing. So, if the table looks like
this:
client date(mm/dd/yyyy) charge
payment
smith 12/01/2006 400.00
smith 12/23/2006
250.00
smith 12/28/2005
50.00
smith 01/01/2006 400.00
smith 01/13/2006
400.00
jones 02/01/2006 400.00
jones 02/05/2006
350.00
the resulting data would look like this:
smith 12/2005 100.00
jones 02/2006 50.00
thanks.
steve.Since you didn't provide DDL or insert scripts this is what I cooked up
I don't think the data for smith is correct, and what is the full
amount? 800?
create table blah (client varchar(50),date datetime, charge decimal
(10,2))
insert into blah
select 'smith', '12/01/2006', 400.00 union all
select 'smith', '12/23/2006', 250.00 union all
select 'smith' , '12/28/2005', 50.00 union all
select 'smith' , '01/01/2006', 400.00 union all
select 'smith' , '01/13/2006', 400.00 union all
select 'jones' , '02/01/2006', 400.00 union all
select 'jones' , '02/05/2006', 350.00
select client,800 -sum(charge) ,datepart(m,date) ,datepart(yyyy,date)
from blah
group by client,datepart(m,date),datepart(yyyy,da
te)
having sum(charge) < 800
http://sqlservercode.blogspot.com/|||SELECT
CLIENT,CONVERT(VARCHAR,DATEPART(mm,getda
te()))+'/'+CONVERT(VARCHAR,DATEPART(
year,getdate())) AS MONTHYEAR, CHARGE
WHERE CHARGE <= 100
Lemme me know if this is what your are looking for...
Thanks,
Sree
"molsonexpert" wrote:

> I have a financial database which tracks charges and payments form clients
.
> I need to create a query which returns only those months when a client
> hasn't paid in full and the amount still owing. So, if the table looks lik
e
> this:
> client date(mm/dd/yyyy) charge
> payment
> smith 12/01/2006 400.00
> smith 12/23/2006
> 250.00
> smith 12/28/2005
> 50.00
> smith 01/01/2006 400.00
> smith 01/13/2006
> 400.00
> jones 02/01/2006 400.00
> jones 02/05/2006
> 350.00
> the resulting data would look like this:
> smith 12/2005 100.00
> jones 02/2006 50.00
> thanks.
> steve.
>
>|||select client
, max(date) as LastActivityDate
-- Assuming the last date soemthign happened is the one you want to show
, sum(charge-payment) as Balance
-- above can also be:
--, Sum(charge) - sum(payment) as Balance
from MyPaymentsTable
group by client
having sum(charge-payment) <> 0
-- the above returns anyone with a credit or debit balance
-- the below returns only clients who owe money
-- or having sum(charge-payment) > 0
"molsonexpert" <imdrunk@.work.ca> wrote in message
news:%23bQ2OayKGHA.952@.TK2MSFTNGP10.phx.gbl...
> I have a financial database which tracks charges and payments form
clients.
> I need to create a query which returns only those months when a client
> hasn't paid in full and the amount still owing. So, if the table looks
like
> this:
> client date(mm/dd/yyyy) charge
> payment
> smith 12/01/2006 400.00
> smith 12/23/2006
> 250.00
> smith 12/28/2005
> 50.00
> smith 01/01/2006 400.00
> smith 01/13/2006
> 400.00
> jones 02/01/2006 400.00
> jones 02/05/2006
> 350.00
> the resulting data would look like this:
> smith 12/2005 100.00
> jones 02/2006 50.00
> thanks.
> steve.
>|||-- This return months where the clients
-- charges exceed the payments. I'm
-- assuming that in your test data, the two entries for
-- 'smith' in december should be for 2005 not 2006
SELECT Client,
DATEPART(year,thedate),
DATEPART(month,thedate),
SUM(COALESCE(Charge,0)-COALESCE(Payment,0))
FROM ChargesAndPayments
GROUP BY client,
DATEPART(year,thedate),
DATEPART(month,thedate)
HAVING SUM(COALESCE(Charge,0)-COALESCE(Payment,0))>0|||I should have included the isnull or coalesce around the charge and payment
fields as markc600 did below.
Without the isnull or coalesce, you will get back nulls instead of the
correct values.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23x5XzwyKGHA.3260@.TK2MSFTNGP11.phx.gbl...
> select client
> , max(date) as LastActivityDate
> -- Assuming the last date soemthign happened is the one you want to show
> , sum(charge-payment) as Balance
> -- above can also be:
> --, Sum(charge) - sum(payment) as Balance
> from MyPaymentsTable
> group by client
> having sum(charge-payment) <> 0
> -- the above returns anyone with a credit or debit balance
> -- the below returns only clients who owe money
> -- or having sum(charge-payment) > 0
> "molsonexpert" <imdrunk@.work.ca> wrote in message
> news:%23bQ2OayKGHA.952@.TK2MSFTNGP10.phx.gbl...
> clients.
> like
>

Help with SQL query

I have a ContactTypes table as follows:-
ContactTypeId int PK
ContactType varchar(50)
I have several different types of contact types (i.e. Invoice,
Business, Manager, Home, etc).
I have a Client table as follows:
ClienttId int PK
ClientName varchar(50)
I have a ContactAddresses table as follows:-
ContactAddressId int PK
ClientId
ContactTypeId
Address1
Address2
Town
Postcode
Can anyone tell me how i can write an SQL query to fetch all the
different types of contacts for a client, regardless if they exist or
not?
The query should return something like for a selected Client:-
Contact Type Address1 Address2 Town PostCode
Invoice 123 somewhere there AB12 C12
Business 456 overthere here CD34 E21
Manager
Home 987 here where D21 S12
So far i have come up with the following but it requires separate SQL
statements for each column. Can anyone recommend a way around this?
SELECT ContactType
,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
CA.ContactTypeId = CT.ContactTypeId)
,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
AND CA.ContactTypeId = CT.ContactTypeId)
FROM ContactTypes CT
Thanks in advance for any suggestions.
DanI think you want a cross join to get all possible combinations of client and
contact type, and an outer join to get the addresses that exist, while still
keeping the contact type/client combinations that do not.
select c.clientname
, b.ContactType
, a.Address1
, a.Address2
, a.Town
, a.PostCode
from ContactTypeId b
cross join Client c
left outer join ContactAddresses a
on c.ClienttId = a.ClienttId
and c.ContactTypeID = b.ContactTypeID
<dan_williams@.newcross-nursing.com> wrote in message
news:1148503334.127146.20190@.i39g2000cwa.googlegroups.com...
> I have a ContactTypes table as follows:-
> ContactTypeId int PK
> ContactType varchar(50)
> I have several different types of contact types (i.e. Invoice,
> Business, Manager, Home, etc).
> I have a Client table as follows:
> ClienttId int PK
> ClientName varchar(50)
> I have a ContactAddresses table as follows:-
> ContactAddressId int PK
> ClientId
> ContactTypeId
> Address1
> Address2
> Town
> Postcode
>
> Can anyone tell me how i can write an SQL query to fetch all the
> different types of contacts for a client, regardless if they exist or
> not?
> The query should return something like for a selected Client:-
> Contact Type Address1 Address2 Town PostCode
> Invoice 123 somewhere there AB12 C12
> Business 456 overthere here CD34 E21
> Manager
> Home 987 here where D21 S12
> So far i have come up with the following but it requires separate SQL
> statements for each column. Can anyone recommend a way around this?
> SELECT ContactType
> ,(SELECT Address1 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Address2 FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT Town FROM ContactAddresses CA WHERE ClientId = @.clientId AND
> CA.ContactTypeId = CT.ContactTypeId)
> ,(SELECT PostCode FROM ContactAddresses CA WHERE ClientId = @.clientId
> AND CA.ContactTypeId = CT.ContactTypeId)
> FROM ContactTypes CT
> Thanks in advance for any suggestions.
> Dan
>