logo
down
shadow

How to create custom, dynamic string sequence in SQL Server


How to create custom, dynamic string sequence in SQL Server

By : Gordon
Date : November 20 2020, 11:01 PM
I wish did fix the issue. An IDENTITY column might have gaps. Just imagine an insert which is rollbacked out of any reason...
You could use ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(6),OrderDate,112) ORDER BY OrderDate) in order to start a sorted numbering starting with 1 for each month. What will be best is depending on the following question: Are there parallel insert operations?
code :
CREATE TABLE OrderType(ID INT,Name VARCHAR(100),Abbr VARCHAR(2));
INSERT INTO OrderType VALUES(1,'Normal Order','NO')
                           ,(2,'Special Order','SO');
GO
CREATE FUNCTION dbo.OrderCaption(@OrderTypeID INT,@OrderDate DATETIME,@OrderID INT)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN ISNULL((SELECT Abbr FROM OrderType WHERE ID=@OrderTypeID),'#NA')
         + '/' + CAST(YEAR(@OrderDate) AS VARCHAR(4))
         + '/' + REPLACE(STR(MONTH(@OrderDate),2),' ','0')
         + '/' + REPLACE(STR(@OrderID,5),' ','0')
END
GO


CREATE TABLE YourOrder
(
     ID INT IDENTITY
    ,OrderDate DATETIME DEFAULT(GETDATE())
    ,OrderTypeID INT NOT NULL --foreign key...
    ,Caption AS dbo.OrderCaption(OrderTypeID,OrderDate,ID)
);
GO

INSERT INTO YourOrder(OrderDate,OrderTypeID)
VALUES({ts'2016-01-01 23:23:00'},1)
     ,({ts'2016-02-02 12:12:00'},2)
     ,(GETDATE(),1);
GO

SELECT * FROM YourOrder
ID  OrderDate               OrderTypeID     Caption
1   2016-01-01 23:23:00.000      1          NO/2016/01/00001
2   2016-02-02 12:12:00.000      2          SO/2016/02/00002
3   2016-10-23 23:16:23.990      1          NO/2016/10/00003


Share : facebook icon twitter icon
How to create key-value string sequence from another plain sequence in XPath 2.0

How to create key-value string sequence from another plain sequence in XPath 2.0


By : user3701865
Date : March 29 2020, 07:55 AM
will be helpful for those in need I needs an XSLT function to create key-value strings sequence ( 'key1_val1', 'key2_val2', 'key3_val3' ) from plain strings sequence ( 'key1', 'val1', 'key2', 'val2', 'key3', 'val3' ). , You are looking for
code :
for $i in (1 to count($seq))[. mod 2 = 1] return concat($seq[$i], '_', $seq[$i + 1])
How can I create a map<String, Dynamic> for Firestore with Lists of custom class Objects?

How can I create a map<String, Dynamic> for Firestore with Lists of custom class Objects?


By : leo
Date : March 29 2020, 07:55 AM
this one helps. I'm trying to add persistence to my app using firestore. It seems ace, but I've hit a bump in the road, and googling seems to be failing me. I think this may be more of a dart question than firestore, so please edit as you see fit! , Create a List of Map
code :
returnMap['attributeHistory'] = attributeHistory.map((attribute) {
  Map<String,dynamic> attributeMap = new Map<String,dynamic>();
  attributeMap["id"] = attribute.id;
  // same for xpos and ypos
  return attributeMap;
}).toList();
How to create a Dynamic Sequence based on another field?

How to create a Dynamic Sequence based on another field?


By : user1503906
Date : March 29 2020, 07:55 AM
With these it helps I want to create a dynamic sequence for products based on the field category_id. I've added a category_code in product.category. But once I call it inside the create function, it returns False: , Try like this:
code :
class ProductProduct(models.Model):
_inherit = 'product.product'

     @api.model     
     def create(self, vals):
         record=super(ProductProduct, self).create(vals) 
         if 'default_code' not in vals or vals['default_code'] == '/':
             sequence = self.env.ref('product_sequence.seq_product_auto')
             print record.categ_id.category_code
             record.default_code =record.categ_id.category_code + sequence.next_by_id()
         return record
Create Custom Sequence

Create Custom Sequence


By : user2016431
Date : March 29 2020, 07:55 AM
it helps some times I need to create a Custom Sequence which should create sequence for below ranges XV00AA-XV99ZZ and many other ranges , This should do the trick.
code :
DECLARE @how_many_do_you_want INT = 67599;  -- 67599 is where it runs out of legit values.

