Sql Comparing two tables: February 2012

Saturday, February 25, 2012

complex query in sp

I'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
Background:
1. The innermost query (using the dynamic statements) will run fine on its own.
2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.

Here's the code:

CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
@.column nvarchar(100),
@.value smallint
AS
DECLARE @.SelectString nvarchar(500)

SET @.SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
'FROM dbo.v_maindata ' +
'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @.column + ' = @.value ' +
'ORDER BY dateOccured DESC) DERIVEDTBL ' +
'GROUP BY thedate ' +
'ORDER BY thedate DESC) DERIVEDTBL'

EXEC sp_executesql @.SelectString, N'@.column nvarchar(100), @.value smallint', @.column, @.value
GO

----------------------
Owen Eustice
MNC-I Webmaster
Victory Base SouthI'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
Background:
1. The innermost query (using the dynamic statements) will run fine on its own.
2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.

Here's the code:

CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
@.column nvarchar(100),
@.value smallint
AS
DECLARE @.SelectString nvarchar(500)

SET @.SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
'FROM dbo.v_maindata ' +
'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @.column + ' = @.value ' +
'ORDER BY dateOccured DESC) DERIVEDTBL ' +
'GROUP BY thedate ' +
'ORDER BY thedate DESC) DERIVEDTBL'

EXEC sp_executesql @.SelectString, N'@.column nvarchar(100), @.value smallint', @.column, @.value
GO

----------------------
Owen Eustice
MNC-I Webmaster
Victory Base South
The problem is that the variable @.SelectString is too short for the SELECT statement you assigning to it so your SQL is being truncated. Use this declaration instead:

DECLARE @.SelectString nvarchar(1000)|||The task is only as difficult as you make it, I guess.

Thanks!|||I can personally assure you that you are at least the second person to make this mistake...|||I've never made such a misteak :eek: in my life, but I've read about them in this book once ;)

On a slightly more serious note, if you don't run into a wall at full speed every now and then, you forget how good it feels when you quit!

-PatP|||I'm supposed to quit? Nobody ever told me that part.

OUCH!

Complex query I need help with.

CREATE TABLE test (stk_num varchar(3), avg_num real, import_dt smalldatetime
)
INSERT INTO test values('aaa',27.44,'1/23/2006')
INSERT INTO test values('aaa',25.00,'1/30/2006')
INSERT INTO test values('aaa',1.76,'2/6/2006')
INSERT INTO test values('bbb',2.45,'1/23/2006')
INSERT INTO test values('bbb',3.98,'1/30/2006')
INSERT INTO test values('bbb',11.99,'2/6/2006')
INSERT INTO test values('ccc',0.00,'1/23/2006')
INSERT INTO test values('ccc',0.00,'1/30/2006')
INSERT INTO test values('ccc',4.11,'2/6/2006')
INSERT INTO test values('ddd',1.87,'1/23/2006')
INSERT INTO test values('ddd',3.87,'1/30/2006')
INSERT INTO test values('ddd',0.0,'2/6/2006')
INSERT INTO test values('eee',0.00,'1/23/2006')
INSERT INTO test values('eee',0.00,'1/30/2006')
INSERT INTO test values('eee',0.00,'2/6/2006')
INSERT INTO test values('fff',57.89,'1/23/2006')
INSERT INTO test values('fff',9.80,'1/30/2006')
INSERT INTO test values('fff',10.15,'2/6/2006')
INSERT INTO test values('ggg',22.09,'1/23/2006')
INSERT INTO test values('ggg',2.44,'1/30/2006')
INSERT INTO test values('ggg',17.82,'2/6/2006')
I have a table that contains the stock # and avg # and import date.
I need to return all the records with the same stock numbers that have a +
or - >= 20% change between their avg numbers but only for the last two impor
t
dates.
So for the info given above I need the output to look like this:
Stk_num avg_num import_dt
aaa 25.00 1/30/2006
aaa 1.76 2/6/2006
bbb 3.98 1/30/2006
bbb 11.99 2/6/2006
ccc 0.00 1/30/2006
ccc 4.11 2/6/2006
ddd 3.87 1/30/2006
ddd 0.00 2/6/2006
ggg 2.44 1/30/2006
ggg 17.82 2/6/2006
TIAPlease chaec if this works for you and let me know.
Thanks!
-- BEGIN SCRIPT
select a.stk_num
, a.avg_num
, a.import_dt
from (
select t1.stk_num
, t1.avg_num
, t1.import_dt
from dbo.test t1
join ( SELECT rank
, stk_num
, import_dt import_dt
FROM ( SELECT T1.stk_num
, T1.import_dt
, (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE
T1.stk_num = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
FROM dbo.test T1) AS X
where rank < 3
) t2
on t1.stk_num = t2.stk_num
and
t1.import_dt = t2.import_dt
) a
inner join
(
select stk_num
from
(
SELECT stk_num
, MAX(CASE rank WHEN 2 THEN import_dt ELSE NULL END) import_dt1
, MAX(CASE rank WHEN 2 THEN avg_num ELSE NULL END) avg_num1
, MAX(CASE rank WHEN 1 THEN import_dt ELSE NULL END) import_dt2
, MAX(CASE rank WHEN 1 THEN avg_num ELSE NULL END) avg_num2
FROM ( SELECT T1.stk_num
, T1.avg_num
, T1.import_dt
, (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE T1.stk_num
= T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
FROM dbo.test T1) AS X
where rank < 3
group by stk_num
) t
where avg_num2 > ((avg_num1*0.2)+avg_num1)
or
(avg_num2 < (avg_num1-(avg_num1*0.2)))
) b
on b.stk_num = a.stk_num
-- END SCRIPT
"Chesster" wrote:

> CREATE TABLE test (stk_num varchar(3), avg_num real, import_dt smalldateti
me)
> INSERT INTO test values('aaa',27.44,'1/23/2006')
> INSERT INTO test values('aaa',25.00,'1/30/2006')
> INSERT INTO test values('aaa',1.76,'2/6/2006')
> INSERT INTO test values('bbb',2.45,'1/23/2006')
> INSERT INTO test values('bbb',3.98,'1/30/2006')
> INSERT INTO test values('bbb',11.99,'2/6/2006')
> INSERT INTO test values('ccc',0.00,'1/23/2006')
> INSERT INTO test values('ccc',0.00,'1/30/2006')
> INSERT INTO test values('ccc',4.11,'2/6/2006')
> INSERT INTO test values('ddd',1.87,'1/23/2006')
> INSERT INTO test values('ddd',3.87,'1/30/2006')
> INSERT INTO test values('ddd',0.0,'2/6/2006')
> INSERT INTO test values('eee',0.00,'1/23/2006')
> INSERT INTO test values('eee',0.00,'1/30/2006')
> INSERT INTO test values('eee',0.00,'2/6/2006')
> INSERT INTO test values('fff',57.89,'1/23/2006')
> INSERT INTO test values('fff',9.80,'1/30/2006')
> INSERT INTO test values('fff',10.15,'2/6/2006')
> INSERT INTO test values('ggg',22.09,'1/23/2006')
> INSERT INTO test values('ggg',2.44,'1/30/2006')
> INSERT INTO test values('ggg',17.82,'2/6/2006')
>
> I have a table that contains the stock # and avg # and import date.
> I need to return all the records with the same stock numbers that have a +
> or - >= 20% change between their avg numbers but only for the last two imp
ort
> dates.
> So for the info given above I need the output to look like this:
> Stk_num avg_num import_dt
> aaa 25.00 1/30/2006
> aaa 1.76 2/6/2006
> bbb 3.98 1/30/2006
> bbb 11.99 2/6/2006
> ccc 0.00 1/30/2006
> ccc 4.11 2/6/2006
> ddd 3.87 1/30/2006
> ddd 0.00 2/6/2006
> ggg 2.44 1/30/2006
> ggg 17.82 2/6/2006
>
> TIA
>|||Chesster,
it's easy to accomplish with a join:
select firsts.*, seconds.import_dt dt2, seconds.avg_num num2
from
(select * from #test t1 where not exists(
select 1 from #test t2 where t1.stk_num = t2.stk_num
and t1.import_dt < t2.import_dt)) firsts,
(select * from #test t1 where(
select count(*) from #test t2 where t1.stk_num = t2.stk_num
and t1.import_dt < t2.import_dt
) = 1) seconds
where firsts.stk_num = seconds.stk_num
and firsts.avg_num NOT between seconds.avg_num*0.8 and
seconds.avg_num*1.2
it'll give you 5 rows, not 10 as you requested. To get 10 rows, use
cross join:
select stk_num,
case when n=1 then import_dt else dt2 end import_dt,
case when n=1 then avg_num else num2 end avg_num
from(
select firsts.*, seconds.import_dt dt2, seconds.avg_num num2
from
(select * from #test t1 where not exists(
select 1 from #test t2 where t1.stk_num = t2.stk_num
and t1.import_dt < t2.import_dt)) firsts,
(select * from #test t1 where(
select count(*) from #test t2 where t1.stk_num = t2.stk_num
and t1.import_dt < t2.import_dt
) = 1) seconds
where firsts.stk_num = seconds.stk_num
and firsts.avg_num NOT between seconds.avg_num*0.8 and
seconds.avg_num*1.2
)t1,
(select 1 n union all select 2) t2|||Today and maybe tomorrow I will not be at work to try this because my
daughter is sick. When I get back to work I will try it and let you know.
Thanks!
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Please chaec if this works for you and let me know.
> Thanks!
> -- BEGIN SCRIPT
> select a.stk_num
> , a.avg_num
> , a.import_dt
> from (
> select t1.stk_num
> , t1.avg_num
> , t1.import_dt
> from dbo.test t1
> join ( SELECT rank
> , stk_num
> , import_dt import_dt
> FROM ( SELECT T1.stk_num
> , T1.import_dt
> , (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE
> T1.stk_num = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
> FROM dbo.test T1) AS X
> where rank < 3
> ) t2
> on t1.stk_num = t2.stk_num
> and
> t1.import_dt = t2.import_dt
> ) a
> inner join
> (
> select stk_num
> from
> (
> SELECT stk_num
> , MAX(CASE rank WHEN 2 THEN import_dt ELSE NULL END) import_dt1
> , MAX(CASE rank WHEN 2 THEN avg_num ELSE NULL END) avg_num1
> , MAX(CASE rank WHEN 1 THEN import_dt ELSE NULL END) import_dt2
> , MAX(CASE rank WHEN 1 THEN avg_num ELSE NULL END) avg_num2
> FROM ( SELECT T1.stk_num
> , T1.avg_num
> , T1.import_dt
> , (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE T1.stk_n
um
> = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
> FROM dbo.test T1) AS X
> where rank < 3
> group by stk_num
> ) t
> where avg_num2 > ((avg_num1*0.2)+avg_num1)
> or
> (avg_num2 < (avg_num1-(avg_num1*0.2)))
> ) b
> on b.stk_num = a.stk_num
> -- END SCRIPT
> "Chesster" wrote:
>|||Both queries appear to have worked.
Thanks!
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Please chaec if this works for you and let me know.
> Thanks!
> -- BEGIN SCRIPT
> select a.stk_num
> , a.avg_num
> , a.import_dt
> from (
> select t1.stk_num
> , t1.avg_num
> , t1.import_dt
> from dbo.test t1
> join ( SELECT rank
> , stk_num
> , import_dt import_dt
> FROM ( SELECT T1.stk_num
> , T1.import_dt
> , (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE
> T1.stk_num = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
> FROM dbo.test T1) AS X
> where rank < 3
> ) t2
> on t1.stk_num = t2.stk_num
> and
> t1.import_dt = t2.import_dt
> ) a
> inner join
> (
> select stk_num
> from
> (
> SELECT stk_num
> , MAX(CASE rank WHEN 2 THEN import_dt ELSE NULL END) import_dt1
> , MAX(CASE rank WHEN 2 THEN avg_num ELSE NULL END) avg_num1
> , MAX(CASE rank WHEN 1 THEN import_dt ELSE NULL END) import_dt2
> , MAX(CASE rank WHEN 1 THEN avg_num ELSE NULL END) avg_num2
> FROM ( SELECT T1.stk_num
> , T1.avg_num
> , T1.import_dt
> , (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE T1.stk_n
um
> = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
> FROM dbo.test T1) AS X
> where rank < 3
> group by stk_num
> ) t
> where avg_num2 > ((avg_num1*0.2)+avg_num1)
> or
> (avg_num2 < (avg_num1-(avg_num1*0.2)))
> ) b
> on b.stk_num = a.stk_num
> -- END SCRIPT
> "Chesster" wrote:
>

Complex Query Help.. Please :)

Hello group,

First time post..

I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..

Here are the tables.

evaluations - holds a record for each evauation that is created: evalID, evalName

fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)

fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average

fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April... or whatever they like) : optionID, optionText, fieldID

submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID

eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data

Here's the problem..
I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.

an example of a posted evaluation will create:
1 row in the submitted table..
and a row in the eval_data table for each field submitted..as well as

with any luck this explanation makes sense and the problem will be easy to solve..

:rolleyes:

any replys are greatly appreciated..

Thanks
Willok.. here is some clarification..

I started thinking about the first post and decided that much of that information was probably not needed..

Here is the sql i'm workin with so far..

SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText
FROM eval_eval_data AS d
INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON f.intField_ID = d.intField_ID
INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
WHERE fe.intAverage = '1'
AND c.intOption_ID = '26'
Group By f.chEvalFieldText

my question boils down to this..
regarding the rows in the data table..

can i group by a value in a liked table (f),
and average values in the data table
while limiting by values in teh data table as well.

anybody?

Am i on the wrong track?

Am i totally lost?

have i lost everybody else??

thanks
ws
-|||You are right on track, good job.
:D|||me again...

I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..

here is some example data from the data table..

intSub intField_ID chData
1 1 1
1 2 1
1 3 4
1 4 2
1 26 27
2 1 1
2 2 2
2 3 3
2 4 5
2 26 28

intField_ID is a key that relates to fields in the evaluation
chData is the value that was submitted and intSubmission indicates which submission the data came from.
In this case the field_ids 1-4 ratings 1- 5 that will be averaged
the average will be grouped by the field ID no biggie.. here's where i'm stumped.
I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.

make sense?
there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
for the data above .. i need to

Average the values for the indicated fields grouped by the field id..
but only where the value for fieldID = 26 are equal..

any help is greatly appreciated.
If you need more info.. please let me know..

thanks
will

Complex query help needed....

I have been working with SQL for a while...but I am stumped. I can not
seem to get my arms around this query...can anyone help...

Here it is:

Table = 12 rows, 4 columns (id, name, amount, date)
row1 = 771, "steve", $50.00, "01/01/2005"
row2 = 772, "steve", $100.00, "01/11/2005"
row3 = 773, "steve", $200.00, "01/11/2005"
row4 = 774, "dave", $300.00, "01/01/2005"
row5 = 775, "dave", $400.00, "01/12/2005"
row6 = 776, "dave", $500.00, "01/12/2005"
row7 = 777, "mike", $600.00, "01/01/2005"
row8 = 778, "mike", $700.00, "01/13/2005"
row9 = 789, "mike", $800.00, "01/13/2005"
row10 = 790, "chuck", $900.00, "01/01/2005"
row11 = 791, "chuck", $950.00, "01/14/2005"
row12 = 792, "chuck", $975.00, "01/14/2005"

I need a query that returns (1) ONE ROW PER NAME based on the MOST
RECENT DATE and returns the correct corresponding information. The
keys to this question are the following:
1. The query needs to return ONE ROW PER NAME
2. I do not want to use a First() function (in MS Access)
3. Even though (2) two DATE for each NAME are the same, i want the
query to return one record and whatever record it returns, i have to be
able to have all the corresponding records (id, name, amount, and
date). I recorgnize that the DATE is ambiguous and that SQL may return
one or the other...but that is ok.
4. The return set should include (4) four rows

Any help with this would be thoroughly appreciated...Assuming that your id column is unique and does not allow NULL values,
the SQL below should return a single record per name, using the record
with the latest date and in this case if the dates are identical then
it will return the record with the greater id.

SELECT T1.id, T1.name, T1.amount, T1.date
FROM Test T1
LEFT OUTER JOIN Test T2 ON T2.name = T1.name
AND ((T2.date > T1.date) OR (T2.date = T1.date
AND T2.id > T1.id))
WHERE T2.id IS NULL

-Tom.

Complex Query help needed fast!

The goal is to take the low and high values out of each record, then get the
average of the remaining fields. For ex, ID1 has five values, remove 2 and
100 then add (40+20+10)/3...then do this for each record. The number of
values in the five fields can vary as shown below.
I have a table that looks like this:
ID F1 F2 F3 F4 F5 field names
1 100 40 20 2 10 Values
2 4 140 10 42
3 10 189 22
4 20 10 24 332 3
Thanks in advance for this query.One way is to use the unpivot operator. It's tested in MSSQL2005.
select id, (sum(f) - max(f) - min(f))/(count(*) - 2.0) avg
from (select id, f from test unpivot (f for t in (f1,f2,f3,f4,f5)) p) t
group by id
"KT" <ktdev@.hotmail.com> wrote in message
news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...
> The goal is to take the low and high values out of each record, then get
> the
> average of the remaining fields. For ex, ID1 has five values, remove 2
> and
> 100 then add (40+20+10)/3...then do this for each record. The number of
> values in the five fields can vary as shown below.
>
>
> I have a table that looks like this:
>
> ID F1 F2 F3 F4 F5 field names
> 1 100 40 20 2 10 Values
> 2 4 140 10 42
> 3 10 189 22
>
> 4 20 10 24 332 3
>
>
> Thanks in advance for this query.|||See if this helps. I'm assuming the blank column values contain NULL.
If they contain something else, you'll have to change F1 through F5 to
CASE WHEN F1 = <whatever the blank is> THEN NULL ELSE F1 END,
and so on.
select
ID, (SUM(Fval)-MAX(Fval)-MIN(Fval))/(COUNT(Fval)-2) as trimMean
from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
group by ID
go
Steve Kass
Drew University
KT wrote:

>The goal is to take the low and high values out of each record, then get th
e
>average of the remaining fields. For ex, ID1 has five values, remove 2 and
>100 then add (40+20+10)/3...then do this for each record. The number of
>values in the five fields can vary as shown below.
>
>
>I have a table that looks like this:
>
>ID F1 F2 F3 F4 F5 field names
>1 100 40 20 2 10 Values
>2 4 140 10 42
>3 10 189 22
>
>4 20 10 24 332 3
>
>
>Thanks in advance for this query.
>
>|||My solutions :) For the 2005 version, I like my solution for readability as
it is pretty straightforward. If you aren't using 2005, then Kass's is
really slick. Don't get me wrong, the person named mason's solution is
slicker than mine (and shorter) but I think that my solution is probably
more understandable later in the process. Either way, one of these'll do
you :)
Note that I use integer math, while Steve's uses floats. So my answers are
rounded off , while his arent
--create the table
create table looksLikeThis
(
id int primary key,
f1 int,
f2 int,
f3 int,
f4 int,
f5 int
)
insert into looksLikeThis
select 1, 100, 40, 20, 2, 10
union all
select 2, 4, 140, 10, 42, null
union all
select 3, 10, 189, 22,null, null
union all
select 4, 20, 10, 24, 332, 3
go
---
-- In 2005
---
--so much easier to do with the partition statement and the CTE. Allows the
first two
--views to be rolled up into one query pretty easy:
with shouldLookLikeThis as
(select *, row_number() over (partition by id order by value,uniqueifier )
as ordering
from (
select id, f1 as value, 1 as uniqueifier
from looksLikeThis
union all
select id, f2, 2
from looksLikeThis
union all
select id, f3, 3
from looksLikeThis
union all
select id, f4, 4
from looksLikeThis
union all
select id, f5, 5
from looksLikeThis ) as denorm
where value is not null)
select id, avg(value) as averageValue
from shouldLookLikeThis
where ordering not in (select max(ordering)
from shouldLookLikeThis s2
where s2.id = shouldLookLikeThis.id)
and ordering not in (select min(ordering)
from shouldLookLikeThis s2
where s2.id = shouldLookLikeThis.id)
group by id
-- Using 2000 and recent versions
--
--normalize the table, including some value to make sure of uniqueness (very
important to the query
--so you don't lose rows if the min or max have multiple values
create view shouldLookLikeThis
as
select *
from (
select id, f1 as value, 1 as uniqueifier
from looksLikeThis
union all
select id, f2, 2
from looksLikeThis
union all
select id, f3, 3
from looksLikeThis
union all
select id, f4, 4
from looksLikeThis
union all
select id, f5, 5
from looksLikeThis ) as denorm
where value is not null
go
--this view adds an ordering to the output (this is what the uniqueifier was
about
create view includeOrder
as
select id, value, (select count(*)
from shouldLookLikeThis s2
where shouldLookLikeThis.id = s2.id
and (shouldLookLikeThis.value <= s2.value
or (shouldLookLikeThis.value = s2.value
and shouldLookLikeThis.uniqueifier <=
s2.uniqueifier))
) as ordering
from shouldLookLikeThis
GO
--then just exclude the min and max orderwise
select id, avg(value) as averageValue
from includeOrder
where ordering not in (select max(ordering)
from includeOrder s2
where s2.id = includeOrder.id)
and ordering not in (select min(ordering)
from includeOrder s2
where s2.id = includeOrder.id)
group by id
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"KT" <ktdev@.hotmail.com> wrote in message
news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...
> The goal is to take the low and high values out of each record, then get
> the
> average of the remaining fields. For ex, ID1 has five values, remove 2
> and
> 100 then add (40+20+10)/3...then do this for each record. The number of
> values in the five fields can vary as shown below.
>
>
> I have a table that looks like this:
>
> ID F1 F2 F3 F4 F5 field names
> 1 100 40 20 2 10 Values
> 2 4 140 10 42
> 3 10 189 22
>
> 4 20 10 24 332 3
>
>
> Thanks in advance for this query.
>|||I'm still learning MSSQL2005 features and the pivot/unpivot operators are an
interesting implementation. The idea is to transpose the rows into a column
so that we can use aggregate functions to calculate the average. If
"unpivot" is too unconventional, we can always use a case-based or a "union
all" derived table to achieve the same effect. I should add HAVING
COUNT(*)>2 to take care of the devide-by-0 situation.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eLIh84CPGHA.420@.tk2msftngp13.phx.gbl...
> My solutions :) For the 2005 version, I like my solution for readability
> as it is pretty straightforward. If you aren't using 2005, then Kass's is
> really slick. Don't get me wrong, the person named mason's solution is
> slicker than mine (and shorter) but I think that my solution is probably
> more understandable later in the process. Either way, one of these'll do
> you :)
> Note that I use integer math, while Steve's uses floats. So my answers
> are rounded off , while his arent
> --create the table
> create table looksLikeThis
> (
> id int primary key,
> f1 int,
> f2 int,
> f3 int,
> f4 int,
> f5 int
> )
> insert into looksLikeThis
> select 1, 100, 40, 20, 2, 10
> union all
> select 2, 4, 140, 10, 42, null
> union all
> select 3, 10, 189, 22,null, null
> union all
> select 4, 20, 10, 24, 332, 3
> go
> ---
> -- In 2005
> ---
> --so much easier to do with the partition statement and the CTE. Allows
> the first two
> --views to be rolled up into one query pretty easy:
> with shouldLookLikeThis as
> (select *, row_number() over (partition by id order by value,uniqueifier )
> as ordering
> from (
> select id, f1 as value, 1 as uniqueifier
> from looksLikeThis
> union all
> select id, f2, 2
> from looksLikeThis
> union all
> select id, f3, 3
> from looksLikeThis
> union all
> select id, f4, 4
> from looksLikeThis
> union all
> select id, f5, 5
> from looksLikeThis ) as denorm
> where value is not null)
> select id, avg(value) as averageValue
> from shouldLookLikeThis
> where ordering not in (select max(ordering)
> from shouldLookLikeThis s2
> where s2.id = shouldLookLikeThis.id)
> and ordering not in (select min(ordering)
> from shouldLookLikeThis s2
> where s2.id = shouldLookLikeThis.id)
> group by id
> --
> -- Using 2000 and recent versions
> --
> --normalize the table, including some value to make sure of uniqueness
> (very important to the query
> --so you don't lose rows if the min or max have multiple values
> create view shouldLookLikeThis
> as
> select *
> from (
> select id, f1 as value, 1 as uniqueifier
> from looksLikeThis
> union all
> select id, f2, 2
> from looksLikeThis
> union all
> select id, f3, 3
> from looksLikeThis
> union all
> select id, f4, 4
> from looksLikeThis
> union all
> select id, f5, 5
> from looksLikeThis ) as denorm
> where value is not null
> go
> --this view adds an ordering to the output (this is what the uniqueifier
> was about
> create view includeOrder
> as
> select id, value, (select count(*)
> from shouldLookLikeThis s2
> where shouldLookLikeThis.id = s2.id
> and (shouldLookLikeThis.value <= s2.value
> or (shouldLookLikeThis.value = s2.value
> and shouldLookLikeThis.uniqueifier <=
> s2.uniqueifier))
> ) as ordering
> from shouldLookLikeThis
> GO
> --then just exclude the min and max orderwise
> select id, avg(value) as averageValue
> from includeOrder
> where ordering not in (select max(ordering)
> from includeOrder s2
> where s2.id = includeOrder.id)
> and ordering not in (select min(ordering)
> from includeOrder s2
> where s2.id = includeOrder.id)
> group by id
> go
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "KT" <ktdev@.hotmail.com> wrote in message
> news:%23RBOH3BPGHA.456@.TK2MSFTNGP15.phx.gbl...|||No, the unpivot thing isn't what is unconventional. It is the:
sum(f) - max(f) - min(f))/(count(*) - 2.0)
part that makes the brain work harder than I cared to think about last
night. This is actually a more elagant solution too because it handles
duplicates easier.

> The idea is to transpose the rows into a column so that we can use
> aggregate functions to calculate the average
This is because SQL works well with rows, not vectors, particularly not of
variable length like this. This is part of what the Basically all we are
doing is rotating the set to be a SQL table in first normal form which then
makes it natural.
I know might go with mason's solution over mine, but that is so not easy to
admit :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:et31s%23GPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> I'm still learning MSSQL2005 features and the pivot/unpivot operators are
> an interesting implementation. The idea is to transpose the rows into a
> column so that we can use aggregate functions to calculate the average. If
> "unpivot" is too unconventional, we can always use a case-based or a
> "union all" derived table to achieve the same effect. I should add HAVING
> COUNT(*)>2 to take care of the devide-by-0 situation.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eLIh84CPGHA.420@.tk2msftngp13.phx.gbl...
>|||And 2.0 rather than 2 floats the whole thing. :p
In reality, it's often quantity over quality in people's eyes, and you are
right that readability may go with quantity in many cases. :p
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23$CSpDJPGHA.3360@.TK2MSFTNGP09.phx.gbl...
> No, the unpivot thing isn't what is unconventional. It is the:
> sum(f) - max(f) - min(f))/(count(*) - 2.0)
> part that makes the brain work harder than I cared to think about last
> night. This is actually a more elagant solution too because it handles
> duplicates easier.
>
> This is because SQL works well with rows, not vectors, particularly not of
> variable length like this. This is part of what the Basically all we are
> doing is rotating the set to be a SQL table in first normal form which
> then makes it natural.
> I know might go with mason's solution over mine, but that is so not easy
> to admit :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "mason" <masonliu@.msn.com> wrote in message
> news:et31s%23GPGHA.3460@.TK2MSFTNGP15.phx.gbl...|||Until performance gets involved, it is almost always the case :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"mason" <masonliu@.msn.com> wrote in message
news:uJEVfUJPGHA.3144@.TK2MSFTNGP11.phx.gbl...
> And 2.0 rather than 2 floats the whole thing. :p
> In reality, it's often quantity over quality in people's eyes, and you are
> right that readability may go with quantity in many cases. :p
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23$CSpDJPGHA.3360@.TK2MSFTNGP09.phx.gbl...
>|||This one worked perfectly...could you elaborate on what it is actually
doing.
Thanks Steve.
"Steve Kass" <skass@.drew.edu> wrote in message
news:Oz3EseCPGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See if this helps. I'm assuming the blank column values contain NULL.
> If they contain something else, you'll have to change F1 through F5 to
> CASE WHEN F1 = <whatever the blank is> THEN NULL ELSE F1 END,
> and so on.
> select
> ID, (SUM(Fval)-MAX(Fval)-MIN(Fval))/(COUNT(Fval)-2) as trimMean
> from (
> select
> ID,
> ColNum,
> case ColNum
> when 1 then F1
> when 2 then F2
> when 3 then F3
> when 4 then F4
> when 5 then F5
> end*1.0
> from yourTable
> cross join (
> select 1 union all select 2 union all
> select 3 union all select 4 union all select 5
> ) as F(ColNum)
> ) as T(ID,ColNum,Fval)
> group by ID
> go
> Steve Kass
> Drew University
> KT wrote:
>
the
and|||The cross join turns each single row such as
ID F1 F2 F3 F4 F5
--
101 13 24 35 46 NULL
into five separate rows like this:
ID, ColNum, Fval
--
101 1 13
101 2 24
101 3 35
101 4 46
101 5 NULL
Then it groups over ID values, finding the sum of
the Fval values, the number of those values that are
not blank (count(Fval)), and the largest and smallest
of the non-blank values. It gets the average you
need by adding the non-blank values, subtracting the
largest and smallest, and dividing by two less than
the number of non-blank values, all of this for each ID.
To understand it better, evaluate these queries separately
(there may be typos, but the idea is to look at it step by
step)
-- 1
select * from (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
-- 2
select * from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
order by ID, ColNum, Fval
-- 3
select
ID
COUNT(Fval) as count_fval,
SUM(Fval) as sum_fval,
MAX(Fval) as max_fval,
MIN(Fval) as min_fval
from (
select
ID,
ColNum,
case ColNum
when 1 then F1
when 2 then F2
when 3 then F3
when 4 then F4
when 5 then F5
end*1.0
from yourTable
cross join (
select 1 union all select 2 union all
select 3 union all select 4 union all select 5
) as F(ColNum)
) as T(ID,ColNum,Fval)
group by ID
-SK
KT wrote:

>This one worked perfectly...could you elaborate on what it is actually
>doing.
>Thanks Steve.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:Oz3EseCPGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>the
>
>and
>
>
>

complex query help needed

Hello all,

I'm stuck with this one:

Step 1
I have a stored proc like this

SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view1 on table1.id = view1.id
WHERE (bunch of criteria)

view one basically returns ToDo0 ... ToDo8

Everything works fine.

Step 2

As I have different ToDo's depending on who is logged on, there are view2 ... view6 returning the ToDo's accordingly. So I have

If @.grp = 1
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view1 on table1.id = view1.id
WHERE (bunch of criteria)
else if @.grp = 2
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view2 on table1.id = view2.id
WHERE (same bunch of criteria)
else ... (you get the point :)

works fine, though a little slow.

Step 3

To keep things maintainable (I'm not the only one working on that) and somewhat modular, I'd like to have something like

SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN just-take-the-right-view-please as Yep on table1.id = Yep.id
WHERE (bunch of criteria)

No Go ...

I tried:

1.
Create #ttbl_ToDo (...)
if @.grp = 1
Insert #ttbl_ToDo SELECT * from view1
else ...

and then joining on the #ttbl

I got timeouts (view1 ... 6 are quite expensive).

2.
Built a stored proc that already returns ToDo1 .. 8 for the right group but then I can't access the resultset from the calling sp.

3.
Try to build dynamic SQL with EXEC (expected timeouts there, too) - the SQL string exceeds maximum length (as things are a little more complex in reality)

I'm using MSSQL 7 (no option to migrate to 2000 an use functions yet :( )

Some more explanation why I'm not happy with Step2 (which at least is working):
1. the where clause is kind of complex an needs to be adopted from time to time. It's just a pain to do this 6 times.
2. Other developers should be able to add ToDo-groups without changing the query itself. Changing the part with the temp table wouldn't be perfect but acceptable, but changing the whole thing is not what we want.

Any hints are appreciated.

TIA, ChrisTo simplify you could use dynamic SQL, which as you stated cause timeouts, so I don't know if this will help.

DECLARE @.view varchar(35)

SELECT @.view = CASE
WHEN @.grp=1 THEN "view1"
WHEN @.grp=2 THEN "view2"
WHEN @.grp=3 THEN "view3"
WHEN @.grp=4 THEN "view4"
WHEN @.grp=5 THEN "view5"
ELSE "view6"
END

EXEC ("SELECT ... FROM... " + @.view + " WHERE...")

If your views only differed by a WHERE clause like this "@.grp" value then you could combine the views into one view and leave off the WHERE criteria until you use it.

CREATE VIEW view1 AS
SELECT .....
WHERE grp = 1

CREATE VIEW view2 AS
SELECT .....
WHERE grp = 2

etc.

Change to

CREATE VIEW view AS
SELECT .....

Then
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view on table1.id = view.id
WHERE (bunch of criteria)
AND view.grp = @.grp <-- Add here

This would not require dynamic SQL.

Also you can execute a stored procedure and have it's result go into a table.

INSERT table EXEC myProc

Note:
You have to watch out with views they are not a performance saver. If the view is a 6 table JOIN then when you execute it, it is a 6 table JOIN.

complex query help - count

Hi,
I am trying to add an aggregate function to the query below but I am not
able to get the intended results. I want to get the count to return total
downloads by each user but my query returns the total downloads by all users
.
Current Output
1,ttt,rrr,6/1/3005,30
2,ddd,jjj,5/31/2005,30
3,ppp,yyy,5/20/2005,30
Desired Output
1,ttt,rrr,6/1/3005,15
2,ddd,jjj,5/31/2005,5
3,ppp,yyy,5/20/2005,10
QUERY:
select distinct spl. [main_id],fname,lname,subscription_ends,
count(download_id)
from
main m,
subscribers spl,
downloads
where
m.main_id = spl.main_id
and
spl.confnum like 'T12%'
and
subscription_ends > = Getdate()
group by
spl.[main_id],
fname,lname,subscription_ends
order by
subscription_ends DESC
CREATE TABLE [dbo].[Main] (
[main_id] [int] IDENTITY (1, 1) NOT NULL ,
[fname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
CREATE TABLE [dbo].[subscribers] (
[main_id] [numeric](18, 0) NOT NULL ,
[confnum] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subscription_starts] [datetime] NOT NULL
)
GO
CREATE TABLE [dbo].[Downloads] (
[main_id] [numeric](18, 0) NOT NULL ,
[download_id] [numeric](18, 0) NOT NULL
)
GOMike,
Try this,
select distinct spl. [main_id],fname,lname,subscription_ends,
count(main_id)
from
main m,
subscribers spl,
downloads
where
m.main_id = spl.main_id
and
spl.confnum like 'T12%'
and
subscription_ends > = Getdate()
and downloads.main_id = m.main_id
group by
spl.[main_id],
fname,lname,subscription_ends
order by
subscription_ends DESC
Thanks
"Mike" wrote:

> Hi,
> I am trying to add an aggregate function to the query below but I am not
> able to get the intended results. I want to get the count to return total
> downloads by each user but my query returns the total downloads by all use
rs.
> Current Output
> 1,ttt,rrr,6/1/3005,30
> 2,ddd,jjj,5/31/2005,30
> 3,ppp,yyy,5/20/2005,30
>
> Desired Output
> 1,ttt,rrr,6/1/3005,15
> 2,ddd,jjj,5/31/2005,5
> 3,ppp,yyy,5/20/2005,10
> QUERY:
> select distinct spl. [main_id],fname,lname,subscription_ends,
> count(download_id)
> from
> main m,
> subscribers spl,
> downloads
> where
> m.main_id = spl.main_id
> and
> spl.confnum like 'T12%'
> and
> subscription_ends > = Getdate()
> group by
> spl.[main_id],
> fname,lname,subscription_ends
> order by
> subscription_ends DESC
> CREATE TABLE [dbo].[Main] (
> [main_id] [int] IDENTITY (1, 1) NOT NULL ,
> [fname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [lname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> GO
>
> CREATE TABLE [dbo].[subscribers] (
> [main_id] [numeric](18, 0) NOT NULL ,
> [confnum] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [subscription_starts] [datetime] NOT NULL
> )
> GO
> CREATE TABLE [dbo].[Downloads] (
> [main_id] [numeric](18, 0) NOT NULL ,
> [download_id] [numeric](18, 0) NOT NULL
> )
> GO
>

complex query / advice needed

Hello!

I'm seeking advice on a rather complex type of query I need to build
in an Access ADP (SQL-Server 7). There are four tables:

tblPeople
ID(PK)PRENAME
-----
1Thomas
2Frank
3Chris

tblInventoryClasses
ID(PK)INVENTORYCLASS
-------
1Car
2Phone

tblInventoryItems
ID(PK)relInvClass(FK)ITEM
-----------
11Dodge Viper
21Chrysler
32Nokia
42Samsung

tblPeopleInventory
ID(PK)relPeople(FK)relInvItem(FK)
------------
112
213
321
423
534

In this example the last table tells me that
Thomas owns a Chrysler (class Car) and a Nokia (class Phone).

Can someone tell me how to write a query or a stored procedure which
produces a resultset like this:

qryOwners
PeopleCarPhone
----------
ThomasChryslerNokia
FrankDodge ViperNokia
Chris[NULL]Samsung

The main idea is that I need to be able to iterate such a collection.
It is guranteed that one "People" only owns one or zero "Car" and one
or zero "Phone".

I guess that it might be impossible to design a stored procedure with
such a variable amount of columns (in this case, each item from
tblInventoryClasses would mean another column).

Ary there any possibilities in accomplishing this without creating
temporary tables?

Any help would be really appreciated ;-)

Greetings,
Christoph BispingChristoph Bisping <bisping.nospamplease@.unikopie.de> wrote in message news:<85d21051enbtmd5g679kpf5bddcml7f3va@.4ax.com>...
> Hello!
> I'm seeking advice on a rather complex type of query I need to build
> in an Access ADP (SQL-Server 7). There are four tables:
> tblPeople
> ID(PK)PRENAME
> -----
> 1Thomas
> 2Frank
> 3Chris
> tblInventoryClasses
> ID(PK)INVENTORYCLASS
> -------
> 1Car
> 2Phone
> tblInventoryItems
> ID(PK)relInvClass(FK)ITEM
> -----------
> 11Dodge Viper
> 21Chrysler
> 32Nokia
> 42Samsung
> tblPeopleInventory
> ID(PK)relPeople(FK)relInvItem(FK)
> ------------
> 112
> 213
> 321
> 423
> 534
> In this example the last table tells me that
> Thomas owns a Chrysler (class Car) and a Nokia (class Phone).
> Can someone tell me how to write a query or a stored procedure which
> produces a resultset like this:
> qryOwners
> PeopleCarPhone
> ----------
> ThomasChryslerNokia
> FrankDodge ViperNokia
> Chris[NULL]Samsung

This particular query looks like:

CREATE VIEW qryOwners AS
SELECT
person.PRENAME AS People,
car.ITEM AS Car,
phone.ITEM AS Phone
FROM
tblPeople person
LEFT JOIN
(
tblInventoryClasses carClass
INNER JOIN tblInventoryItems car
ON carClass.INVENTORYCLASS = 'Car'
AND carClass.ID = car.relInvClass
INNER JOIN tblPeopleInventory carPerson
ON car.ID = carPerson.relInvItem
) ON person.ID = carPerson.relPeople
LEFT JOIN
(
tblInventoryClasses phoneClass
INNER JOIN tblInventoryItems phone
ON phoneClass.INVENTORYCLASS = 'Phone'
AND phoneClass.ID = phone.relInvClass
INNER JOIN tblPeopleInventory phonePerson
ON phone.ID = phonePerson.relInvItem
) ON person.ID = phonePerson.relPeople

> The main idea is that I need to be able to iterate such a collection.
> It is guranteed that one "People" only owns one or zero "Car" and one
> or zero "Phone".
> I guess that it might be impossible to design a stored procedure with
> such a variable amount of columns (in this case, each item from
> tblInventoryClasses would mean another column).

You cannot get each InventoryClass row to magically add a column to
the resultset unless you use a stored procedure that builds dynamic
SQL statements using the EXECUTE(string) command. That would be
qualified as UGLY code, though.

You can see the pattern though.

It's simpler/more efficient on the database side if you just join all
the tables together and use an ORDER BY clause to make your client
code easier to write.

-Russell|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. There is no such thing as a universal "id" -- to be
something is to be something in particular -- a row number or IDENTITY
is a way of destroying a RDBMS and making into a sequential file
system. Why do you have that silly, redundant "tbl-" prefix on data
element name -- tell me what it is LOGICALLY and not how you are
PHYSICALLY representing it. It makes you look like a FORTRAN or BASIC
programmer.

Let's get some DDL and fix the schema. An inventory class is an
attribute of an inventory item.

CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(15) NOT NULL,
..);

CREATE TABLE Inventory
(inventory_nbr INTEGER NOT NULL PRIMARY KEY,
inv_class CHAR(5) NOT NULL
CHECK (inv_class IN (..)),
item_description CHAR(15) NOT NULL,
..,);

>> Can someone tell me how to write a query or a stored procedure
which produces a result set like this: <<

This is a report and not a query; this ought to be done in the front
end and not in the database at all. Thanks to the lack of specs and
DDL, we have no idea if people can have more than one car or more than
one phone. Here is one possible guess at an answer:

SELECT P1.name,
MAX (CASE WHEN I1.inv_class = 'car'
THEN I1.item_description
ELSE NULL END) AS auto,
MAX (CASE WHEN I1.inv_class = 'phone'
THEN I1.item_description
ELSE NULL END) AS phone
FROM People AS P1, Allocations AS A1, Inventory as I1
WHERE A1.person_id = P1.person_id
AND A1.inventory_nbr = I1.inventory_nbr
GROUP BY P1.name;

if you allowed only one item per class, then use:

CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
name CHAR(15) NOT NULL,
..);

CREATE TABLE Inventory
(inventory_nbr INTEGER NOT NULL PRIMARY KEY,
item_description CHAR(15) NOT NULL,
..,);

CREATE TABLE Allocations
(person_id INTEGER NOT NULL
REFERENCES People(person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
inventory_nbr INTEGER NOT NULL
REFERENCES Inventory (inventory_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
inv_class CHAR(5) NOT NULL
CHECK (inv_class IN (..)),
UNIQUE (person_id, inv_class),
..,
PRIMARY KEY (person_id, inventory_nbr));|||rustleb@.hotmail.com (Russell Bevers) wrote:

>You cannot get each InventoryClass row to magically add a column to
>the resultset unless you use a stored procedure that builds dynamic
>SQL statements using the EXECUTE(string) command. That would be
>qualified as UGLY code, though.
>You can see the pattern though.
>It's simpler/more efficient on the database side if you just join all
>the tables together and use an ORDER BY clause to make your client
>code easier to write.
>-Russell

That was exactly what I (didn't) want to read...
After digging through hundreds of url's everything looks to me like
that's the only way to accomplish this. Anyway, thanks for your answer
which showed me that I definitely have to handle these things in the
frontend.

In fact, the overall processing performance seems to be good enough if
I just read all of these tables in order to build lookup-tables in my
application. Being familiar with UGLY code, your suggestion using
EXECUTE(strSQL) sounds like an alternative ;-)

Greetings,

Christoph Bisping|||joe.celko@.northface.edu (--CELKO--) wrote:
>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in
>your schema are. There is no such thing as a universal "id" -- to be
>something is to be something in particular -- a row number or IDENTITY
>is a way of destroying a RDBMS and making into a sequential file
>system. Why do you have that silly, redundant "tbl-" prefix on data
>element name -- tell me what it is LOGICALLY and not how you are
>PHYSICALLY representing it.
>It makes you look like a FORTRAN or BASIC programmer.

Well, this wouldn't be a wrong statement. I do agree that I'm using
way to much of these "universal id"-cols in my tables which are surely
wasted.

As you might guess I'm quite inexperienced here and for now pure DDL
isn't one of my favorite languages but I'll see if I'm able to adapt
what you've written. But it seems to be much easier for me to go the
"sequential file system" way and do the complex People<->InventoryItem
mappings entirely at frontend level.

Thanks for your detailed explanation!

Greetings,

Christoph Bisping

Complex Query (atleast I think so)

We have a couple of Bill of Material tables in our SQL 2000 database and I
need to extract information.
The first table is the master table and the only relevant field is fpartno.
Table two is the child table, and it contains all of the parts that make up
the fpartno in the master table. The only two relevant fields are
fcomponent and fcparent.
In the child table a component (Part No) may be in the table several times
because we may use it in different parent parts. And to complicate things,
the child table usually includes multiple levels of material.
How can I query the table so that it will pass over the table as many times
as needed to get the full multiple level bill of material.
Here would be a simple example.
PartA
PartB
PartC
PartD
PartE
PartF
As you can see the BOM for PartA goes out multiple levels. Any ideas on how
I can query this?
Thanks.Oracle has a CONNECT BY clause that makes this easy. Unfortunitly for
SQL Server you have to go thru this mess:
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm|||SQL 2000 or SQL 2005?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Preacher Man> wrote in message
news:u3iA4ULvGHA.4384@.TK2MSFTNGP04.phx.gbl...
> We have a couple of Bill of Material tables in our SQL 2000 database and I
> need to extract information.
> The first table is the master table and the only relevant field is
> fpartno.
> Table two is the child table, and it contains all of the parts that make
> up the fpartno in the master table. The only two relevant fields are
> fcomponent and fcparent.
> In the child table a component (Part No) may be in the table several times
> because we may use it in different parent parts. And to complicate
> things, the child table usually includes multiple levels of material.
> How can I query the table so that it will pass over the table as many
> times as needed to get the full multiple level bill of material.
> Here would be a simple example.
> PartA
> PartB
> PartC
> PartD
> PartE
> PartF
> As you can see the BOM for PartA goes out multiple levels. Any ideas on
> how I can query this?
> Thanks.
>
>|||SQL 2000.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%2319ACkLvGHA.4472@.TK2MSFTNGP02.phx.gbl...
> SQL 2000 or SQL 2005?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Preacher Man> wrote in message
> news:u3iA4ULvGHA.4384@.TK2MSFTNGP04.phx.gbl...
>> We have a couple of Bill of Material tables in our SQL 2000 database and
>> I need to extract information.
>> The first table is the master table and the only relevant field is
>> fpartno.
>> Table two is the child table, and it contains all of the parts that make
>> up the fpartno in the master table. The only two relevant fields are
>> fcomponent and fcparent.
>> In the child table a component (Part No) may be in the table several
>> times because we may use it in different parent parts. And to complicate
>> things, the child table usually includes multiple levels of material.
>> How can I query the table so that it will pass over the table as many
>> times as needed to get the full multiple level bill of material.
>> Here would be a simple example.
>> PartA
>> PartB
>> PartC
>> PartD
>> PartE
>> PartF
>> As you can see the BOM for PartA goes out multiple levels. Any ideas on
>> how I can query this?
>> Thanks.
>>
>|||Perhaps these articles on recursive queries will get you moving in a
direction that works to solve your problem.
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=9
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
http://www.wwwcoder.com/main/parentid/191/site/1857/68/default.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03i8.asp
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Preacher Man> wrote in message
news:eLd2X4LvGHA.4160@.TK2MSFTNGP06.phx.gbl...
> SQL 2000.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%2319ACkLvGHA.4472@.TK2MSFTNGP02.phx.gbl...
>> SQL 2000 or SQL 2005?
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> <Preacher Man> wrote in message
>> news:u3iA4ULvGHA.4384@.TK2MSFTNGP04.phx.gbl...
>> We have a couple of Bill of Material tables in our SQL 2000 database and
>> I need to extract information.
>> The first table is the master table and the only relevant field is
>> fpartno.
>> Table two is the child table, and it contains all of the parts that make
>> up the fpartno in the master table. The only two relevant fields are
>> fcomponent and fcparent.
>> In the child table a component (Part No) may be in the table several
>> times because we may use it in different parent parts. And to
>> complicate things, the child table usually includes multiple levels of
>> material.
>> How can I query the table so that it will pass over the table as many
>> times as needed to get the full multiple level bill of material.
>> Here would be a simple example.
>> PartA
>> PartB
>> PartC
>> PartD
>> PartE
>> PartF
>> As you can see the BOM for PartA goes out multiple levels. Any ideas on
>> how I can query this?
>> Thanks.
>>
>>
>

Complex Query - Part II

I am passing the following query:
select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
else 0 end) as 'Closed',
sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
from ticket where location in (select location from location where
lstate = 'mp')
and received_date between '5/1/2004' and '5/10/2004' group by type
My requirement is for totals of each column generated, suggest. I am
using an automated asp script written by me for generating dynamic sql
reports, for which i just need to give the sql statement, suggest how to
get totals of each column generated automatically if it is of numeric
value and to ignore totals if it is of non numeric value.
Through sql query only.
Part II because my earlier post on May 10, 2004 was not answered.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
On Wed, 16 Jun 2004 23:03:50 -0700, Preet Kanwaljit Singh Shergill wrote:

>
>I am passing the following query:
>select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
>else 0 end) as 'Closed',
>sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
>sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
>from ticket where location in (select location from location where
>lstate = 'mp')
>and received_date between '5/1/2004' and '5/10/2004' group by type
>My requirement is for totals of each column generated, suggest. I am
>using an automated asp script written by me for generating dynamic sql
>reports, for which i just need to give the sql statement, suggest how to
>get totals of each column generated automatically if it is of numeric
>value and to ignore totals if it is of non numeric value.
>Through sql query only.
>
>Part II because my earlier post on May 10, 2004 was not answered.
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
Hi Preet,
If I understand your question correctly, you can either use this:
SELECT type, COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
GROUP BY type
UNION ALL
SELECT 'Total', COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
(untested)
Or you can look up ROLLUP in Books Online and see if that helps.
Final notes:
1) I changed the ambiguous date format you used to the recommended
YYYYMMDD format. Check if I didn't exchange the DD and MM parts of your
query.
2) I *think* that the subquery for location can be changed to an inner
join, but I'd have to know your table structure to be sure. The inner join
might yield better performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks a ton, it does solve my problem.
With this solution my SQL based reporting will be even faster than
before with minimal network traffic.
I no longer have to bother about html lipstick work.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

complex query - need help

I have three tables-

The MemberTable contains name,address of members along with companyID (CompID)
The CompanyTable contains list of all comapny name along with CompID
The memberData table contains multiple rows for each memberID in memberTable.

I want to write a query which will give me the latest entry in memberDataTable for every member of a particular company. How do I write the query?

For eg, if I want compID=1, i need to get:
Select max(DataID) from memberdatatable where memberID=__
I would have to repeat the above for every member in the company with companyID=1

Any help in forming a proper query would be greatly appreciated

One way to do this is:

Code Snippet

declare @.MemberTable Table(memberID int,name varchar(100),address varchar(500),companyID int)
declare @.MemberDataTable table(DataID int,memberID int)
insert into @.MemberTable select 1,'name1','-',1
insert into @.MemberTable select 2,'name2','-',1
insert into @.MemberDataTable select 1,1
insert into @.MemberDataTable select 2,1
insert into @.MemberDataTable select 3,2
insert into @.MemberDataTable select 4,1
insert into @.MemberDataTable select 5,2

declare @.companyID int
set @.companyID=1

select max(DataID) DataID,memberID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID

|||What about:

SELECT MAX(DataId), CompId
From MemberDataTable
Group by CompId

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for the reply Mobin

but I still have a problem. I have more data in the memberdatatable which i need to retrieve along with the last row. When i add these data fileds: data1, data2, it gives me duplicate values of memberID.

declare @.MemberTable Table(memberID int,name varchar(100),address varchar(500),companyID int)
declare @.MemberDataTable table(DataID int,memberID int,data1 int, data2 int)
insert into @.MemberTable select 1,'name1','-',1
insert into @.MemberTable select 2,'name2','-',1
insert into @.MemberDataTable select 1,1,9,9
insert into @.MemberDataTable select 2,1,9,8
insert into @.MemberDataTable select 3,2,8,9
insert into @.MemberDataTable select 4,1,8,8
insert into @.MemberDataTable select 5,2,9,8

declare @.companyID int
set @.companyID=1

select max(DataID) DataID,memberID,data1,data2 from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID,data1,data2

Another thing i want to know is why does the query show an error when data1, data2 are fetched without being written in group by clause
|||Thanks Jens, but :
MemberDataTable doesn't have CompId.
|||I just modified what Mobin sent and this worked:

select * from @.memberdatatable where dataid in (select max(DataID) DataID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID)

Mobin, I'd really appreciate it if u could explain why the query showed an error when i tried to fetch data1, data2 without listing it in the groupby clause as in:
select max(DataID) DataID,memberID,data1,data2 from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyID ) group by memberID
|||

