SQL Query to get customers with one product

SQL Query using min and max aggregate function

You may want to find Customer that only has only bought one particular product in the Sales table
You will know the Product_ID is the in the Products table, in this example we use ProductID=10

In this example for each client you will find the lowest value Product_ID and the highest value Product_ID, then filters rows to those where Product_ID is the same as the variable.

--Written by Rishka Booran-Johnson
-- Get Customers with only one product

CREATE TABLE #Customers (
customer_ID int,
firstname varchar(30),
lastname varchar(30)
)

CREATE TABLE #Sales (
Transaction_ID int,
Customer_ID int,
Product_ID int,
Sale_date datetime
)


CREATE TABLE #Products (
Product_ID int,
Product_name varchar(30),
Product_description varchar(50)
)


INSERT #customers SELECT 1, 'john', 'smith'
INSERT #customers SELECT 2, 'sally', 'johnson'
INSERT #customers SELECT 3, 'joe', 'bloggs'



INSERT #Products SELECT 1, 'bicycles', ''
INSERT #Products SELECT 2, 'trains', ''
INSERT #Products SELECT 3, 'dolls', ''



INSERT #Sales SELECT 1, 1, 2, '2009-06-01'
INSERT #Sales SELECT 2, 1, 1, '2009-06-08'
INSERT #Sales SELECT 2, 1, 1, '2009-06-07'
INSERT #Sales SELECT 3, 2, 2, '2009-06-12'
INSERT #Sales SELECT 4, 2, 2, '2009-06-05'
INSERT #Sales SELECT 5, 3, 3, '2009-06-06'
INSERT #Sales SELECT 6, 3, 2, '2009-06-01'
INSERT #Sales SELECT 7, 3, 1, '2009-06-03'


declare
@vproduct_ID int

set @vproduct_ID = 2; -- this would be your input variable for trains


SELECT s.customer_ID, c.firstname, c.lastname, MIN(s.product_ID), MAX(s.product_ID)
FROM #sales s, #customers c
WHERE s.customer_ID = s.customer_ID
AND s.customer_ID = c.customer_ID
GROUP BY s.customer_ID, c.firstname, c.lastname
HAVING MIN(s.product_ID) = @vproduct_ID and MAX(s.product_ID) = @vproduct_ID


DROP TABLE #Customers
DROP TABLE #Sales
DROP TABLE #Products

How to add Adsense in blog

When setting up this blog, I tried to add the Adsense account gadget that Google provides, for the side bar. When selecting this I got an error of "An internal error occurred. Please try again."

What will fix this problem is :
Log into your Adsense account
Select My Account then account access.
once there you will see a section headed Hosts with access
you will see that blogger is listed but you need to click "grant access", then select it, which should change it to "disable access" !

Hope this helps those that have the same problem.

How to check database growth

Sometimes you’ll want to know how much and how fast your database has been growing. Usually you will have a document that tells you the size information on all the databases, but this is not always the case in the real world. Considering that you perform regular backups, the following script can give you a estimated idea of growth over time of your database.

select
BackupDate = convert(varchar(10),backup_start_date, 111),
SizeInGigs=floor( backup_size/1024000000)
from msdb..backupset
where
database_name = ‘DatabaseName’
and type = ‘d’
order by
backup_start_date desc

Furthermore the Backup type can be used for the following types as well , making it easier to track other types of objects:

• D = Database
• I = Differential database
• L = Log
• F = File or filegroup
• G =Differential file
• P = Partial
• Q = Differential partial

Keywords for your website

When looking to promote your site you want to increase traffic to your website, which is when you need good SEO (Search Engine Optimization) and is the latest buzz word around.

One of the key factors in SEO are good keywords, a keyword is what a users use to conduct a search in a search engine, such as Google, Yahoo, MSN, etc. A keyword can consist of a single word, such as “cars” or an entire phrase, like “cars for sale” or even “cars for sale in Australia”. Over time you will find out the common keyword selections website users use to find your site and you can use these keywords to try to rank your website highly in search engine results pages.

A keyword tool I use is : https://adwords.google.com/select/KeywordToolExternal

RRN function in DB2

While doing some ETL I have just learned a new command in DB2 called RRN, which stands for relative record number. It has probably been used a long time, but had the rownum familiarity from Oracle.

