Microsoft Sharepoint and SQL

[BiggestNizzy]

Hi All, I am looking for some help with Microsoft SharePoint 2007 (out of date but you piss with what you've got)

I am trying to execute a SQL query from sharepoint to MSSQL2008 and I'm not getting very far. I have a SQL query that works in excel but I want to be able to convert that to a web part.

I can connect to the database fine and I can get the following to run ok

SELECT * FROM dbo . [Activity_List]

I am looking to get the following excel SQL query to run.

SELECT
WO_Op_List."Op No",
WO_Op_List."WC Name",
WO_Op_List."Last Start Date",
Activity_List."Complete",
Activity_List."Activity Type",
Activity_List."For Employee",
Activity_List."Notes",
Work_Orders_List."WO No",
Work_Orders_List."Description",
Work_Orders_List."SO Customer",
Work_Orders_List."Drawing No",
Activity."Next_Date"
FROM
(((WORK_ORDERS WORK_ORDERS INNER JOIN WO_Op_List WO_Op_List ON
WORK_ORDERS."WORK_ORDERS_ID" = WO_Op_List."WORK_ORDERS_ID")
INNER JOIN Activity_List Activity_List ON
WORK_ORDERS."WORK_ORDERS_ID" = Activity_List."WORK_ORDERS__WORK_ORDERS_ID")
INNER JOIN Work_Orders_List Work_Orders_List ON
WORK_ORDERS."WORK_ORDERS_ID" = Work_Orders_List."WORK_ORDERS_ID")
INNER JOIN Activity Activity ON
Activity_List."Activity_ID" = Activity."Activity_ID"
WHERE
Activity_List."Complete" = 0 AND
WO_Op_List."Op No" = '30' AND
(Activity_List."For Employee" = 'Hugh Jarse' OR
Activity_List."For Employee" = 'Ivor Biggun' OR
Activity_List."For Employee" = 'Willie Annall' OR
Activity_List."For Employee" = 'Team')

Any help would be greatly appreciated.

[graeme]

Shortened a bit for readability. Should be functionally equivalent though, so if one doesn't work for whatever reason, I doubt the other will.

1) INNER JOIN and JOIN are the same thing. INNER is the default type of join, so we can remove the keyword INNER.
2) used shorter aliases (my convention is initial, all lower case, so "Activity_List" becomes "al". There's no point specifying (as in the original) an alias that's identical to the table name. Either leave them out or shorten them as I've done.
3) The parenthesis around the JOINS don't do anything (I don't think). Inner join table order doesn't matter as the optimiser will probably change it anyway at runtime.
4) Used the IN clause instead of a big list of OR for employee names. More readable.
5) If the field name is the same in both tables in a JOIN then the table names or aliases can be removed.

SELECT
wool."Op No",
wool."WC Name",
wool."Last Start Date",
al.Complete,
al."Activity Type",
al."For Employee",
al.Notes,
wol."WO No",
wol.Description,
wol."SO Customer",
wol."Drawing No",
a.Next_Date
FROM
WORK_ORDERS AS wo
JOIN WO_Op_List AS wool ON WORK_ORDERS_ID
JOIN Activity_List al ON wo.WORK_ORDERS_ID = al.WORK_ORDERS__WORK_ORDERS_ID
JOIN Work_Orders_List wol ON WORK_ORDERS_ID
JOIN Activity a ON Activity_ID
WHERE
al.Complete = 0 AND
wool."Op No" = '30' AND
al."For Employee" IN ('Hugh Jarse', 'Ivor Biggun', 'Willie Annall', 'Team')



Is there any error returned when you run it?

[Corranga]

I'm fairly certain that graeme's number 5 is incorrect. It might work in other SQL, but not in MS land.
I just tried similar on 3 MS SQL servers (2005, 2008 R2 and 2012) and got:

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'fieldName'.

So it would be:

SELECT
wool."Op No",
wool."WC Name",
wool."Last Start Date",
al.Complete,
al."Activity Type",
al."For Employee",
al.Notes,
wol."WO No",
wol.Description,
wol."SO Customer",
wol."Drawing No",
a.Next_Date
FROM
WORK_ORDERS AS wo
JOIN WO_Op_List AS wool ON wo.WORK_ORDERS_ID = wool.WORK_ORDERS_ID
JOIN Activity_List al ON wo.WORK_ORDERS_ID = al.WORK_ORDERS__WORK_ORDERS_ID
JOIN Work_Orders_List wol ON wo.WORK_ORDERS_ID = wol.WORK_ORDERS_ID
JOIN Activity a ON al.Activity_ID = a.Activity_ID
WHERE
al.Complete = 0 AND
wool."Op No" = '30' AND
al."For Employee" IN ('Hugh Jarse', 'Ivor Biggun', 'Willie Annall', 'Team')

I've restrained myself and not replaced " with [ ] and put the INNERs back in as I guess those are personal preference

[graeme]

Cheers Chris! Guess who didn't test?

[BiggestNizzy]

Did a cut and paste and it changed it to

SELECT wool



Swopped the "" for [] and it thought about it for a second before coming up with an unspecified error. looking back at the query it chops it off at
SELECT
wool.[Op No],
wool.[WC Name],
wool.[Last Start Date],
al.Complete,
al.[Activity Type],
al.[For Employee],
al.Notes,
wol.[WO No],
wol.Description,
wol.[SO Customer],
wol.[Drawing No],
a.Next_Date
FROM
WORK_ORDERS AS wo
JOIN WO_Op_List AS wool ON wo.WORK_ORDERS_ID = wool.WORK_ORDERS_ID
JOIN Activity_List

Cheers for this

[BiggestNizzy]

Hi All, been working on this back and forth as I slowly teach myself asp and SQL but I have hit a snag and I am looking for some help.

Code: Select all

SELECT   
	st.WorkCentre, 
	st.Work_Order, 
	stl.[Start Time],
	stl.[WC_name],
	stl.[Start Date],
	stl.Name, 
	stl.Operation,
	ao.[Run Time],
	ao.[Actual Run Time],
	ao.Customer,
	ao.[Setup Time],
	ao.[Actual Setup Time],
	ao.[Customers PO],
	ao.[Drawing Number],
	wo.Qty, 
	wodl.[Qty Complete],
	wodl.[Part No],
	wodl.Description, 
	cast
	(		
		cast  
			(getdate() -  
			(stl.[Start Time] )	AS DECIMAL(18,5)  )  +
			(  
			(cast (ao.[Actual Run Time] AS DECIMAL(18,5))/24  )  )
		AS DATETIME)    
	AS [runtime]    

FROM 
Active_Ops AS ao,
SFDC_Transactions AS st,
SFDC_Transactions_List AS stl,
WORK_ORDERS AS wo,
Work_Orders_Drop_list AS wodl 
 
 WHERE 
 st.SFDC_Transactions_ID = stl.SFDC_Transactions_ID 
 AND wo.WO_No = st.Work_Order 
 AND ao.WORK_ORDERS_ID = wo.WORK_ORDERS_ID 
 AND wodl.WORK_ORDERS_ID = ao.WORK_ORDERS_ID 
 AND wodl.WORK_ORDERS_ID = wo.WORK_ORDERS_ID 
 AND ao.[Op No] = st.Operation 
 AND ((stl.WorkCentre Between '297' And '383') 
 AND (st.Live=1) 
 AND (stl.[End Time] Is Null))

99% of this works fine by problem all come from the way the database handles time.

Code: Select all

	cast
	(		
		cast  
			(getdate() -  
			(stl.[Start Time] )	AS DECIMAL(18,5)  )  +
			(  
			(cast (ao.[Actual Run Time] AS DECIMAL(18,5))/24  )  )
		AS DATETIME)    
	AS [runtime]   

Firstly I take todays date in iso format (2007-03-01T13:00:00Z) and turn it into a decimal add it to actual runtime which is held in hours as a decimal (7.5) before I turn it into a date again and it works fine until you go over 24h, 30h comes out as 6:00:00

Does anyone know how to get it calculating properly. I tried doing it in Java but that causes it to crash.

[robin]

I know nothing about MSSQL but other databases I have used support the concept of an interval. For example:

select now(); // Return time right now

select now() + '3 days'::interval; // Return time 3 days from now; the :: is a cast - it says change the type of '3 days' from text to interval.

This MSDN page implies that intervals exist in MS land - generally MSSQL has everything any other database has:

https://msdn.microsoft.com/en-us/librar ... s.85).aspx

I cannot understand the variables in your code - getdate() presumably returns today, stl.[Start Time] is presumably an absolute time - maybe the time a job started - ao.[Actual Run Time] could be a duration or it could be the time that a job actually started vs. stl.[Start Time] as scheduled time? Anyway the arithmetic seems confused to me - what are you actually trying to compute?

Cheers,
Robin

[robin]

P.S If you want to do the arithmetic in Java then the Calendar class is your friend. You get an instance of this with the static method Calendar.getInstance() (there are other variants for different timezones/locales). You can set the calendar to a particular date (assuming you have a SqlDate object), then you can add days, months, intervals, then you call getTime() to get back a java Date object.

Google Java Calendar example.

Cheers,
Robin

[BiggestNizzy]

GETDATE() and NOW() are pretty much the same. Possible just the MS version of the same thing.

The ao.[] and stl.[] are values from the database.
[start time] is when the operator started the job. Its stored in a date format 2015-10-12T21:00:00Z
[Actual run time] is past runtime, if someone has been working on a job for a few days it will have the amount of time in it. Its in a decimal format ie 10.543, this is only updated when the part is finished or at the end of a shift.
[start time] is when it was started, its stored in date format but as the company who designed the database stores the date in [start date] it is stored as 1900-01-01T21:00:00Z start date is stored as 2015-10-12T00:00:00Z. We tried adding them together in the same way as excel but we couldn't get it to work.

What I need to do is calculate how much time has passed since the operator started and add it to the amount of time already logged. To give a total time.

Should be easy.

[BiggestNizzy]

In bed last night I thought to myself....

Code: Select all

   
DIFFFDATE(minute,getdate(),
cast
   (      
      cast  
         (getdate() -  
         (stl.[Start Time] )   AS DECIMAL(18,5)  )  +
         (  
         (cast (ao.[Actual Run Time] AS DECIMAL(18,5))/24  )  )
      AS DATETIME)  )  
   AS [runtime] 

Didn't work.

[robin]

Dude, that is clear as mud

Can you dump the schema such that we can see the definition of the tables (or at least these fields)?

From what you've written above, I think that both [Start Date] and [Start Time] are stored as date types; in the case of Start Date the HH:MM:SS are 00:00:00 and conversely in the case of time the YYYY-MM-DD is stored as 1900-01-01 (the so called epoch).

To convert these two into a single date value that is correct you need to do:

Code: Select all

[Start Date] + ([Start Time] - '1900-01-01T00:00:00Z'::<some date type>);  -- some date type should be the same type as Start Time itself is

The section in () will return an interval type because it is the difference between two dates. Thus when you add is to [Start Date] (which is just the date without the time) you should end up with a normal date/time value.

To then work out the interval from now() back to [Start Date/Time] you just subtract one from the other:

Code: Select all

getdate() - ([Start Date] + ([Start Time] - '1900-01-01T00:00:00Z'::<some date type>))

Is the SQL type of [Actual run time] decimal (or similar) or is it a time (or similar) type?

If it's decimal (and assuming it decimal in hours, i.e. 1.0 is the same as 01:00:00) then you need to convert decimal hours into an interval. This isn't something I've done before but I would expect that this will work:

Code: Select all

select getdate() + cast('1.3 hours' as interval);

So we just need a bit of a hack to convert your decimal type to a string of this type - try:

Code: Select all

select getdate() + cast((1.3 || ' hours')) as interval);

Assuming that doesn't syntax error on your machine then your total expression would be:

Code: Select all

(getdate() - ([Start Date] + ([Start Time] - '1900-01-01T00:00:00Z'::<some date type>))) + cast(([Actual Run Time] || ' hours') as interval)

Cheers,
Robin

[robin]

P.S. People that actually use SQL will probably have a better way of converting decimal hours to an interval without going via a textual format ...

[BiggestNizzy]

Got it

Code: Select all

ao.[Actual Run Time] + ao.[Actual Setup Time]+  
cast(  datediff(minute,stl.[Start Time] ,convert(varchar(10),getdate(),108)) as decimal(18,5))/60  as [Run Time] ,
	ao.[Run Time]+ao.[Setup Time]-(ao.[Actual Run Time] + ao.[Actual Setup Time]+  
	cast(  datediff(minute,Stl.[Start Time] ,convert(varchar(10),getdate(),108)) as decimal(18,5))/60)  as [Var]      
	

[BiggestNizzy]

New issue now I am passing a variable across in the URL and I want to use this in the SQL code.

I have an asp variable called @number stored in the URL
http://w3.website.com/page.aspx?number=123456

in the SQL select command I have

Code: Select all

DECLARE @num numeric(6) = 123456 
SELECT number.No
FROM Number.No
WHERE number.No = @num

I am looking to substite the number (123456) for the number held in the variable number that comes from the URL (123456)

[BiggestNizzy]

Fixed it, nothing to see here.