Why same SQL get different output? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Why same SQL get different output?

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 07:05:48

I am so confused about one SQL. I spent quite a bit of time to test it, but it still doesn't work the way as I wanted to. I'll try to explain it clearly but please let me know if i didn't make myself clear.

We have a table called F_SALES contains 3200 records and the structure like below:

WCDATE: Date
SKU_Key: int
Sales_Vol: float

WCDate means Week Commencing Date, one WCDate stands for one week. what I want is to find the non-consecutive WCDate and use the first week as the starting week. For instance:


WCDate                                                 SKU_Key     Sales       Start_WCDate                                           
------------------------------------------------------ ----------- ----------- ------------------------------------------------------ 
2005-09-04 00:00:00                                    2           1222        2005-09-04 00:00:00.000
2005-09-11 00:00:00                                    2           1013        2005-09-04 00:00:00.000
2006-02-26 00:00:00                                    2           2343        2006-02-26 00:00:00.000
2006-03-05 00:00:00                                    2           2538        2006-02-26 00:00:00.000
2006-03-12 00:00:00                                    2           2832        2006-02-26 00:00:00.000
2006-03-19 00:00:00                                    2           3034        2006-02-26 00:00:00.000
2004-01-04 00:00:00                                    3           3822        2004-01-04 00:00:00.000
2004-01-18 00:00:00                                    3           3371        2004-01-18 00:00:00.000
2004-01-25 00:00:00                                    3           3524        2004-01-18 00:00:00.000
2004-12-05 00:00:00                                    3           2031        2004-12-05 00:00:00.000
2004-12-12 00:00:00                                    3           2543        2004-12-05 00:00:00.000
2004-12-19 00:00:00                                    3           2786        2004-12-05 00:00:00.000
2004-12-26 00:00:00                                    3           1784        2004-12-05 00:00:00.000
2005-01-02 00:00:00                                    3           3386        2004-12-05 00:00:00.000
2005-01-09 00:00:00                                    3           3895        2004-12-05 00:00:00.000
2005-01-16 00:00:00                                    3           4292        2004-12-05 00:00:00.000
2005-01-23 00:00:00                                    3           4534        2004-12-05 00:00:00.000
2005-12-11 00:00:00                                    3           1394        2005-12-11 00:00:00.000
2005-12-18 00:00:00                                    3           2102        2005-12-11 00:00:00.000
2006-01-01 00:00:00                                    3           999         2006-01-01 00:00:00.000
2006-01-08 00:00:00                                    3           723         2006-01-01 00:00:00.000
2006-01-15 00:00:00                                    3           1227        2006-01-01 00:00:00.000
2006-01-22 00:00:00                                    3           1420        2006-01-01 00:00:00.000
...........................

(1971 row(s) affected)

Here is my SQL:


select	t.WCDate, t.SKU_Key, t.Sales_Vol,
	(select	max(a.WCDate) as Max_Week_No
	from
	(
	select		c.WCDate,c.SKU_Key,
			(
			select 	count(*)
			--	Select only 300 SKUs from F_SALES table
			from	(select * from F_Sales where (sku_key BETWEEN 0 AND 300)) b

			where 	b.WCDate = c.WCDate -7
			and	b.SKU_Key = c.SKU_Key
			) as flag
	--	Select only 300 SKUs from F_SALES table
	from 		(select * from F_Sales where (sku_key BETWEEN 0 AND 300)) c
	)	a
	where 	a.flag =0
	and	a.SKU_Key = t.SKU_Key
	and	a.WCDate <= t.WCDate
	) as sdate
	--	Select only 300 SKUs from F_SALES table
from	(select * from F_Sales where (sku_key BETWEEN 0 AND 300)) t
order by 2,1

It works perfect when I select less than 2000 records as above, but if select more than 2000 records, something rather strange happened as below, I got a lot NULL values.


WCDate                                                 SKU_Key     Sales       start_date                                             
------------------------------------------------------ ----------- ----------- ------------------------------------------------------ 
2005-09-04 00:00:00                                    2           1222        NULL
2005-09-11 00:00:00                                    2           1013        NULL
2006-02-26 00:00:00                                    2           2343        2006-02-26 00:00:00.000
2006-03-05 00:00:00                                    2           2538        2006-02-26 00:00:00.000
2006-03-12 00:00:00                                    2           2832        2006-02-26 00:00:00.000
2006-03-19 00:00:00                                    2           3034        2006-02-26 00:00:00.000
2004-01-04 00:00:00                                    3           3822        NULL
2004-01-18 00:00:00                                    3           3371        2004-01-18 00:00:00.000
2004-01-25 00:00:00                                    3           3524        2004-01-18 00:00:00.000
2004-12-05 00:00:00                                    3           2031        2004-12-05 00:00:00.000
2004-12-12 00:00:00                                    3           2543        2004-12-05 00:00:00.000
2004-12-19 00:00:00                                    3           2786        2004-12-05 00:00:00.000
2004-12-26 00:00:00                                    3           1784        2004-12-05 00:00:00.000
2005-01-02 00:00:00                                    3           3386        2004-12-05 00:00:00.000
2005-01-09 00:00:00                                    3           3895        2004-12-05 00:00:00.000
2005-01-16 00:00:00                                    3           4292        2004-12-05 00:00:00.000
2005-01-23 00:00:00                                    3           4534        2004-12-05 00:00:00.000
2005-12-11 00:00:00                                    3           1394        2005-12-11 00:00:00.000
2005-12-18 00:00:00                                    3           2102        2005-12-11 00:00:00.000
2006-01-01 00:00:00                                    3           999         2006-01-01 00:00:00.000
2006-01-08 00:00:00                                    3           723         2006-01-01 00:00:00.000
2006-01-15 00:00:00                                    3           1227        2006-01-01 00:00:00.000
2006-01-22 00:00:00                                    3           1420        2006-01-01 00:00:00.000
...................
(3223 row(s) affected)

