Query Help

Hi all,
I have one ‘tall’ table that records the following on a regular basis:
STATE SERIAL# DATE
====== ======== ========
VA Z32WE12 12/31/2003
CA QWEFD1 05/04/2005
VA Z32WE13 01/01/2003
CA QWEFD2 05/05/2005
TX POISD21 05/03/2005
TX POISD21 05/04/2005
TX POISD21 05/05/2005
We are tracking the serial number for each state and would like to report on
the current and previous serial number for each state. Can someone please
help me with building the query in order to get the following:
StateCurrent Serial#SincePrevious Serial#
CAQWEFD205/05/2005QWEFD1
VAZ32WE1301/01/2003Z32WE12
TXPOISD21 05/03/2005 Never Changed
Thanks in advance,
-Appreciator
It seems to be a flaw in the data:

> VA Z32WE12 12/31/2003
> VA Z32WE13 01/01/2003
Did you mean "12/31/2002" for serial# "Z32WE12"?
try:
use northwind
go
create table t1 (
state char(2) not null,
serial# varchar(10) not null,
[date] datetime,
)
go
insert into t1 values('VA','Z32WE12', '12/31/2002')
insert into t1 values('CA','QWEFD1' , '05/04/2005')
insert into t1 values('VA','Z32WE13', '01/01/2003')
insert into t1 values('CA','QWEFD2' , '05/05/2005')
insert into t1 values('TX','POISD21', '05/03/2005')
insert into t1 values('TX','POISD21', '05/04/2005')
insert into t1 values('TX','POISD21', '05/05/2005')
go
create view v1
as
select state, serial#, max([date]) as [date] from t1 group by state, serial#
go
create view v2
as
select
a.state,
a.serial# as current_serial#,
a.[date] as since,
isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
previous_serail#
from
v1 as a
left join
v1 as b
on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
where c.state = a.state and c.[date] > b.[date])
go
select
*
from
v2 as a
where
previous_serail# != 'have_not_changed_since'
or (previous_serail# = 'have_not_changed_since' and not exists(select *
from v2 as b where b.state = a.state and b.previous_serail# !=
'have_not_changed_since'))
order by
case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
a.state
go
drop view v2, v1
go
drop table t1
go
AMB
"URG" wrote:

> Hi all,
> I have one ‘tall’ table that records the following on a regular basis:
> STATE SERIAL# DATE
> ====== ======== ========
> VA Z32WE12 12/31/2003
> CA QWEFD1 05/04/2005
> VA Z32WE13 01/01/2003
> CA QWEFD2 05/05/2005
> TX POISD21 05/03/2005
> TX POISD21 05/04/2005
> TX POISD21 05/05/2005
> We are tracking the serial number for each state and would like to report on
> the current and previous serial number for each state. Can someone please
> help me with building the query in order to get the following:
> StateCurrent Serial#SincePrevious Serial#
> CAQWEFD205/05/2005QWEFD1
> VAZ32WE1301/01/2003Z32WE12
> TXPOISD21 05/03/2005 Never Changed
> Thanks in advance,
> -Appreciator
>
|||Hi Alejandro,
Thanks a bunch! That really works perfect..!!
Sorry about the flaw - I had typed in the sample data.
URG
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> It seems to be a flaw in the data:
>
> Did you mean "12/31/2002" for serial# "Z32WE12"?
> try:
> use northwind
> go
> create table t1 (
> state char(2) not null,
> serial# varchar(10) not null,
> [date] datetime,
> )
> go
> insert into t1 values('VA','Z32WE12', '12/31/2002')
> insert into t1 values('CA','QWEFD1' , '05/04/2005')
> insert into t1 values('VA','Z32WE13', '01/01/2003')
> insert into t1 values('CA','QWEFD2' , '05/05/2005')
> insert into t1 values('TX','POISD21', '05/03/2005')
> insert into t1 values('TX','POISD21', '05/04/2005')
> insert into t1 values('TX','POISD21', '05/05/2005')
> go
> create view v1
> as
> select state, serial#, max([date]) as [date] from t1 group by state, serial#
> go
> create view v2
> as
> select
> a.state,
> a.serial# as current_serial#,
> a.[date] as since,
> isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
> previous_serail#
> from
> v1 as a
> left join
> v1 as b
> on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as c
> where c.state = a.state and c.[date] > b.[date])
> go
> select
> *
> from
> v2 as a
> where
> previous_serail# != 'have_not_changed_since'
> or (previous_serail# = 'have_not_changed_since' and not exists(select *
> from v2 as b where b.state = a.state and b.previous_serail# !=
> 'have_not_changed_since'))
> order by
> case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
> a.state
> go
> drop view v2, v1
> go
> drop table t1
> go
>
> AMB
> "URG" wrote:

Query help

I am sure this is something totally simple that I am forgetting, but
it is driving me crazy!
I have a cross-reference table of items and attributes. So each row
of the table contains one item and one attribute. Most items have
many lines. I need a select statement (or statements) where I can get
back all the items that have 3 specific attributes. So it would be an
AND thing, not an OR thing. How do I do this?
Thanks,
Gala
Please post your DDL. Perhaps relational division will do it:
select
ItemID
from
MyTable
where
attribute in ('ATTR1', 'ATTR2', 'ATTR3')
group by
ItemID
having
count (*) = 3
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gala" <gala@.sonic.net> wrote in message
news:14dba7aa.0408231103.5df59f81@.posting.google.c om...
I am sure this is something totally simple that I am forgetting, but
it is driving me crazy!
I have a cross-reference table of items and attributes. So each row
of the table contains one item and one attribute. Most items have
many lines. I need a select statement (or statements) where I can get
back all the items that have 3 specific attributes. So it would be an
AND thing, not an OR thing. How do I do this?
Thanks,
Gala

query help