The following query may help you..

Code Snippet

Create Table #companytable (

[CompID] Varchar(100) ,

[name] Varchar(100)

);

Insert Into #companytable Values('1','Comp1');

Insert Into #companytable Values('2','Comp2');

Insert Into #companytable Values('3','Comp3');

Insert Into #companytable Values('4','Comp4');

Insert Into #companytable Values('5','Comp5');

Create Table #membertable (

[memberID] Varchar(100) ,

[name] Varchar(100) ,

[address] Varchar(100) ,

[companyID] Varchar(100)

);

Insert Into #membertable Values('1','Mem1','Address1','1');

Insert Into #membertable Values('2','Mem2','Address2','3');

Insert Into #membertable Values('3','Mem3','Address3','2');

Insert Into #membertable Values('4','Mem4','Address4','2');

Insert Into #membertable Values('5','Mem5','Address5','1');

Insert Into #membertable Values('6','Mem6','Address6','2');

Insert Into #membertable Values('7','Mem7','Address7','2');

Insert Into #membertable Values('8','Mem8','Address8','3');

Create Table #memberdatatable (

[memberID] Varchar(100)

);

Insert Into #memberdatatable Values('1');

Insert Into #memberdatatable Values('4');

Insert Into #memberdatatable Values('5');

Insert Into #memberdatatable Values('8');