Here is my SQL, exactly same as above, the only difference is I didn't filter the SKU number.


select	t.WCDate, t.SKU_Key, t.Sales_Vol,
	(select	max(a.WCDate) as Max_Week_No
	from
	(
	select		c.WCDate,c.SKU_Key,
			(
			select 	count(*)
			from	F_Sales b

			where 	b.WCDate = c.WCDate -7
			and	b.SKU_Key = c.SKU_Key
			) as flag
	from 		F_Sales c
	)	a
	where 	a.flag =0
	and	a.SKU_Key = t.SKU_Key
	and	a.WCDate <= t.WCDate
	) as sdate
from	F_Sales t
order by 2,1

I used the exactly same table, but get very different output, could anybody tell why it's happened. Is it a bug of SQL server? I am using MS SQL Server2000.

Thanks very much for your time and help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-12 : 07:37:03

I have no idea of what

js_sku_skey

is. It is not documented as field in the database and it doesn't seem to be a variable either. But it is very important to the query when selecting the 300 records.

Most probably the NULL output derives from that they don't exist in the selected 300 range, since thers is not order by. Not having an order by, can cause the SQL Server to output different rows.

Peter Larsson
Helsingborg, Sweden

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 07:51:32

Hi, I've updated the SQL, it's should be SKU_Key. Sorry about that.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-12 : 08:05:24

What are possible values for SKU_Key?

Try also to rewrite

select * from F_Sales

to only include the necessary fields needed for each of the three times.

1. from	(select * from F_Sales where (sku_key BETWEEN 0 AND 300)) b
   from	(select sku_key from F_Sales where (sku_key BETWEEN 0 AND 300)) b

2. (select * from F_Sales where (sku_key BETWEEN 0 AND 300)) c
   (select WCDate, SKU_Key from F_Sales where (sku_key BETWEEN 0 AND 300)) c

3. (select * from F_Sales where (sku_key BETWEEN 0 AND 300)) t
   (select WCDate, SKU_Key, Sales_Vol from F_Sales where (sku_key BETWEEN 0 AND 300)) t

Otherwise, the only thing I have to test at this moment is rewriting

where 	b.WCDate = c.WCDate -7

to

where 	b.WCDate <= c.WCDate - 7

since I can't tell if there are gaps in WCDate column, such as 2004-01-11 00:00:00 and 2005-09-18 00:00:00.

Peter Larsson
Helsingborg, Sweden

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 08:40:40

Hi Peso, thanks for your help. I changed the SQL as you suggested above, it didn't work. Again it works when I select less than 2000 records. But if I put the SKU range as sku_key BETWEEN 0 AND 500, I would get a lot NULL values. What a bizarre!

What are possible values for SKU_Key?
The SKU_Key should be integer from 1 to 550

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 08:50:05

Forget to let you know another very strange thing:

This SQL takes about 2 minutes and half to run:


select	t.WCDate, t.SKU_Key, t.Sales_Vol,
	(select	max(a.WCDate) as Max_Week_No
	from
	(
	select		c.WCDate,c.SKU_Key,
			(
			select 	count(*)

			from	(select * from F_Sales where (sku_key BETWEEN 0 AND 300)) b 

			where 	b.WCDate = c.WCDate -7
			and	b.SKU_Key = c.SKU_Key
			) as flag

	from 		(select * from F_Sales where (sku_key BETWEEN 0 AND 300)) c 
	)	a
	where 	a.flag =0
	and	a.SKU_Key = t.SKU_Key
	and	a.WCDate <= t.WCDate
	) as sdate

from	(select * from F_Sales where (sku_key BETWEEN 0 AND 300)) t 
order by 2,1

but this one only take 2 seconds but the output messed up:


select	t.WCDate, t.SKU_Key, t.Sales_Vol,
	(select	max(a.WCDate) as Max_Week_No
	from
	(
	select		c.WCDate,c.SKU_Key,
			(
			select 	count(*)

			from	(select * from F_Sales where (sku_key BETWEEN 0 AND 500)) b 

			where 	b.WCDate = c.WCDate -7
			and	b.SKU_Key = c.SKU_Key
			) as flag

	from 		(select * from F_Sales where (sku_key BETWEEN 0 AND 500)) c

	)	a
	where 	a.flag =0
	and	a.SKU_Key = t.SKU_Key
	and	a.WCDate <= t.WCDate
	) as sdate

