Microsoft Sharepoint and SQL

[robin]

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]      
	

That might work, but I really don't think you should need to be using substrings to extract time fields and then doing arithmetic on them - any sane system stores dates/times in some basic unit (seconds, milliseconds, whatever) and should be able to do arithmetic at that level. Anyway, I am assuming you're happy with the solution, so shall not agonise over it too long

[BiggestNizzy]

Guys your help was greatly appreciated last time and I was wondering if I could call on the collective wisdom of the group again
I have been playing with SQL again but I am stuck.

First off I made this up and it worked

Code: Select all

SELECT 
	SFDC_Transactions.Operation, 
	SUM
		(datediff
			(minute,
				(SFDC_Transactions.Start_Time+SFDC_Transactions.Start_Date),
				(SFDC_Transactions.End_Time+SFDC_Transactions.End_Date)
			)
		)
FROM 
"Emax-Live".dbo.SFDC_Transactions SFDC_Transactions
WHERE 
	(SFDC_Transactions.Work_Order='NP') AND 
	(SFDC_Transactions.Live=0) AND 
	(SFDC_Transactions.End_Date>=getdate()-7)
GROUP BY 
	SFDC_Transactions.Operation

(Dates and times are held seperatly and I need to join them together)
It produces a table like so
code - time (in mins)
10 - 500
20 - 500
30 - 500
40 - 500

I then wanted to relate the codes to something meaningfull and I had to jump through hoops (jump across some tables) to find this.

Code: Select all

SELECT 
	SFDC_Transactions.Operation, 
	WO_Op_List.'Op Description',
SUM
	(
	datediff
		(
			minute,
			(SFDC_Transactions.Start_Time+SFDC_Transactions.Start_Date),
			(SFDC_Transactions.End_Time+SFDC_Transactions.End_Date)
		)
	)

FROM 
	"Emax-Live".dbo.SFDC_Transactions SFDC_Transactions,
	"Emax-Live".dbo.WO_Op_List WO_Op_List, 
	"Emax-Live".dbo.WO_Operations WO_Operations, 
	"Emax-Live".dbo.WORK_ORDERS WORK_ORDERS

WHERE 
	SFDC_Transactions_List.Operation = WO_Operations.Op_No AND
	WO_Operations.WO_ID = WORK_ORDERS.WORK_ORDERS_ID AND 
	WO_Op_List.WORK_ORDERS_ID = WORK_ORDERS.WORK_ORDERS_ID AND 
	(
		(SFDC_Transactions_List."Work Order"='np') AND
		(WORK_ORDERS.WO_No='136012') AND
		(SFDC_Transactions_List."End Date">=getdate()-7)
	)

GROUP BY SFDC_Transactions.Operation

When I try to run It I initially get a syntax error before I get
The multi-part identifier "SFDC_transactions_list.operation" could not be bound.
The multi-part identifier "SFDC_transactions_list.Work Order" could not be bound.
The multi-part identifier "SFDC_transactions_list.End Date" could not be bound.

I expect it to be something stupid like a missing comma but I can't get it to work.

Any help would be greatly appreciated.

[hendeg]

You don't have a SFDC_Transactions_List table listed in your FROM section.

Should that be SFDC_Transactions?

[BiggestNizzy]

You don't have a SFDC_Transactions_List table listed in your FROM section.

Should that be SFDC_Transactions?

I am a nugget!

Code: Select all

SELECT 
	WO_Op_List."Op Description",
SUM
	(
	datediff
		(
			minute,
			(SFDC_Transactions.Start_Time+SFDC_Transactions.Start_Date),
			(SFDC_Transactions.End_Time+SFDC_Transactions.End_Date)
		)
	)

FROM 
	"Emax-Live".dbo.SFDC_Transactions_List SFDC_Transactions_List, 
	"Emax-Live".dbo.SFDC_Transactions SFDC_Transactions,
	"Emax-Live".dbo.WO_Op_List WO_Op_List, 
	"Emax-Live".dbo.WO_Operations WO_Operations, 
	"Emax-Live".dbo.WORK_ORDERS WORK_ORDERS

WHERE 
	SFDC_Transactions_List.Operation = WO_Operations.Op_No AND
	WO_Operations.WO_ID = WORK_ORDERS.WORK_ORDERS_ID AND 
	WO_Op_List.WORK_ORDERS_ID = WORK_ORDERS.WORK_ORDERS_ID AND 
	(
		(SFDC_Transactions_List."Work Order"='np') AND
		(WORK_ORDERS.WO_No='136012') AND
		(SFDC_Transactions_List."End Date">=getdate()-7)
	)

GROUP BY WO_Op_List."Op Description"

now I have a new problem

arithmetic overflow error converting expression to data type int warning:null value is eliminated by an agrregate

[hendeg]

Not sure but as you're looking at transactions in the last 7 days would you have any that aren't complete and have NULL end date or time?

[BiggestNizzy]

Not sure but as you're looking at transactions in the last 7 days would you have any that aren't complete and have NULL end date or time?

When I did the >=getdate()-7 on the end date I had hoped that it wouldn't pull anything that wasn't running. I have taken the SUM and Group BY out and I appear to be getting duplicate values (65k+ records) and that is bringing with it some mega numbers when you add them together.

[hendeg]

Try replacing getdate()-7 with dateadd(day, -7, getdate())

[Ferg]

Not sure if helpful in your specific context, but if your doing elapsed time comparisons from a DB then I always used to use 'epoch' as it's literally a date in a number of seconds. Then you do all your calculatuons in seconds and it makes life a lot easier. You can then convert epoch to date if you need a result in readable format. Just had a look and MSSQL supports epoch.

It's been a while since I was an SQL slinger but this approach was often the most powerful. Not been on front line for a few years though.

[BiggestNizzy]

Cheers guys I managed to get it working but will take another look at using your suggestions. The database is a bit of a nightmare it's from an ERp system where it appears you have fudge built upon fudge, dates are not always stored as dates (sometimes as text) for example. I have tables that are identical but with a couple of extra columns that allow it to link in.

It would probably help if I could get my head around "JOIN" but I just end up using value = value

[johncam]

The database is a bit of a nightmare it's from an ERp system

Its not EFACS by any chance is it?

Cheers,

John

[BiggestNizzy]

The database is a bit of a nightmare it's from an ERp system

Its not EFACS by any chance is it?

Cheers,

John

No it's emax. To be fair to them I haven't met anyone who likes the particular ERP system they use.

[BiggestNizzy]

Hi guys got all that to work. But I am trying to get my head around joining tables rather than pulling everything in and then filtering it .

If I have 2 tables (table 1 and 2) and I want to join them together on fields with the same values but different names

Table1.opno
Table2.operation

FROM
Dbo.Table1 CROSS JOIN
Dbo.Table2

I am guessing just adding a join won't do as the column names are different but I am also having problems as one is an integer and the other a varchar field with data that's wrong (instead of numbers we have random txt) should I be using something like a RIGHT JOIN ?

edited to change JOIN to CROSS JOIN

[hendeg]

If you want to join the tables and return only rows where the values of your join match then you'd user INNER JOIN.

SELECT *
FROM Table1 INNER JOIN
Table 2 ON Table1.opno = Table2.operation

However, because you have different data types I don't think you can use a join. You'll have to do;

SELECT *
FROM Table1, Table 2
WHERE Table1.opno = CAST(Table2.operation AS INT)

assuming Table2.operation is the varchar field and the values can be converted to INT.

[BiggestNizzy]

Cheers I changed it to an INNER JOIN ON table1.op table2.operation It appears to cut out all the crap as the data in table2 should be concidered the gospel.

[BiggestNizzy]

Been busying myself away with this and I have a reasonable idea of what I am doing with basic sql. As a result I have created a good few webpages that pull data for a database and display it on a TV using a raspberrypi booting straight to a webpage.

Problem I now have is I have lots of webpages that are usefull and some that are nice to have on the odd occasion.
I am using
<meta http-equiv="refresh" content="60;url=http://webpage/page2.aspx">
on page 1 and when it gets to page 5 it sends it back to page 1

However pages 1 & 3 contain inportant information. the rest are more like notices.
Does anyone know how to write a page that will
Load page 1
then load either page (2,4 or 5)
Load page 2
repeat

It would be great if it could be handled from a seperate XML file or something. I have tried googling but can't find anything similar.

I was going to try a kiosk example with win 10 iot core but my play raspberrypi got used.

Any help in pointing me in the right direction would be greatly appreciated.