Monday, August 12, 2013

[T-SQL] How to add interim summary using T_SQL?

[T-SQL] How to add interim summary using T_SQL?

How to add interim summary using T_SQL?

Posted: 12 Aug 2013 12:13 AM PDT

My client has an application which also creates summary reports. My task is to improve those reports performance, as they have been written outside of SQL scope and only query SQL for the row data, i.e. SELECT * FROM myTable WHERE myRange.I need your advise to improve one of the reports, please. The following is an example of the expected result.I have used "Empty Space" because sqlservercentral removes the spaces for some reason, but if you will run my SQL you will see it in a correct way[b]"Customer Name" "Order Date" "Order Amount"[/b]HappyCustomer 1 01/05/2013 1000 "Empty Space" 04/05/2013 500 "Empty Space" 09/07/2013 20 TOTAL FOR HappyCustomer 1 1520HappyCustomer 2 01/01/2013 200"Empty Space" 08/04/2013 1200 TOTAL FOR HappyCustomer 2 1400Again, the application at the moment knows how to deal with it, but I need to re-write it in sql (Stored Procedure).The first task, to show customer's name only at the first line and to calculate total per customer was easy one(see working example below - implemented it via PARTITON BY), but I need your advise how to present in a required way, please. I mean how to add those [b]TOTAL FOR[/b] additional lines?WITH myResults AS(SELECT 1 AS customer_id, 'HappyCustomer 1' AS customer_name, GETDATE()-10 AS order_date, 12345 AS order_amount UNION ALL SELECT 1,'HappyCustomer 1', GETDATE()-9,322.32 UNION ALL SELECT 1,'HappyCustomer 1', GETDATE()-5,9875 UNION ALL SELECT 2,'HappyCustomer 2', GETDATE()-50,2387.5 UNION ALL SELECT 2,'HappyCustomer 2', GETDATE()-4,5467) SELECT CASE 1 WHEN ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) THEN customer_name ELSE '' END AS customer_name, order_date, order_amount, SUM(order_amount) OVER (PARTITION BY customer_id) AS total_per_customer FROM myResults; Thank you in advance

Logic for handling comma delimited column - tally table?

Posted: 11 Aug 2013 10:50 AM PDT

I have an excel sheet being imported with 8 columns. One column (say name)has comma delimited string.Difference with most other posts.. is that the 1 column that has the delimited string can have any number of values delimited. So some name field could be 12 substrings, some could be just 2.I need to ensure entire string < 2048 and every substring < 128Values will be checked for existence in current table.At the end, I will be displaying the values in the column as a string, but I could probably modify the stored procedure that displays info.I was thinking1) Use Tally Table to convert substrings to rows, but that would have a lot of repeated info with 8 columns.2) Use some other technique to convert substring to columns, but I have to check for the string with max values and then set those many columns with maybe a lot of nulls.I need some input as to what is the best way to handle this scenario.Any help is much appreciated.

NOT IN vs NOT EXISTS

Posted: 11 Aug 2013 07:13 PM PDT