from	(select * from F_Sales where (sku_key BETWEEN 0 AND 500)) t 
order by 2,1

The first SQL return around 1900 records. the second one returns 3000 records. Thanks for your time and help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-12 : 08:51:16

It makes some sense that widening the range produces more records.

1. What is the resultset of

	SELECT DISTINCT SKU_Key FROM F_SALES ORDER BY SKU_Key

2. How do you define "If I select 2000 records or less"?

3. Is there a chance you can export F_SALES to text file, zip it, add CREATE TABLE script, and attach here? Or provide a link to download the file?

4. A long shot would be the parantesis around the WHERE, if it is not evaluated properly. Change

	(select * from F_Sales where (sku_key BETWEEN 0 AND 300))

to

	(select * from F_Sales where sku_key BETWEEN 0 AND 300)

5. Rewrite

	select  count(*)
	from    (select * from F_Sales where (sku_key BETWEEN 0 AND 500)) b 
	where   b.WCDate = c.WCDate -7
	        and b.SKU_Key = c.SKU_Key

as (easier to read/understand)

	select  count(*)
	from    F_Sales b
	where   b.WCDate = c.WCDate -7
	        and b.SKU_Key = c.SKU_Key
	        and b.sku_key BETWEEN 0 AND 500

Peter Larsson
Helsingborg, Sweden

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 09:39:53

Please find the attachment of table structure and data output.

~~[url]http://www.mysharefile.com/v/1651630/F_Sales.zip.html[/url]~~

Please let me know if you have any problems of downloading the file. Thanks very much for your help!

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 09:53:06

Sorry, the data output may have duplicate records, I am uploading another one now!

Here is the link:
[url]http://www.mysharefile.com/v/8659360/1_F_Sales.zip.html[/url]

Sorry about that and thanks for your help!

I rewrote my SQL, hope it makes easier to read. The below SQL works fine


select	t.WCDate, t.SKU_Key, t.Sales_Vol,
	(select	max(a.WCDate) as Max_Week_No
	from
	(
	select		c.WCDate,c.SKU_Key,
			(
			select 	count(*)

			from	F_Sales b 

			where 	b.WCDate = c.WCDate -7
			and	b.SKU_Key = c.SKU_Key
			and	b.sku_key BETWEEN 0 AND 300
			) as flag

	from 		F_Sales c
	where		c.sku_key BETWEEN 0 AND 300

	)	a
	where 	a.flag =0
	and	a.SKU_Key = t.SKU_Key
	and	a.WCDate <= t.WCDate
	) as sdate

from	F_Sales t 
where	t.sku_key BETWEEN 0 AND 300
order by 2,1

but if I change it to sku_key BETWEEN 0 AND 310, the whole output would mess up with a lot of NULL vulue!

Appreciate any thought you have on it!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-12 : 11:53:29

Very strange indeed.

I created the table with the provided script. I imported the values from the file and your latest version of SQL query works fine for me, no matter what values I select for BETWEEN operator. Even commenting the BETWEEN line out, the code works.

I would guess some kind of memory problem. Not likely since there isn't much data.
Do you have indexes on the table? Are they corrupt? As of now, I don't have indexes at all.

Peter Larsson
Helsingborg, Sweden

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 12:30:28

Hi Peso. Thanks so much for your time and help. I don't have indexes either, the one I gave to you is actually a temporary table for testing purposes.

Can you tell me how long it took when the SQL ran. By me if I choose BETWEEN 0 to 300, it takes about 2 minutes, but if I comment the BETWEEN line out or even change it to 0 to 350, it would only take 2 seconds but returning very funny output. So weird.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-12 : 12:40:28

When using the query, data (3,729 rows) and table you provided, it takes about 400 ms to run the query on my laptop (sql2ksp4, winxpsp2 with p4 2.0 gig proc with 1 gig ram).

Removing the BETWEEN lines made the query to take about 700 ms.

Peter Larsson
Helsingborg, Sweden

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 13:35:51

Rebooted the server, still doesn't work. Don't know what to do, have to find another way to achieve this.

Thanks a lot though.

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-06-12 : 14:14:29

Rewrite the SQL as below and solve the problem. Use Join instead of Recursive. Still have no idea why the original one doesn't work.


select 	t1.WCDate, t1.SKU_Key, t1.Sales_Vol, max(t2.SDate) as Start_WCDate

from	f_sales t1
left join
(
	select		c.WCDate as SDate,c.SKU_Key,
			(
			select 	count(*)

			from	F_Sales b 

			where 	b.WCDate = c.WCDate -7
			and	b.SKU_Key = c.SKU_Key
		
			) as flag

	from 		F_Sales c
)	t2

on	t1.SKU_Key = t2.SKU_Key

where	t2.flag = 0
and	t2.SDate <= t1.WCDate

group by t1.WCDate, t1.SKU_Key, t1.Sales_Vol

order by 2,1

Thanks again Peso et al

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources