Thank god

MySql stored Procedures with multiple statements

2012-02-01T16:31:00.002+05:30

Without Parameter

delimiter ;;

drop procedure if exists usp_Income_updateApproved;;

create procedure usp_Income_updateApproved()

begin

START TRANSACTION;

Insert into `backup_income`

SELECT incitem_id, person, date, amount, comments, user_id, approved, date_inserted

FROM `m_income`

WHERE id

IN (

SELECT DISTINCT id

FROM unapproved_income

)

AND approved =1;

update m_income orig, unapproved_mahallu_income unappr SET

orig.incitem_id = unappr.incitem_id,

orig.person = unappr.person,

orig.date = unappr.date,

orig.amount = unappr.amount,

orig.amount = unappr.comments

Where orig.id = unappr.id and orig.approved = 1;

Update `mahallu_income` SET approved = 1

where id

IN (

SELECT DISTINCT id FROM unapproved_income

);

Delete from unapproved_income;

COMMIT;

end

;;

With Parameter

delimiter ;;

drop procedure if exists usp_Income_updateApproved;;

create procedure usp_Income_updateApproved(IN userID INT)

begin

START TRANSACTION;

Insert into `backup_income`

SELECT incitem_id, person, date, amount, comments, user_id, approved, date_inserted

FROM `m_income`

WHERE id

IN (

SELECT DISTINCT id

FROM unapproved_income

)

AND approved =1;

update mincome orig, unapproved_income unappr SET

orig.incitem_id = unappr.incitem_id,

orig.person = unappr.person,

orig.date = unappr.date,

orig.amount = unappr.amount,

orig.comments = unappr.comments,

orig.user_id = userID,

orig.approved = 1

Where orig.id = unappr.id;

Delete from unapproved_income;

COMMIT;

end

;;

How to view definition of a stored procedure?

It is stored in ROUTINES table under database INFORMATION_SCHEMA .

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

Setting password in mysql account

2012-01-26T18:18:00.000+05:30

1. How to get into mysql command prompt?

a) You need to find where does mysql.exe file resides and navigate to that location. Incase if you have installed wampserver then you can find it at

“C:\wamp\bin\mysql\mysql5.1.53\bin”

b) Traverse through location using cd command. Then type mysql.

2. Login to mysql with command prompt with password

mysql –u root –p

3. How to set mysql to ‘No Password’

use mysql;
update user set password=null where User='root';
flush privileges;
quit;

4. Create a password for the 'root' mysql account...

shell> mysql -u root

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');

mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');

mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');

5. Create a new user

Mysql> Create User ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;

6. How to set password to a user (if no password is set)

a) Login to mysql with the user

mysql –u root –p

b) Type as set password = password('Test');

7. If any of the internal tables are updates it’s preferred to execute below statement

mysql> FLUSH PRIVILEGES;

8. Granting access for a user to a specific database

GRANT SELECT,INSERT,UPDATE,DELETE ON .* TO 'custom'@'localhost';

Alternative coloring and Image background color

2010-09-08T22:08:00.001+05:30

I wanted to simple create a report with alternative background colors. Which I could achieve easily. Now I have a column in this report which is supposed to display image. Based on value from the data field a red or green or blue icon is displayed in this column based on expression. Which is also working fine. Now the problem is after I place an image in this details column (with or without expression). the alternative row coloring stopped working. its always white. I even tried creating a colored background image and still its not helping. Can some one help me out please?

This is expression I have used

=iif(RowNumber(Nothing) mod 2 = 0,Switch(Fields!StatusFlag.Value=10,"red_bg",Fields!StatusFlag.Value=20,"yellow_bg",Fields!StatusFlag.Value=30,"green_bg",True,"Blank")
,Switch(Fields!StatusFlag.Value=10,"red20x20",Fields!StatusFlag.Value=20,"yellow20x20",Fields!StatusFlag.Value=30,"green20x20",True,"Blank"))

red_bg is image is background color while red20x20 is with transparent background. but its not helping...l

If the question is still not clear, please let me know....

Thanks a lot.

Fayaz

Beloved prophet Mohammed's (SAW) last sermon

2010-07-19T19:25:00.000+05:30

O People

Lend me an attentive ear, for I know not whether after this year, I shall ever be
amongst you again. Therefore listen to what I am saying to you very carefully
and take these words to those who could not be present here today.

O People

Just as you regard this month, this day, this city as sacred, so regard the life
and property of every Muslim as a sacred trust. Return the goods entrusted to

you to their rightful owners. Hurt no one so that no one may hurt you.

Remember that you will indeed meet your Lord, and that He will indeed

reckon your deeds. Allahhas forbidden you to take usury (interest); therefore

all interest obligations shall henceforth be waived. Your capital, however, is

yours to keep. You will neither inflict nor suffer any inequity.

Allah has judged that there shall be no interest and that all interest due to

