Access Query to Stored Procedure...HOW DO I??? - 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

SQL Server Development (2000)

Access Query to Stored Procedure...HOW DO I???

Author

Topic

susies
Starting Member

7 Posts

Posted - 2004-10-27 : 10:57:51

Hello all. Ok, I'll get straight to the point: I need help!
Here's the scenario-
Currently, I'm re-creating an access database to sql to view on an intranet in order to display adjusted time records.
I am having a bit of trouble converting some of the IF-THEN queries.

For example:

SELECT dbo_TimeCard.date_in, 
IIf(DatePart("n",[date_in]) Between 53 And 59 Or DatePart("n",[date_in]) Between 0 And 7,0,
IIf(DatePart("n",[date_in]) Between 8 And 22,15,
IIf(DatePart("n",[date_in]) Between 23 And 37,30,
IIf(DatePart("n",[date_in]) Between 38 And 52,45)))) AS AdjMinIn
FROM dbo_TimeCard;

(NOTE: Time Card Table is dbo.TimeCard.)
Any ideas how how I can convert this? Any help is appreciated.

Thanks in advance!

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-10-27 : 11:09:02

Look up the CASE Statement in BOL.

susies
Starting Member

7 Posts

Posted - 2004-10-27 : 11:22:19

Thanks for the input.
I reworked it a bit, but I am still producing errors. I'm sure it is probably something really simple, but it's before 11am here and already my brain is friend for the day!

SELECT  dbo_TimeCard.date_in 
CASE 
   WHEN (DatePart(n, date_in)) BETWEEN 53 AND 59 OR
        ((DatePart(n, date_in)) BETWEEN 0 AND 7) THEN 0
   WHEN (DatePart(n, date_in)) BETWEEN 8 AND 22 THEN 15 
   WHEN (DatePart(n, date_in)) BETWEEN 23 AND 37 THEN 30 
   WHEN (DatePart(n, date_in)) BETWEEN 38 AND 52 THEN 45 
END AS AdjMinIn
FROM dbo_TimeCard

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 11:36:18

You've missed out a comma after dbo_TimeCard.date_in

SELECT dbo_TimeCard.date_in,
CASE
WHEN (DatePart(n, date_in)) BETWEEN 53 AND 59 OR
((DatePart(n, date_in)) BETWEEN 0 AND 7) THEN 0
WHEN (DatePart(n, date_in)) BETWEEN 8 AND 22 THEN 15
WHEN (DatePart(n, date_in)) BETWEEN 23 AND 37 THEN 30
WHEN (DatePart(n, date_in)) BETWEEN 38 AND 52 THEN 45
END AS AdjMinIn
FROM dbo_TimeCard

Andy

susies
Starting Member

7 Posts

Posted - 2004-10-27 : 12:03:15

That worked like a charm. Grazi! I even did a few more. Once I have all the queries worked out, I am using them in a stored procedure for a .NET application. I simple hate 'porting' from Access to SQL, although I do use word porting loosely.
Mind if I pick your brain once more? (Hopefully, you won't mind!)

This query was designed to breakdown the time records into groups by day. Please let me know if you know of an easier was other than nested CASE.

SELECT TimeCard.pid, WeeklyEmployeeList.FirstName, WeeklyEmployeeList.LastName, DATEPART(yyyy, TimeCard.date_in) AS Year, DATEPART(ww, TimeCard.date_in) AS Week, DATEPART(w, TimeCard.date_in) AS Day

IIF([Day]=2,"Mon",
IIf([Day]=3,"Tue",
IIf([Day]=4,"Wed",
IIf([Day]=5,"Thu",
IIf([Day]=6,"Fri",
IIf([Day]=7,"Sat",Null)))))) AS DayName, dbo.TimeCard.date_in AS [Date], DatePart("h",[date_in]) AS InHour, DatePart("n",[date_in]) AS InMin, 

IIf([InMin] Between 53 And 59,[InHour]+1,[InHour]) AS AdjInHour, 
IIf([InMin] Between 53 And 59 Or [InMin] Between 0 And 7,0,
IIf([InMin] Between 8 And 22,15,
IIf([InMin] Between 23 And 37,30,
IIf([InMin] Between 38 And 52,45)))) AS AdjInMin, DatePart("h",[Lunch_out]) AS LOHour, DatePart("n",[Lunch_out]) AS LOMin, 

IIf([LOMin] Between 53 And 59,[LOHour]+1,[LOHour]) AS AdjLOHour, 
IIf([LOMin] Between 53 And 59 Or [LOMin] Between 0 And 7,0,IIf([LOMin] Between 8 And 22,15,
IIf([LOMin] Between 23 And 37,30,
IIf([LOMin] Between 38 And 52,45)))) AS AdjLOMin, DatePart("h",[lunch_in]) AS LIHour, DatePart("n",[lunch_in]) AS LIMin, 
IIf([LIMin] Between 53 And 59,[LIHour]+1,[LIHour]) AS AdjLIHour, 
IIf([LIMin] Between 53 And 59 Or [LIMin] Between 0 And 7,0,
IIf([LIMin] Between 8 And 22,15,IIf([LIMin] Between 23 And 37,30,
IIf([LIMin] Between 38 And 52,45)))) AS AdjLIMin, DatePart("h",[date_out]) AS OutHour, DatePart("n",[date_out]) AS OutMin, 

IIf([OutMin] Between 53 And 59,[OutHour]+1,[OutHour]) AS AdjOutHour, 
IIf([OutMin] Between 53 And 59 Or [OutMin] Between 0 And 7,0,
IIf([OutMin] Between 8 And 22,15,
IIf([OutMin] Between 23 And 37,30,
IIf([OutMin] Between 38 And 52,45)))) AS AdjOutMin, ((([AdjLOHour]-[AdjInHour])*60)+([AdjLOMin]-[AdjInMin]))/60 AS AMHours, ((([AdjOutHour]-[AdjLIHour])*60)+([AdjOutMin]-[AdjLIMin]))/60 AS PMHours, [AMHours]+[PMHours] AS TotalDay

FROM TimeCard INNER JOIN WeeklyEmployeeList ON TimeCard.pid = WeeklyEmployeeList.Person_ID

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 12:20:57

For a start this lot

quote:

IIF([Day]=2,"Mon",
IIf([Day]=3,"Tue",
IIf([Day]=4,"Wed",
IIf([Day]=5,"Thu",
IIf([Day]=6,"Fri",
IIf([Day]=7,"Sat",Null)))))) AS DayName

Can be replaced with

LEFT(DATENAME(dw ,GETDATE()),3) AS DayName

Edit: GETDATE() = today replace with your column name (NOT the aliase) TimeCard.date_in

Andy

susies
Starting Member

7 Posts

Posted - 2004-10-27 : 12:38:48

You are fabuluous!

So would the next case go as:

CASE
WHEN ([InMin] Between 53 And 59 THEN [InHour]+1,[InHour]) AS AdjInHour
WHEN ([InMin] Between 53 And 59 Or [InMin] Between 0 And 7 THEN 0
WHEN ([InMin] Between 8 And 22 THEN 15
WHEN ([InMin] Between 23 And 37 THEN 30
WHEN ([InMin] Between 38 And 52 THEN 45 
END AS AdjInMin, DatePart(hh,[Lunch_out]) AS LOHour, DatePart(n,[Lunch_out]) AS LOMin

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 13:07:11

Close but no cigar
This line of your Access query

quote:
IIf([InMin] Between 53 And 59,[InHour]+1,[InHour]) AS AdjInHour

Would be something like

CASE WHEN DATEPART(n,date_in) BETWEEN 53 AND 59 THEN
DATEPART(hh,(DATEADD(hh,+1,date_in)))
ELSE
DATEPART(hh,date_in)
END AS AdjInHour

----------------

You were very close with the rest

Change this to read

quote:
WHEN ([InMin] Between 53 And 59 Or [InMin] Between 0 And 7 THEN 0
WHEN ([InMin] Between 8 And 22 THEN 15
WHEN ([InMin] Between 23 And 37 THEN 30
WHEN ([InMin] Between 38 And 52 THEN 45
END AS AdjInMin

CASE WHEN (DATEPART(n,date_in) Between 53 And 59) OR (DATEPART(n,date_in) Between 0 And 7) THEN 0
WHEN DATEPART(n,date_in) Between 8 And 22 THEN 15
WHEN DATEPART(n,date_in) Between 23 And 37 THEN 30
WHEN DATEPART(n,date_in) Between 38 And 52 THEN 45
END AS AdjInMin

You only need to use [] if you are using a reserved word as a column/alias which "date_in" isnt but Day & Year & week are, so you would need [] around [Day] & [Year] & [Week]

I avoid using reserved words and would recomend doing so, in your case I would call them DayNo, YearNo and WeekNo as in essence thats what they are - numbers

Andy

susies
Starting Member

7 Posts

Posted - 2004-10-27 : 16:09:02

Thanks again for your help.
So far this works. I have one more small sections to put in it.


SELECT TimeCard.pid, WeeklyEmployeeList.FirstName, WeeklyEmployeeList.LastName, DATEPART(yyyy, TimeCard.date_in) AS Year, DATEPART(ww, TimeCard.date_in) AS Week, DATEPART(w, TimeCard.date_in) AS Day, LEFT(DATENAME(dw, GETDATE()), 3) AS DayName, TimeCard.date_in AS Date, DATEPART(hh, TimeCard.date_in) AS InHour, DATEPART(n, TimeCard.date_in) AS InMin,

CASE 
WHEN DATEPART(n, date_in) BETWEEN 53 AND 59 THEN DATEPART(hh, (DATEADD(hh, + 1, date_in))) 
ELSE DATEPART(hh, date_in) 
END AS AdjInHour, 

CASE 
WHEN (DATEPART(n, date_in) BETWEEN 53 AND 59) OR
     (DATEPART(n, date_in) BETWEEN 0 AND 7) THEN 0 
WHEN DATEPART(n, date_in) BETWEEN 8 AND 22 THEN 15 
WHEN DATEPART(n, date_in) BETWEEN 23 AND 37 THEN 30 
WHEN DATEPART(n, date_in) BETWEEN 38 AND 52 THEN 45 
END AS AdjInMin, DATEPART(hh, Lunch_out) AS LOHour, DATEPART(n, Lunch_out) AS LOMIN,

CASE
WHEN DATEPART(n, Lunch_out) BETWEEN 53 AND 59 THEN
DATEPART(hh,(DATEADD(hh,+1,Lunch_out)))
ELSE
DATEPART(hh,Lunch_out)
END AS AdjLOHour,

CASE 
WHEN (DATEPART(n, lunch_in) BETWEEN 53 AND 59) OR
     (DATEPART(n, lunch_in) BETWEEN 0 AND 7) THEN 0 
WHEN DATEPART(n, lunch_in) BETWEEN 8 AND 22 THEN 15 
WHEN DATEPART(n, lunch_in) BETWEEN 23 AND 37 THEN 30 
WHEN DATEPART(n, lunch_in) BETWEEN 38 AND 52 THEN 45 
END AS AdjLOMin, DATEPART(hh, lunch_in) AS LIHour, DatePart(n,lunch_in) AS LIMin,

CASE
WHEN DATEPART(n, lunch_in) BETWEEN 53 AND 59 THEN
DATEPART(hh,(DATEADD(hh,+1,lunch_in)))
ELSE
DATEPART(hh,lunch_in)
END AS AdjLIHour,

CASE 
WHEN (DATEPART(n, lunch_in) BETWEEN 53 AND 59) OR
     (DATEPART(n, lunch_in) BETWEEN 0 AND 7) THEN 0 
WHEN DATEPART(n, lunch_in) BETWEEN 8 AND 22 THEN 15 
WHEN DATEPART(n, lunch_in) BETWEEN 23 AND 37 THEN 30 
WHEN DATEPART(n, lunch_in) BETWEEN 38 AND 52 THEN 45 
END AS AdjLIMin, DATEPART(hh,date_out) AS OutHour, DATEPART(n,date_out) AS OutMin,

CASE
WHEN DATEPART(n, date_out) BETWEEN 53 AND 59 THEN
DATEPART(hh,(DATEADD(hh,+1,date_out)))
ELSE
DATEPART(hh,date_out)
END AS AdjOutHour,

CASE
WHEN (DATEPART(n, date_out) BETWEEN 53 AND 59) OR
     (DATEPART(n, date_out) BETWEEN 0 AND 7) THEN 0 
WHEN DATEPART(n, date_out) BETWEEN 8 AND 22 THEN 15 
WHEN DATEPART(n, date_out) BETWEEN 23 AND 37 THEN 30 
WHEN DATEPART(n, date_out) BETWEEN 38 AND 52 THEN 45 
END AS AdjOutMin

FROM TimeCard INNER JOIN WeeklyEmployeeList ON TimeCard.pid = WeeklyEmployeeList.Person_ID

susies
Starting Member

7 Posts

Posted - 2004-10-27 : 17:27:32

Wahoo!
It works...well, kinda.

OK, here goes.
I need to incorporate the calculations.
In the Access query, it was place in the final case (if/then).
When I attempt to do that in SQL, it does not work.
Any ideas on how I can do this?

This is the Access View:

IIf([OutMin] Between 53 And 59,[OutHour]+1,[OutHour]) AS AdjOutHour, 
IIf([OutMin] Between 53 And 59 Or [OutMin] Between 0 And 7,0,
IIf([OutMin] Between 8 And 22,15,IIf([OutMin] Between 23 And 37,30,
IIf([OutMin] Between 38 And 52,45)))) AS AdjOutMin, ((([AdjLOHour]-[AdjInHour])*60)+([AdjLOMin]-[AdjInMin]))/60 AS AMHours, ((([AdjOutHour]-[AdjLIHour])*60)+([AdjOutMin]-[AdjLIMin]))/60 AS PMHours, [AMHours]+[PMHours] AS TotalDay

----------------------------------------

Any ideas on the AMHours, PmHours, TotalDay stuff?

Thanks in advance!

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 17:50:52

Heres a little bit more

quote:
IIf([LIMin] Between 53 And 59,[LIHour]+1,[LIHour]) AS AdjLIHour

CASE
WHEN DATEPART(n,lunch_in) BETWEEN 53 AND 59
DATEPART(hh,(DATEADD(hh,+1,lunch_in)))
ELSE
DATEPART(hh,lunch_in)
END AS AdjLIHour

quote:
IIf([LIMin] Between 53 And 59 Or [LIMin] Between 0 And 7,0,
IIf([LIMin] Between 8 And 22,15,
IIf([LIMin] Between 23 And 37,30,
IIf([LIMin] Between 38 And 52,45)))) AdjLIMin

CASE
WHEN (DATEPART(n,lunch_in) BETWEEN 53 AND 59) OR (DATEPART(n,lunch_in) BETWEEN 0 AND 7) THEN 0
WHEN DATEPART(n,lunch_in) BETWEEN 8 AND 22 THEN 15
WHEN DATEPART(n,lunch_in) BETWEEN 23 AND 37 THEN 30
WHEN DATEPART(n,lunch_in) BETWEEN 38 AND 52 THEN 45
END AS AdjLIMin

DATEPART(hh,date_out) AS OutHour, DATEPART(n,date_out) AS OutMin,

quote:
IIf([OutMin] Between 53 And 59,[OutHour]+1,[OutHour]) AS AdjOutHour

CASE
WHEN DATEPART(n,date_out) BETWEEN 53 AND 59
DATEPART(hh,(DATEADD(hh,+1,date_out)))
ELSE
DATEPART(hh,date_out)
END AS AdjOutHour

quote:
IIf([OutMin] Between 53 And 59 Or [OutMin] Between 0 And 7,0,
IIf([OutMin] Between 8 And 22,15,IIf([OutMin] Between 23 And 37,30,
IIf([OutMin] Between 38 And 52,45)))) AS AdjOutMin

CASE
WHEN (DATEPART(n,date_out) BETWEEN 53 AND 59) OR (DATEPART(n,date_out) BETWEEN 0 AND 7) THEN 0
WHEN DATEPART(n,date_out) BETWEEN 8 AND 22 THEN 15
WHEN DATEPART(n,date_out) BETWEEN 23 AND 37 THEN 30
WHEN DATEPART(n,date_out) BETWEEN 38 AND 52 THEN 45
END AS AdjOutMin

Hope that helps
For the other calculations, it might be better if you create a view from this and then another view/sproc selecting from the 1st view that way your formulas will work in the syntax they are in

Andy

susies
Starting Member

7 Posts

Posted - 2004-10-27 : 17:54:27

Thank you sql god.

I will try this out.

Thanks for making the who access-sql deal bearable!

Susan

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-27 : 18:04:14

No problem
SQL god

(i wish), im merely an amateur compared to most on here. Now they ARE GODS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources