tag:blogger.com,1999:blog-26231424444661538202024-02-08T05:59:27.690-08:00My BlogsKnowlege I would like to share and get feedback on. gaurav gaglanihttp://www.blogger.com/profile/16388697855572911151noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-2623142444466153820.post-76074975589957006482013-11-30T11:01:00.003-08:002013-11-30T11:08:18.154-08:00Grow Local Businesses Around You<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif; font-size: x-small;">I have always been fascinated by the term "Local is the new Global" and thought if there was a way where the small and medium sized businesses could engage and build a great relationship with the consumers whom they interact on a frequent basis. </span><span style="font-family: Verdana, sans-serif; font-size: x-small;"> </span><br />
<br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Market to me better:</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">I spend maximum time of my life at home and at the office. It would be great if I could get offers from the shops in the local area based on my preferences and the messages I would like to see from the merchants. I don't have time to spend, going through the coupons that I need to pick at the shop entrance. </span><span style="font-family: Verdana, sans-serif; font-size: x-small;">I would love to see offer email messages from the coffee, grocery, and beauty shops around me as those are places I visit the most frequently. </span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Discover and Encourage Hidden talent in my local area:</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Its amazing how you discover talents in your local areas like a solopreneurs in your around you makes awesome cup cakes or you have been trying to find a good Yoga trainer and your friend tells you the person was only like a couple blocks from where you live. There are products out there letting me find them, I don't get suggestions. I would love to encourage them and at the same time get I need served. </span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">How can I better engage with my merchants:</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Lets say I keep buying candy and nuts from the a small shop near my office. It would be wonderful if that shop takes a small 30 second survey from me about my likes and dislikes at the shop and then reach out to me appropriately. It just encourages me to keep going to that shop as I feel take care off then the shop owner will start offering me the right things and receive right offers. </span><span style="font-family: Verdana, sans-serif; font-size: x-small;"> </span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Win win situation for all:</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">By getting more personalized service from the merchants, I am obviously gonna get better service from the provider and the same time I could have other advantages like saving time, less clutter and I could go on and on. </span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Obviously the merchants generates more revenue from their existing customers as the customers are going to automatically spread the word for through their social graph, word of mouth etc.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br />Using email marketing:</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Earlier this year(2013) when I was planning to start a new venture in this space. I met my co-founders who were planning an <a href="http://www.optyn.com/" target="_blank">email marketing tool</a> called <a href="http://www.optyn.com/" target="_blank">Optyn</a> specifically catering to the needs of small businesses. </span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Its is so simple to use one could create a marketing messages in 2 minutes or less. No getting lost in templates. Write the content that matters the most.</span><br />
<br />
<br />
<br /></div>
gaurav gaglanihttp://www.blogger.com/profile/16388697855572911151noreply@blogger.com0tag:blogger.com,1999:blog-2623142444466153820.post-50203097871031435912012-09-18T04:04:00.000-07:002012-11-26T23:27:20.881-08:00Case Statements in SQL<div dir="ltr" style="text-align: left;" trbidi="on">
One of the complains that businesses have is that reporting is slow. There are multiple ways that I have seen developers handling this using caching putting in pagination in place etc. My thought process for reporting queries why not write good SQL queries and fetch data efficiently rather than putting in hacks at the application level. Now, there are few things that you will need to take care of at the database level like putting indexes on the right columns using unions over joins or vice a versa. I am not going to get into all of that here. I will be talking about one of the biggest boons that the SQL programming language offers the "CASE" statement.<br />
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif;">
<span style="font-family: "Courier New",Courier,monospace;">CASE case_value</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> WHEN when_value THEN statement_list</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> [WHEN when_value THEN statement_list] ...</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> [ELSE statement_list]</span><br />
<span style="font-family: "Courier New",Courier,monospace;">END</span><br />
Or<br />
<span style="font-family: "Courier New",Courier,monospace;">CASE</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> WHEN search_condition THEN statement_list</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> [WHEN search_condition THEN statement_list] ...</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> [ELSE statement_list]</span><br />
<span style="font-family: "Courier New",Courier,monospace;">END</span></div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif;">
Lets begin with a small example where it could be used as a "CASE" programming language construct. Lets say you are building a report for a teacher to display count of students in various Grades. Now instead of writing the logic at the application level to iterate over the student records and create a hash of grades and the count of students for each key. A more efficient way would be doing it through a SQL query and a "CASE" statement. </div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: "Courier New",Courier,monospace;">
SELECT DISTINCT(s.grades), <br />
CASE s.grades <br />
WHEN 'A' THEN COUNT(s.grades) <br />
WHEN 'B' THEN COUNT(s.grades) <br />
WHEN 'C' THEN Count(s.grades) <br />
ELSE Count(0)<br />
END AS number_of_students <br />
FROM students s</div>
<div style="font-family: "Courier New",Courier,monospace;">
GROUP BY s.grades</div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif;">
If calculations are involved or you want to use the sql case statement as if-else if-else we can also do that. Lets take an example where you want to calculate grades for students based on their marks:</div>
<div style="font-family: "Courier New",Courier,monospace;">
SELECT DISTINCT(s.grades), <br />
CASE <br />
WHEN s.grades BETWEEN 90 AND 100 THEN SUM(1) <br />
WHEN s.grades BETWEEN 80 AND 90 THEN SUM(1) <br />
WHEN s.grades BETWEEN 60 AND 80 THEN SUM(1) <br />
ELSE Count(0) END AS number_of_students <br />
FROM students s <br />
GROUP BY s.grades; </div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif;">
A CASE statement in SQL is just an expression that needs to return a value and can be used in group by and order by clauses of a query as well for example: </div>
<div style="font-family: "Courier New",Courier,monospace;">
ORDER BY events.date, (CASE(events.display_on_homepage) WHEN true THEN 0 when false then 1 ELSE 2 END) ASC</div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif;">
Again using CASE statements will not make the the performance better but I would take the business logic closer to the database. It could contested that case statements are the best way of doing things. Our <a href="http://www.idyllic-software.com/" target="_blank">Ruby on Rails development</a> team at <a href="http://www.idyllic-software.com/">Idyllic Software</a> believe it is. Feel free to pass on you comments/suggestions etc. I have used case statements to a good extent in Mysql and Postgres. I hope it works in other databases.</div>
<div style="font-family: Verdana,sans-serif;">
<br /></div>
<div style="font-family: Verdana,sans-serif;">
</div>
</div>
gaurav gaglanihttp://www.blogger.com/profile/16388697855572911151noreply@blogger.com4