Abbas Ibn ‘Abd al Muttalib (the Prophet's uncle) shall henceforth be waived.

Beware of Satan for the safety of your religion. He has lost all hope that he

will ever be able to lead you astray in big things, so beware of following him in

small things.

O People

it is true that you have certain rights in regard to your women, but they also

have rights over you. Remember that you have taken them as your wives,

only under Allah's trust and with His permission. If they abide by your right

then to them belongs the right to be fed and clothed in kindness. Do treat you

women well and be kind to them, for they are your partners and committed

helpers. And it is your right that they do not make friends with anyone of

whom you do not approve, as well as never to be unchaste.

O People

Listen to me in earnest, worship Allah, say your five daily prayers (Salah),

fast during the month of Ramadan, and give your wealth in Zakat.

Perform Hajj if you can afford to.

All mankind is from Adam and Eve, an Arab has no superiority over a

non-Arabnor a non-Arab has any superiority over an Arab; also a white

has no superiority over a black, nor does a black have any superiority over

a white- except by pietyand good action. Learn at every Muslim is a

brother to every Muslim and that the Muslims constitute one brotherhood.

Nothing shall be legitimate to a Muslim, which belongs to a fellow Muslim

unless it was given freely and willingly. Do not therefore, do injustice to

yourselves.

Remember one day you will appear before Allah and answer for your deeds. So

beware, do not stray from the path of righteousness after I am gone. People,

no prophet or apostle will come after me and no new faith will be born. Reason

well therefore, O people, and understand words which I convey to you. I leave

behind me two things, the Quran and the Sunnah (Hadith), and if you follow

these you will never go astray. All those who listen to me shall pass on my

words to others and those to others again; and may the last ones understand

my words better than those who listened to me directly. Be my witness,

O Allah, that I have conveyed your message to your people."

Ya Allah

2010-07-04T13:05:00.000+05:30

Ya Allah, please include us among those who always showed and expressed thankfulness to you
Ya Allah, please make us stay happy and satisfied with what you provided us mercifully
Ya Allah, please make us fearless
Ya Allah, please help us get away from poverty
Ya Allah, please help us from being getting humiliated
Ya Allah, please forgive our parents and us as well
Ya Allah, please help us lead a healthier and longer life
Ya Allah, please help us get back beloved masjidul Aqsa
Ya Allah, please help the natives of Gaza to win
Ya Allah, please help them stay patient

Some T SQL Interview questions and Answers

2009-06-28T13:49:00.001+05:30

1. How to use sql query to copy only structure?
Ans: select * into table2 from table1 where 1 = 2

2. How do we handle Error?
Ans: I think we can use @@Error. Right after the query condition is executed we can check for @@Error <> 0, if it is not returning zero mean some error occured. Raiserror is another command for raising error We can also use Try catch block

3. What is PatIndex?
Ans: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
Syntax - PATINDEX ( '%pattern%' , expression )
Eg: USE AdventureWorks;
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

4. How to query a string contains %?
Ans: SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'

5. How to get values from a table with comma seperated?
Ans: declare @vName nvarchar(100)
set @vName = ''
select @vName = @vName + ','+ [Name] from HumanResources.Shift
select @vName

6. How to update 'Yes' to 'No' and viceversa in a query?
Ans: Update tablename set ColumnName1 = (case ColumnName1 when 'Yes'
then 'No'else 'Yes' end)

7. Consider you have a table with columns ID(primary key), Country and State.
Now if you have some rows with combination of country and state repeating,
ie, two rows with combination India, Kerala. Write a query for deleting
duplicate records?

Ans: With T1 as
(Select *,Row_Number() over (partition by Country, State order by ID)
as 'RowNo' From TableName)
Delete from T1 where RowNo > 1;

8. How to create temporary table? How do we apply noncluster index? What is nolock? When and where is nolock applied normally?

Ans. Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.

1.
CREATE TABLE #tmpTable
(
OfficeName varchar(50)
, officeid int
, CustID int
, AgentID int
, mlsid varchar(4)
, RequestMoreDetails int null
, Emails int null
)
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)

2.
select
OfficeName
, officeid
, o.CustID
, AgentID -
, o.mlsid
, PrintBrochure_Views = null
, RequestMoreDetails = null
, Emails = null
into #ForOffices from #Offices o
LEFT JOIN dbo.planparts WITH (NOLOCK)
ON bppa.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)

9. Another simple example for using temporary table.
Also how to split a single column in to multiple columns based on column value.
Here date_format function format date to yyyymm format.
period_diff function returns number of months between two date parameters passed. Also parameter expect value in either yymm or yyyymm format.

IN MYSQL

CREATE TEMPORARY TABLE monthlyoffer( MemberName VARCHAR(50) ,January INT DEFAULT 0 ,February INT DEFAULT 0 ,March INT DEFAULT 0 ,April INT DEFAULT 0 ,May INT DEFAULT 0 ,June INT DEFAULT 0,July INT DEFAULT 0 ,August INT DEFAULT 0 ,September INT DEFAULT 0 ,October INT DEFAULT 0 ,November INT DEFAULT 0 ,December INT DEFAULT 0 ,PaidDate date ,CurrentYearPaid INT ,TotalPaid INT ,Balance INT);

Insert into monthlyoffer (MemberName ,January,February,March,April,May,June,July,August,September,October,November,December,PaidDate,CurrentYearPaid ,TotalPaid,Balance)

Select

mem.first_name,

sum(case when monthname(paid_date)='January' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='February' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='March' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='April' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='May' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='June' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='July' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='August' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='September' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='October' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='November' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(case when monthname(paid_date)='December' and Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

max(paid_date),

sum(case when Year(mo.paid_date) = 2012 then mo.amount_paid else 0 end),

sum(mo.amount_paid),(mem.offer_amount*(1+PERIOD_DIFF(DATE_FORMAT(Now(), '%Y%m'),DATE_FORMAT(mem.offer_start_date, '%Y%m'))))-sum(case when mo.amount_paid is null then 0 else mo.amount_paid end)

from members mem

left join monthly_offered_members_list mo on mo.member_id = mem.member_id and mo.approved = 1

where mem.offer_amount is not null and member_status = 'Active' and Year(mem.offer_start_date) <= 2012

group by mem.first_name;

Select * from monthlyoffer;

SQL Server 2005: Using OVER() with Aggregate Functions

2009-02-10T11:57:00.000+05:30

http://www.sqlteam.com/article/sql-sever-2005-using-over-with-aggregate-functions