When posting the PHP menu post, I found the post to distort the html and php sections and found the following to blog which assist to fix the display html in Blogger or even Wordpress issue:
display html, javascript and php code in blogger
Friday, July 17, 2009
PHP Include menu and footer
Header and Footer Inclusion
This is a beginners tutorial on how to create and include header/footer files at the top and bottom of all pages in your site. This comes in very handy, for example, when you want to update something that is the same on each page such as a menu or copyright notice. You would only need to update a single file to have the changes appear on all pages of your site.
Let's pretend you wish to create have 20 web pages for your site with the same design and you want to change a link in your menu. You would have to edit all 20 pages, right? Nope, there's a much quicker way allowing you to edit just one file for the mene or footer and really it's not as hard as it sounds.
What you have to do is create a header file and a footer file. The header file contains all the HTML code for the top portion of your site. The footer contains the HTML for the bottom. Content goes between the header and footer. Let's get started...
This is an entire HTML document to give you an idea of what the header and footer are and where the content fits in. The header is in blue, content is in red, and footer is in green.
create a file named :
---------------------------------------------------
index.html
---------------------------------------------------
<html>
<head>
<title>Title of your website</title>
</head>
<body text=black link=blue alink=red vlink=purple>
<!--- menu at the top --->
<p align=center>
<a href='index.html'>Home</a> | <a href='about.html'>About My Site</a> | <a href='links.html'>Links</a>
</p>
This is the content of a page on my site.
<p align=center>Copyright 2009. All Rights Reserved</p>
</body>
</html>
---------------------------------------------------
After determining which portions are the header and footer, you need to cut and paste them into new files, lets name them header.html and footer.html.
The index.html file you are using as an example should only contain the content after this. Here's an example of how things are divided up.
---------------------------------------------------
header.html <html>
---------------------------------------------------
<head>
<title>Title of Site</title>
</head>
<body text=black link=blue alink=red vlink=purple>
<!--- menu at the top --->
<p align=center>
<a href='index.html'>Home</a> | <a href='about.html'>About My Site</a> | <a href='links.html'>Links</a>
</p>
---------------------------------------------------
---------------------------------------------------
footer.html
---------------------------------------------------
<p align=center>Copyright 2002. All Rights Reserved</p>
</body>
</html>
---------------------------------------------------
index.html
---------------------------------------------------
This is the content of a page on my site.
---------------------------------------------------
You now have header and footer HTML code in their own files. This means we can include these two files at the top and bottom (with content between) of any number of pages on our site. This is very convenient! For example, if you wanted to change a link in your menu, you would just edit it in header.html instead of every page of your site!
Now you only want to call the index page and have the header.html and footer.html included. The way to do this is to rename the page to index.php or anything.php as and add the follows:
---------------------------------------------------
index.php or anything.php for other pages on your site
---------------------------------------------------
<? include("header.html"); ?>
This is the content of a page on my site.
<? include("footer.html"); ?>
---------------------------------------------------
This is a beginners tutorial on how to create and include header/footer files at the top and bottom of all pages in your site. This comes in very handy, for example, when you want to update something that is the same on each page such as a menu or copyright notice. You would only need to update a single file to have the changes appear on all pages of your site.
Let's pretend you wish to create have 20 web pages for your site with the same design and you want to change a link in your menu. You would have to edit all 20 pages, right? Nope, there's a much quicker way allowing you to edit just one file for the mene or footer and really it's not as hard as it sounds.
What you have to do is create a header file and a footer file. The header file contains all the HTML code for the top portion of your site. The footer contains the HTML for the bottom. Content goes between the header and footer. Let's get started...
This is an entire HTML document to give you an idea of what the header and footer are and where the content fits in. The header is in blue, content is in red, and footer is in green.
create a file named :
---------------------------------------------------
index.html
---------------------------------------------------
<html>
<head>
<title>Title of your website</title>
</head>
<body text=black link=blue alink=red vlink=purple>
<!--- menu at the top --->
<p align=center>
<a href='index.html'>Home</a> | <a href='about.html'>About My Site</a> | <a href='links.html'>Links</a>
</p>
This is the content of a page on my site.
<p align=center>Copyright 2009. All Rights Reserved</p>
</body>
</html>
---------------------------------------------------
After determining which portions are the header and footer, you need to cut and paste them into new files, lets name them header.html and footer.html.
The index.html file you are using as an example should only contain the content after this. Here's an example of how things are divided up.
---------------------------------------------------
header.html <html>
---------------------------------------------------
<head>
<title>Title of Site</title>
</head>
<body text=black link=blue alink=red vlink=purple>
<!--- menu at the top --->
<p align=center>
<a href='index.html'>Home</a> | <a href='about.html'>About My Site</a> | <a href='links.html'>Links</a>
</p>
---------------------------------------------------
---------------------------------------------------
footer.html
---------------------------------------------------
<p align=center>Copyright 2002. All Rights Reserved</p>
</body>
</html>
---------------------------------------------------
index.html
---------------------------------------------------
This is the content of a page on my site.
---------------------------------------------------
You now have header and footer HTML code in their own files. This means we can include these two files at the top and bottom (with content between) of any number of pages on our site. This is very convenient! For example, if you wanted to change a link in your menu, you would just edit it in header.html instead of every page of your site!
Now you only want to call the index page and have the header.html and footer.html included. The way to do this is to rename the page to index.php or anything.php as and add the follows:
---------------------------------------------------
index.php or anything.php for other pages on your site
---------------------------------------------------
<? include("header.html"); ?>
This is the content of a page on my site.
<? include("footer.html"); ?>
---------------------------------------------------
Thursday, July 16, 2009
Oracle SQL Usage: PARTITION BY
PARTITION BY: divide the groups into subgroups
This example will demonstrate grouping all sales by month, per product.
SQL> -- create your table
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );
Table created.
SQL> -- ad data into your table
SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);
1 row created.
SQL>
SQL> -- retrieve the records, just to check the content
SQL> --of the values inserted above
SQL> select * from all_sales;
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.64
25 rows selected.
SQL>
SQL> --PARTITION BY: divide the groups into subgroups.
SQL> --calculate the total sales amount per month
SQL>
SQL>
SQL> SELECT
2 prd_type_id, month, SUM(amount),
3 RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank
4 FROM all_sales
5 where amount IS NOT NULL
6 GROUP BY prd_type_id, month
7 ORDER BY prd_type_id, month;
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 1 16034.84 2
1 2 28289.3 1
1 4 25026.45 2
1 5 17212.66 1
1 6 31128.92 1
1 7 15644.65 2
1 8 16434.82 2
1 9 19654.57 1
1 10 21764.19 1
1 11 26053.46 1
1 12 10032.64 2
2 1 42669.68 1
2 3 20167.83 1
2 4 25056.45 1
2 7 62654.82 1
2 8 26434.82 1
2 9 15644.65 2
2 10 21264.19 2
2 12 10034.64 1
19 rows selected.
SQL>
SQL> --drop table
SQL> drop table all_sales;
Table dropped.
SQL>
SQL>
This example will demonstrate grouping all sales by month, per product.
SQL> -- create your table
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );
Table created.
SQL> -- ad data into your table
SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);
1 row created.
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);
1 row created.
SQL>
SQL> -- retrieve the records, just to check the content
SQL> --of the values inserted above
SQL> select * from all_sales;
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.64
25 rows selected.
SQL>
SQL> --PARTITION BY: divide the groups into subgroups.
SQL> --calculate the total sales amount per month
SQL>
SQL>
SQL> SELECT
2 prd_type_id, month, SUM(amount),
3 RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank
4 FROM all_sales
5 where amount IS NOT NULL
6 GROUP BY prd_type_id, month
7 ORDER BY prd_type_id, month;
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
----------- ---------- ----------- ----------
1 1 16034.84 2
1 2 28289.3 1
1 4 25026.45 2
1 5 17212.66 1
1 6 31128.92 1
1 7 15644.65 2
1 8 16434.82 2
1 9 19654.57 1
1 10 21764.19 1
1 11 26053.46 1
1 12 10032.64 2
2 1 42669.68 1
2 3 20167.83 1
2 4 25056.45 1
2 7 62654.82 1
2 8 26434.82 1
2 9 15644.65 2
2 10 21264.19 2
2 12 10034.64 1
19 rows selected.
SQL>
SQL> --drop table
SQL> drop table all_sales;
Table dropped.
SQL>
SQL>
can you believe it : A real SQL joke
Thursday, July 9, 2009
Divide by zero error encountered
You may have a request to extract percentages and you encounter this dreaded error, when developing in SQL 2000, SQL 2005 or even SQL 2008.
The workaround for the Divide by zero error encountered. error is by using the NULLIF Function as the the example below demonstrates. The NULLIF function returns the first expression if the two expressions are not equivalent otherwise a NULL is returned.
Usage: SELECT NULLIF(col1, 0)
When col1 = 0 a NULL will be returned.
Example
CREATE TABLE #Products
(
Product VARCHAR(10),
Price DECIMAL(7, 2),
CostPrice DECIMAL(7, 2)
)
INSERT #Products SELECT 'A4 Books', 12.00, 6.00
INSERT #Products SELECT 'Pens', 0.10, 0.05
INSERT #Products SELECT 'Pencils', 0, 0.05
SELECT Product, (CostPrice / Price) * 100 AS PercentProfit FROM #Products
Will Produce the following Error:
(3 row(s) affected)
Server: Msg 8134, Level 16, State 1, Line 14
Divide by zero error encountered.
---------------------------------------------------------------------
SELECT Product, ((CostPrice/NULLIF(Price, 0))*100) AS PercentProfit
FROM #Products
Returns:
Product PercentProfit
---------- -----------------------
A4 Books 50.0000000000
Pens 50.0000000000
Pencils NULL
(3 row(s) affected)
The workaround for the Divide by zero error encountered. error is by using the NULLIF Function as the the example below demonstrates. The NULLIF function returns the first expression if the two expressions are not equivalent otherwise a NULL is returned.
Usage: SELECT NULLIF(col1, 0)
When col1 = 0 a NULL will be returned.
Example
CREATE TABLE #Products
(
Product VARCHAR(10),
Price DECIMAL(7, 2),
CostPrice DECIMAL(7, 2)
)
INSERT #Products SELECT 'A4 Books', 12.00, 6.00
INSERT #Products SELECT 'Pens', 0.10, 0.05
INSERT #Products SELECT 'Pencils', 0, 0.05
SELECT Product, (CostPrice / Price) * 100 AS PercentProfit FROM #Products
Will Produce the following Error:
(3 row(s) affected)
Server: Msg 8134, Level 16, State 1, Line 14
Divide by zero error encountered.
---------------------------------------------------------------------
SELECT Product, ((CostPrice/NULLIF(Price, 0))*100) AS PercentProfit
FROM #Products
Returns:
Product PercentProfit
---------- -----------------------
A4 Books 50.0000000000
Pens 50.0000000000
Pencils NULL
(3 row(s) affected)
Thursday, July 2, 2009
Sequence number in MSSQL
Today someone asked me how to place a sequential number as a primary key value in a table. He did not want to write a loop in his procedure to increment the primary key value, just a 'plain and simple insert statement' with the table doing the calculations.
In Oracle, the SEQUENCE is the object place holder to use on a table trigger event.
In MSSQL, INCREMENT is the property and can be used as follows:
-------------------------------------------------------------------------
/*
To test this, first set up your table, with the IDENTITY property,
followed with two variables (starting int , increment by value),
in this example the ProductID starts at one, and every new record
will increase the Product counter ID by 1.
*/
CREATE TABLE product
(productID INT IDENTITY(1,1) ,
productDescription VARCHAR(20),
CONSTRAINT ID_PK PRIMARY KEY (productID)
)
/*
Insert some data into the table
*/
insert into product (productDescription) values ('bicycles')
insert into product (productDescription) values ('cars')
insert into product (productDescription) values ('trains')
insert into product (productDescription) values ('buses')
insert into product (productDescription) values ('trams')
/*
Select the data, to test the increment
*/
select * from product
/*
Drop test table
*/
drop table product
-------------------------------------------------------------------------
I hope this article can you, and as always I welcome feedback on this article.
In Oracle, the SEQUENCE is the object place holder to use on a table trigger event.
In MSSQL, INCREMENT is the property and can be used as follows:
-------------------------------------------------------------------------
/*
To test this, first set up your table, with the IDENTITY property,
followed with two variables (starting int , increment by value),
in this example the ProductID starts at one, and every new record
will increase the Product counter ID by 1.
*/
CREATE TABLE product
(productID INT IDENTITY(1,1) ,
productDescription VARCHAR(20),
CONSTRAINT ID_PK PRIMARY KEY (productID)
)
/*
Insert some data into the table
*/
insert into product (productDescription) values ('bicycles')
insert into product (productDescription) values ('cars')
insert into product (productDescription) values ('trains')
insert into product (productDescription) values ('buses')
insert into product (productDescription) values ('trams')
/*
Select the data, to test the increment
*/
select * from product
/*
Drop test table
*/
drop table product
-------------------------------------------------------------------------
I hope this article can you, and as always I welcome feedback on this article.
Friday, June 26, 2009
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
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
Wednesday, June 24, 2009
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
Subscribe to:
Posts (Atom)