WITH 
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), 
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Tally (n) AS (
        SELECT TOP (@how_many_do_you_want)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )
SELECT 
    rn = t.n,
    CONCAT('XV', cn.char_num, a.alpha_1, a.alpha_2)
FROM
    cte_Tally t
    CROSS APPLY ( VALUES (t.n % 100) ) m (mod_100)
    CROSS APPLY ( VALUES (((t.n - m.mod_100) / 100) % 26 + 1) ) g1 (group_1)
    CROSS APPLY ( VALUES (t.n / 2600 + 1) ) g2 (group_2)
    CROSS APPLY ( VALUES (RIGHT(CONCAT('0', m.mod_100), 2)) ) cn (char_num)
    CROSS APPLY ( VALUES (CHAR(g1.group_1 + 64), CHAR(g2.group_2 + 64)) ) a (alpha_1, alpha_2);
How can I create custom dynamic data mask function in SQL Server?

How can I create custom dynamic data mask function in SQL Server?


By : venkat karthikeyan
Date : March 29 2020, 07:55 AM
like below fixes the issue SQL Server hasn't the ability to define a custom function for masking. You can try another tool for DDM as DataSunrise. DataSunrise works as reverse proxy and can rewrite SQL on the fly for data masking purpose
Related Posts Related Posts :
  • SQL subquery to joins -
  • how many result test we can give a for execute sql task
  • Checking quantities for future dates and returning shorts
  • What is the quickest way in Oracle SQL to find out if one or more duplicates exist in a table?
  • Oracle SQL: Dynamic timeframe calculation
  • Get rows from current month if older is not available
  • Is CAP theorem wrong?
  • Average and group by in SQL but for best 10 records only
  • using where in count sql
  • SQL - How create a select that makes search possible
  • Which is the best approach for creating Shopping cart for E-commerce site
  • How to define array/collection in stored procedure?
  • Why does my Access SQL VBA code jump out of the Sub?
  • MS SQL - Conditional UPDATE return result
  • SQL Server - How to check if string is text or any representation of 0?
  • How do I create this output in SQL
  • Optimizing rank() query
  • Get the information in terms of time about people who are free on a particular day
  • Find the month in which maximum number of employees hired
  • Count posts threads comments but also return those with no threads
  • Count consecutive NULL values for given ID
  • How to create an integer from a certain string cell in PSQL
  • SQL - Using SELECT in the WHERE clause of another SELECT
  • Find the gap in ID column + select the prev/next date column
  • EXEC an INSERT with a dynamic Schema TSQL SSMS
  • Try optimize a specific SQL query
  • Importing excel to access with wildcards
  • How to select 1st and 2nd row instance in SQL table having the same value in another table?
  • Invalid Operation Exception when filling DataSet
  • Select max from calculated column alongside other columns
  • Selecting non duplicates from distint query
  • How to stop a single database in an sql instance?
  • Oracle error:missing right parenthesis
  • Non-Equi Self Join
  • Combine two tables for one output with separation on different columns
  • Select the used and not used Rows from Foreign key constraints
  • SQL stored procedure, Column calculated from two other calculated columns
  • Default to a value when a sub-query statement fails?
  • How do I do an SQL query based on a foreign key field?
  • How can you have two COUNT queries GROUPED BY the same column in SQL?
  • How to select only the second max date from a table
  • how to get previous 11 month names from sysdate using oracle
  • figuring out how many block accesses are needed
  • Calculate difference of dates belonging to same group in sql
  • ERROR Message: ORA-00923: FROM keyword not found where expected
  • Oracle SQL statement to update column values based on specific condition
  • SQL Server throwing errors when trying to reference newly created column in already existing table
  • SQL Concatenation Query - Four As in Concatenated Name
  • When utilizing a microservices architecture, will the underlying read/write database become a bottleneck?
  • SQLite GROUP BY on result of union not using indexes of constituent tables
  • Prevent duplicates for a certain GraphCool model
  • Finding entry page, exit page and bounces -sql
  • Complex Self-Join
  • Connected Components
  • SQL Queries Processing order is not consistent
  • Find 3rd highest cost from table
  • How to log queries written in eclipse BIRT
  • Why does round(143.23,-1) return 140?
  • Find highest value in column using ANY or ALL
  • Need SQL query for hierarchical data in a flat result set
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com