I will try to be brief
I have two tables I am trying to join that share a foreign key.
the structures are:
table a: column table b: columns
foreign key employee ID employeeID
status earnings
effectivedate checkdate
each table can have multiple rows with the same employee ID. Table b can have multiple rows with the same checkdate. I am trying to query the two tables so I can get the sum of the earnings for a particular checkdate and the employees status at the time
of the check. Here is an example of the data and what I have written so far:
tableA:
employeeID STATUS Effectivedate
100 fulltime 01/01/03
100 parttime 01/01/04
100 fulltime 03/27/04
101 fulltime 01/01/03
101 parttime 04/01/04
tableB:
employeeID earnings checkdate
100 25.00 03/25/04
100 97.00 03/25/04
101 10.00 03/25/04
If I query with the employeeID it is no problem:
select tableB.employeeID, STATUS, Effectivedate, sum(earnings), checkdate
from tableA, tableB
where tableA.employeeID=tableB.employeeID
and effectivedate=(select max(effectivedate) from tableA where tableA.employeeID=100 and effectivdate <='03/25/04')
and checkdate='03/25/04'
group by tableB.employeeID, STATUS, Effectivedate, checkdate
but... to do this on my tables which have thousands and thousands of rows for each ID each month will be painful.
How can I write this query so that the select will return the sum of earnings by employeeID on a specific checkdate and the employee status on that date. I thought of using a cursor, but I am not advanced enough to write one. I am sure there has to be a
way to do this. Any help will be greatly appreciated!!!!
I realize I should have posted this in data mining, but I don't want to double post
sql

Query help

I need to update 6 column on 1 table depending on 2 other
columns on another table (All 8 columns exists on both
tables)
here is what I am trying to write and it gives me error:
Update Table1
Set start_date = (Select start_date from Table2),
end_date = (Select end_date from Table2),
user1 = (Select user1 from Table2),
user2 = (Select user2 from Table2),
user3 = (Select user3 from Table2),
user4 = (Select user4 from Table2)
FROM Table2
Where Table2.project = Table1.project AND
Table2.Pjt_entity = Table1.Pjt_entity
Thanks for any help.
Hi,
Try this,
Update Table1
Set a.start_date = b.start_date ,
a.end_date = b.end_date,
a.user1 = b.user1,
a.user2 = b.user2,
a.user3 = b.user3,
a.user4 = b.user4
FROM Table1 a,Table2 b
Where a.project = b.project
AND a..Pjt_entity = b.Pjt_entity
Tahnks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2001c486d0$5a97bdb0$a301280a@.phx.gbl...
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
|||Todd wrote:
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
Well if you have a 1:1 between the tables, you just need to specify the
column to update:
Update Table1
Set start_date = b.start_date,
end_date = b.end_date,
etc...
FROM Table2 b
Where b.project = Table1.project AND
b.Pjt_entity = Table1.Pjt_entity
David G.
|||David G. wrote:
> Todd wrote:
> Well if you have a 1:1 between the tables, you just need to specify
> the column to update:
> Update Table1
> Set start_date = b.start_date,
> end_date = b.end_date,
> etc...
> FROM Table2 b
> Where b.project = Table1.project AND
> b.Pjt_entity = Table1.Pjt_entity
Left off a table in the FROM clause. See Hari's post instead.
David G.

Query Help

I have a table <ClearanceORDER>
The Fields of interest are <ClearDate> , <ClearanceType>, <ClearanceOrderID>
The Problem:
When an order is Canceled (CNCL) or Change of address (COA) there is an
entry in this table for it. The entry should look like this:
ClearDate ClearanceType ClearanceOrderID
09/14/2007 COA 212
canceled order
07/09/2007 CNCL 547
Change of address
A person can NOT be 'canceled' and 'change of address' in the same
clearanceorderid and the same cleardate!
IN an other way.
How can I list all the records that have COA and CNCL with the same
cleardate and clearanceorderid?
I dont know how else to explain this.
Scott BurkeScott,
To prevent this from happening in the future, you could add an unique
index/constraint on ClearDate and ClearanceOrderID, so there will be only
one entry per day and per OrderID, no matter what type that is.
To display what entries in the table break that rule, use this:
select ClearanceOrderID, ClearDate
from ClearanceOrder
group by ClearanceOrderID, ClearDate
having count(*) > 1
Andrei.
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:5497C818-B7C0-4A17-835E-2AA139899281@.microsoft.com...
>I have a table <ClearanceORDER>
> The Fields of interest are <ClearDate> , <ClearanceType>,
> <ClearanceOrderID>
> The Problem:
> When an order is Canceled (CNCL) or Change of address (COA) there is an
> entry in this table for it. The entry should look like this:
> ClearDate ClearanceType ClearanceOrderID
> 09/14/2007 COA 212
> canceled order
> 07/09/2007 CNCL 547
> Change of address
> A person can NOT be 'canceled' and 'change of address' in the same
> clearanceorderid and the same cleardate!
> IN an other way.
> How can I list all the records that have COA and CNCL with the same
> cleardate and clearanceorderid?
> I dont know how else to explain this.
> Scott Burke
>|||Thanks Andrei !
For some dam reason I thought I had to generate a list of COA and CNCL then
compare them.
Tunnal vission I guess. :)
Thanks again.
Just for giggles......
is is possibe to do the above?
Scott Burke
"Andrei" wrote:
> Scott,
> To prevent this from happening in the future, you could add an unique
> index/constraint on ClearDate and ClearanceOrderID, so there will be only
> one entry per day and per OrderID, no matter what type that is.
> To display what entries in the table break that rule, use this:
> select ClearanceOrderID, ClearDate
> from ClearanceOrder
> group by ClearanceOrderID, ClearDate
> having count(*) > 1
>
> Andrei.
> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
> news:5497C818-B7C0-4A17-835E-2AA139899281@.microsoft.com...
> >I have a table <ClearanceORDER>
> > The Fields of interest are <ClearDate> , <ClearanceType>,
> > <ClearanceOrderID>
> >
> > The Problem:
> > When an order is Canceled (CNCL) or Change of address (COA) there is an
> > entry in this table for it. The entry should look like this:
> > ClearDate ClearanceType ClearanceOrderID
> > 09/14/2007 COA 212
> > canceled order
> > 07/09/2007 CNCL 547
> > Change of address
> >
> > A person can NOT be 'canceled' and 'change of address' in the same
> > clearanceorderid and the same cleardate!
> >
> > IN an other way.
> > How can I list all the records that have COA and CNCL with the same
> > cleardate and clearanceorderid?
> >
> > I dont know how else to explain this.
> > Scott Burke
> >
>
>sql

query help

Here is a simplified example of what I need to accomplish.

I have a table that keeps track of plastic balls in tubs. Based on a bit column the record is either defining balls added to a tub or taken away. When the record is defined as adding balls to a tub it will say how many and what color, but when the flag says they have been taken away from a tub I only know the number removed and not the color.

There are multiple tubs and multiple colors. So say tub1 has 20 balls in it (50% red, %25 green, and %25 blue). Also say tub2 has 10 balls in it (100% red). This is our starting point.

