Old School syntax - 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 2000 Forums

SQL Server Development (2000)

Old School syntax

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-16 : 06:50:20

Hi guys hope everything is good.
I have just located a old school delete statement that I cant quiet figure out what it does without the explicit joins:

delete #sta from #sta t, StockAdjustments s
where s.store = '103'
and s.type collate database_default = substring(t.adj,6,3)
and s.ean collate database_default = substring(t.adj,10,13)
and s.tdate = cast(dateadd(dd,(cast(substring(t.adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime)
and s.ttime = substring(t.adj collate database_default,100,6)

Does all the text in red act like the on clause in a inner join as I can't figure out why the s.store = '103' is used if the delete is only taking stuff out of the #sta table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 06:57:36

[code]DELETE t
FROM #sta AS t
INNER JOIN StockAdjustments AS s ON s.type = SUBSTRING(t.adj, 6, 3)
AND s.ean = SUBSTRING(t.adj, 10, 13)
AND s.tdate = DATEADD(day, CAST(SUBSTRING(t.adj, 93, 6) AS INT) - 146766, '31/10/2002')
AND s.ttime = SUBSTRING(t.adj, 100, 6)
AND s.store = '103'[/code]

Peter Larsson
Helsingborg, Sweden

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-16 : 07:07:25

thanks Peter I thought as much cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 07:11:35

Seems strange though, that tDate and tTime has different datatypes.

Peter Larsson
Helsingborg, Sweden

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-16 : 09:20:01

They dont need to though Peso they are all getting bulk inserted into a one column wide #sta table that has a char datatype declared for it.
This is the code I inheritted from a seasoned developer in case you are interested in which I have to add some more things to :

create table #sta (adj [char] (127))

bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.001'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.002'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.003'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.004'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.005'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.006'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.007'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.008'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.009'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.010'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.011'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.012'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.013'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.014'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.015'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.016'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.017'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.018'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.019'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.020'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.021'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.022'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.023'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.024'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.025'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.026'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.027'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.028'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.029'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.030'
bulk insert #sta from '\\server\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.031'
Go

delete #sta from #sta t, StockAdjustments s
where 	s.store = '103'
	and s.type collate database_default = substring(t.adj,6,3)
	and s.ean collate database_default = substring(t.adj,10,13)
	and s.tdate = cast(dateadd(dd,(cast(substring(t.adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime)
	and s.ttime = substring(t.adj collate database_default,100,6)
Go

insert into StockAdjustments
	Select
	'103' as Store,
	substring(adj,6,3) Type,
	substring(adj,10,13) EAN,
	Qty = 		case -- Check if value is minus
				when substring(adj,35,1) = '-' 
				then cast(substring(adj,24,6) as int) * -1
				else cast(substring(adj,24,6) as int)
			end,
	Retail = 	case -- Check if value is minus
				when substring(adj,46,1) = '-' 
				then cast(substring(adj,37,9) as money) * -1
				else cast(substring(adj,37,9) as money)
			end,
	Cost = 		case -- Check if value is minus
				when substring(adj,57,1) = '-' 
				then cast(substring(adj,48,9) as money) * -1
				else cast(substring(adj,48,9) as money)
			end,
	substring(adj,59,8) A,
	substring(adj,68,3) B,
	substring(adj,72,20) C,
	cast(dateadd(dd,(cast(substring(adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime) tDate,
	substring(adj,100,6) tTime, null

from #sta

These dayadjst files are supposed to get imported everyday and replace the exsisting one for that same date and time last month, I am going to add some checks as some days there are none coming in which causes all sorts of problems.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-16 : 16:46:33

just out of interest please can anyone tell me what the name of this type of date format highlighted is please :

s.tdate = cast(dateadd(dd,(cast(substring(t.adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime)

If this

cast(substring(t.adj,93,6)

= 148296 in the above calculation.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 17:21:00

Probably that number is some kind of date serial number equal to 406 years (probably number of days since previous leapyear even divided by 400, year 1600).

And then you want to remove 147766 days from that number and add the difference to October 31, 2002.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources