Codeboxer.com > Searching

Do the SQL cha cha!
And be happy you are alive.

Did you know that:

Walt Disney was afraid of mice?

Search the Feed

Codeboxer search results for SQL

Alexa CSS Dreamhost Javascript Joyent Lighthouse Migration MySQL Oracle PHP Pivot SQL ssh status codes svn Unit Test

Silly Dev jokes

14 July 2010 | 4:22 pm by codeboxer

copied from: http://blogs.adobe.com/charles/2010/05/ill_trade_you_a_joke_for_a_book.h...

By Jeremy - 6:01 PM on May 20, 2010

There are only 10 types of people in the world: those who understand binary and those who don’t.
By Richie - 12:34 AM on May 24, 2010

A programmers high-school girlfriend is like the square root of -2…irrational and imaginary.
By gem_reader - 4:29 PM on May 26, 2010

“Knock, knock.”
“Who’s there?”
very long pause…..
“Java.”
By Borek Bernard - 2:32 AM on May 27, 2010

Rather a geek joke but a good one:
Schrodinger’s cat walks into a bar…and doesn’t
By codeabonde - 12:27 PM on June 1, 2010

In C we had to code our own bugs. In C++ we can inherit them.
By Yazbar - 1:11 PM on June 3, 2010

Q: How many programmers does it take to change a light bulb?
A: None. That’s a hardware problem.
By Hoover - 1:20 PM on June 4, 2010

My other car is a CDR
By mastersson - 9:12 AM on June 8, 2010

Q: How many IBM Processors does it take to execute a job?
A: Four. Three to hold it down, and one to rip it’s head off.
By Penny - 8:15 PM on July 12, 2010

Why do programmers always mix up Halloween and Christmas?
Because Oct 31 equals Dec 25.
By James hall - 10:23 PM on July 14, 2010

a sql query walks into a bar and sees two tables. He walks up and says “can i join you?”

compiling mysql and getting the gem installed correctly

17 May 2010 | 3:45 pm by codeboxer

Download your preferred MYSQL version from the community download site. If you’re on Snow Leopard, you may want to install the 64-bit version.

Install mysql via dmg or compile it from source.

Type ‘which mysql_config’ on the terminal to locate said config. Take note of this location as we’ll use it on the next step.

Install the mysql gem with the command below. If the location for your mysql config is different with the one below, use that instead.

sudo env ARCHFLAGS="-Os -arch x86_64 -fno-common" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

worked for me!

thanks to http://www.sylv3rblade.com/2010/04/installing-the-mysql-gem-on-os-x-10-6...

make sqlite work with snow leopard

29 March 2010 | 11:14 am by codeboxer

mv /Applications/Firefox.app/Contents/MacOS/libsqlite3.dylib /Applications/Firefox.app/Contents/MacOS/libsqlite3.dylib.orig
cp /usr/lib/libsqlite3.dylib /Applications/Firefox.app/Contents/MacOS/libsqlite3.dylib

copied from:

http://support.mozilla.com/tiki-view_forum_thread.php?locale=nl&forumId=...

sql via rake

26 February 2010 | 9:13 am by codeboxer

Oh, so sexy.

ActiveRecord::Base.connection.execute <<-SQL
select id from drug_records limit 1
SQL

tunnel of love

23 April 2009 | 2:43 pm by codeboxer

This little puppy is my tunnel of love. Connecting to SQL databases just keeps getting more complicated. :)

ssh -L 8888:localhost:3306 rails@staging.gmgdev.railsmachina.com

i missed writing sql

25 March 2009 | 12:17 am by codeboxer

Here is the first stored procedure that I have written in long time, fully finished. This puppy runs like a charm.

CREATE PROCEDURE sp_generate_forecast (IN p_run_day INT(10), IN p_month INT(10), IN p_year INT(10), IN revplanid INT(10), IN month_days INT(10))
BEGIN
DECLARE r_units, r_day, r_partner int;
DECLARE done int DEFAULT 0;

-- fields - partial_total day_ave retail_chg margin_chg
-- retail_margin net_sales p_rev_share gross_margin
-- clear out any previous forecast
DELETE FROM revplan_infos
WHERE revplan_id = revplanid
AND (revplan_type = 'Forecast' OR revplan_type = 'Variance');

-- enter Forecast
INSERT INTO revplan_infos (partner_id, partial_total, revplan_id, revplan_type,
created_at, updated_at, retail_margin, net_sales, p_rev_share,
gross_margin, amount, day_ave, p_name)
SELECT p.id partner_id
,sum(s.total) partial_total
,revplanid
,'Forecast'
,SYSDATE()
,SYSDATE()
,-1 * (((sum(s.total)/p_run_day) * month_days) * (p_info.retail_margin/100)) retail_margin
,((sum(s.total)/p_run_day) * month_days) - (((sum(s.total)/p_run_day) * month_days) * (p_info.retail_margin/100)) net_sales
,-1 * ((((sum(s.total)/p_run_day) * month_days) - (((sum(s.total)/p_run_day) * month_days) * (p_info.retail_margin/100))) * p_info.rev_share/100) p_rev_share
,((((sum(s.total)/p_run_day) * month_days) - (((sum(s.total)/p_run_day) * month_days) * (p_info.retail_margin/100))) * ((100-p_info.rev_share)/100)) gross
,(sum(s.total)/p_run_day) * month_days forecast_total
,(sum(s.total)/p_run_day) day_ave
,p.name
FROM summaries s
,accounts a
,partners p
JOIN partner_infos p_info
ON p_info.partner_id = p.id
WHERE item_month = p_month
AND item_year = p_year
AND s.account_id = a.id
AND a.partner_id = p.id
GROUP BY p.name
ORDER BY sum(s.total) DESC;