Here is an example:
Return the relative record number and employee name from table EMPLOYEE for those employees in department 10.

SELECT RRN(EMPLOYEE), LASTNAME
FROM EMPLOYEE
WHERE DEPTNO = 10

Formatted date in SQL Server 2008

Yesterday I found an interesting article about Formating a date in SQL Server 2008 in the http://www.databasejournal.com website.

There are quite a few occasions when a developer wants to extract a date in a specific format extracted.

Having used Oracle before I would use somthing like:

select to_char(sysdate,'MM/DD/YYYY') from dual; or

select TRUNC(DateTimeField) to get the date only.

There is no equivalent in MS SQL Server, and you always have to use CAST or CONVERT, however using the SQL script developed by the author in this article, any MS SQL developer can write one common function and write a simple command :

select dbo.format_date(GETDATE(),'YYYY-MM-DD') as Date, to retrieve a date, the SQL code for the format_date as follows below, if you cannot find the web page.

Have fun and read the comments below the article when you are using SQL 2000 and SQL 2005.



/*---------------------------------------------------------------------------------------------
SQL FORMAT DATE code
----------------------------------------------------------------------------------------------*/

/****** Object:  UserDefinedFunction [dbo].[format_date]    Script Date: 05/12/2009 23:19:35 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[format_date]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[format_date]
GO

/****** Object:  UserDefinedFunction [dbo].[format_date]    Script Date: 05/12/2009 23:19:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET CONCAT_NULL_YIELDS_NULL OFF
go
CREATE function [dbo].[format_date] (@inputdate datetime ,@format varchar(500))
returns varchar(500)
as
begin
declare @year varchar(4)    --YYYY
declare @shortyear varchar(4)   --Yr
declare @quarter varchar(4)    --QQ
declare @month varchar(2)    --MM
declare @week varchar(2)    --WW
declare @day varchar(2)     --DD
declare @24hours varchar(2)    --24HH
declare @12hours varchar(2)    --HH
declare @minutes varchar(2)    --MI
declare @seconds varchar(2)    --SS
declare @milliseconds varchar(3)  --MS
declare @microseconds varchar(6)  --MCS
declare @nanoseconds varchar(9)   --NS
declare @dayname varchar(15)   --DAY
declare @monthname varchar(15)   --MONTH
declare @shortmonthname varchar(15)     --MON
declare @AMPM  varchar(15)   --AMPM
declare @TZ  varchar(15)       --TZ
declare @UNIXPOSIX  varchar(15)  --UNIXPOSIX
          --UCASE
          --LCASE

declare @formatteddate varchar(500)  

--Assign current date and time to
if (@inputdate is NULL or @inputdate ='')
begin
set @inputdate = getdate()
end

if (@format is NULL or @format ='')
begin
set @format ='YYYY-MM-DD 12HH:MI:SS AMPM'
end

--set all values

set @year    = convert(varchar(4),year(@inputdate))
set @shortyear    = right(@year,2)
set @quarter   = convert(varchar(1),datepart(QQ,(@inputdate)))
set @month    = right('0'+convert(varchar(2),month(@inputdate)),2)
set @week    = right('0'+convert(varchar(2),datepart(ww,(@inputdate))),2)
set @day    = right('0'+convert(varchar(2),day(@inputdate)),2)
set @24hours      = right('0'+convert(varchar(2),datepart(hh,@inputdate)),2)
set @TZ           = convert(varchar(10),datename(TZ,convert(varchar(20),@inputdate)))
set @UNIXPOSIX    = convert(varchar(15),datediff(ss,convert(datetime,'01/01/1970 00:00:000'),@inputdate))

if datepart(hh,@inputdate) >12
begin
set @12hours      = right('0'+convert(varchar(2),datepart(hh,@inputdate)) -12,2)
end
else
begin
set @12hours      = right('0'+convert(varchar(2),datepart(hh,@inputdate)) ,2)
end

if datepart(hh,@inputdate) >11
begin
set @AMPM ='PM'
end
else
begin
set @AMPM ='AM'
end

set @minutes      = right('0'+convert(varchar(2),datepart(n,@inputdate)),2)
set @seconds      = right('0'+convert(varchar(2),datepart(ss,@inputdate)),2)
set @milliseconds = convert(varchar(3),datepart(ms,@inputdate))
set @microseconds = convert(varchar(6),datepart(mcs,@inputdate))
set @nanoseconds  = convert(varchar(9),datepart(ns,@inputdate))
set @dayname      = datename(weekday,@inputdate)
set @monthname    = datename(mm,@inputdate)
set @shortmonthname= left(datename(mm,@inputdate),3)
set @formatteddate = @format
set @formatteddate=replace(@formatteddate,'MONTH',@monthname)
set @formatteddate=replace(@formatteddate,'MON',@shortmonthname)
set @formatteddate=replace(@formatteddate,'AMPM',@AMPM)

set @formatteddate=replace(@formatteddate,'YYYY',@year)
set @formatteddate=replace(@formatteddate,'Yr',@shortyear)
set @formatteddate=replace(@formatteddate,'QQ',@quarter)
set @formatteddate=replace(@formatteddate,'WW',@week)
set @formatteddate=replace(@formatteddate,'MM',@month)
set @formatteddate=replace(@formatteddate,'DD',@Day)
set @formatteddate=replace(@formatteddate,'24HH',@24hours)
set @formatteddate=replace(@formatteddate,'12HH',@12hours)
set @formatteddate=replace(@formatteddate,'Mi',@minutes)
set @formatteddate=replace(@formatteddate,'SS',@seconds)
set @formatteddate=replace(@formatteddate,'MS',@milliseconds)
set @formatteddate=replace(@formatteddate,'MCS',@microseconds)
set @formatteddate=replace(@formatteddate,'NS',@nanoseconds)
set @formatteddate=replace(@formatteddate,'DAY',@dayname)
set @formatteddate=replace(@formatteddate,'TZ',@TZ)
set @formatteddate=replace(@formatteddate,'UNIXPOSIX',@UNIXPOSIX)

if charindex('ucase',@formatteddate)<>0
begin
set @formatteddate=replace(@formatteddate,'ucase','')
set @formatteddate=upper(@formatteddate)
end

if charindex('lcase',@formatteddate)<>0
begin
set @formatteddate=replace(@formatteddate,'lcase','')
set @formatteddate=lower(@formatteddate)
end


return @formatteddate
end


GO

SQL.. the beginning.

The first time I ever saw a database was in Paradox (database), in 1995. It was a huge learning curve, but I quickly learned what tables, columns and rows were. Then I learned how to build relationships and write SQL queries. (SQL is short for Structured Query Language)

This is how I then figured it was put together.

Every database has collection of one or more tables.
Tables are made up of rows and columns.
The rows contain the data and columns describe the format of the data, namely data type, for that column.

Imagine a telephone directory, information of a person’s first name, surname , address and ph number, this is how the basic table would be constructed:

Table: Phone_Directory

FirstName	LastName	Address	Phone
Jake	Black	123 Round Road, CA	123 321-1234
Sonia	Coke	19 Sailfish Road, WA	232 555-5555
Mel	Brown	32 71st Ave, NY	614 555-5432

Hello world

Welcome to my blog. As you noticed I left the ‘Hello World!’ heading as this is how programmers start their first program! With this blog I hope to help fellow SQL developers, whether Oracle, MsSQL, MySQL. And my new journey of web development in PHP. Starting with wordpress….

My websites

Besides this blog I own and manage the following websites which are also to help others.

http://www.bokaap.co.za
The area of Bo Kaap is from my origin in South Africa, both my parents were born there and gre up in Bo Kaap. It is rich with history, culture and a growing tourist area.

http://www.muslimdirectory.com.au
When arriving in Australia, I was looking for a Halal Butcher and on a journey one day was looking for a mosque to pray in, I googled it and could not find anything, so started this website.


http://www.ozarrivals.com.au
As we are a family that migrated to Australia from South Africa in February 2008, and everyone Kept asking me the same questions about what visa do I need? what to do when arriving in Australia, what to pack, how do I get there? etc.. etc.. I thought I'ld blog them, as these questions are asked. If you have a question don't be shy to ask, I do try and answer the questions in a blogm so that everyone can benefit :)

NB:
All the websites above are free to use and sustains themselves with the adverts placed on them. I hope they are able help you, as just in this blog.

Arrived in Australia

Well at last, we arrived in Brisbane 21 February 2008!