I have a query and i need to do a join to a table that needs to be created using DTS.
I have a list of items in excel . The query i have list all the items in the database. I simply want to create a table for the items in my excel book and do a join and get the results. Can anyone help me with this ?
SELECT a.location_id, a.inv_mast_uid, a.item_id, sum(a.inv_period_usage) as Actual_Usage_1YR, sum(a.number_of_orders) as No_Of_Orders, sum(a.filtered_usage) as filtered_usage FROM dbo.p21_view_inv_period_usage a INNER JOIN dbo.p21_view_demand_period b ON a.demand_period_uid = b.demand_period_uid WHERE convert(varchar(4),year_for_period) + case when period <10 then '0' + convert(varchar(2),period) else convert(varchar(2),period) end between convert(varchar(4),year(getdate())-1)+ case when convert(varchar(4),month(getdate()))<10 then '0' + convert(varchar(4),month(getdate())) else convert(varchar(4),month(getdate())) end AND convert(varchar(4),year(getdate()))+ case when convert(varchar(4),month(getdate()))<10 then '0' + convert(varchar(4),month(getdate())) else convert(varchar(4),month(getdate())) end-1 AND a.location_id=10052 GROUP BY a.location_id, a.inv_mast_uid, a.item_id FM
You can either join the excel data directly onto the existing query or insert the excel data into a table and join that data onto the existing query. Either way you will have to add another inner join and use the correct on clause to join the data.
SELECT a.location_id, a.inv_mast_uid, a.item_id,
sum(a.inv_period_usage) as Actual_Usage_1YR,
sum(a.number_of_orders) as No_Of_Orders,
sum(a.filtered_usage) as filtered_usage
FROM dbo.p21_view_inv_period_usage a
INNER JOIN dbo.p21_view_demand_period b ON
a.demand_period_uid = b.demand_period_uid
INNER JOIN(
SELECT *
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\ahaines\Desktop\test.xls', [sheet1$])
) as Excel
ON Excel.MyColumn = a.MyColumn
WHERE
convert(varchar(4),year_for_period) +
case when period <10 then '0' + convert(varchar(2),period)
else convert(varchar(2),period)
end
between
convert(varchar(4),year(getdate())-1)+
case when convert(varchar(4),month(getdate()))<10 then
'0' + convert(varchar(4),month(getdate()))
else convert(varchar(4),month(getdate()))
end
AND
convert(varchar(4),year(getdate()))+
case when convert(varchar(4),month(getdate()))<10 then
'0' + convert(varchar(4),month(getdate()))
else convert(varchar(4),month(getdate()))
end-1
AND
a.location_id=10052
GROUP BY a.location_id, a.inv_mast_uid, a.item_id
OR
SELECT a.location_id, a.inv_mast_uid, a.item_id,
sum(a.inv_period_usage) as Actual_Usage_1YR,
sum(a.number_of_orders) as No_Of_Orders,
sum(a.filtered_usage) as filtered_usage
FROM dbo.p21_view_inv_period_usage a
INNER JOIN dbo.p21_view_demand_period b ON
a.demand_period_uid = b.demand_period_uid
INNER JOIN readonly.uitems as Excel
ON Excel.MyColumn = a.MyColumn
WHERE
convert(varchar(4),year_for_period) +
case when period <10 then '0' + convert(varchar(2),period)
else convert(varchar(2),period)
end
between
convert(varchar(4),year(getdate())-1)+
case when convert(varchar(4),month(getdate()))<10 then
'0' + convert(varchar(4),month(getdate()))
else convert(varchar(4),month(getdate()))
end
AND
convert(varchar(4),year(getdate()))+
case when convert(varchar(4),month(getdate()))<10 then
'0' + convert(varchar(4),month(getdate()))
else convert(varchar(4),month(getdate()))
end-1
AND
a.location_id=10052
GROUP BY a.location_id, a.inv_mast_uid, a.item_id
Also, why are you converting the periods in the predicate? Doing this prohibits the optimizer from using indexes. You should use integer values, if that is what you column is. This way your between statement will be SARG compliant and allow index seeks.
Proposed as answer byAdam HainesWednesday, December 17, 2008 4:17 PM
Marked as answer byAdam HainesMonday, March 9, 2009 3:13 PM
You can use openrowsource, opendatasource, or openquery to tie directly into the spreadsheet. Please note that you will not be able to use a jet driver from SQL if you are using a 64 bit version of SQL. At this point you must load the spreadsheet using SSIS. Here is how you join the spreadsheet onto the current query.
INNER JOIN(
SELECT *
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\ahaines\Desktop\test.xls', [sheet1$])
I was plahying around was i was able to upload the data from excel sheet into the PUBS database .
When i queried select * from readonly.uitems i got the all the items
I just want to join readonly.uitems to the query and i think i should be able to get all .
Can you help ????
SELECT a.location_id, a.inv_mast_uid, a.item_id, sum(a.inv_period_usage) as Actual_Usage_1YR, sum(a.number_of_orders) as No_Of_Orders, sum(a.filtered_usage) as filtered_usage FROM dbo.p21_view_inv_period_usage a INNER JOIN dbo.p21_view_demand_period b ON a.demand_period_uid = b.demand_period_uid WHERE convert(varchar(4),year_for_period) + case when period <10 then '0' + convert(varchar(2),period) else convert(varchar(2),period) end between convert(varchar(4),year(getdate())-1)+ case when convert(varchar(4),month(getdate()))<10 then '0' + convert(varchar(4),month(getdate())) else convert(varchar(4),month(getdate())) end AND convert(varchar(4),year(getdate()))+ case when convert(varchar(4),month(getdate()))<10 then '0' + convert(varchar(4),month(getdate())) else convert(varchar(4),month(getdate())) end-1 AND a.location_id=10052 GROUP BY a.location_id, a.inv_mast_uid, a.item_id FM
You can either join the excel data directly onto the existing query or insert the excel data into a table and join that data onto the existing query. Either way you will have to add another inner join and use the correct on clause to join the data.
SELECT a.location_id, a.inv_mast_uid, a.item_id,
sum(a.inv_period_usage) as Actual_Usage_1YR,
sum(a.number_of_orders) as No_Of_Orders,
sum(a.filtered_usage) as filtered_usage
FROM dbo.p21_view_inv_period_usage a
INNER JOIN dbo.p21_view_demand_period b ON
a.demand_period_uid = b.demand_period_uid
INNER JOIN(
SELECT *
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\ahaines\Desktop\test.xls', [sheet1$])
) as Excel
ON Excel.MyColumn = a.MyColumn
WHERE
convert(varchar(4),year_for_period) +
case when period <10 then '0' + convert(varchar(2),period)
else convert(varchar(2),period)
end
between
convert(varchar(4),year(getdate())-1)+
case when convert(varchar(4),month(getdate()))<10 then
'0' + convert(varchar(4),month(getdate()))
else convert(varchar(4),month(getdate()))
end
AND
convert(varchar(4),year(getdate()))+
case when convert(varchar(4),month(getdate()))<10 then
'0' + convert(varchar(4),month(getdate()))
else convert(varchar(4),month(getdate()))
end-1
AND
a.location_id=10052
GROUP BY a.location_id, a.inv_mast_uid, a.item_id
OR
SELECT a.location_id, a.inv_mast_uid, a.item_id,
sum(a.inv_period_usage) as Actual_Usage_1YR,
sum(a.number_of_orders) as No_Of_Orders,
sum(a.filtered_usage) as filtered_usage
FROM dbo.p21_view_inv_period_usage a
INNER JOIN dbo.p21_view_demand_period b ON
a.demand_period_uid = b.demand_period_uid
INNER JOIN readonly.uitems as Excel
ON Excel.MyColumn = a.MyColumn
WHERE
convert(varchar(4),year_for_period) +
case when period <10 then '0' + convert(varchar(2),period)
else convert(varchar(2),period)
end
between
convert(varchar(4),year(getdate())-1)+
case when convert(varchar(4),month(getdate()))<10 then
'0' + convert(varchar(4),month(getdate()))
else convert(varchar(4),month(getdate()))
end
AND
convert(varchar(4),year(getdate()))+
case when convert(varchar(4),month(getdate()))<10 then
'0' + convert(varchar(4),month(getdate()))
else convert(varchar(4),month(getdate()))
end-1
AND
a.location_id=10052
GROUP BY a.location_id, a.inv_mast_uid, a.item_id
Also, why are you converting the periods in the predicate? Doing this prohibits the optimizer from using indexes. You should use integer values, if that is what you column is. This way your between statement will be SARG compliant and allow index seeks.
Proposed as answer byAdam HainesWednesday, December 17, 2008 4:17 PM
Marked as answer byAdam HainesMonday, March 9, 2009 3:13 PM
