Tag: sql

sum sql group by values from another table

I’ve got 2 tables: Online Orders, Online orders details I have an issue on how to calculate the total amount of the order in Online Order table based on the detail order table. The detail table looks like: id_order Id_product quantity price value 1 2 1 3 3 1 3 2 2 4 2 1 […]

SQL Transpose \ Pivot

I am trying to do a weekly summary of individual items from the original table. The query should return 1 row for each specified Item type (i.e. Doors, Windows, etc) if no item is returned, it should return null values Original Table +——-+———–+———-+———–+——-+——+ |ITEMID | Date |ItemType |Account No |Amt | Desc | +——-+———–+———-+———–+——-|——| |10 […]

Cache looks like never been made in ASP.NET SQL

I’m developing website with query notification using SqlCacheDependency. When the website loads for the first time, if there is any cache, it retrieves the data from the cache while the opposite, if there is no cache, it retrieves data from database and then insert the cache. When the user inserts data, and clicks a button, […]

How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST[‘user_input’]; mysql_query(“INSERT INTO `table` (`column`) VALUES (‘$unsafe_variable’)”); That’s because the user can input something like value’); DROP TABLE table;–, and the query becomes: INSERT INTO `table` (`column`) VALUES(‘value’); DROP […]

Pivot rows to columns SQL 2008

I have done quite a bit of review here and have gotten close to what I need, but I am stumped on the last little bit (or so I hope). I am using dynamic pivot to transpose my data from DocID PropID StrVal NumVal DateVal 5938 21 TARRO, THOMAS A III NULL NULL 5938 21 […]

SQL Server : Replication Error – the row was not found at the Subscriber when applying the replicated command

I got the following error in Activity Monitor, The row was not found at the Subscriber when applying the replicated command. (Source: SQL Server, Error number: 20598) After some research, I found out the error occurs because it’s trying to delete records which are not exists in the subscriber(Also are not exists in publisher) {CALL […]

Choosing and assigning a value based among the duplicate records in sql

My data looks as below: Student_ID State 1 WI 1 IL 1 IL 2 WI 3 IL 4 IL 4 MN I want my Output to be as follows: Ouput: If the same student is in both WI and any other state then we need to mention as ‘multiple’, when student is only in WI […]

Is it possible to create read-only synonym in SQL Server?

We have 2 different databases DB1 and DB2 with tables in DB2 referring to tables in DB1. We are looking into the possibility of using SYNONYMS with read-only access in DB2 and use EntityFramework to refer to these SYNONYMS as tables in DB2. Since currently EF doesn’t span across multiple databases, after reading this article […]

Move nVarChar data from one table to another and return PK to origin table

I am looking for advice on the best SQL Server 2008 R2 approach to move a reasonably high number of nVarChar data type rows from one table to another (actually there are a number of tables). In summary the objective is: chapterOne is a table that has a nVarChar column called page_content a new table […]

calculation of amount for quarter

I have a question, I want merge my table data through date, All information provide in given table If Aggregation =Quaterly My table Table-1 Amount | Date | Aggregation 100 | 2013-01-01 | Quaterly 100 | 2013-02-01 | Quaterly 100 | 2013-03-01 | Quaterly 200 | 2013-04-01 | Quaterly 200 | 2013-05-01 | Quaterly 200 […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.