Select * From #membertable Main

Join #companytable Comp On Comp.CompID= Main.companyID

Join (Select Max(MD.memberID) memberID From #memberdatatable MD

Join #membertable MT On MD.memberID = MT.memberID Group BY companyID) as Data

On data.memberID=Main.memberID

|||

error is caused since the fields data1 and data2 are not mentioned in the group by clause. the following query will fix the problem:

Code Snippet

select * from @.MemberDataTable where DataID in(
select max(DataID) DataID from @.MemberDataTable where memberID in (select memberID from @.MemberTable where companyID=@.companyIDmailto:companyID=@.companyID">companyID=@.companyID</A< A>> ) group by memberID)

regards

Complex Query - Need help

Hi,

I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ........., Sum of Amount for December, Total for All months, Average for all months.

I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?

Thanks!Are you just looking for something like this?

select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate|||No, I need total for each month for each employee, here is a sample of what I am looking for:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ...... $341

Thanks a lot for your help!|||Is an employee's rate going to change? If so what rate should be displayed?|||It will use group by "corpcode, EmpName, rate". So if rate changes for an employee, there should be a new line in the query output.

Thanks again!|||select corpcode
, EmpName
, rate
, sum(Amount) as test
, Sum (CASE DATEPART(yy,reportdate)
WHEN 1 THEN Amount Else 0) As January
End,
, Sum (CASE DATEPART(yy,reportdate)
WHEN 2 THEN Amount Else 0) As February
End,
...List rest oh the months here
...

FROM <table>
Group By corpcode, EmpName, rate|||I am getting this error message:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

also instead of DATEPART(yy,reportdate), don't we need DATEPART(mm,reportdate)?

Thanks again!|||I believe jaraba's response will still have all the months in seperate rows. You may have to do something like this. I am not sure if there is a better way it is just the first thing that came to mind.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

select corpcode, EmpName, rate,
(SELECT top 1 Amount From #tmp a where mymonth = 1
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as January,
(SELECT top 1 Amount From #tmp a where mymonth = 2
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as February
...
from #tmp

drop table #tmp

This will only display the most recent months, if you want years too you could have a whole mess of columns.

Hope this helps|||If you don't mind, send me some data in an excel spreadsheet. i will work a solution for you.|||Thanks a lot, you guys are big help.

I got jaraba's query to work, but I want to ask one more question,

Whenever CorpCode changes, I need a line for sub totals and grand total as follows:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ......$341
----------------------
Sub-Total--2 employees--$134--$903--$690--$458.........$1019
----------------------
B1114444--ABC Zeb--$15--$13--$46--$0.00 ...... $68.0
B1114444--TTT Bob--$11--$0--$23--$48.0 .......$31
B1114444--GTH Bob--$19--$70--$3--$8.0 .........$15
----------------------
Sub-Total--3 employees--$45--$83--$73--$56..........$114
----------------------
----------------------
Grand Total--5 employees--$189--$986--$763--$514.........$1133
----------------------|||Actually, my response was no better. Sorry, I think something like this should get you what you are looking for.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

CREATE TABLE #emp (corpcode int, EmpName varchar(50), rate int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

insert into #emp
SELECT DISTINCT corpcode, EmpName, rate
FROM #tmp

select #emp.corpcode, #emp.EmpName, #emp.rate, j.Amount as January, f.Amount as February ...
from #emp
LEFT OUTER join #tmp j on #emp.corpcode = j.corpcode
and #emp.EmpName = j.EmpName
and #emp.rate = j.rate and j.mymonth = 1
LEFT OUTER join #tmp f on #emp.corpcode = f.corpcode
and #emp.EmpName = f.EmpName
and #emp.rate = f.rate and f.mymonth = 2
...

drop table #tmp
drop table #emp

Sorry for the confusion.|||Sorry again, you can ignore my last post if jaraba's query got you what you are looking for. If you want subtotals for each corpcode you will have to write a seperate query or use the functionality of your report writer.|||Look up COMPUTE BY in BOL

Complex Query - at least for me

I've been trying to do this on and off for over a month, just can't seem to
get it. I have a table that looks something like
Name Activity
==== ======== Mike Arrest
John Victim
Fred Suspect
John Suspect
Mike Victim
John Victim
Mike Arrest
For graphing purposes, I'd like to end up with a view that looks like
Name Victim_Count Arrest_Count Suspect_Count
==== ============ ============ ============= Mike 1 2 0
John 2 0 1
Fred 0 0 1
I've tried things like select name, (select count(*) where activity = 'Victim') as Victim_Count group by name. Which doesn't work.
Can anyone point me in the right direction? I know there are fixed number of
Activity types (3 in this case).
Any help is much appreciated,
MikeSELECT [name],
COUNT(CASE activity WHEN 'victim' THEN 1 END) AS victim_count,
COUNT(CASE activity WHEN 'arrest' THEN 1 END) AS arrest_count,
COUNT(CASE activity WHEN 'suspect' THEN 1 END) AS suspect_count
FROM Sometable
GROUP BY [name]
--
David Portas
--
Please reply only to the newsgroup
--|||This is a multi-part message in MIME format.
--=_NextPart_000_0185_01C3CF8B.993AB0B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Try:
select
Name
, sum (case when Activity = 'Victim' then 1 else 0 end) as Victim_Count
, sum (case when Activity = 'Arrest' then 1 else 0 end) as Arrest_Count
, sum (case when Activity = 'Suspect' then 1 else 0 end) as Suspect_Count
from
MyTable
group by
Name
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:Ofw7lO7zDHA.2456@.TK2MSFTNGP12.phx.gbl...
I've been trying to do this on and off for over a month, just can't seem to
get it. I have a table that looks something like
Name Activity
==== ========Mike Arrest
John Victim
Fred Suspect
John Suspect
Mike Victim
John Victim
Mike Arrest
For graphing purposes, I'd like to end up with a view that looks like
Name Victim_Count Arrest_Count Suspect_Count
==== ============ ============ =============Mike 1 2 0
John 2 0 1
Fred 0 0 1
I've tried things like select name, (select count(*) where activity ='Victim') as Victim_Count group by name. Which doesn't work.
Can anyone point me in the right direction? I know there are fixed number of
Activity types (3 in this case).
Any help is much appreciated,
Mike
--=_NextPart_000_0185_01C3CF8B.993AB0B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try:
select
=Name
, sum (case =when Activity =3D 'Victim' then 1 else 0 end) as Victim_Count
, sum =(case when Activity =3D 'Arrest' then 1 else 0 end) as Arrest_Count
, sum (case =when Activity =3D 'Suspect' then 1 else 0 end) as Suspect_Count
from
MyTable
group by
Name
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike Hildner" wrote in =message news:Ofw7lO7zDHA.2456=@.TK2MSFTNGP12.phx.gbl...I've been trying to do this on and off for over a month, just can't seem =toget it. I have a table that looks something likeName Activity=3D=3D=3D=3D &=nbsp; =3D=3D=3D=3D=3D=3D=3D=3DMike =; ArrestJohn VictimFred SuspectJohn SuspectMike VictimJohn VictimMike ArrestFor graphing purposes, I'd like to end up with a view that =looks likeName Victim_Count Arrest_Count Suspect_Count=3D=3D=3D=3D &n=bsp; =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &=nbsp; =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D &=nbsp; =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DMike &n=bsp; 1 = 2 = 0John 2 = 0 = 1Fred 0 = 0 = 1I've tried things like select name, (select count(*) where =activity =3D'Victim') as Victim_Count group by name. Which doesn't =work.Can anyone point me in the right direction? I know there are fixed number ofActivity types (3 in this case).Any help is much appreciated,Mike

--=_NextPart_000_0185_01C3CF8B.993AB0B0--|||Tom and David,
Sincere thanks. Both methods seem to produce the same result - exactly what
I need. If you only knew the trouble I was having. Gives me something to
read up on.
Much appreciated,
Mike
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:UtmdnWcR0ITBcm-i4p2dnA@.giganews.com...
> SELECT [name],
> COUNT(CASE activity WHEN 'victim' THEN 1 END) AS victim_count,
> COUNT(CASE activity WHEN 'arrest' THEN 1 END) AS arrest_count,
> COUNT(CASE activity WHEN 'suspect' THEN 1 END) AS suspect_count
> FROM Sometable
> GROUP BY [name]
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||Greetings...
Is there a way to do this type of Query from the View designer or must I do
it from query analyzer'
Thanks!
S.
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:Ofw7lO7zDHA.2456@.TK2MSFTNGP12.phx.gbl...
> I've been trying to do this on and off for over a month, just can't seem
to
> get it. I have a table that looks something like
> Name Activity
> ==== ========> Mike Arrest
> John Victim
> Fred Suspect
> John Suspect
> Mike Victim
> John Victim
> Mike Arrest
> For graphing purposes, I'd like to end up with a view that looks like
> Name Victim_Count Arrest_Count Suspect_Count
> ==== ============ ============ =============> Mike 1 2 0
> John 2 0 1
> Fred 0 0 1
> I've tried things like select name, (select count(*) where activity => 'Victim') as Victim_Count group by name. Which doesn't work.
> Can anyone point me in the right direction? I know there are fixed number
of
> Activity types (3 in this case).
> Any help is much appreciated,
> Mike
>

Complex Query

Hello,
I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,cd,cycdt,amt from joining two tables. The criteria is that if the transdt greater than same month of cycdt then we need get the next month cycdt and corresponding amount for that, if it is less than or equal to same months cycdt then get the same months cycdt and amt. Cd is dummy field which can be anything. I am using sqlserver 8.0

table1
idno,trandt,cd
12345,04/15/2005,cd1
12345,04/15/2005,cd2
12345,04/22/2005,cd3
12345,07/03/2005,cd4
12345,09/10/2005,cd5
3421,03/05/2005,cd6
3421,05/06/2005,cd7
3421,07/04/2005,cd8
3421,07/15/2005,cd9
3421,09/15/2005,cd10

idno,cycdt,amt
12345,02/10/2005,15.43
12345,03/13/2005,40.84
12345,04/18/2005,10.10
12345,05/24/2005,13.00
12345,06/16/2005,20.89
12345,07/18/2005,12.12
12345,08/17/2005,10.89
12345,09/17/2005,12.87
12345,10/16/2005,13.89
3421,05/10/2005,15.00
3421,06/11/2005,20.00
3421,07/11/2005,14.15
3421,08/12/2005,15.54

Expected result.
12345,04/15/2005,cd1,04/18/2005,10.10
12345,04/15/2005,cd2,04/18/2005,10.10
12345,04/22/2005,cd3,05/24/2005,13.00
12345,07/03/2005,cd4,07/18/2005,12.12
12345,09/10/2005,cd5,09/17/2005,12.87
3421,05/06/2005,cd7,05/10/2005,15.00
3421,07/04/2005,cd8,07/11/2005,14.15
3421,07/15/2005,cd9,08/12/2005,15.54

I really appreciate if someone can give solution for this using a query (SQL server,Access,Foxpro) is fine.

I am able to do this oracle using count(trandt) over(partition trandt order by 1) as cnt.

and cnt=1.

If u want I can post that too for getting any idea.

I need more information. I would assume that idno is the primary key on both tables, except that it is not unique for each row. If the key is the idno and the date column on each table, then you will not be able to perform this query as you won't be able to join the two tables together. I will attempt the query, but I am not sure it is what you want without knowing more information. Please reply to let me know whether this answere your question, or whether you need more help. As you either want the current month's cycdt or the next months, I suggest join to two copies of the second table, one joining on the same month, one joining on the subsequent month:

select

case t1.transdt > t2a.cycdt

then t2b.cycdt -- get next month's cycdt

else t2a.cycdt -- get this month's cycdt

end as cycdt,

case t1.transdt > t2a.cycdt

then t2b.amt -- get next month's amt

else t2a.amt -- get next month's amt

end as amt

from table1 as t1

join table2 as t2a -- current month join

on t2a.idno = t1.idno and month(t2a.cycdt) = month(t1.transdt)

join table2 as t2b -- joins to next month.

on t2b.idno = t1.idno and month(t2b.cycdt) = month(t1.transdt) + 1

-- The second join may need to be an outer join, as will restrict result set to

-- include only dates upto the month before the latest month. I don't know if

-- this is going to be a problem for you

For more T-SQL tips and advice, visit my blog:

|||

Whoops, forgot the first three columns.

select

t1.Idno,

t1.transdt,

t1.cd,

case

when t1.transdt > t2a.cycdt then t2b.cycdt -- get next month's cycdt

else t2a.cycdt -- get this month's cycdt

end as cycdt,

case when t1.transdt > t2a.cycdt then t2b.amt -- get next month's amt

else t2a.amt -- get next month's amt

end as amt

from table1 as t1

join table2 as t2a -- current month join

on t2a.idno = t1.idno and month(t2a.cycdt) = month(t1.transdt)

join table2 as t2b -- joins to next month.

on t2b.idno = t1.idno and month(t2b.cycdt) = month(t1.transdt) + 1

|||

First, I really appreciate you in taking time to write the query.

Basically, its not a formal table but just set of two result set and need to produce report out of it as one time. so, I am not making it as any primary key or something. But Idno is the main link between the tables.

Also, this is test tables as my original tables have 3million in table1 and 500k in table2.

The solution you had given works fine as long as there are no missing cycdt in between. But if one cycdt misses the query is not pulling the proper records.

(E.g) try removing the record 12345,05/24/05 from table2.

Let me know if you need more info.

I have this in Oracle but not able to use "Over - Partition" thing since I am using SQL server 8.0

Here it is in Oracle.

SELECT IDNO,TRANDT,IDNO1,CYCDT,AMT FROM
(SELECT IDNO,TRANDT,IDNO1,CYCDT,AMT,COUNT(TRANDT) OVER(PARTITION BY TRANDT ORDER BY 1) CNT
FROM
( SELECT T1.IDNO IDNO,T1.TRANDT,T2.IDNO IDNO1,T2.CYCDT,T2.AMT
FROM tab1 T1,tab2 T2 WHERE T1.IDNO=T2.IDNO )
WHERE (TO_CHAR(TRANDT,'MM') = TO_CHAR(CYCDT,'MM') AND
TO_CHAR(TRANDT,'DD') < TO_CHAR(CYCDT,'DD')) OR
(TO_NUMBER(TO_CHAR(TRANDT,'MM'))+1 = TO_CHAR(CYCDT,'MM'))
ORDER BY 1,2,4
)
WHERE TO_CHAR(TRANDT,'MM') = TO_CHAR(CYCDT,'MM') OR CNT=1ORDER BY 1 DESC,2,4

|||Use LEFT OUTER JOINS on both the JOINS instead, in which case when cycdt is null, then the columns from the second table will be null. You can then test for this using ISNULL, and specify the value to return in this case.

Complex Query

Hi,
I have a tree database with the following fields:
- themeid
- description
- parent id
I have the following registries:
Themeid description parentid
1 food null
2 cars null
3 others null
4 meat 1
But i need to generate a list like:
Food + (level or depth, in this case 1)
Meat + (level or depth in this case 2)
Cars + (level or depth, in this case 1)
Others + (level or depth, in this case 1)
Meat + (level or depth, in this case 1)
Im trying to figure out how to use the with clause to get the list with the
level or depth or each registry, but at this moment i dont have idea about
how to do it.
Any help would be appreciated.
Thanks a lot.
Kind regards.
This question was asked and answered in the programming group. Please don't
post the same question in multiple groups as this can cause duplication of
effort.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Josema" <Josema@.discussions.microsoft.com> wrote in message
news:4A9CF7D0-E8BA-4054-9F0C-53F521D0EC9C@.microsoft.com...
> Hi,
> I have a tree database with the following fields:
> - themeid
> - description
> - parent id
> I have the following registries:
> Themeid description parentid
> 1 food null
> 2 cars null
> 3 others null
> 4 meat 1
> But i need to generate a list like:
> Food + (level or depth, in this case 1)
> Meat + (level or depth in this case 2)
> Cars + (level or depth, in this case 1)
> Others + (level or depth, in this case 1)
> Meat + (level or depth, in this case 1)
> Im trying to figure out how to use the with clause to get the list with
> the
> level or depth or each registry, but at this moment i dont have idea about
> how to do it.
> Any help would be appreciated.
> Thanks a lot.
> Kind regards.
>
|||Here is one way using recursive CTE in SQL Server 2005:
CREATE TABLE Themes (
themeid INT PRIMARY KEY,
description VARCHAR(35),
parentid INT REFERENCES Themes(themeid));
INSERT INTO Themes VALUES (1, 'food', NULL);
INSERT INTO Themes VALUES (2, 'cars', NULL);
INSERT INTO Themes VALUES (3, 'others', NULL);
INSERT INTO Themes VALUES (4, 'meat', 1);
WITH ThemesPath
AS
(SELECT themeid, description, parentid,
CAST('.' + CAST(themeid AS VARCHAR(8)) + '.'
AS VARCHAR(2000)) AS theme_path,
1 AS depth
FROM Themes
WHERE parentid IS NULL
UNION ALL
SELECT T.themeid, T.description, T.parentid,
CAST(P.theme_path + CAST(T.themeid AS VARCHAR(8)) + '.'
AS VARCHAR(2000)),
P.depth + 1
FROM Themes AS T
JOIN ThemesPath AS P
ON T.parentid = P.themeid)
SELECT themeid, description, parentid, depth
FROM ThemesPath
ORDER BY theme_path;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Saturday, February 25, 2012

complex query in sp

Complex query I need help with.

Complex Query Help.. Please :)

Complex query help needed....

Complex Query help needed fast!

complex query help needed

complex query help - count

complex query / advice needed

Complex Query (atleast I think so)

Complex Query (atleast I think so)

Complex Query - Part II

complex query - need help

Complex Query - Need help

Complex Query - at least for me

Complex Query

Complex Query

Sql Comparing two tables

Blog Archive

About Me