I cant understand why these two statements gets different result for me.Statement with NOT IN gets no results.select *from Job_Queuewhere JobQueueID not in (SELECT JobQueueID from Payment)Statement with NOT EXISTS get a list of results.select *from Job_Queue jqwhere not exists (select * from Payment p where jq.JobQueueID = p.JobQueueID) Table definitions:CREATE TABLE [dbo].[Job_Queue]( [JobQueueID] [int] IDENTITY(1,1) NOT NULL, ... CONSTRAINT [Job_Queue_PK] PRIMARY KEY CLUSTERED ([JobQueueID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[Payment]( [PaymentID] [bigint] IDENTITY(1,1) NOT NULL, [JobQueueID] [int] NULL, ... CONSTRAINT [Payment_PK] PRIMARY KEY CLUSTERED ([PaymentID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO

How to alternate between two values in a column?

Posted: 11 Aug 2013 07:41 PM PDT

ShortType Outstanding Short_IdLong 2333.12 1Short 2333.12 1Long 2333.12 2Short 2333.12 2Long 2333.12 3Short 2333.12 3Long 2333.12 4Short 2333.12 4Long 2333.12 5Short 2333.12 5Is also possible to generate sql test data like the one above for 10000 rows without a table? By generating 10000 numbered rows in the Short_ID column at runtime would populate the other columns. All but two columns will have the same values repeating and ShortType will alternate between "Long" and "Short".Short_Id numbers will be repeat twice up to 10000 SQL Code so farSelect Long' AS ShortType ---- Long,Short everyother row,'2333.12' AS Outstanding,' ' AS Short_ID ?Thanks for the help.

Can this WHILE loop be replaced with a Tally table?

Posted: 11 Aug 2013 11:16 AM PDT

I'm making some changes to a report written with a while loop, and thought I would look at some performance improvements. I've been doing some reading on tally tables, and how they can in a lot of instances operate more efficiently than a WHILE loop. However, I'm having trouble getting my head around how it would work with a looping date logic.I've written a query below using adventure works, which is very similar to the structure of the the data I'm working with (note the date logic around start and end dates on the product dimension).Can anyone point me in the right direction on how a tally table might replace the WHILE loop?[code="sql"] USE [AdventureWorksDW2008R2]DECLARE @OrderDate DATETIME, @EndDate DATETIMESELECT @OrderDate = '20070625', @EndDate = '20070705'DECLARE @ResultsTable TABLE (SalesOrderNumber nvarchar(20),EnglishProductName nvarchar(50),EnglishDescription nvarchar(400), DealerPrice money, ProductStandardCost money, SalesAmount money, TaxAmt money, Freight money)WHILE @OrderDate <= @EndDateBEGININSERT INTO @ResultsTable SELECT fis.SalesOrderNumber , dp.EnglishProductName , dp.EnglishDescription , dp.DealerPrice , ProductStandardCost , SalesAmount , TaxAmt , Freight FROM dbo.FactInternetSales fis INNER JOIN dbo.DimDate orderdate ON fis.OrderDateKey = orderdate.DateKey INNER JOIN dbo.DimProduct dp ON fis.ProductKey = dp.ProductKey AND dp.StartDate <= orderdate.FullDateAlternateKey AND ( dp.EndDate IS NULL OR dp.EndDate > orderdate.FullDateAlternateKey )WHERE orderdate.FullDateAlternateKey = @OrderDate AND dp.ProductAlternateKey = 'BK-M68B-38' SET @OrderDate = @OrderDate + 1END SELECT * FROM @ResultsTable [/code]

self join - resolve Tsql

Posted: 11 Aug 2013 01:58 AM PDT

Hi,i am having the table as belowCol1 Col2G1 G2G3 P1P1 P2P2 G4G5 G6G7 G8but my expected out is :G1 G2G3 G4G5 G6G7 G8can you guys provide solution for this.

How to restrict to save duplicate date entry?

Posted: 11 Aug 2013 03:59 PM PDT

Hi Friends, I 've the table like below create table stude(stu_id int,leave_date datetime,reason varchar(20),is_save_mode varchar(20))insert into stude (stu_id,leave_date,reason,is_save_mode) values('002258','2013-08-15','Market Visit',' ')insert into stude (stu_id,leave_date,reason,is_save_mode) values('002258','2013-08-16','Market Visit',' ')Now My requirement is when student insert the date and try to make an save.before save the date and reason ve to validate already exists on table.if already exists means ll show show error mess?(i.e trying to insert into stude (stu_id,leave_date,reason,is_save_mode) values('002258','2013-08-15','Market Visit',' '))new date means update the column Is_save_mode='y' how to make procedure for these table?

locking queries

Posted: 11 Aug 2013 02:06 AM PDT

what type of locking will be happens when user executedselect * from table.what type of locking will be happens when user executedupdate table set col1=23 where col2=100when we have index in col2 what type of lock will be called.when we don't have index in col2 what type of lock will be called.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog