sql-server,sqlite , want to generate coupon code 5 digit number [duplicate]


want to generate coupon code 5 digit number [duplicate]

Question:

Tag: sql-server,sqlite

This question already has an answer here:

i want to create a coupon code generator by using SQL database but i don't know how to generate 1000 of random number without repeating them. so can someone help me it's important. thanks


Answer:

Records in a relational database tables are unordered by nature. therefor, you can simply create a table that has all the values between @First and @Last (0 and 9999 in your case), and then use a random order by when selecting from that table. you can also use a simple int in the database table and just format it when you select the data from the table.

Since my main database is Sql server, and I have no experience with sqlite, I will use Sql Server syntax in my code example, and leave it up to you to find the sqllite equivalent.

First, create the table:

CREATE TABLE Tbl 
(
    IntValue int PRIMARY KEY,
    IsUsed bit NOT NULL DEFAULT 0
)

Then, populate it with numbers between 0 and 9999:

;With CTE AS (
    SELECT 0 As IntValue
    UNION ALL
    SELECT  IntValue + 1
    FROM CTE
    WHERE IntValue + 1 < 10000
)

INSERT INTO Tbl (IntValue)
SELECT IntValue
FROM CTE
OPTION(MAXRECURSION 0)

Then, you want to select multiple values each time, so I would write a stored procedure like this:

CREATE PROCEDURE stp_GetCouponCodes
(
    @Number int = 5 -- or whatever number is default
)
AS
BEGIN
  DECLARE @UsedValues AS TABLE
  (
      IntValue int
  )

  BEGIN TRY

  BEGIN TRANSACTION

  INSERT INTO @UsedValues 
  SELECT TOP(@Number) IntValue
  FROM Tbl
  WHERE IsUsed = 0
  ORDER BY NEWID()

  UPDATE Tbl
  SET IsUsed = 1
  FROM Tbl 
  INNER JOIN 
  @UsedValues uv ON(Tbl.IntValue = uv.IntValue)

  SELECT RIGHT('00000' + CAST(IntValue as varchar), 5)
  FROM @UsedValues 

  COMMIT TRANSACTION
  END TRY

  BEGIN CATCH
     IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
  END CATCH
END

Then, when ever you want to generate coupons, simply execute the stored procedure with the number of coupons you want:

EXEC stp_GetCouponCodes 10;

See working fiddle example here.


Related:


sqlite: How can results be made unique according to a column