-- UPDATE REV PLAN
UPDATE revplan_infos AS ri SET
ri.retail_margin = -1 * (SELECT (ri.amount * partner_infos.retail_margin/100)
FROM partner_infos
WHERE ri.partner_id = partner_infos.partner_id),
ri.net_sales = (SELECT ri.amount - (ri.amount * partner_infos.retail_margin/100)
FROM partner_infos
WHERE ri.partner_id = partner_infos.partner_id),
ri.p_rev_share = -1 * (SELECT (ri.amount - (ri.amount * partner_infos.retail_margin/100)) * partner_infos.rev_share/100
FROM partner_infos
WHERE ri.partner_id = partner_infos.partner_id),
ri.gross_margin = (SELECT (ri.amount - (ri.amount * partner_infos.retail_margin/100)) * (100-partner_infos.rev_share)/100
FROM partner_infos
WHERE ri.partner_id = partner_infos.partner_id),
ri.p_name = (SELECT name FROM partners where partners.id = ri.partner_id)
WHERE revplan_id = revplanid
AND revplan_type = 'Plan';

INSERT INTO revplan_infos (partner_id, amount, revplan_id, revplan_type, retail_margin, net_sales,
p_rev_share, gross_margin, retail_chg, margin_chg, created_at, updated_at, p_name)
SELECT t_plan.partner_id
,for_amount - plan_amount GROSS_RETAIL
,revplanid
,'Variance'
,(for_amount * p_infos.retail_margin/100) - (plan_amount * p_infos.retail_margin/100) RETAIL_MARGIN
,(for_amount - (for_amount * p_infos.retail_margin/100)) - (plan_amount - (plan_amount * p_infos.retail_margin/100)) NET_SALES
,((for_amount - (for_amount * p_infos.retail_margin/100)) * p_infos.rev_share/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * p_infos.rev_share/100) P_REV_SHARE
,((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) GROSS_MARGIN
,(((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100))/plan_amount RETAIL_CHG
,(((for_amount - (for_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100) - ((plan_amount - (plan_amount * p_infos.retail_margin/100)) * (100-p_infos.rev_share)/100))/t_plan.gross_margin MARGIN_CHG
,SYSDATE()
,SYSDATE()
,p.name
FROM
(SELECT partner_id, amount plan_amount, gross_margin
FROM revplan_infos
WHERE revplan_type = 'Plan'
AND revplan_id = revplanid) t_plan
JOIN (
SELECT partner_id, amount for_amount
FROM revplan_infos
WHERE revplan_type = 'Forecast'
AND revplan_id = revplanid) t_for
ON t_for.partner_id = t_plan.partner_id
JOIN partner_infos p_infos
ON p_infos.partner_id = t_plan.partner_id
JOIN partners p
ON p.id = t_plan.partner_id;

END

Original post blogged on codeboxer.com.

Top 5 sql query with other included

20 March 2009 | 4:25 pm by codeboxer

Top 5 sql query

This week has been all about the SQL. Here is some fun I had with it. This is dynamic sql, and you are seeing the end product - this was originally built as a find_by_sql call, and of course had bind variables that you don't see here - but you get the idea.

select sum(total * 1), r.merchant
from summaries s
join retailers r
on s.retailer_id = r.id
join (
select sum(total) top_5_total, r.merchant as top_5_merchant
from summaries s
join retailers r
on s.retailer_id = r.id
where item_date >= '2007-01-01'
and item_date <= '2009-12-31'
and case 0
when 0 then 1=1
else account_id in (select id from accounts where partner_id = 0) end
group by merchant
order by sum(total) desc
limit 5 ) top_5
on r.merchant = top_5.top_5_merchant
where item_date >= '2007-01-01'
and item_date <= '2009-12-31'
and case 0
when 0 then 1=1
else account_id in (select id from accounts where partner_id = 0) end
group by merchant
order by merchant, total

an old pivot table report procedure

26 February 2009 | 10:24 am by codeboxer

Back in the day, I wrote reports for Rupert himself at FOX IT. Here is an old query from those days (written in T-SQL for .NET Reporting Services):

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.PR_T09 Script Date: 2/3/2006 4:17:47 PM ******/
ALTER PROCEDURE dbo.PR_T09
(
@YEAR1 char(4) = '2006'
,@QTR1 char(6) = 'Q1'
,@VER1 char(5) = '100'

,@YEAR2 char(4) = '2006'
,@QTR2 char(6) = 'Q2'
,@VER2 char(5) = '100'

,@BU VARCHAR(50) = ''
,@BU_GRP VARCHAR(50) = ''
,@BU_GRP_SUM VARCHAR(50) = 'TCFT-CONS-NOFOX21'

,@PERIOD_START INT = 1
,@PERIOD_END INT = 12
,@AMT BIT = 1
,@PRE_AMT BIT = 1
,@POST_AMT BIT = 1
,@II BIT = 0

)

/*************************************************************************

Created By: Krister Tidics - copied from F221 written by Livia Squires
Created Date: Feb 2, 2006
Called From: reporting services TCFT T09
Description: TCFT Operating Profit and Loss PL VARIANCE

Input: 1] Version info
2] II - switch to set whether Imputed interest is broken out separately

Output: PL data pivot for Operating Profit and Loss Reports

Revision History

Date: Initials: Desc:
**************************************************************************/

AS
BEGIN

SET NOCOUNT ON
/** Test harness
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DROP TABLE #PROJ
DECLARE
@YEAR1 CHAR(4)
,@QTR1 CHAR(6)
,@VER1 CHAR(5)
,@YEAR2 CHAR(4)
,@QTR2 CHAR(6)
,@VER2 CHAR(5)
,@BU_GRP_SUM VARCHAR(50)
,@BU_GRP VARCHAR(50)
,@BU CHAR(5)
,@II BIT
,@BY_PERIOD BIT
,@AMT INT
,@PRE_AMT INT
,@POST_AMT INT
,@PERIOD_START INT
,@PERIOD_END INT

SET @YEAR1 = '2006'
SET @QTR1 = 'Q1'
SET @VER1 = '100'

SET @YEAR2 = '2006'
SET @QTR2 = 'Q2'
SET @VER2 = '100'

SET @BU_GRP_SUM = 'TCFT-CONS-NOFOX21'
SET @BU_GRP = ''
SET @BU = ''

SET @II = 0
SET @BY_PERIOD = 0
SET @AMT = 1
SET @PRE_AMT = 1
SET @POST_AMT = 1
SET @PERIOD_START = 1
SET @PERIOD_END = 12

--*/

DECLARE @BU_NAME VARCHAR(50), @SCALE INT, @VER_ID INT
SET @SCALE = 5 -- no rounding

--DROP TABLE #PROJ

CREATE TABLE #PROJ (
COMPANY CHAR(5)
,PLAN_YEAR CHAR(4)
,FORECAST_QUARTER CHAR(6)
,VERSION_ID CHAR(5)
,BUSINESS_UNIT CHAR(5)
,REPORT_BUSINESS_UNIT CHAR(5)
,PERIOD INT NULL
,BUDGET_CODE CHAR(10)
,BC_DESC VARCHAR(60)
,PROJECT_ID CHAR(6) NOT NULL
,REPORT_PROJECT_ID CHAR(6) --NOT NULL
,SERIES_ID CHAR(6)
,TBD_ID CHAR(6)
,REPORT_SERIES_ID CHAR(6)
,FIRST_SEASON_PROJECT_ID CHAR(6) NULL
,LAST_SEASON_PROJECT_ID CHAR(6) NULL
,SEASON_COUNT CHAR(1) NULL
,PROJECT_GROUP NVARCHAR(15)
,PROJECT_GROUP_NAME NVARCHAR(50)
,PROJECT_GROUP_ORDER INT
,PROJECT_GROUP_ROLLUP NVARCHAR(15)
,PROJECT_GROUP_ROLLUP_NAME NVARCHAR(50)
,PROJECT_GROUP_ROLLUP_ORDER INT
,PROJECT_GROUP_MASTER NVARCHAR(15)
,PROJECT_GROUP_MASTER_NAME NVARCHAR(50)
,PROJECT_GROUP_MASTER_ORDER INT
,PROJECT_DESC VARCHAR (100)
,SERIES_DESC VARCHAR (100)
,INIT_AIR VARCHAR(5)
,AMOUNT MONEY
,PRE_AMOUNT MONEY
,POST_AMOUNT MONEY
,NETWORK MONEY
,DOM_SYN MONEY
,INT_TV MONEY
,DOM_HV MONEY
,INT_HV MONEY
,LM MONEY
,MUSIC MONEY
,PAY_TV MONEY
,OTHER MONEY
,REL_COST MONEY
,SERIES_PROD MONEY
,FINANCING MONEY
,POH MONEY
,RES MONEY
,PAR MONEY
,AMORT_ADJ MONEY
,VER_ID INTEGER NULL
)

CREATE NONCLUSTERED INDEX IX_PROJECT_ID ON #PROJ
(
PROJECT_ID
,PERIOD
)

CREATE NONCLUSTERED INDEX IX_FIRST_SEASON_PROJECT_ID ON #PROJ
(
FIRST_SEASON_PROJECT_ID
)

CREATE NONCLUSTERED INDEX IX_LAST_SEASON_PROJECT_ID ON #PROJ
(
LAST_SEASON_PROJECT_ID
)

CREATE NONCLUSTERED INDEX IX_SERIES_ID ON #PROJ
(
SERIES_ID
)

-- GET THE PROJECT DATA
-- VER 1
INSERT #PROJ (
COMPANY
,PLAN_YEAR
,FORECAST_QUARTER
,VERSION_ID
,BUSINESS_UNIT
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,SERIES_ID
,TBD_ID
,PROJECT_GROUP
,FIRST_SEASON_PROJECT_ID
,LAST_SEASON_PROJECT_ID
,SEASON_COUNT
,PROJECT_DESC
,SERIES_DESC
,INIT_AIR
,AMOUNT
,PRE_AMOUNT
,POST_AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
,VER_ID
)
EXEC dbo.PR_TCFT_OPL_BU
@YEAR1
,@QTR1
,@VER1
,@BU
,@BU_GRP
,@BU_GRP_SUM
,@PERIOD_START
,@PERIOD_END
,@II
,@AMT
,@PRE_AMT
,@POST_AMT
,1 -- TEMP TABLE
,1 -- VER_ID

-- GET THE PROJECT DATA
-- VER 2
INSERT #PROJ (
COMPANY
,PLAN_YEAR
,FORECAST_QUARTER
,VERSION_ID
,BUSINESS_UNIT
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,SERIES_ID
,TBD_ID
,PROJECT_GROUP
,FIRST_SEASON_PROJECT_ID
,LAST_SEASON_PROJECT_ID
,SEASON_COUNT
,PROJECT_DESC
,SERIES_DESC
,INIT_AIR
,AMOUNT
,PRE_AMOUNT
,POST_AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
,VER_ID
)
EXEC dbo.PR_TCFT_OPL_BU
@YEAR2
,@QTR2
,@VER2
,@BU
,@BU_GRP
,@BU_GRP_SUM
,@PERIOD_START
,@PERIOD_END
,@II
,@AMT
,@PRE_AMT
,@POST_AMT
,1 -- TEMP TABLE
,2 -- VER_ID

-- UPDATE THE TBD PROJECTS IN PRIOR TO MATCH THE PROJECT HEADERS IN THE CURRENT VERSION
UPDATE PRIOR_VER
SET PRIOR_VER.REPORT_PROJECT_ID = CURR_VER.REPORT_PROJECT_ID
,PRIOR_VER.REPORT_SERIES_ID = CURR_VER.REPORT_SERIES_ID
,PRIOR_VER.SERIES_DESC = CURR_VER.SERIES_DESC
,PRIOR_VER.PROJECT_DESC = CURR_VER.PROJECT_DESC
--,PRIOR_VER.RELEASE_STATUS = CURR_VER.RELEASE_STATUS
--SELECT *
FROM #PROJ PRIOR_VER
JOIN
(SELECT REPORT_PROJECT_ID,REPORT_SERIES_ID,TBD_ID,SERIES_DESC,PROJECT_DESC--,RELEASE_STATUS
FROM #PROJ
WHERE VER_ID = 1 AND TBD_ID IS NOT NULL)
CURR_VER
ON CURR_VER.TBD_ID = PRIOR_VER.PROJECT_ID
WHERE PRIOR_VER.VER_ID = 2

/** The next 4 insert queries are to make sure both versions have the same project ids
and budget codes in order for the inner join to work in the final select
**/
--insert any projects in ver2 that dont exist in ver1 into ver1
INSERT #PROJ (
VER_ID
--,PERIOD
,BUDGET_CODE
,BC_DESC
,COMPANY
,PROJECT_ID
,REPORT_PROJECT_ID
,SERIES_ID
,REPORT_SERIES_ID
,PROJECT_DESC
,SERIES_DESC
,BUSINESS_UNIT
,REPORT_BUSINESS_UNIT
,TBD_ID
--,RELEASE_STATUS
,AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
)
SELECT 1 VER_ID
--,VER2.PERIOD
,VER2.BUDGET_CODE
,VER2.BC_DESC
,VER2.COMPANY
,VER2.PROJECT_ID
,VER2.REPORT_PROJECT_ID
,VER2.SERIES_ID
,VER2.REPORT_SERIES_ID
,VER2.PROJECT_DESC
,VER2.SERIES_DESC
,VER2.BUSINESS_UNIT
,VER2.REPORT_BUSINESS_UNIT
,VER2.TBD_ID
--,VER2.RELEASE_STATUS
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM #PROJ VER2
WHERE VER2.VER_ID = 2
AND NOT EXISTS (SELECT VER1.PROJECT_ID
FROM #PROJ VER1
WHERE VER1.VER_ID = 1
AND VER2.PROJECT_ID = VER1.PROJECT_ID
)
--projects in ver1 that don't exist in ver2 into ver2
UNION ALL

SELECT 2 VER_ID
--,VER1.PERIOD
,VER1.BUDGET_CODE
,VER1.BC_DESC
,VER1.COMPANY
,VER1.PROJECT_ID
,VER1.REPORT_PROJECT_ID
,VER1.SERIES_ID
,VER1.REPORT_SERIES_ID
,VER1.PROJECT_DESC
,VER1.SERIES_DESC
,VER1.BUSINESS_UNIT
,VER1.REPORT_BUSINESS_UNIT
,VER1.TBD_ID
--,VER1.RELEASE_STATUS
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM #PROJ VER1
WHERE VER1.VER_ID = 1
AND NOT EXISTS (SELECT VER2.PROJECT_ID
FROM #PROJ VER2
WHERE VER2.VER_ID = 2
AND VER2.PROJECT_ID = VER1.PROJECT_ID
)

-- This update is to move Amort in the prior version up to the series level
-- of the current version on the report. It needs to be done here
-- so that the next query for inserting missing budget codes into VER1
-- does not miss the amort which we are moving to a different series id
UPDATE #PROJ
SET PROJECT_ID = TBD_NEW_SERIES.SERIES_ID
--select #FIN.*,TBD_NEW_SERIES.TBD_ID,TBD_NEW_SERIES.SERIES_ID
FROM #PROJ
JOIN (
SELECT REPORT_SERIES_ID SERIES_ID,TBD_ID
FROM #PROJ
WHERE VER_ID = 1 AND TBD_ID IS NOT NULL
)
TBD_NEW_SERIES ON TBD_NEW_SERIES.TBD_ID = #PROJ.PROJECT_ID
WHERE #PROJ.VER_ID = 2
AND (#PROJ.AMORT_ADJ <> 0 OR #PROJ.PAR <> 0 OR #PROJ.RES <> 0 )

-- insert budget codes that exist in ver2 but not ver1 into ver1
-- and vice versa
INSERT #PROJ (
VER_ID
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,REPORT_PROJECT_ID
,SERIES_ID
,REPORT_SERIES_ID
,PROJECT_DESC
,SERIES_DESC
,BUSINESS_UNIT
,REPORT_BUSINESS_UNIT
,TBD_ID
--,RELEASE_STATUS
,AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
)
SELECT 1 VER_ID
--,FIN2.PERIOD
,FIN2.BUDGET_CODE
,FIN2.BC_DESC
,FIN2.PROJECT_ID
,FIN2.REPORT_PROJECT_ID
,FIN2.SERIES_ID
,FIN2.REPORT_SERIES_ID
,FIN2.PROJECT_DESC
,FIN2.SERIES_DESC
,FIN2.BUSINESS_UNIT
,FIN2.REPORT_BUSINESS_UNIT
,FIN2.TBD_ID
--,FIN2.RELEASE_STATUS
,0.00 AMOUNT
,0.00 NETWORK
,0.00 DOM_SYN
,0.00 INT_TV
,0.00 DOM_HV
,0.00 INT_HV
,0.00 LM
,0.00 MUSIC
,0.00 PAY_TV
,0.00 OTHER
,0.00 REL_COST
,0.00 SERIES_PROD
,0.00 FINANCING
,0.00 POH
,0.00 RES
,0.00 PAR
,0.00 AMORT_ADJ
FROM #PROJ FIN2
WHERE FIN2.VER_ID = 2
AND NOT EXISTS (SELECT *
FROM #PROJ FIN1
WHERE FIN1.VER_ID = 1
AND FIN1.PROJECT_ID = FIN2.PROJECT_ID
AND FIN1.BUDGET_CODE = FIN2.BUDGET_CODE)

-- budget codes that exist in ver1 but not ver2 into ver2
UNION ALL
SELECT 2 VER_ID
--,FIN1.PERIOD
,FIN1.BUDGET_CODE
,FIN1.BC_DESC
,FIN1.PROJECT_ID
,FIN1.REPORT_PROJECT_ID
,FIN1.SERIES_ID
,FIN1.REPORT_SERIES_ID
,FIN1.PROJECT_DESC
,FIN1.SERIES_DESC
,FIN1.BUSINESS_UNIT
,FIN1.REPORT_BUSINESS_UNIT
,FIN1.TBD_ID
--,FIN1.RELEASE_STATUS
,0.00 AMOUNT
,0.00 NETWORK
,0.00 DOM_SYN
,0.00 INT_TV
,0.00 DOM_HV
,0.00 INT_HV
,0.00 LM
,0.00 MUSIC
,0.00 PAY_TV
,0.00 OTHER

,0.00 REL_COST
,0.00 SERIES_PROD
,0.00 FINANCING
,0.00 POH
,0.00 RES
,0.00 PAR
,0.00 AMORT_ADJ
FROM #PROJ FIN1
WHERE FIN1.VER_ID = 1
AND NOT EXISTS (SELECT *
FROM #PROJ FIN2
WHERE FIN2.VER_ID = 2
AND FIN1.PROJECT_ID = FIN2.PROJECT_ID
AND FIN1.BUDGET_CODE = FIN2.BUDGET_CODE)

--UPDATE PROJECT_GROUPING
UPDATE #PROJ
SET PROJECT_GROUP_NAME = RG3.GROUP_NAME
,PROJECT_GROUP_ORDER = RG3.GROUP_SORT
,PROJECT_GROUP_ROLLUP = RG2.GROUP_CODE
,PROJECT_GROUP_ROLLUP_NAME = RG2.GROUP_NAME
,PROJECT_GROUP_ROLLUP_ORDER = RG2.GROUP_SORT
,PROJECT_GROUP_MASTER = RG1.GROUP_CODE
,PROJECT_GROUP_MASTER_NAME = RG1.GROUP_NAME
,PROJECT_GROUP_MASTER_ORDER = RG1.GROUP_SORT
FROM REPORT R
JOIN REPORT_FRAME F ON R.REPORT_FRAME_ID = F.REPORT_FRAME_ID
JOIN REPORT_GROUP RG1 ON RG1.REPORT_FRAME_ID = F.REPORT_FRAME_ID AND RG1.GROUP_LEVEL = 1
JOIN REPORT_GROUP RG2 ON RG2.PARENT_REPORT_GROUP_ID = RG1.REPORT_GROUP_ID AND RG2.GROUP_LEVEL = 2
JOIN REPORT_GROUP RG3 ON RG3.PARENT_REPORT_GROUP_ID = RG2.REPORT_GROUP_ID AND RG3.GROUP_LEVEL = 3
JOIN #PROJ ON #PROJ.PROJECT_GROUP = RG3.GROUP_CODE
WHERE R.REPORT_NUMBER = 'T01A'

--SELECT * FROM #PROJ

--ADDED KT - I AM UPDATING THE REPORT_PROJECT_IDs for all those that don't come in
UPDATE #PROJ
SET REPORT_PROJECT_ID = PROJECT_ID
,REPORT_BUSINESS_UNIT = BUSINESS_UNIT
,REPORT_SERIES_ID = SERIES_ID
WHERE REPORT_PROJECT_ID IS NULL

-- final select statement
SELECT VER1.SERIES_DESC
,VER1.REPORT_SERIES_ID
,VER1.REPORT_PROJECT_ID
,VER1.PROJECT_DESC
--VER1.RELEASE_STATUS,
--,VER1.PERIOD
,VER1.BUDGET_CODE
--VER1.BC_CAT,
,VER1.BC_DESC
,VER1.PROJECT_GROUP
,VER1.PROJECT_GROUP_NAME
,VER1.PROJECT_GROUP_ORDER
,VER1.PROJECT_GROUP_ROLLUP
,VER1.PROJECT_GROUP_ROLLUP_NAME
,VER1.PROJECT_GROUP_ROLLUP_ORDER
,VER1.PROJECT_GROUP_MASTER
,VER1.PROJECT_GROUP_MASTER_NAME
,VER1.PROJECT_GROUP_MASTER_ORDER
,(VER1.AMOUNT - VER2.AMOUNT) AMOUNT
,(VER1.NETWORK - VER2.NETWORK) NETWORK
,(VER1.DOM_SYN - VER2.DOM_SYN) DOM_SYN
,(VER1.INT_TV - VER2.INT_TV) INT_TV
,(VER1.DOM_HV - VER2.DOM_HV) DOM_HV
,(VER1.INT_HV - VER2.INT_HV) INT_HV
,(VER1.LM - VER2.LM) LM
,(VER1.MUSIC - VER2.MUSIC) MUSIC
,(VER1.PAY_TV - VER2.PAY_TV) PAY_TV
,(VER1.OTHER - VER2.OTHER) OTHER
,(VER1.REL_COST - VER2.REL_COST) REL_COST
,(VER1.SERIES_PROD - VER2.SERIES_PROD) SERIES_PROD
,(VER1.FINANCING - VER2.FINANCING) FINANCING
,(VER1.POH - VER2.POH) POH
,(VER1.RES - VER2.RES) RES
,(VER1.PAR - VER2.PAR) PAR
,(VER1.AMORT_ADJ - VER2.AMORT_ADJ) AMORT_ADJ
FROM (
SELECT
--#PROJ.PERIOD,
#PROJ.SERIES_DESC,
#PROJ.REPORT_SERIES_ID,
#PROJ.REPORT_PROJECT_ID,
#PROJ.PROJECT_DESC,
--#PROJ.RELEASE_STATUS,
#PROJ.BUDGET_CODE
,#PROJ.BC_DESC
,#PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER,
--#PROJ.BC_CAT,
--#PROJ.BC_DESC,
-1*SUM(AMOUNT) AMOUNT,
-1*SUM(NETWORK) NETWORK,
-1*SUM(DOM_SYN) DOM_SYN,
-1*SUM(INT_TV) INT_TV,
-1*SUM(DOM_HV) DOM_HV,
-1*SUM(INT_HV) INT_HV,
-1*SUM(LM) LM,
-1*SUM(MUSIC) MUSIC,
-1*SUM(PAY_TV) PAY_TV,
-1*SUM(OTHER) OTHER,
-1*SUM(REL_COST) REL_COST,
-1*SUM(SERIES_PROD) SERIES_PROD,
-1*SUM(FINANCING) FINANCING,
-1*SUM(POH) POH,
-1*SUM(RES) RES,
-1*SUM(PAR) PAR,
-1*SUM(AMORT_ADJ) AMORT_ADJ

FROM #PROJ
WHERE #PROJ.VER_ID =1
GROUP BY #PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER
,#PROJ.SERIES_DESC
,#PROJ.REPORT_SERIES_ID
,#PROJ.REPORT_PROJECT_ID
,#PROJ.PROJECT_DESC
--,#PROJ.RELEASE_STATUS
--,#PROJ.PERIOD
,#PROJ.BUDGET_CODE
--,#FIN.BC_CAT
,#PROJ.BC_DESC
--ORDER BY #PROJ.RELEASE_ORDER,#PROJ.SERIES_DESC,#PROJ.REPORT_PROJECT_ID,#FIN.BUDGET_CODE
--ORDER BY #PROJ.REPORT_PROJECT_ID,#FIN.BUDGET_CODE
) VER1
JOIN (
SELECT
#PROJ.SERIES_DESC,
--#PROJ.PERIOD,
#PROJ.REPORT_SERIES_ID,
#PROJ.REPORT_PROJECT_ID,
#PROJ.PROJECT_DESC,
--#PROJ.RELEASE_STATUS,
#PROJ.BUDGET_CODE
,#PROJ.BC_DESC
,#PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER,
--#PROJ.BC_CAT,
--#PROJ.BC_DESC,
-1*SUM(AMOUNT) AMOUNT,
-1*SUM(NETWORK) NETWORK,
-1*SUM(DOM_SYN) DOM_SYN,
-1*SUM(INT_TV) INT_TV,
-1*SUM(DOM_HV) DOM_HV,
-1*SUM(INT_HV) INT_HV,
-1*SUM(LM) LM,
-1*SUM(MUSIC) MUSIC,
-1*SUM(PAY_TV) PAY_TV,
-1*SUM(OTHER) OTHER,
-1*SUM(REL_COST) REL_COST,
-1*SUM(SERIES_PROD) SERIES_PROD,
-1*SUM(FINANCING) FINANCING,
-1*SUM(POH) POH,
-1*SUM(RES) RES,
-1*SUM(PAR) PAR,
-1*SUM(AMORT_ADJ) AMORT_ADJ

FROM #PROJ
WHERE #PROJ.VER_ID =2

GROUP BY #PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER
,#PROJ.SERIES_DESC
,#PROJ.REPORT_SERIES_ID
,#PROJ.REPORT_PROJECT_ID
,#PROJ.PROJECT_DESC
--,#PROJ.PERIOD
--,#PROJ.RELEASE_STATUS
,#PROJ.BUDGET_CODE
--,#PROJ.BC_CAT
,#PROJ.BC_DESC

) VER2
ON VER1.REPORT_PROJECT_ID = VER2.REPORT_PROJECT_ID
AND VER1.BUDGET_CODE = VER2.BUDGET_CODE
ORDER BY VER1.SERIES_DESC
,VER1.REPORT_PROJECT_ID
,VER1.BUDGET_CODE

--select * from #PROJ

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Original post blogged on codeboxer.com.

dump in your stored proc via rake task

2 February 2009 | 3:35 pm by codeboxer

These days, a remote login to your prod database is a luxury, one not everyone has.

See below for how insert a stored proc (or 'stored routine', as MySQL likes to say) via rake task:

namespace :db do
task :load_sp1 do
sqlconn = ActiveRecord::Base.connection();

sql = %Q! CREATE PROCEDURE `sp_generate_forecast`(IN p_run_day INT(2), IN p_month INT(2), IN p_year INT(4), IN revplanid INT(10), IN month_days INT(2))
BEGIN
DECLARE r_units, r_day, r_partner int;
DECLARE done int DEFAULT 0;

END!

sqlconn.execute("DROP PROCEDURE IF EXISTS `sp_generate_forecast`")
sqlconn.execute sql
end
end

Original post blogged on codeboxer.com.

minor league pivot query

27 January 2009 | 10:33 pm by codeboxer

This would be a pivot query 101 kind of snippet. They teach this in SQL kindergarten - I only imagine, of course, because I never went.

The use case: it's a single table, with values grouped by partner_id and two enums for revplan_type. You need a variance report. What do you do?

