We are working on a C# application, we've been using Linq to SQL or standard ADO (when performance needed) to work with SQL Server. We have a table layed out like so: Customer ID, Year/Month, Product Name, Quantity Each customer has additional columns per product. We need display this information in a data grid like so: Customer, Y...
I have a table with Student ID, Service, and Provider. I want to show the DISTINCT Providers for each Service, but ONLY show NULL Providers if there is no other Provider for that Service and ID. In other words, if a Student has a certain Provider and Service, I don't want to select where the Provider is NULL, unless that specific Studen...
I need to create a sales & commission report Basically it goes (please forgive the blatent craziness of the SaleDate table, but I'm simplifying the business logic, and in reality it actually makes sense to have it this way) SELECT agentName, SUM(sales.Amount) AS Gross, SUM(sales.Amount * sales.Commission) AS Commission F...
Consider the need to create a resultset of dates. We've got a start and end dates, and we'd like to generate a list of dates in between. DECLARE @Start datetime ,@End datetime DECLARE @AllDates table (@Date datetime) SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009' --need to fill @AllDates. Trying to avoid loop her...
How can i get this result with changing query below. foo 01 02 03 declare @a as int set @a = 1 select single.* from (select case when 0=0 then '0'+ case when @a = 1 then '1' when @a = 2 then '2' when @a = 3 then '3' end end as foo) single cross join (select 1 as bir union all select 2 union all select 3) multi ...
Hi, How do I Select records between two dates in two columns? Select * From MyTable Where 2009-09-25 is between ColumnDateFrom to ColumnDateTo I have a date (2009-09-25) and I like to select the records that is in the timeframe ColumnDateFrom to ColumnDateTo. Sample Record 1 ColumnDateFrom = 2009-08-01 AND ColumnDateTo = 2009-...
Say I write the query: INSERT INTO DestinationTable (ColumnA, ColumnB, ColumnC, etc.) SELECT FROM SourceTable (ColumnA, ColumnB, ColumnC, etc.) And my source table has 22 million rows. SQL server fills up my hard drive, and errors out. Why can't SQL server handle my query? Should I use a cursor and insert a row at a time? PS - it ...
Many times when I've written stored procedures, etc. I use a CURSOR at first and later find some performance issue with my procedure. Every thing I read says CURSORS are awful, cause unnecessary locking, etc. and performance testing proves the same. My question is when do you use a CURSOR and in what situations are they useful or good...
I want to clone multiple tables' rows that have a single primary key (PrimKey). What's the quickest way to clone a row in SQL Server 2005? Here's an example, Clone PrimKey1 to get PrimKey2. So I try the following : INSERT INTO PrimKeys SELECT 'PrimKey2' AS PrimKey,* FROM PrimKeys WHERE PrimKey='PrimKey1' But of course the is...
I want to update a single record in a table to reflect that a given client session has acquired the record (and now owns it for further updates) within a multi-session environment. I've got this so far: create procedure AcquireRow( @itemNo int, -- Item ID to acquire @sessNo int, -- Session ID @res char(1...
CREATE PROCEDURE [dbo].[PL_GEN_PROVN_NO1] @GAD_COMP_CODE VARCHAR(2) =NULL, @@voucher_no numeric =null output AS BEGIN DECLARE @NUM NUMERIC DECLARE @PNO NUMERIC SET @PNO = 0 DECLARE @PNO1 NUMERIC SET @PNO1=0 -- begin transaction IF NOT EXISTS (select GLDC_...
Typically when you specify an identity column you get a convenient interface in SQL Server for asking for particular row. SELECT * FROM $IDENTITY = @pID You don't really need to concern yourself with the name if the identity column because there can only be one. But what if I have a table which mostly consists of temporary data. Lots...
i have a table in SQL Server 2008 which its name is Table1. Table1 has a column named CreateDate which its data type is datetime. Now, I wanna to get records that their createDate field values are more than for instance 1 hour. ...
I want to execute this query : INSERT INTO [Order] (FactorId, ProductId, Entity) VALUES((SELECT Top 1 FactorId FROM Factor WHERE UserId = @UserId AND Status = -1), @ProductId, @Entity) but the following error occurs : Subqueries are not allowed in this context. Only scalar expressions are allowed. ...
How can I build a query statement like this: select * from men where Tdate between 01/01/01 and 02/02/02 The Tdate column is of type nvarchar I am working with SQL Server Compact Edition (sqlCE) Thanks ...
I've got a stored procedure which runs this to create a new ticket number: INSERT INTO [Test_DB42].[dbo].[TicketNumber] ([On], [By]) VALUES (CURRENT_TIMESTAMP, CURRENT_USER) When I run with a user with db_datareader and execute permissions, I get the Active Directory samAccountName value which is what I want - this user gets a...
Using SQL 2000, SQL 2005 Old Database Name is – Sysdatabase New Database Name is - Dual_Proone, Dual_Protwo In the above two database table name and column name are different, but values are same. For Example Sysdatabase (Database Name) Person (Table Name) Column Names and values are ID Date 001 23-02-2009 002 24-02-2009 So o...
Using SQL Server 2000 I make a trigger in new database CREATE TRIGGER [CARDEVENTSAVING] ON [dbo].[T_CARDEVENT] AFTER INSERT AS DECLARE @EDATE VARCHAR(50) DECLARE @ETIME VARCHAR(50) DECLARE @EDOOR NVARCHAR(50) DECLARE @EFLOOR NVARCHAR(50) DECLARE @EMPID NVARCHAR(50) DECLARE @TAG NVARCHAR(50) DECLARE @ENAME NVARCHAR(50) DECLARE @ELNA...
How to retrieve the names of all Nonsystem-databases from SQL Server 2000 using a TSQL query? I have anticipated: SELECT * FROM sysdatabases where dbid >4 order by dbid it does not seem to be reliable. Anything else? ...
I have a table with an Identity column which provides my ticketNumber. I want another table to provide a ticketStepNumber. A ticket may have 0 or more steps, so I won't always be creating a ticketStepNumber. The ticketStepNumber value is a combination of the ticketNumber column (int) and the stepNumber column (int). I'd like to def...