Now on day one, 5 balls from tub two are put into tub one. So we know that 5 balls of 100% red are put into tub one. This means that tub one now has 25 balls in it. By doing some weighted percentages, tub one now has these percentages: red = 60%, green = 20%, and blue = 20%.

Say on day two however, 5 balls are removed from tub one and placed back into tub two. We cannot say anything about the colors, but that they are: .6red, .2green, and .2 blue. So if we want a percentage for tub two on day 2 we now get: .8red, .1green and .1blue.

The math for the new percentage is I believe = ((originalPercent * originalCount) + (addedPercent * addedAmount)) / newTotalBallCount

I need a query that will give me the percentages of the different colors in the tub for any given day. This is really a running percentage that has to take every transaction into account.

This is a complicated query fro me to figure out, but can someone point me in the right direction?

LLeuthard wrote:

Say on day two however, 5 balls are removed from tub one and placed back into tub two. We cannot say anything about the colors, but that they are: .6red, .2green, and .2 blue. So if we want a percentage for tub two on day 2 we now get: .8red, .1green and .1blue.

too superfluous.

is this thing about permutation and combination/? how did u say taht 6r,2g,2b?

|||Are you saying it is not worth my time or that it is impossible? Is there a stored proc I could write that would do this easily?|||

I got .6Red .2Blue and .2Green by saying that:

5 balls of 100% red were moved to tub1. so tub1 originally has a estimate of .5*20 = 10Red balls.

Take the estimate of 10Red and add the estimate of 5Red and we get an estimate of 15Red out of 25 in tub1. This makes for .6Red at the end of day 1.

Do the same with the others.

Query help

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)

-- select statment
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)

--selecting all record
select * from @.test

need helpDo you want to delete all except the last date in each account or only delete the earliest date in each account?|||

Dhaval:

I looked through what you requested. See if below is what you mean

Dave

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1180, '07/05/2006',5 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

/*
--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)
*/

print '-- '
print '-- Records before deletions: -- '
print '--'
select * from @.test order by accountNo, invoiceDate