SELECT t_plan.partner_id, plan_amount - for_amount variance
FROM
(SELECT partner_id, amount plan_amount
FROM revplan_infos
WHERE revplan_type = 'Plan') t_plan
JOIN (
SELECT partner_id, amount for_amount
FROM revplan_infos
WHERE revplan_type = 'Forecast') t_for
ON t_for.partner_id = t_plan.partner_id

Original post blogged on codeboxer.com.

how to call a stored routine from Rails

27 January 2009 | 9:43 pm by codeboxer

So you wanna call your own stored routine, eh?
Here ya go - the right syntax. I read on a blog somewhere that you send any command to the database through a .find_by_sql command, but that turned out to be a load of baloney. Is there anything Ruby + ActiveRecord can't do?

def forecast
x = params[:revplan_id].to_i
@revplan = Revplan.find(x)
sql = ActiveRecord::Base.connection();
sql.execute "SET autocommit=0";
sql.begin_db_transaction
result = sql.execute("call sp_generate_forecast(#{Time.now.month}, #{Time.now.year}, #{x}, #{month_days(Time.now.month)})")
sql.commit_db_transaction
end

Clothing, I mean transaction support is optional.Original post blogged on codeboxer.com.

MySQL 5.0 SP with Cursor Logic

23 January 2009 | 11:43 am by codeboxer

I have written a bunch of SPs over the years (stored procedure, as I learned it, but MySQL calls them Stored Routines - SRs?). I even have my best/largest/most complicated SP ever linked here on codeboxer.com at http://codeboxer.com/read.php?a=the-Monstertrak-migration.

That one was of course written in PL/SQL, and I have now officially moved out of the world of Oracle and into the world of MySQL 5.0 - which is sort of like moving from New York City to San Jose (weather notwithstanding).

Anyway, I found precious little RECENT coverage on how MySQL implemented it's 'stored routines', and I got to tell ya it took some digging - never mind trying to figure out how cursors work. MySQL, btw, has an ok documentation section but it is mediocre at best, somehow following in the fragmented example of the Rails community in what seems to be the curse of the open-source ADD.

So in the spirit of this site which has always been to keep pieces of code lying around that I may use later - here is my very first MySQL sp, which does nothing really useful except use a cursor to insert values into a table. Come back next week for the syntax needed to use parameters, lol.

Other things to notice - you have to declare a separate handler for pulling out of a cursor loop when you run out of records (does anyone else remember fondly how easy ADO recordsets were to use?) but other than that things look all right in MySQL land. Bye Oracle!

CREATE PROCEDURE testy ()
BEGIN
declare a, b int;

DECLARE done int DEFAULT 0;

declare masterCursor CURSOR FOR
SELECT SUM(sales.amount) amt, sales.item_month
FROM sales
WHERE RETAILER_ID = 336
GROUP BY sales.item_month;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN masterCursor;
REPEAT
FETCH masterCursor INTO a, b;
IF NOT done THEN
insert into revplan (amount, item_month)
values (a,b);
END IF;

UNTIL done END REPEAT;

CLOSE masterCursor;

END

ps, gotta give a shout out to http://www.razorsql.com/ which so far has been easy to use and has served as stand-in IDE for Toad, which is what I was used to.Original post blogged on codeboxer.com.

Perl linux and database backups

21 January 2009 | 4:52 pm by codeboxer

I got to tell ya, most of the greatest people don't get the recognition they deserve. Open Source is all about everyone putting in what they got. So mosey on over to http://worldcommunity.com/opensource/ and tell Peter Brown that his Perl-based mysql backup script works like a charm.

It is very easy to set up, so I won't go too far into it - just download and follow the instructions. I also opted in to for the ftp push, so after the script dumps out a mysql txt file for every table, it will tar it, zip it, and push it to the ftp server of your choice. Cool.

This is where it gets tricky - I pulled this little gem out of a comment on Peter Brown's site. This script leaves you with a directory of txt files - most of us are used to the single .sql file of the whole db, so getting at a full restore from a bunch of txt files does not sound fun - until linux jumps in for the rescue.

Get this, just untar it:

tar -xf bak.mysql.2009-01-20_14.55.30_.tar.gz

Move into that directory:

cd bak.mysql.2009-01-20_14.55.30

and hit it with the cat xargs combo, shooting it right into mysql:

find . -name "*.txt" -print | xargs -t --replace cat {} | mysql your_db_name -u your_db_user -p -h subdir.yoursite.com

and you're done! sweet, huh?

p.s. - the -h switch at the end (-h subdir.yoursite.com) is not always necessary, if it is localhost you can leave it out.Original post blogged on codeboxer.com.

right joins versus the subquery

14 January 2009 | 4:37 pm by codeboxer

If you're anything like me, you are a serious pragmatist. I like to get at the problem immediately, and if there are shortcuts, then we have to weight short-term versus long-term gain before we can choose sides.

Here is a great SQL example of this principle.

The old-school way to write this query would be with the subquery, like so:

select id from retailers
where id in (select retailer_id from sales)
and merchant = 'Ralphs'

I am simply trying to verify that I have not seen sales for the 'Ralphs' retailer come in yet, despite that 'merchant' value already being present in the retailers table.

I know, it's a little confusing, but bear with me.

One might also write:

select id from sales
where retailer_id in (select id from retailers where merchant = 'Ralphs')

Neither of these run very well. Without some added indexing, the first query won't return anything at all before it times out, and the second one runs ok but it is about 3 to 4 times slower than the right join (below).

select r.id from retailers r
right join sales s on s.retailer_id = r.id
where r.merchant = 'Ralphs'

There you have it - right joins FTW.Original post blogged on codeboxer.com.

add aggregates to a group by

9 December 2008 | 3:02 pm by codeboxer

Pull in YTD aggregates, along with the numbers grouped by month - good example of a self-join as well as a 'derived table' which in this example is called 'counted'.

