find the rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

find the rows

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-04-29 : 08:08:32

i have this table:

Date       Quant  Product
1.1.10       10     a
2.1.10       0      a
3.1.10       0      a
4.1.10       4      a
1.1.10       0      b
2.1.10       1      b
3.1.10       0      b
4.1.10       4      b

i want to see

number with 0 product
2 a
1 b

b have one day with 0, because i count the days with 0 between last day and sencond max day that day is not 0 (so bwtween them i have 0-3.1.10)
i do it in sql 2000

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-29 : 09:04:48

select count(*),product from table
where Quant =0
group by product

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-04-29 : 09:09:06

if you do that ,then in product b i will get 2 but i need to get 1.

i want to count quant=0 between two last quant <>0
(i mean between quant=1 and quant=4 for product b ,i have one 0)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 09:09:45

Does this do what you need? NOTE: It assume that there is a date entry for each product/date combination. It assumes any date not specified for a specific product to be a 0 quantity. eg

Date       Quant  Product
1.1.10       10     a
2.1.10       0      a
5.1.10       4      a

Will be assumed to be

Date       Quant  Product
1.1.10       10     a
2.1.10       0      a
3.1.10       0      a
4.1.10       0      a
5.1.10       4      a

So a will be 3. However

Date       Quant  Product
1.1.10       10     a
5.1.10       4      a

Will not return a result for a at all. If you have a record for each product/date, then this won't be an issue. If not, you may need something more specific.

SELECT MAX(NumberWith0) AS NumberWith0, Product
FROM (	SELECT t.Date, t.Product, 
			DATEDIFF(d, t.Date, 
				COALESCE(
					(SELECT MIN(Date) 
					FROM tableName
					WHERE Date >= t.Date
						AND Product = t.Product
						AND Quant != 0), 
					(SELECT MAX(Date) + 1
					FROM tableName
					WHERE Date >= t.Date
						AND Product = t.Product
						AND Quant = 0)
				)
			) AS NumberWith0
		FROM tableName t
		WHERE t.Quant = 0 ) z
GROUP BY Product

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-29 : 09:33:14

See if this works for all set of data


declare @t table (Date      datetime ,Quant  int ,Product char(1))
insert into @t
select '1.1.10',       10,     'a' union all
select '2.1.10',       0,      'a' union all
select '3.1.10',       0,      'a' union all
select '4.1.10',       4,      'a' union all
select '1.1.10',       0,      'b' union all
select '2.1.10',       1,      'b' union all
select '3.1.10',       0,      'b' union all
select '4.1.10',       4,      'b'

select count(*),t1.product from
(
select *, row_number() over(partition by product order by date) as sno
from @t
) as t1 
inner join
(
select product,min(sno) as sno from
(
select *, row_number() over(partition by product order by date) as sno
from @t
) as t
where Quant<>0
group by product
) as t2
on t1.product=t2.product and t1.sno>=t2.sno
where t1.quant=0
group by t1.product

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-04-29 : 09:33:57

DBA in the making
yse,3 example that you write can be.
i check it and i get wrong result.

how do you find the second date max when the quant<>0 for each product.?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-29 : 09:35:39

Try my previous solution

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 10:02:18

quote:
Originally posted by madhivanan

Try my previous solution

Has a similar problem to mine. If you try it with the following sample data, it returns a as 3, but it should be 2

select '1.1.10',       10,     'a' union all
select '2.1.10',       0,      'a' union all
select '3.1.10',       0,      'a' union all
select '4.1.10',       4,      'a' union all
select '5.1.10',       0,      'a' union all
select '6.1.10',       4,      'a' union all
select '1.1.10',       0,      'b' union all
select '2.1.10',       1,      'b' union all
select '3.1.10',       0,      'b' union all
select '4.1.10',       4,      'b'

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-30 : 04:07:44

Ok. Let us see what OP says

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-04-30 : 09:12:12

madhivanan

could you do it without "row number",i wrote in sql 2000 .

i want to exaplain again my question:
i want to count how many 0 i have since the last quant!=0.

Date Quant Product
1.1.10 10 a
2.1.10 0 a
3.1.10 0 a
4.1.10 4 a
5.1.10 0 a
6.1.10 0 a
7.1.10 0 a
8.1.10 3 a
1.1.10 0 b
2.1.10 1 b
3.1.10 0 b

product countZero
a 3
b 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-30 : 09:22:25

quote:
Originally posted by inbs

madhivanan

could you do it without "row number",i wrote in sql 2000 .

i want to exaplain again my question:
i want to count how many 0 i have since the last quant!=0.

This may not be very effecient



declare @t table (Date      datetime ,Quant  int ,Product char(1))
insert into @t
select '1.1.10',       10,     'a' union all
select '2.1.10',       0,      'a' union all
select '3.1.10',       0,      'a' union all
select '4.1.10',       4,      'a' union all
select '1.1.10',       0,      'b' union all
select '2.1.10',       1,      'b' union all
select '3.1.10',       0,      'b' union all
select '4.1.10',       4,      'b'

select count(*),t1.product from
(
select *, (select count(*) from @t where Product=t.Product and date<=t.date) as sno
from @t as t
) as t1 
inner join
(
select product,min(sno) as sno from
(
select *, (select count(*) from @t where Product=t.Product and date<=t.date) as sno
from @t as t
) as t
where Quant<>0
group by product
) as t2
on t1.product=t2.product and t1.sno>=t2.sno
where t1.quant=0
group by t1.product

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-04-30 : 09:40:02

it is not very effecient,because my table have 3M rows and it run very low.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-30 : 09:46:01

Is Date always unique for each Product?

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-04-30 : 11:08:32

i think that i figure it out:

SELECT u.Product,Count(1)AS Numberwith0 FROM table u
JOIN (
SELECT MAX(Date) AS Date,Product
FROM table
WHERE Quant<>0
GROUP BY Product )s ON (u.Date>=s.Date AND u.Product=s.Product)
WHERE u.Quantity=0
GROUP BY u.Product

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 03:32:28

quote:
Originally posted by inbs

i think that i figure it out:

SELECT u.Product,Count(1)AS Numberwith0 FROM table u
JOIN (
SELECT MAX(Date) AS Date,Product
FROM table
WHERE Quant<>0
GROUP BY Product )s ON (u.Date>=s.Date AND u.Product=s.Product)
WHERE u.Quantity=0
GROUP BY u.Product

Is this working query?
I dont get any rows when I run against the sample data given above

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources