Query Help :-( - 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

Transact-SQL (2000)

Query Help :-(

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-12-09 : 00:56:49

Good day... I need some help regarding this query...

I had this sample datas from tblStockItems...



SELECT tblStockItems.* 
FROM (
	SELECT 1 as sID, 'AA' as sItemName UNION ALL
	SELECT 2 as sID, 'BB' as sItemName UNION ALL
	SELECT 3 as sID, 'CC' as sItemName UNION ALL
	SELECT 4 as sID, 'DD' as sItemName UNION ALL
	SELECT 5 as sID, 'EE' as sItemName UNION ALL
	SELECT 6 as sID, 'FF' as sItemName UNION ALL
	SELECT 7 as sID, 'GG' as sItemName UNION ALL
	SELECT 8 as sID, 'HH' as sItemName 
	) as tblStockItems

and another one for tblStockMonitoring...



SELECT tblStockMonitoring.*
FROM (
	SELECT 1 as sID, 'Contractor-01' as Contractor, '2006/12/01' as  sFrom, '2006/12/05' as  sTo UNION ALL
	SELECT 3 as sID, 'Contractor-AA' as Contractor, '2006/12/02' as  sFrom, '2006/12/09' as  sTo UNION ALL
	SELECT 3 as sID, 'Contractor-BB' as Contractor, '2006/12/11' as  sFrom, '2006/12/15' as  sTo
	) as tblStockMonitoring

Result is a new monitoring that would look like this...



SELECT tblNewMonitoring.*
FROM (	
	SELECT 1 as sID, 'AA' as sItemName, '2006/12/01' as MonitoringDate, 'Contractor-01' as Contractor UNION ALL
	SELECT 1 as sID, 'AA' as sItemName, '2006/12/02' as MonitoringDate, 'Contractor-01' as Contractor UNION ALL
	SELECT 1 as sID, 'AA' as sItemName, '2006/12/03' as MonitoringDate, 'Contractor-01' as Contractor UNION ALL
	SELECT 1 as sID, 'AA' as sItemName, '2006/12/04' as MonitoringDate, 'Contractor-01' as Contractor UNION ALL
	SELECT 1 as sID, 'AA' as sItemName, '2006/12/05' as MonitoringDate, 'Contractor-01' as Contractor UNION ALL
	
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/02' as MonitoringDate, 'Contractor-AA' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/03' as MonitoringDate, 'Contractor-AA' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/04' as MonitoringDate, 'Contractor-AA' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/05' as MonitoringDate, 'Contractor-AA' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/06' as MonitoringDate, 'Contractor-AA' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/07' as MonitoringDate, 'Contractor-AA' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/08' as MonitoringDate, 'Contractor-AA' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/09' as MonitoringDate, 'Contractor-AA' as Contractor UNION ALL
	
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/11' as MonitoringDate, 'Contractor-BB' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/12' as MonitoringDate, 'Contractor-BB' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/13' as MonitoringDate, 'Contractor-BB' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/14' as MonitoringDate, 'Contractor-BB' as Contractor UNION ALL
	SELECT 3 as sID, 'CC' as sItemName, '2006/12/15' as MonitoringDate, 'Contractor-BB' as Contractor 
	) as tblNewMonitoring

Any help would be much appreciated. Thanks!

Want Philippines to become 1st World COuntry? Go for World War 3...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-09 : 06:32:59

[code]-- prepare test data
declare @a table (id int, itemname varchar(2))

insert @a
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'cc' union all
select 4, 'dd' union all
select 5, 'ee' union all
select 6, 'ff' union all
select 7, 'gg' union all
select 8, 'hh'

declare @b table (id int, contractor varchar(13), [from] datetime, [to] datetime)

insert @b
select 1, 'contractor-01', '2006/12/01', '2006/12/05' union all
select 3, 'contractor-aa', '2006/12/02', '2006/12/09' union all
select 3, 'contractor-bb', '2006/12/11', '2006/12/15'

-- do the work
SELECT b.ID,
a.ItemName,
DATEADD(day, q.Number, b.[From]) MonitoringDate,
b.Contractor
FROM @b b
INNER JOIN @a a ON a.ID = b.ID
INNER JOIN (
SELECT Number
FROM master..spt_values
WHERE Name IS NULL
) q ON q.Number <= DATEDIFF(day, b.[From], b.[To])[/code]

Peter Larsson
Helsingborg, Sweden

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-12-10 : 19:41:06

tnx for the reply peso...

here's my little improvement thru your code. Because I am preparing at the same time the MySQL counterpart for this statement.



SELECT b.sID
	,a.sItemName
	,DATEADD(day, q.dayz, b.sFrom) MonitoringDate
	,b.Contractor
FROM (
	SELECT 1 as sID, 'Contractor-01' as Contractor, '2006/12/01' as  sFrom, '2006/12/05' as  sTo UNION ALL
	SELECT 3 as sID, 'Contractor-AA' as Contractor, '2006/12/02' as  sFrom, '2006/12/09' as  sTo UNION ALL
	SELECT 3 as sID, 'Contractor-BB' as Contractor, '2006/12/11' as  sFrom, '2006/12/15' as  sTo
	) as b
INNER JOIN (
		SELECT 1 as sID, 'AA' as sItemName UNION ALL
		SELECT 2 as sID, 'BB' as sItemName UNION ALL
		SELECT 3 as sID, 'CC' as sItemName UNION ALL
		SELECT 4 as sID, 'DD' as sItemName UNION ALL
		SELECT 5 as sID, 'EE' as sItemName UNION ALL
		SELECT 6 as sID, 'FF' as sItemName UNION ALL
		SELECT 7 as sID, 'GG' as sItemName UNION ALL
		SELECT 8 as sID, 'HH' as sItemName 
		) as a ON a.sID = b.sID
INNER JOIN (
		select (i1 + i2 + i3 + i4 + i5 + i6 + i7 + i8 + i9 + i10 + i11) as dayz
		from      (select 0 as i1 union select 1) as i1
		left join (select 0 as i2 union select 2) as i2 on 1=1
		left join (select 0 as i3 union select 4) as i3 on 1=1
		left join (select 0 as i4 union select 8) as i4 on 1=1
		left join (select 0 as i5 union select 16) as i5 on 1=1
		left join (select 0 as i6 union select 32) as i6 on 1=1
		left join (select 0 as i7 union select 64) as i7 on 1=1
		left join (select 0 as i8 union select 128) as i8 on 1=1
		left join (select 0 as i9 union select 256) as i9 on 1=1
		left join (select 0 as i10 union select 512) as i10 on 1=1
		left join (select 0 as i11 union select 1024) as i11 on 1=1				
	       )  as q ON q.dayz <= DATEDIFF(day, b.sFrom, b.[sTo])
ORDER BY a.sID

thanks once again.

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources