Differences

This shows you the differences between two versions of the page.

--- bw:sql_server_tips [2019/05/01 11:07]
runger created
+++ bw:sql_server_tips [2019/06/04 15:18] (current)
@@ Line 1: / Line 1: @@
-===== Store Procedure for SAP Business One Special Pricing =====
+===== Stored Procedure for SAP Business One Special Pricing =====
 This procedure can be used as the data source in a Versago look-up report to find the lowest price for a business partner (typically a customer) based on price list, special pricing, date sensitive pricing, and quantity break pricing.  The procedure is placed in the target SAP Business One database.
@@ Line 54: / Line 54: @@
 AND a.Amount <= @qty
 </code>
+===== Create a DateTime Value From Two Separate Columns =====
+In many places, SAP Business One (and other applications) store the date and time in two separate columns.  At the same time, it may be necessary to have the two values combined into a single DateTime value.  An example of this is for use in a Versago calendar report.  The following SQL statement (SQL Server only) is used to create the single DateTime value.
+<code sql>
+cast(
+cast(datepart(year,YourDateValue) as varchar)
++ '-'
++ right('00'+cast(datepart(month,YourDateValue) as varchar),2)
++ '-'
++ right('00'+cast(datepart(DAY,worked_date) as varchar),2)
++ ' '
++ left(right('0000'+replace(isnull(YourTimeValue,'0000'),':',''),4),2)
++ ':'
++ left(right('0000'+replace(isnull(YourTimeValue,'0000'),':',''),4),2)
+as datetime) as [CombinedDateTime]
+</code>
+===== Use DISTINCT in a SQL Select Statement =====
+If the DISTINCT operator is needed, it must be done within a sub-query.  For example, assume you have 12 records that have three distinct values of "Name".  You want to display only the three unique names.  If you use the following statement, Versago will reject it.
+<code sql>
+SELECT DISTINCT
+t0.Name
+from TableName t0
+</code>
+However, if this statement is used as a sub-query, it can be used in Versago.  The statement will look like this.
+<code sql>
+SELECT a0.* from
+(
+SELECT DISTINCT
+t0.Name
+from TableName t0
+) a0
+</code>
+In this case, "a0" is an alias for the results of the SELECT DISTINCT statement.  There is no significance to the alias name "a0".  It is simply used here for convenience.

Wiki

User Tools

Site Tools

Differences

Page Tools