sqlite,android-sqlite
Currently, the following sqlite query returns 45 records. I know that only 4 albums exist. How can the query be made to return only those records that are unique to albumTable.album. Pseudo Code: where albumTable.album is UNIQUE query = new StringBuilder(); query.append("select albumTable.album, artistTable.artist, songTable.filepath "); query.append("from albumTable "); query.append("inner...

Fastest way to add a grouping column which divides the result per 4 rows


sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

Error connecting to MSSQL using PHP


php,sql-server,pdo,odbc,sqlsrv
I am receiving an error as below: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08001]: [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Connection string is not valid [87]. ' My codes are as follow: $this->link = new PDO( "sqlsrv:server=$this->serverName:$this->port;Database=$this->db", "$this->uid", "$this->pwd" ); I wish someone can enlighten...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables


sql-server,join
I have two tables, one with various details such as Username, email id, user code and another table with columns which are: UserCode, supervisor email id, active_flag I required an output of a table which only displays the active users i.e. the users with active_flag (Y or N) column as...

SQL: overcoming no ORDER BY in nested query


sql,sqlite
I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...

converting varchar to Int SQL SERVER


sql,sql-server,casting
I needed help with something I am not entirely sure how to resolve. This is my code : SELECT DISTINCT Nr_of_Times_Cust_No_Appears=CASE WHEN CAST(a.TV_Code AS Int)-CAST(BB_Code AS Int)=0 THEN COUNT(*) OVER (PARTITION BY BB_Code) ELSE 'Not same' END FROM table Basically, the above code is meant to make sure that the...

How to use OFFSET and Fetch without Order by in SQL Server


sql-server,sql-server-2012,sql-order-by,fetch,offset
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...

How to select next row after select in SQL Server?


sql,sql-server
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...

How to insert excel formula to cell in Report Builder 3.0


sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

Microsoft SQL Insert into subset of table columns fails [on hold]


sql-server,sql-server-2008
Can anyone help me find the column or columns in this table where data would be truncated? I am trying to execute the SQL insert below, but it is failing with the error: String or binary data would be truncated. Which I understand means a value is too big to...

Default the year based on month value


sql,sql-server
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...

copy table and drop it


sql,sql-server,sql-server-2008,tsql,stored-procedures
I know we can simply create a new table and copy the old table by doing select * into tbl2 from tbl1 i would like to check if table tbl2 exists, if it does then copy all the rows, if it doesn't then create a new one without having to...

Recursive Lag Column Calculation in SQL


sql,sql-server,recursion
I am trying to write a procedure that inserts calculated table data into another table. The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not...

No such table found even though app successfully opens database from assets folder


java,android,sqlite,android-sqlite,sqliteopenhelper
I'm new to android development. I'm trying to use a pre-populated database whoch is stored in my assets folder. Log shows that the database gets opened but 'NO SUCH TABLE ERROR' is shown.I can't find any solution to this error. Please help. MyDatabase.java : package com.example.android.atlas; import android.content.Context; import android.database.Cursor;...

How do I convert this tSQL statement to LINQ using group by in a sub query


c#,sql-server,linq,tsql
I have the following MSSQL query I am trying to convert to LINQ. I am using entity framework with the following syntax to get at the data. var rv = (from i in DC.TableA select i).ToList(); This is the sql I want to write a C# LINQ query for but...

Order by clause in update query in sql server [on hold]


sql-server
I need to update table using order by clause in sql server 2008 e.g Update tblTempChek Set TmpCheckIn='15:50:03' Where TempID IN ( Select TempID From tblTempChek Where convert(date, TmpDate)='2015-06-23' AND UserID='1' Order By TempID Desc ) but this gives error Msg 1033, Level 15, State 1, Line 3 The ORDER...

Setting time limit in SQL Query


sql-server
I want a query to make which return 1 when the time is in between of 10:00 pm - 5:00 pm. Else it should return 0. Select * from table ...

SQLite: Individual tables per user or one table for them all?


database,sqlite
I've already designed a website which uses an SQLite database. Instead of using one large table, I've designed it so that when a user signs up, a individual table is created for them. Each user will possibly use several hundreds of records. I done this because I thought it would...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00


c#,asp.net,sql-server,date,gridview-sorting
I have a connected SQL Server database in Visual Studio and am displaying its content in a grid. I created a dropdown menu with the column names as selectable options and a text field to filter for specific content, e.g., DropDown = "Start" - Textfield = 14.03.2015 = Filter Column...

TSQL - Error in stored procedure due to conversion failure


sql-server,sql-server-2008,tsql
I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...

INSERT INTO fails due to incorrect conversion T-SQL


sql-server,tsql
My table looks like this: CREATE TABLE MyTable ( TableID INT IDENTITY NOT NULL, ForeignID INT NOT NULL, Value sql_variant NOT NULL, CodeOne VARCHAR(4) NOT NULL, CodeTwo VARCHAR(4) NOT NULL ) I'm trying to do a insert with the following code: INSERT INTO MyTable(ForeignID, Value, CodeOne, CodeTwo) VALUES ( 1,...

Return 0 in sum when no values to sum - sql server


sql,sql-server,sql-server-2008-r2,sum
Trying to return 0 if any of these columns have no values returned, in my particular case 'Past Due' has no values to total, so it is returned, so i get the return in the second snippet here. How can I return something if these are no values to count?...

Improving work with SQL DataTime


sql,sql-server,database,tsql
I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...

Connecting to database using Windows Athentication


sql-server,vb.net,authentication,connection-string
I would like to use window authentication in my program to connect to my sql server. users already have certain permissions on the SQL server and I would like to leverage that in my program. The way I currently connect to the server is using this connection string. Dim ConnectionString...

Title search in SQL With replacement of noice words [on hold]


sql,sql-server,sql-server-2008
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

Unable to display SQLite data as listview using phonegap


javascript,html,sqlite,cordova
The function get_list() here is able to execute the query but not able to display the rows. function get_list() { db = window.openDatabase("timeDB", "1.0", "time", 200000); db.transaction(populateDB, transaction_error, populateDB_success); function populateDB(tx) { tx.executeSql("SELECT (date) FROM time", [], function (tx, res) { var len = result.rows.length; if (len > 0) {...

SQL Multiple LIKE Statements


sql,sql-server,tsql,variables,like
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

Join SQL query Results and Get-ChildItem Results


sql-server,sql-server-2008,powershell
Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...

How to Implement Dependent Dropdownlist in MVC4 Razor and using SQL server also


sql-server,asp.net-mvc-4,razor
I've 2 Dropdownlist called States and Country. but only the States DDL(DropdownList) will be enable the country DDL will be diable. when I select a option in States which is already stored in SQL. The Country DDL Should Show the Selected state's Country automatically from DB. Please help Me..... Ex:If...

Sql injected code is inserted to my database . How to remove it


sql-server
One of my sql table is injected with some html code. It is inserted such that the html tags are inserted after actual data. How to remove this from my table.

left join table, find both null and match value


sql,sql-server,join
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

Merging two tables into new table by ID and date


sql,sql-server,phpmyadmin
Let' say I have two tables: table 1: col a | col b | col c 1 | 62215 | 21 1 | 62015 | 22 2 | 62215 | 23 2 | 51315 | 24 and table 2: col a | col b| col f 1 | 62015| z...

Select count Columns group by No


sql,sql-server
Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...

SQL Server: Issues with data type


sql,sql-server,toad
SELECT pa.[type], (SUM(pa.Actions_Logged)/SUM(pi.Impressions_Served)) AS ActionRates from Performance_Actions pa INNER JOIN Performance_Impressions pi ON pa.Alternative = Pi.Alternative GROUP BY pa.[type]; The above query generates an error: Lookup Error - SQL Server Database Error: Arithmetic overflow error converting expression to data type int. Both pa.Actions_Logged and pi.Impressions_Served are int and the result...

Android SQLite: Fast insert/update


java,android,sqlite
In my Android application I am using compiled statements to be able to insert rows quickly: insert = db.compileStatement( "INSERT INTO foo (id, aaa, bbb, ccc) " + "VALUES (?,?,?,?)"); This works correctly and quite fast. However when there is already foo with id, I am receiving following exception: column...

Implement reference key in SQL Server


sql-server,sql-server-2008
I have a TestPack table that can have n no of Lines associated with it. But a Line may exist without a TestPack. Later on, a Line might be assigned a TestPack#. What this relationship is called and how do I implement this relationship in SQL Server 2008?...

One identifier for set of values


sql,sql-server,sql-server-2008,sql-server-2008-r2
I have this two columns. One is filled with some data, second is null. col1|col2 --------- null| 72 null| 72 null| 72 null| 33 null| 33 null| 12 null| 12 null| 55 null| 72 I want to generate values for col1 that will gather and group values from col2. So...

SQL Server 2012 & Polybase - 'Hadoop Connectivity' configuration option missing


sql-server,hadoop,sql-server-2012
As described in the title, I am using SQL Server 2012 Parallel Data Warehouse with Polybase feature to try to access a HDInisght Hadoop cluster. As a starting point for every connection to Hadoop from SQL Server, I find to execute the command sp_configure @configname = 'hadoop connectivity', @configvalue =...

Looping distinct values from one table through another without a join


sql,sql-server,tsql,while-loop
I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...

Executing dynamically created SQL Query and storing the Query results as a temporary table


sql,sql-server
I am creating a SQL Query dynamically. After it's been created I want to execute it and store it as a temporary table. WITH [VALIDACCOUNTS] AS( EXEC (@sqlQuery)) ...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument


sql,sql-server,sql-server-2008
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

Take thousand value in SQL


sql,sql-server
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...

SQL Customized search with special characters


sql,sql-server,sql-server-2008
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

Foreign key in C#


c#,sql,sql-server,database
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

Can someone explain to me how this statement is an exclude?


sql,sql-server,tsql
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

Retrieve data from one table and insert into another table


sql,asp.net,sql-server
I am trying to retrieve data from one table and then insert it into another table. This is a a sample of the first table in which there is the following data. tb1 is the table which consists of data. The two columns Manager and TeamLeader basically means for example...

Purging Database - Count purged/not-purged tables


mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

Get unique row by single column where duplicates exist


sql,sql-server
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...

sqlite query slow, how to optimize (using linq to entities)


c#,linq,entity-framework,sqlite
I have used MS SQL server a few times and have yet to run into a speed issue when querying, using linq to entities. This time, I am using sqlite, as to ship the entire database with the application. I have a winforms app that contains 4 search fields. My...

Cannot Browse in sql to backup bak file


sql-server
As the title says I cannot browse and cannot see backup folder. is there any other way to restore bak file ? or how can I fix this ? ...