UPDATE Statement with Aggregate function in WHERE condition

I seen many places developer get confused about how to use AGGREGATE function in WHERE condition with UPDATE statement. Suppose I have one table with user name and percentage. I want to update percentage if one user’s percentage sum is greater or lower than some value.

–table used

create table test11

(

uName varchar(20),

percentage int

)

–data used

INSERT INTO test11

SELECT ‘RITESH’,90 UNION ALL

SELECT ‘RAJAN’,75 UNION ALL

SELECT ‘RITESH’,80 UNION ALL

SELECT ‘ALKA’,70

–update query

update test11 set percentage=100

where uname=‘Ritesh’

AND

percentage in

(

select percentage from test11

WHERE uname=‘Ritesh’ group by percentage having sum(percentage)>70

)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

DateADD function for Quarter in SQL Server 2005

Recently I have read few questions in forums about limitations of DATEADD function. Support I have one date 30-Mar-09 and I want to find last quarter of it. What should I do?

select CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-30’),106)

above query will display December as last quarter but what If I enter date like 10-Jan-09?

select CONVERT(VARCHAR,dateadd(q, 1, ‘2009-01-10’),106)

It will still show October as a last quarter of date but my need is, if I enter Jan, Feb or March, my quarter month should be December. In that case, I have found one solution which I am going to share with you. If there is any better alternative, I would like my reader to share it here so that people can use it.

select CASE datepart(m,‘2009-07-28’)

WHEN 1 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 2 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 3 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 4 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 5 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 6 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 7 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 8 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 9 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 10 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

WHEN 11 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

WHEN 12 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

END

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Expert and Premium member in Expert Exchange.

If you have read my 101th article at http://www.sqlhub.com/2009/03/milestone-after-100-articles-in.html, you might remember that I have started helping the .NET and SQL Server community at Expert-Exchange web site. Today my earning points become 10600 at expert exchange so that I become qualified expert and premium member there. Have a look at my EE profile at:
http://www.experts-exchange.com/M_4847866.html

Reference: Ritesh Shah
http://www.sqlhub.comNote: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Sp_configure in SQL Server 2005

Today I was reading few of my old articles, when I seen sp_configure there, idea of this article pops up in my mind as there are many properties to set and I have used few of them only in my past article. There are many more one can use in real life so I have started digging into Microsoft Book Online for those properties of sp_configure and here is a results of that.

Sp_configure: When you run this SP in your query window, it will show you current setting of the server. You can execute it like below

EXEC sp_configure

NOTE: many of the server property change will not take effect until and unless your restart server or its service that is why we have to use RECONFIGURE command rather than restart.

“Show Advanced Option”: This property will show you all advanced option you can set with sp_configure. If you run only EXEC sp_configure command as per above, you may see few rows as a result, I got 14 rows in my development server but there are lot more option other than these few rows. To see all the advanced option, run following commands in your query window.

–set advanced option to true

EXEC sp_configure ‘show advanced option’,1

–reconfigure the server so that property change

–take effect right a way

reconfigure

GO

–list the properies after advanced option true

EXEC sp_configure

GO

“Query Wait”: When memory is not available to execute the query, you can specify seconds, how many seconds will it wait to execute before gets time out?

EXEC sp_configure ‘query wait’,30

reconfigure

GO

Min and Max server memory: These properties mainly used to change the memory usage of SQL Server. I have used these properties along with “awe enabled” property in my following article.

http://www.sqlhub.com/2009/03/memory-configuration-of-sql-server-2005.html

“Min memory per query”: default 1MB set as a “Min memory per query” but you can increase or decrease this limit for better performance but beware, increasing limit could cause memory shortage problem as well.

EXEC sp_configure ‘min memory per query’,1024

reconfigure

GO

“Scan for startup procs”: I have used this property in my article of Startup Stored Procedure at http://www.sqlhub.com/2009/03/startup-stored-procedure-sql-server.html

This property is mandatory if you are making startup stored procedure and want SQL Server to scan that SP while SQL Server restart.

EXEC sp_configure ‘scan for startup procs’,1

reconfigure

GO

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

ADO.NET and Microsoft Sync Services

Today I was digging Visual Studio 2008 and SQL Server 2008 more and I found one amazing feature which is supported by SQL Server 2008. The feature is Sync Services for ADO.NET.

If you have many users of your application who used to roam around with lap top or palm top and don’t have constant internet connection than you can give them local copy of database, they can work with application and whenever network or internet available, they can synchronize their own local copy with main database. Isn’t it amazing feature? Yes, it is. This is just an example to make you understand about need this new service but this concept is much much bigger than what I have said.

Let us see what Microsoft is saying about this in its own words from Microsoft Website.

“Sync Services for ADO.NET is a Microsoft Sync Framework powered solution for synchronizing ADO.NET enabled databases in offline and collaboration scenarios. Sync Services for ADO.NET allows developers who are familiar with the concepts of ADO.NET to apply that knowledge to data synchronization through a very similar set of APIs to that of ADO.NET. Sync Services for ADO.NET provides the flexibility of a programming model like offline datasets and a richer synchronization feature set like that found in Merge replication. Sync Services for ADO.NET also supports synchronization over services, such as Windows Communication Foundation (WCF). ”

I have visited following few links to get an idea about this amazing service.

http://msdn.microsoft.com/en-us/sync/bb887608.aspx

http://msdn.microsoft.com/en-us/sync/default.aspx

http://blogs.msdn.com/sync/archive/2008/09/16/now-available-sync-services-for-ado-net-on-windows-mobile-devices.aspx

Reference Collacted By: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Dynamic PIVOT with WHERE condition in SQL Server 2005

I have written many articles on PIVOT even I feel to write one more on practical situation. Suppose you have one table which contain studentID, year of student, total marks, increase and decrease. We need to find list of student on increase and decrease. How can we do that?

Have a look at this example with dummy data.

CREATE TABLE StudDetail

(

StudentID INT,

PassYear VARCHAR(10),

Grades int,

Increase INT,

Decrease INT

)

GO

INSERT INTO StudDetail

SELECT 1, ’08-09′, 3333,0,0 union all

SELECT 1, ’09-10′, 4252,25,0 union all

SELECT 2, ’08-09′, 2100,0,0 union all

SELECT 2, ’09-10′, 2002,0,-10

GO

–pivot query

SELECT * from (select StudentID,PassYear,grades FROM StudDetail where increase>=0) up

PIVOT (sum(grades) for passyear in ([08-09], [09-10])) AS pivo

–I can work with above query but what if I don’t know how much year I have to make column???

–I have to go for dynamic query, let us create it.

–START creating dynamic code for PIVOT

DECLARE @Cols NVARCHAR(2000)

SET @Cols=

–make column list for PIVOT

SELECT @Cols=@Cols+ ‘[‘+s.Passyear +‘]’+ ‘, ‘ FROM

(SELECT DISTINCT PassYear FROM StudDetail) AS s

–remove last comma from column list

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)

–create pivot query as we have just added distinct year list in @Cols variable

SET @Cols=‘SELECT * from (select StudentID,PassYear,grades FROM StudDetail where increase>=0) up

PIVOT (sum(grades) for passyear in (‘+@cols+‘)) AS pivo’

–print query and check

print @cols

EXECUTE sp_executeSQL @Cols

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com