Banging Head Transitioning from Access Queries to T-SQL in SQL 2005

You can do the same thing in SQL...build queries upon queries.  For example:


select * from table3
where fieldname2 in(
select fieldname2 from table2
where fieldname in(
select fieldname from table1
)
)

You are misunderstanding me, I know that you can build Queries upon Queries.  My first Query/View/Stored Procedure has to return data from a table based on a From Date and a To Date.  I want to get a data set from a table based on the date parameters.  These parameters are not static.  Once I have this data set then I can create all kinds of queries.  My problem is that I don't know how link the parameter based data set to a query.  That is, if I have a stored procedure that returns a data set based on parameters how can I use/link this data so I can use it in my Views?  In MS Access I would just use a parameter based Query, but in SQL I don't know what to do.

chapmandew,

That works.  Is this the proper way to do this?

Yeah, it is....its also about the only way to do it in SQL if you want to be able to query it.  That is what they were designed for.  :)

One last thing.  How do I change the paramter values in the query
select * from dbo.myfun('1/1/2008','2/1/2008')
programmatically?
Otherwise this would be static.

How are you trying to call it?

By the last query (Q4).

****** Q1 ******
SELECT     TmClkID, EmpID, Dt, EventType, Loc, YrMnDy
FROM         dbo.fncRAWdataByDate('3/19/2008 00:00:00 AM', '3/19/2008 11:59:59 AM') AS fncRAWdataByDateTable

****** Q2 ******
SELECT     EmpID, YrMnDy
FROM         dbo.vwEvalData_10
GROUP BY EmpID, YrMnDy
HAVING      (COUNT(EmpID) % 2 = 0)

****** Q3 ******
SELECT     dbo.vwEvalData_10.*
FROM         dbo.vwEvalData_10 INNER JOIN
                      dbo.vwEvalData_20 ON dbo.vwEvalData_10.EmpID = dbo.vwEvalData_20.EmpID AND dbo.vwEvalData_10.YrMnDy = dbo.vwEvalData_20.YrMnDy

****** Q4 ******
SELECT     TOP (100) PERCENT outP.EmpID, outP.Dt, outP.EventType, DATEDIFF(mi, inP.Dt, outP.Dt) AS TmMn
FROM         dbo.vwEvalData_30 AS inP INNER JOIN
                      dbo.vwEvalData_30 AS outP ON inP.EmpID = outP.EmpID AND outP.Dt > inP.Dt LEFT OUTER JOIN
                      dbo.vwEvalData_30 AS inbetween ON inP.EmpID = inbetween.EmpID AND inbetween.Dt > inP.Dt AND inbetween.Dt < outP.Dt
WHERE     (inP.EventType = 1) AND (inbetween.EmpID IS NULL) AND (outP.EventType = 2)
ORDER BY inP.EmpID, inP.Dt

Ok, so are you needing to use values from fields in other tables to pass into it?

I am creating a report so the user will, via a web interface or MS Access, select the date range and then run the report.  Here is the queries again with names:

****** Q1 (dbo.vwEvalData_10) ******
SELECT     TmClkID, EmpID, Dt, EventType, Loc, YrMnDy
FROM         dbo.fncRAWdataByDate('3/19/2008 00:00:00 AM', '3/19/2008 11:59:59 AM') AS fncRAWdataByDateTable

****** Q2 (dbo.vwEvalData_20) ******
SELECT     EmpID, YrMnDy
FROM         dbo.vwEvalData_10
GROUP BY EmpID, YrMnDy
HAVING      (COUNT(EmpID) % 2 = 0)

****** Q3  (dbo.vwEvalData_30)******
SELECT     dbo.vwEvalData_10.*
FROM         dbo.vwEvalData_10 INNER JOIN
                      dbo.vwEvalData_20 ON dbo.vwEvalData_10.EmpID = dbo.vwEvalData_20.EmpID AND dbo.vwEvalData_10.YrMnDy = dbo.vwEvalData_20.YrMnDy

****** Q4  (dbo.vwEvalData_40)******
SELECT     TOP (100) PERCENT outP.EmpID, outP.Dt, outP.EventType, DATEDIFF(mi, inP.Dt, outP.Dt) AS TmMn
FROM         dbo.vwEvalData_30 AS inP INNER JOIN
                      dbo.vwEvalData_30 AS outP ON inP.EmpID = outP.EmpID AND outP.Dt > inP.Dt LEFT OUTER JOIN
                      dbo.vwEvalData_30 AS inbetween ON inP.EmpID = inbetween.EmpID AND inbetween.Dt > inP.Dt AND inbetween.Dt < outP.Dt
WHERE     (inP.EventType = 1) AND (inbetween.EmpID IS NULL) AND (outP.EventType = 2)
ORDER BY inP.EmpID, inP.Dt

How are you getting the date values?  Where will they be coming from?

I guess at this point you tell me because I don't know what method or process to do this.  If everything was in MS Access then my User Form would have hidden fields that would have the date that the query would read when it ran.  Now that I am doing this using an MS Access interface I don't know how I would get these values form the form to the query.  I don't know if the SQL statement would take the [Forms]![frm]![DateFrom], if your familar with MS Access.

Hmm...Id ask another question in the Access zone...I would imagine that you would need to be calling a stored procedure, but not sure.
HTH,
Tim

One last question.  Can a Stored Procedure reference a View?

certainly