delete from @.test
from ( select accountNo,
min (invoiceDate) as min_invoiceDate
from @.test
group by accountNo
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.min_invoiceDate = a.invoiceDate

delete from @.test
from (
select accountNo,
invoiceDate,
min (dex_row_id) min_dex_row_id,
count(*) as recCt
from @.test
group by accountNo,
invoiceDate
having count(*) > 1
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.invoiceDate = a.invoiceDate
and x.min_dex_row_id <> a.dex_row_id

-- select statment
/*
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)
*/

--selecting all record
print ' '
print ' '
print '-- '
print '-- Records after deletions: -- '
print '--'
select * from @.test

--need help

-- --
-- -- Records before deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-04-05 00:00:00.000 3
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1180 2006-07-05 00:00:00.000 5
-- 1181 2006-05-05 00:00:00.000 3
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2
-- 1182 2006-06-05 00:00:00.000 1

-- --
-- -- Records after deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2

Query Help

I have this scenario. What will be my query?

Table:

Account#, Name, RMR, Billing_Date, Invoice#

1000,Dave,50, 5/1/2006,10

1000,Dave,50, 6/1/2006,11

1000,Dave,50, 7/1/2006,12

1000,Dave,50, 8/1/2006,13

1000,Dave,50, 9/1/2006,14

2000,Al,50, 5/15/2006,15

2000,Al,50, 6/15/2006,16

2000,Al,50, 7/15/2006,17

2000,Al,50, 8/15/2006,18

3000,Jim,50, 8/10/2006,19

3000,Jim,50, 9/10/2006,20

3000,Jim,50, 10/10/2006,21

I use this query to calculate revenue sharing.

Account hit for revenue sharing after we bill the 4^th billing month. In this case for Account# 1000 qualified after 8/1/2006, 2000 – 8/15/2006 and 3000 will be qualified after 11/10/2006. Each month we will pay 4% of RMR for all qualified accounts.

I want a query result with return for each month and find out how many accounts are qualified for each month.

In this case:

Septmeber-2006 -1000

Septmeber-2006 -2000

Octomber-2006 -1000

Could you define your result again based on the sample data you posted, which will help to understand your question. Thanks.|||

Dhaval:

I was able to mock-up what you described below. Is this more-or-less what you are looking for?

Dave

declare @.rmrMockUp table
( Account# integer not null,
Name varchar (20) not null,
RMR numeric (9,2) not null,
Billing_Date datetime not null,
Invoice# integer not null
)

insert into @.rmrMockUp values ( 1000, 'Dave', 50, '5/1/2006', 10 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '6/1/2006', 11 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '7/1/2006', 12 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '8/1/2006', 13 )
insert into @.rmrMockUp values ( 1000, 'Dave', 50, '9/1/2006', 14 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '5/15/2006', 15 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '6/15/2006', 16 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '7/15/2006', 17 )
insert into @.rmrMockUp values ( 2000, 'Al', 50, '8/15/2006', 18 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '8/10/2006', 19 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '9/10/2006', 20 )
insert into @.rmrMockUp values ( 3000, 'Jim', 50, '10/10/2006', 21 )

declare @.monthList varchar (250)
set @.monthList = 'January February March April May June July August September October November December '

select Account#,
rtrim (substring (@.monthList,
month (dateadd (month, 1, Billing_date))*10-9, 10)) + '-' +
convert (char (4), year (dateadd (month, 1, Billing_date)))
as RMR_Month
from
( select Account#,
rmr,
Rank() over (partition by Account# order by Billing_Date)
as activeMonths,
Billing_Date
from @.rmrMockUp
) x
where activeMonths >= 4
order by Billing_Date, Account#

-- -
-- Query Output:
-- -

-- Account# RMR_Month
-- --
-- 1000 September-2006
-- 2000 September-2006
-- 1000 October-2006

query help

I have a table with multiple records for an identical person. It is a rolling history of the applications a user has submitted. How can I grab the most current application by the date most closest to todays date. Obviously one of my fields is an applied date.
I need help in creating a query.select * from table t where datefield = (select max(datefield) from table where userid = t.userid)

Nick

Query help

My table are

Customer: customerId ,name

Order: orderId, customerId, product,date

I want to display all of the customer which have order or not. I want display name, product,date . If the customer do not order I want display only customer name.For example:

Name Product Date

John Video 09/20/2007

Mary -- ----

How can I write sql or sp?

I suggest you do some reading on SQL and joins in particular as this is something that you should learn so you can write these queries yourself.

DECLARE @.CUSTOMERTABLE (customeridint IDENTITY(1,1),name varchar(20))DECLARE @.ORDERSTABLE (orderidint IDENTITY(1,1), customeridint, productvarchar(20), orderdatedatetime)INSERT @.CUSTOMERVALUES ('Fred')INSERT @.CUSTOMERVALUES ('Joe')INSERT @.ORDERSVALUES (1,'Video',GetDate())SELECT c.name, o.product, o.orderdateFROM @.CUSTOMER cLEFTOUTER JOIN @.ORDERS oON o.customerid = c.customerid

|||

use left join instead of inner join

select cust.customerId ,cust.name,ord.orderId, ord.customerId, ord.product,ord.date from Customer cust left outer join orders ord on

cust.CustomerId = ord.CusomerId

|||

Hi,

I think you have to create a cross-tab query, it's ilttle tricky but interesting.

Check these following links

http://www.databasejournal.com/features/mssql/article.php/3521101

http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html

or you can adopt the following solution

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html

Regards,

Sandeep

|||

ASP.NET Dev:

I think you have to create a cross-tab query,

That's not necessary as they aren't pivoting any data (or at least that doesn't appear to be the case based on their description).

|||

Ozo:

How can I write sql or sp?

you can write sql as..

select customer.name, order.product, order.orderdate from customer left outer join order on customer.customerid = order.customerid

and sp as..

CREATE PROCEDURE [dbo].[usp_CustomerOrder]
AS
BEGIN
SET NOCOUNT ON
select customer.name, order.product, order.orderdate from customer leftouter join order on customer.customerid = order.customerid
END

|||

Tahnk you for your helping.

|||

Ozo:

Tahnk you for your helping.

You should also mark all the posts that helped by using the "Mark As Answer" link so that future readers with the same problem will know which methods to use.

|||

I have a new question I want to display the latest order from the customer .Can you help me?

|||

Yes, but please start a new question if you have something else to ask as it helps keep the forum tidy and easier to search.

Query help

Hello Everyone,

Please can you help me with this?

I have an audit table, so there are many id's and modified dates... I am looking to get the last updated record for each ID. This is what I have so far... "which still gives duplicate ID's"

I have see many pages about distincta and max/min... I cannot make sense of it... HELP

1Select *2FROM TabelA3WhereExists (SELECT distinct max (id)as id ,max (ModifiedDate)as ModifiedDate4FROM TabelA)5Order by id

I tried to break it down, and still I get duplicate ID's

1Select *2FROM TabelA3Where idIN (SELECT distinct id4FROM TabelA)5Order by id

Try

SELECT *FROM TableAWHERECONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDEXISTS IN (selectCONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDas [key]from TableAgroup by ID)ORDER BY ID

|||

Thanks for your reply,

I get the following error

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'EXISTS'.

|||

SQL Server 2005:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate, row_number()OVER(partitionby idorderby ModifiedDateDESC)as RowNum

FROM TableA) t

WHERE t.RowNum=1

For SQL Server 2000, you can try:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate,(SELECTcount(*)FROM TABLEA aWHERE a.id=a1.idand a1.ModifiedDate<=a.ModifiedDate)as RowNum

FROM TableA a1) t

WHERE t.RowNum=1

|||

That's why your anAll-Star!....Shot for the help... it works very well.

Thanks B

sql

Query Help

Hi All,

I want to display the data in vertical format though the data is stored horizontally in the datatable.Suppose i have table with five columns-id.a,b,c,d.If i use a select statement that will give me the data in the format-ID A B C D

but now i want to display the data as

ID A

ID B

ID C

ID D

Any help on this pls??I m thinking to use the self join but would it take a long time??

Thanks

select id,a

union

select id,b

union

select id,c

union

select id,d

|||It sounds to me more like you require a pivot query rather than a union. If you have Sql Server 2005 you can do pivot queries but, unfortunately, not in the older versions. There a good article you can readhere that explains how to do pivot queries.|||

You can do pivot tables in Sql Server 2000. Just search for "pivot tables" in the Books Online. The example there was greatly helpful to me with the same issue.

Hope this helps

query help

i have a table that stores employee records. I then have a history employee table that stores changes to each particular employee (one to many ). Everytime a changes is made to an employee I record their old record in a history table. I want to be able to query against the history table and pull up the "last entry". How would I write a query for this, I am recording a timestamp.You haven't shared any DDL, so I'm guessing on keys and column names. But you should get the idea from this...
SELECT *
FROM EmployeeHistory eh
WHERE eh.theDate =
(SELECT MAX(eh2.theDate)
FROM EmployeeHistory eh2
WHERE eh2.EmployeeId = eh.EmployeeId)

|||

You might want to match on a identity field instead. It will be more reliable then a date field. The date field could always have multiple records with the same date.
Nick

|||Depends on the business rules. If there are multiple rows thatwere updated at exactly the same time, which one is most recent? Or should both be displayed?

Query help

I have a project almost (or so I thought!) completed but now I need to add a column to a table which is throwing everything off. Right now I have TableMain which queries TableA. I need to add a column (bit) to TableMain and then run a query that is basicallyif (bitColumn) query TableB, else query TableA.for each row.

TableA contains a listing, TableB is groups where each group contains multiple items from TableA. The columns I want to pull from each table have the same names (ie. regardless of bitColumn, I want TableA.Name or TableB.Name)

I'm not sure how I can go about doing this, I greatly appreciate any help.

Can you please give following details.

Primary key and Fkey in each table

Example input and output data

|||

Paper (id(pkey), name, code, description, clipID(fkey), etc) <= Think of these as a sheet of paper.

Clip(id(pkey), name, description) <= Think of these as a group of papers paperclipped together.

Cart(id(pkey),user,paperID(fkey),qty)

if someone tries to order a paper that has a clipID, they are forced to buy the entire paperclip. My shopping cart works for regular papers, and I have it notifying the user that they are really ordering the paperclip. What I'm stuck on is displaying the shopping cart, etc.

Shopping Cart: Paper1 + Paper2 + Clip1, where Clip1 has Paper3 and Paper4.

I don't know how to make the cart show Clip1 or whether I should just force add each individual item to the shopping cart (making editing qtys difficult), etc. I was thinking about adding an isClip column to the cart and then the stored procudure returns Paper.name or Clip.name depending on whether isClip is 0 or 1... so that it would be

Cart(id(pkey),user,paperID(fkey),qty,isClip)

I don't know how I would write that query, and I'm not sure its even the best design

|||

I would design like this.

Category table

Products Table

Product details table

In category table : categories are paper and clip

In products table: Different papers(only papers not associated with clips) and clips

In product Details table: Papers that goes with clips

Papers that are associated to clips are only sold as a bunch

When user click on paper, show paper details and when user clicks on clip, show all the papers associated to clip by querying from product details table.

|||A better design would be to have ALL papers clipped, even if it is only clipping a single paper. Then the user can only buy clips.|||

With all of the code already written, I'd like to avoid a complete design restructuring.

Motley: this is what I started working on last night until I ran into a problem. Each paper belongs to a certain topic, and in the catalog are listed under topic headings as the sp returns with order by topic. The papers in a clip do not need to (and rarely will) belong to the same topic. I could have a Clips topic that I display first and then the individual topics, but it would involve checking each clip and seeing how many papers reference it; or perhaps adding a Count field to the Clips table? I would still then need to figure out a way to return the correct ordering...I'm open to hearing other suggestions...

|||

edit...yeah, that doesn't work, never mind...

SELECT

clip.id, clip.name, clip.description,

ISNULL(paper.topic,'CLIP')

FROM

clips clip

LEFT JOIN

papers paper

ON

clip.id = paper.kitID

ORDER BY

topic

|||

Basic query:

SELECT c.id as ClipID,c.Name as ClipName,c.Description as ClipDescription,p.id as PaperID,p.name as PaperName, p.code as PaperCode, p.Description as PaperDescription, etc

FROM clip c

JOIN paper p ON (c.PaperID=p.id)

Return clips and how many papers are attached:

SELECT c.id AS ClipID,c.name,c.description,COUNT(*)

FROM clip c

JOIN paper p ON (c.PaperID=p.id)

GROUP BY c.id,c.name,c.description

Of course this assumes that each paper has a clip associated with it.

With your original structure:

SELECT s.*,name,description

FROM cart s

JOIN clip c ON (s.ID=c.id and s.isClip=1)

UNION

SELECT s.*,name,description

FROM cart s

JOIN paper p ON (s.ID=p.id and s.isClip=0)

OR

SELECT s.*,CASE WHEN c.id IS NOT NULL THEN c.name ELSE p.name END as Name, CASE WHEN c.id IS NOT NULL THEN c.description ELSE p.description END as Description

FROM cart s

LEFT JOIN clip c ON (c.id=s.id and s.isClip=1)

LEFT JOIN paper p ON (p.id=s.id and s.isClip=0)

sql

Query Help

I have two tables: Trans & History. For each record in Trans, there can be
many in History. I want to return records in the Trans table that have a
certain status in the History table but not other statuses. Here's an
example:
select transactions.transaction_id,
name
from transactions
inner join statusHistory SH1 on
transactions.transaction_id = SH1.transaction_id
where eMonth = '10' and eYear = '2005' and
SH1.status in ('Rec', 'R1Rec') and
SH1.status not in ('Coll', 'RTR')
The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
want to filter out those that contain either 'Coll' or 'RTR'On Wed, 23 Nov 2005 10:36:02 -0800, Eric wrote:

>I have two tables: Trans & History. For each record in Trans, there can b
e
>many in History. I want to return records in the Trans table that have a
>certain status in the History table but not other statuses. Here's an
>example:
>select transactions.transaction_id,
> name
>from transactions
>inner join statusHistory SH1 on
> transactions.transaction_id = SH1.transaction_id
>where eMonth = '10' and eYear = '2005' and
> SH1.status in ('Rec', 'R1Rec') and
> SH1.status not in ('Coll', 'RTR')
>
>The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
>want to filter out those that contain either 'Coll' or 'RTR'
Hi Eric,
Since you didn't post your table structure (CREATE TABLE statements),
sample data (INSERT statements) and required output, I'll have to do
some wild guess about which of your unprefixed columns belog to which
table. You'll probably have to make some changes. But here's a general
outline:
SELECT T.transaction_id, T.name
FROM transactions AS T
WHERE T.eMonth = '10'
AND T.eYear = '2005'
AND EXISTS
(SELECT *
FROM statusHistory AS SH1
WHERE SH1.transaction_id = T.transaction_id
AND SH1.status IN ('Rec', 'R1Rec'))
AND NOT EXISTS
(SELECT *
FROM statusHistory AS SH2
WHERE SH2.transaction_id = T.transaction_id
AND SH2.status IN ('Col1', 'RTR'))
BTW, why store a date in seperate columns eMonth and eYear? Isn't that
what the datetime datatype is for?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi There,
Use LEFT JOIN instead of Join
With Warm regards
Jatinder Singh

Query Help

I have a database table with data like this (some fields ommited):
reserveID owner creator
-- -- --
39009 1 0
39009 0 1
39100 0 1
I would like to do a query that would return all rows in which a reserveID
has a row with creator = 1 but no rows with owner = 1. So with the rows in m
y
example above, 39100 would be returned by my query but 39009 would not.
I've been experimenting with different queries with out much luck. How would
I accomplish this?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You might also want to learn why rows and columns are nothing
whatsoever like records and fields. You will never write RDBMS until
you do. Let's make a wild guess with bad names, no constraints, etc.
CREATE TABLE Reservations
(reservation_nbr INTEGER NOT NULL PRIMARY KEY,
reservation_owner INTEGER NOT NULL,
reservation_creator INTEGER NOT NULL);
SELECT R1.reservation_nbr, R1.reservation_owner,
R1.reservation_creator
FROM Reservations AS R1
WHERE R1.reservation_creator = 1
AND NOT EXISTS
(SELECT *
FROM Reservations AS R2
WHERE R2.reservation_owner = 1
A ND R1.reservation_creator = 1);|||Hi There,
I think this will solve your problem.
Select * From tmpData4 T4 Where cr = 1 And
Not Exists
(
Select * from tmpData4 T5 Where own = 1 And T5.res = T4.res
)
Where tmpdata4 is your tableName
With Warm regards
Jatinder Singh|||select * from <tblname> where creator = 1 and reserveID not in (select
distinct reserveid from <tblname> where owner = 1)
i hope this will work
with reagards
Rajeev Shukla|||not sure what your requirements are, but a let me have a shot in the
dark, assuming that reserveID is not nullable:
select * from your_table
where reserveID in(select reserveID from your_table where creator = 1)
and reserveID NOT in(select reserveID from your_table where owner = 1)
if reserveId is nullable , go for EXISTS/NOT EXISTS instead of IN/NOT IN

Query Help

Dear All,
I have a single table name "Remark". It contains the item code, date,
status and remark. If I want to build a query that select items out which
the latest status is still in "pending" for example. How do I make it?
Thanks
Best Rdgs
EllisEllis
SELECT <columns list>
FROM Remarks WHERE[date]=(SELECT TOP 1 [date]
FROM Remarks R WHERE
R.Itemcode=Remarks.Itemcode
ORDER BY [date] DESC)
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:uSSJ$UXbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I have a single table name "Remark". It contains the item code,
date,
> status and remark. If I want to build a query that select items out which
> the latest status is still in "pending" for example. How do I make it?
> Thanks
> Best Rdgs
> Ellis
>sql

Query help

I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanksrick,
try this:
update ts
set tsfbr1=(select t.tsfbr1 from ts t where t.[datetime]=(select
max(t2.[datetime]) from ts t2 where t2.[datetime]<ts.[datetime] and tsfbr1
is not null))
where tsfbr1 is null and tsfbr1on<>0
and please, don't use reserved words or typenames for column names :)
dean
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
>I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try
select t1.[datetime], "tsfbr1" =
CASE
WHEN t1.tsfbr1 IS NULL AND t1.tsfbr1on = 0 THEN NULL
WHEN t1.tsfbr1 IS NULL THEN (SELECT TOP 1 t2.tsfbr1 FROM TS t2
WHERE (t2.[datetime] < t1.[datetime] AND t2.tsfbr1 IS NOT NULL) ORDER
BY t2.[datetime] DESC)
ELSE t1.tsfbr1
END,
t1.tsfbr1on
FROM TS t1
This will produce the output that you want through a SELECT.
I'll try producing an UPDATE statement that accomplishes the same thing and
post back.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>|||Try:
update t1
set
TSFBR1 = t2.TSFBR1
from
TS t1
join
TS t2 on t2.[Datetime] =
(
select
max (t3.[Datetime])
from
TS t3
where
t3.Datetime < t1.Datetime
and t3.TSFBR1 is not null
)
where
t1.TSFBR1 is null
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:FCBBDF93-0B5E-4406-A7C4-019C1336A097@.microsoft.com...
I have a table with the following structure
CREATE TABLE [dbo].[TS] (
[Datetime] smalldatetime NOT NULL ,
TSFBR1 real null,
TSFBR1On tinyint
)
Data set is:
Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
Insert into TS values('2005-01-03 00:00:00', null, 25)
Insert into TS values('2005-01-04 00:00:00', null, 1)
Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
Insert into TS values('2005-01-06 00:00:00', null, 61)
Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
Insert into TS values('2005-01-08 00:00:00', null, 42)
Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
Insert into TS values('2005-01-10 00:00:00', null, 0)
I need help with query that will populate null's with most recent previous
date's non null value considering if TSFBR1ON is not zero.
The result set should look like:
'2005-01-01 00:00:00', 23.4, 12
'2005-01-02 00:00:00', 25.4, 23
'2005-01-03 00:00:00', 25.4, 25 -- previous date's value
'2005-01-04 00:00:00', 25.4, 1 -- changed
'2005-01-05 00:00:00', 28.7, 26
'2005-01-06 00:00:00', 28.7, 61 -- Changed
'2005-01-07 00:00:00', 22.4, 52
'2005-01-08 00:00:00', 22.4, 42 -- changed
'2005-01-09 00:00:00', 35.7, 32
'2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
Any help will be greatly appreciated.
Thanks|||Thanks Guys. I appreciate it.
"Rick" wrote:

> I have a table with the following structure
> CREATE TABLE [dbo].[TS] (
> [Datetime] smalldatetime NOT NULL ,
> TSFBR1 real null,
> TSFBR1On tinyint
> )
> Data set is:
> Insert into TS values('2005-01-01 00:00:00', 23.4, 12)
> Insert into TS values('2005-01-02 00:00:00', 25.4, 23)
> Insert into TS values('2005-01-03 00:00:00', null, 25)
> Insert into TS values('2005-01-04 00:00:00', null, 1)
> Insert into TS values('2005-01-05 00:00:00', 28.7, 26)
> Insert into TS values('2005-01-06 00:00:00', null, 61)
> Insert into TS values('2005-01-07 00:00:00', 22.4, 52)
> Insert into TS values('2005-01-08 00:00:00', null, 42)
> Insert into TS values('2005-01-09 00:00:00', 35.7, 32)
> Insert into TS values('2005-01-10 00:00:00', null, 0)
> I need help with query that will populate null's with most recent previous
> date's non null value considering if TSFBR1ON is not zero.
> The result set should look like:
> '2005-01-01 00:00:00', 23.4, 12
> '2005-01-02 00:00:00', 25.4, 23
> '2005-01-03 00:00:00', 25.4, 25 -- previous date's value
> '2005-01-04 00:00:00', 25.4, 1 -- changed
> '2005-01-05 00:00:00', 28.7, 26
> '2005-01-06 00:00:00', 28.7, 61 -- Changed
> '2005-01-07 00:00:00', 22.4, 52
> '2005-01-08 00:00:00', 22.4, 42 -- changed
> '2005-01-09 00:00:00', 35.7, 32
> '2005-01-10 00:00:00', null, 0 -- should not change as TSFBR1On is 0
> Any help will be greatly appreciated.
> Thanks
>

Query help

Hi,
I have an employee table having fields empid and managerid.
I need a query which returns all the children once the parentid is given.
Here is the script
create table employee (empid int , parentID int )
GO
insert into employee select 1,null
insert into employee select 2,1
insert into employee select 3,1
insert into employee select 4,3
insert into employee select 5,3
insert into employee select 6,4
insert into employee select 7,4
insert into employee select 8,5
insert into employee select 9,5
insert into employee select 10,6
insert into employee select 11,6
go
So, when i pass the empid 3, it should return all the children under 3; i.e.
4,5,6,7,8,9,10 and 11
Thanking in advance
regards
Laraselect * from employee as e
where e.parentid=<managerID>
"Lara" <lara169@.gmail.com> wrote in message
news:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
>
> Thanking in advance
> regards
> Lara
>|||Thanks martin,
But this is not the one i needed. i need all the children under this
parentid
"Martin" <x@.y.z> wrote in message
news:%23tqUlX18FHA.636@.TK2MSFTNGP10.phx.gbl...
> select * from employee as e
> where e.parentid=<managerID>
> "Lara" <lara169@.gmail.com> wrote in message
> news:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
>|||Do you mean recursively?
"Lara" <lara169@.gmail.com> wrote in message
news:exAsfZ18FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Thanks martin,
> But this is not the one i needed. i need all the children under this
> parentid
>
> "Martin" <x@.y.z> wrote in message
> news:%23tqUlX18FHA.636@.TK2MSFTNGP10.phx.gbl...
>|||Select
c.*
from employee as m
left join employee as c
on c.ParentID = m.EmpID
"Lara" <lara169@.gmail.com>, haber iletisinde unlar
yazd:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
>
> Thanking in advance
> regards
> Lara
>|||Something like this (I've not tested it - there are some syntax errors)
declare @.tab table (empid int ,parentid int)
declare @.rowsaffected int
set @.rowsaffected=-1
declare @.empid int
set @.empid=3
insert into @.tab (select * from employee where empid=@.empid)
set @.rowsaffected=@.@.rowcount
while @.rowsaffected<>0 -- stop when there are no more employees added to
table
begin
insert into @.tab (select * from employee
where
parentid in (select * empid from @.tab) and -- select next level down
from those already selected
empid not in (select empid from @.tab) -- don't double select
)
set @.rowsaffected=@.@.rowcount
end
select empid from @.tab where empid<>@.empid -- don't return orginal employee
id
"Martin" <x@.y.z> wrote in message
news:Oi$Jzb18FHA.808@.TK2MSFTNGP09.phx.gbl...
> Do you mean recursively?
> "Lara" <lara169@.gmail.com> wrote in message
> news:exAsfZ18FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||Here's the correct syntax
declare @.tab table (empid int ,parentid int)
declare @.rowsaffected int
set @.rowsaffected=-1
declare @.empid int
set @.empid=3
insert into @.tab(empid,parentid) (select empid,parentid from employee where
empid=@.empid)
set @.rowsaffected=@.@.rowcount
while @.rowsaffected<>0
begin
insert into @.tab(empid,parentid) (select empid,parentid from employee
where
parentid in (select empid from @.tab) and -- select next level down from
those already selected
empid not in (select empid from @.tab) -- don't double select
)
set @.rowsaffected=@.@.rowcount
end
select empid from @.tab where empid<>@.empid -- don't return orginal employee
id
"Martin" <x@.y.z> wrote in message
news:eA1O6n18FHA.3984@.TK2MSFTNGP11.phx.gbl...
> Something like this (I've not tested it - there are some syntax errors)
> declare @.tab table (empid int ,parentid int)
> declare @.rowsaffected int
> set @.rowsaffected=-1
> declare @.empid int
> set @.empid=3
> insert into @.tab (select * from employee where empid=@.empid)
> set @.rowsaffected=@.@.rowcount
> while @.rowsaffected<>0 -- stop when there are no more employees added to
> table
> begin
> insert into @.tab (select * from employee
> where
> parentid in (select * empid from @.tab) and -- select next level down
> from those already selected
> empid not in (select empid from @.tab) -- don't double select
> )
> set @.rowsaffected=@.@.rowcount
> end
> select empid from @.tab where empid<>@.empid -- don't return orginal
> employee id
>
> "Martin" <x@.y.z> wrote in message
> news:Oi$Jzb18FHA.808@.TK2MSFTNGP09.phx.gbl...
>|||Have a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
When I say 'look' I mean copy/paste/test.
ML|||Lara (lara169@.gmail.com) writes:
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
Here is how you can do this on SQL 2005:
with emp (empid, parentID) as
(select empid, parentID = NULL
from employee
where empid = 3
union all
select e.empid, e.parentID
from emp
join employee e on e.parentID = emp.empid)
select empid from emp where empid <> 3
I reckon that you are likely to still use SQL 2000, but I wanted to show
that this is a lot easier on SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Martin, But can we do it in a single query ?
"Martin" <x@.y.z> wrote in message
news:%23loxEq18FHA.4084@.TK2MSFTNGP10.phx.gbl...
> Here's the correct syntax
>
> declare @.tab table (empid int ,parentid int)
> declare @.rowsaffected int
> set @.rowsaffected=-1
> declare @.empid int
> set @.empid=3
> insert into @.tab(empid,parentid) (select empid,parentid from employee
> where empid=@.empid)
> set @.rowsaffected=@.@.rowcount
> while @.rowsaffected<>0
> begin
> insert into @.tab(empid,parentid) (select empid,parentid from employee
> where
> parentid in (select empid from @.tab) and -- select next level down from
> those already selected
> empid not in (select empid from @.tab) -- don't double select
> )
> set @.rowsaffected=@.@.rowcount
> end
> select empid from @.tab where empid<>@.empid -- don't return orginal
> employee id
> "Martin" <x@.y.z> wrote in message
> news:eA1O6n18FHA.3984@.TK2MSFTNGP11.phx.gbl...
>sql

Query help

I'm trying to build a somewhat complicated query, well for me at least, and
I just can't figure it out.
I have a table called Inventory and a table called Auctions. The Auctions
table has a foreign key called InventoryID that links back to the Inventory
table. What I want to be able to do is to retrieve everything from the
Inventory table, and the most recent associated record in the Auctions
table. To complicate things, some Inventory items won't have any auctions
records so the columns that are returned from the auctions table need to be
null.
Here's the keys for the two tables:
Inventory Auctions
--
InventoryID AuctionID
InventoryID
An inventory item can have 0 or more auction records. The AuctionID field is
an identity field so the most recent auction record for an inventory item
can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
AuctionID DESC
The result of the query would give me a single row that would contain all
the information from the inventory table and all of the information from the
most recent auction record. If no auction record was found, then these
fields would be null.
The fact that an inventory item might not have any auction records means
that I can't do a INNER JOIN, because if I do, any inventory items that
don't have at least one auction record won't get returned.
Most of my time in SQL has been spent doing pretty basic queries so I'm not
even sure where to begin to look for the best way to pull this off. If
someone could just point me in the right direction, I'll do the legwork, I
just don't know what my options are.
I appreciate any input or advice!Rachel
> table. What I want to be able to do is to retrieve everything from the
> Inventory table, and the most recent associated record in the Auctions
Since you did not provide ddl and sample data I tested it on Northwind db .
SELECT O.OrderId, Quantity from Orders O JOIN
(
SELECT OrderId,MAX(Quantity) Quantity FROM
[Order Details] GROUP BY OrderId
) OD ON OD.OrderId=O.OrderId
"Rachel Devons" <nononon@.nononon.com> wrote in message
news:ufAKr$0BFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I'm trying to build a somewhat complicated query, well for me at least,
and
> I just can't figure it out.
> I have a table called Inventory and a table called Auctions. The Auctions
> table has a foreign key called InventoryID that links back to the
Inventory
> table. What I want to be able to do is to retrieve everything from the
> Inventory table, and the most recent associated record in the Auctions
> table. To complicate things, some Inventory items won't have any auctions
> records so the columns that are returned from the auctions table need to
be
> null.
> Here's the keys for the two tables:
> Inventory Auctions
> --
> InventoryID AuctionID
> InventoryID
> An inventory item can have 0 or more auction records. The AuctionID field
is
> an identity field so the most recent auction record for an inventory item
> can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
> AuctionID DESC
> The result of the query would give me a single row that would contain all
> the information from the inventory table and all of the information from
the
> most recent auction record. If no auction record was found, then these
> fields would be null.
> The fact that an inventory item might not have any auction records means
> that I can't do a INNER JOIN, because if I do, any inventory items that
> don't have at least one auction record won't get returned.
> Most of my time in SQL has been spent doing pretty basic queries so I'm
not
> even sure where to begin to look for the best way to pull this off. If
> someone could just point me in the right direction, I'll do the legwork, I
> just don't know what my options are.
> I appreciate any input or advice!
>
>|||Rachel,
It should look something like this:
select
I.InventoryID, <other inventory columns>,
A.AuctionID, <other auction columns>
from Inventory as I left outer join Auctions as A
on A.InventoryID = I.InventoryID
and A.AuctionID = (
select max(AuctionID) from Auctions as Acopy
where Acopy.InventoryID = A.InventoryID
)
The left outer join will make sure each inventory item
shows up in the result (since the join has no WHERE
clause filtering out any inventory items).
The AuctionID = (select max...) will make sure that
whenever there are multiple AuctionID values for one
inventory item, you will only see the latest one.
A clustered index on Auctions(InventoryID, AuctionID)
should help if you don't already have one.
If this doesn't work out, post back and explain what
isn't working, preferably including CREATE TABLE statements
for your tables (simplified if necessary) and a few rows of
made-up by representative sample data as INSERT .. VALUES
statements to help explain what you need.
Steve Kass
Drew University
Rachel Devons wrote:

>I'm trying to build a somewhat complicated query, well for me at least, and
>I just can't figure it out.
>I have a table called Inventory and a table called Auctions. The Auctions
>table has a foreign key called InventoryID that links back to the Inventory
>table. What I want to be able to do is to retrieve everything from the
>Inventory table, and the most recent associated record in the Auctions
>table. To complicate things, some Inventory items won't have any auctions
>records so the columns that are returned from the auctions table need to be
>null.
>Here's the keys for the two tables:
>Inventory Auctions
>--
>InventoryID AuctionID
> InventoryID
>An inventory item can have 0 or more auction records. The AuctionID field i
s
>an identity field so the most recent auction record for an inventory item
>can be found by: SELECT TOP 1 FROM Auctions WHERE InventoryID=XXX ORDER BY
>AuctionID DESC
>The result of the query would give me a single row that would contain all
>the information from the inventory table and all of the information from th
e
>most recent auction record. If no auction record was found, then these
>fields would be null.
>The fact that an inventory item might not have any auction records means
>that I can't do a INNER JOIN, because if I do, any inventory items that
>don't have at least one auction record won't get returned.
>Most of my time in SQL has been spent doing pretty basic queries so I'm not
>even sure where to begin to look for the best way to pull this off. If
>someone could just point me in the right direction, I'll do the legwork, I
>just don't know what my options are.
>I appreciate any input or advice!
>
>
>|||Steve,
Thank you very much! That was much simpler than I had thought!
"Steve Kass" <skass@.drew.edu> wrote in message
news:u4%23xfZ1BFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Rachel,
> It should look something like this:
> select
> I.InventoryID, <other inventory columns>,
> A.AuctionID, <other auction columns>
> from Inventory as I left outer join Auctions as A
> on A.InventoryID = I.InventoryID
> and A.AuctionID = (
> select max(AuctionID) from Auctions as Acopy
> where Acopy.InventoryID = A.InventoryID
> )
> The left outer join will make sure each inventory item
> shows up in the result (since the join has no WHERE
> clause filtering out any inventory items).
> The AuctionID = (select max...) will make sure that
> whenever there are multiple AuctionID values for one
> inventory item, you will only see the latest one.
> A clustered index on Auctions(InventoryID, AuctionID)
> should help if you don't already have one.
> If this doesn't work out, post back and explain what
> isn't working, preferably including CREATE TABLE statements
> for your tables (simplified if necessary) and a few rows of
> made-up by representative sample data as INSERT .. VALUES
> statements to help explain what you need.
> Steve Kass
> Drew University
> Rachel Devons wrote:
>
and
Inventory
be
is
BY
the
not
I|||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.
Could I make a sugesstion, based on guessing at your narrative and lack
of DDL?
CREATE TABLE Inventory
(item_id INTEGER NOT NULL,
.=2E.);
CREATE TABLE InventoryHistory
(item_id INTEGER NOT NULL
REFERENCES IInventory(item_id)
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
item_status CHAR(3) NOT NULL
CHECK (item_status IN ('rec', 'bid', 'sld', ...)),
.=2E.
PRIMARY KEY (item_id, start_date));
This will let you build a status code system and track it over time.
An item has to be received, cataloged, bid on, sold, damaged, unsold,
etc. This lets you track the history and validate the transitions --
an item cannot be sold before it is cataloged, etc.
contain all the information from the inventory table and all of the
info=ADrmation from the
most recent auction record [sic]. If no auction record [sic] was found,
=ADthen these fields [sic] would be null. <<
Row are not records and columns are not fields. If you don't start
thinking in relational terms, you will imitate paper forms and
sequential file systems in SQL.
See what I mean about mimicking a sequential file or paper list? By
definition, an IDENTITY is a non-relational, unverifible physical
locator (like a record number on a magnetic tape) and cannot ever be a
relational key. You have a natural key with (item_id, start_date) to
track its status changes. Your query is now simple -- look for
"end_date IS NULL" and return that row.
Most of the time, a compicated query for a simple basic fact comes from
a bad data model.