select s.product, count(s.serial_number) number_sold, year(s.item_date) y, month(s.item_date) m, counted.units
from sales s
join (select product, count(serial_number) units from sales group by product) counted on s.product = counted.product
group by s.product, year(s.item_date), month(s.item_date)
order by s.product, year(s.item_date), month(s.item_date)
Original post blogged on codeboxer.com.

Tableless models and a checkbox collection

4 December 2008 | 5:44 pm by codeboxer

I have always loved the sound of a table-less model. It feels so dirty. Here is one from the school of hard knocks, known as the Merchant model:

class Merchant < ActiveRecord::Base
def self.columns() @columns ||= []; end

def self.column(name, sql_type = nil, default = nil, null = true)
columns << ActiveRecord::ConnectionAdapters::Column.new(name.to_s, default, sql_type.to_s, null)
end

column :merchant, :string
validates_presence_of :merchant

def self.get_edit_values(page_id)
new_arr = []
Summary.find_by_sql(["
SELECT merchant, CASE LENGTH(selected) > 0 WHEN TRUE THEN 1 ELSE 0 END value FROM
(SELECT value merchant, merchant selected
FROM
(SELECT merchant from merchant_pages where page_id=?) signed_up
RIGHT JOIN (SELECT DISTINCT MERCHANT value FROM retailers) distinct_m
ON distinct_m. value = signed_up. merchant) new_alias
ORDER BY merchant
", page_id.to_i]).each do |x|
new_arr << [x.merchant, x.value]
end
new_arr
end

def self.get_values
Summary.find_by_sql(["
SELECT DISTINCT MERCHANT value FROM retailers order by merchant
"]).each do |x|
@columns << x.value
end
@columns
end

end

Of course, it does hit the database, but there is no Merchants table, per se, there is just this model. What returns from a 'SELECT * FROM MERCHANTS' is the distinct selection for the value of 'merchant' from the Retailers table. These come in from the partners, and they are very consistent. I can't tell you how helpful this is. The system auto-magically created a Target.com that showed up immediately alongside it's older bother, Target. Watch out, however, for cache-ing concerns that will necessarily stem from this approach in terms of scale - in this case, that was not a concern because we have a very small (but highly lucrative) user base.

This is the corresponding controller code. This is from the pages_controller, because there is no direct merchants_controller.

def update
@page = Page.find(params[:id])

if @page.update_attributes(params[:page])
if dump_all_merchant_pages(@page.id)
params[:merchants].each do |p|
create_or_update_merchant_page(p[0].to_s, @page.id, true)
end
end
flash[:notice] = 'Page was successfully updated.'
redirect_to pages_path
else
render :action => 'edit'
end
end

the functions, from the bottom of the page:

private
def dump_all_merchant_pages(page_id)
Page.find(page_id).merchant_pages.each do |x|
x.destroy
end
true
end

def create_or_update_merchant_page(m, p, val)
x = MerchantPage.find_by_sql(["SELECT merchant
FROM merchant_pages
WHERE merchant = ?
AND page_id = ? LIMIT 1", m, p])
if !x.first
x = MerchantPage.new(:merchant => m, :page_id => p)
x.save
else
if !val
x.destroy
end
true
end
end

Finally, the _page partial:

form_for ([@page]) do |f|
Page Name
f.text_field :title, :size => 60
Main Content
f.text_area :page_body, :cols => 100
Locator Footer (Link back to Partner homepage)
f.text_area :page_footer, :cols => 100, :rows => 5
Merchants
ul class="Merchant-list"
if button_name=="Save Changes"
@merchants.each do |m|
li INPUT type="checkbox" name="merchants[<=m[0]%>][]" <=m[1]=="1" ? "checked" : "" label m[0].to_s /label
end
else
@merchants.each do |m|
li INPUT type="checkbox" name="merchants[<=m>][]"/> <=m.to_s /label
end
end
/ul
f.submit button_name
endOriginal post blogged on codeboxer.com.

The old INSERT SELECT syntax example

4 December 2008 | 2:29 am by codeboxer

A good example of SQL's favorite timesaver.

INSERT INTO `evo_bloggroups`
SELECT '4',
bloggroup_group_ID,
bloggroup_ismember,
bloggroup_perm_poststatuses,
bloggroup_perm_edit,
bloggroup_perm_delpost,
bloggroup_perm_comments,
bloggroup_perm_cats,
bloggroup_perm_properties,
bloggroup_perm_admin,
bloggroup_perm_media_upload,
bloggroup_perm_media_browse,
bloggroup_perm_media_change
FROM `evo_bloggroups`
WHERE bloggroup_blog_ID = 1
Original post blogged on codeboxer.com.

Ruby find_by_sql and a select statement

4 December 2008 | 2:05 am by codeboxer

This is how I still do things. I don't think it's terribly old-fashioned. Databases are still faster than web servers, aren't they?

Summary.find_by_sql(["
(select 'GMG' name, 0 id, 1 orderby
from partners where 1=1)
union
(select display_val name, p.id, 2 orderby
from partners p where active = 1)
order by orderby, name, id"
])

Original post blogged on codeboxer.com.

My Site Links

Screenshots are featured above. If you visit gmgpulse, you may login as demo/demo.

CODEBOXER.com

Search the Feed

Silly Dev jokes

compiling mysql and getting the gem installed correctly

make sqlite work with snow leopard

sql via rake

tunnel of love

i missed writing sql

Top 5 sql query with other included

an old pivot table report procedure

dump in your stored proc via rake task

minor league pivot query

how to call a stored routine from Rails

MySQL 5.0 SP with Cursor Logic

Perl linux and database backups

right joins versus the subquery

add aggregates to a group by

Tableless models and a checkbox collection

The old INSERT SELECT syntax example

Ruby find_by_sql and a select statement

My Site Links

Other Links

Rockstar Television