One big row...
-
Hi gang,
Sorry for the vagary in the subject and description, but I couldn't think of a one-liner to explain this.
I have a bunch of aggregate data that I want to flatten into a single row for consumption by an SSRS report.
I have written an SP to do this work, but essentially I am just doing a bunch of sub-selects and assigning aliases:
(SELECT ... FROM table1 WHERE ...) as XDay,
(SELECT ... FROM table1 WHERE other conditions) as XWeek
...
(SELECT ... FROM table1 WHERE ...) as YDay,
(SELECT ... FROM table1 WHERE other conditions) as YWeek
...
Basically there are a bunch of blocks of these statements for aggregating various report data over Day, Week, Month, Quarter, and Year.
This works fine, but seems inefficient and ugly... can I have a Halleluja?!?
So, I thought I would be clever and write on SELECT for all of the "Day" data, then another SELECT for all of the "Week" data, etc., and UNION them together:
SELECT ... AS XDay, ... AS YDay, ... FROM table1 WHERE ...
UNION
SELECT ... AS XWeek, ... AS YWeek, ... FROM table1 WHERE other conditions
UNION
...
Problem is, and I didn't expect this... obviously I did not understand how UNION worked, I got several rows of data with column headings defined by the first SELECT statement, regardless of the aliases in subsequent SELECTS. I thought this would give me on wide row with the column headings being the aliases I'd assigned to each item.
What am I doing wrong, and is there a more elegant way of doing this?
Thanks for any input, OTHER THAN "WTF, you idiot..." 🙂
Chris
-
Hi,
The result set column names of a UNION are the same as the column names in the result set of the first SELECT statement in the UNION. The result set column names of the other SELECT statements are ignored.
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.
-
But in your case, I am not getting what you want to do.
Can you post sample data with example.
-
Hi Hari,
I got that about UNION from what I saw happening.
I thought I posted some examples there... I basically need to create one single-but-wide row from multiple subselects, however the WHERE clause for each one repeats over 5 permutations, so rather than have 10 subselects with the same WHERE clause, I wanted to make that one statement that returns 10 columns, then "union" that to the other 10 columns returned by the statements using each of the other WHERE clause permutations.
Does that make more sense?
Thanks,
C
-
can you post some sample data and table definition ?
* Noel -
Yes, and please post your query. It seems very likely to me that you can transform your query from a series of Select's to a single select over aggregated Case's, but I need to see it to know that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Ok... the end table is nothing that is defined, just a feed for a report, so it is built from the 130 or so subselects.
Here is the SP that gathers it all together:
USE [myShapeDW]
GO
/****** Object: StoredProcedure [dbo].[DORMainSelect] Script Date: 06/27/2008 20:23:57 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[DORMainSelect]
(
@RptDate datetime
)
AS
/* $Header: dbo.DORMainSelect.prc,v1.0 04/12/2007 */
/********************************************************************
*
* PROCEDURE NAME: DORMainSelect
*
* Copyright 2008 myShape, Inc.
*
* CHANGE HISTORY: Created 6/22/08 @ 11:25pm CB
*
* CALLED FROM: SSRS
*
* DATABASE(S) USED: myShapeDW
*
* DESCRIPTION: Aggregates various data for Daily Order Report
*into a single row that servers as the basis for
*the report.
*
* INPUT PARAMETERS
* @RptDatedatetime
*
* OUTPUT PARAMETERS[NONE]
*
***************************************************************************/
DECLARE @f_yr int
DECLARE @f_qtr int
DECLARE @f_mon int
DECLARE @f_wk int
DECLARE @dateKey INT
DECLARE @fWeekBeginKey INT
DECLARE @fWeekEndKey INT
DECLARE @fMonthBeginKey INT
DECLARE @fMonthEndKey INT
DECLARE @fQtrBeginKey INT
DECLARE @fQtrEndKey INT
DECLARE @fYearBeginKey INT
DECLARE @fYearEndKey INT
SELECT @dateKey = id, @f_yr = fiscal_year, @f_qtr = fiscal_quarter, @f_mon = fiscal_month, @f_wk = fiscal_week FROM Utility.Calendar WHERE [date] = @RptDate
PRINT 'fYR = ' + CAST(@f_yr AS VARCHAR(4))
PRINT 'fQTR = ' + CAST(@f_qtr AS VARCHAR(4))
PRINT 'fMON = ' + CAST(@f_mon AS VARCHAR(4))
PRINT 'fWK = ' + CAST(@f_wk AS VARCHAR(4))
PRINT 'fDAY = ' + CAST(@dateKey AS VARCHAR(4))
SELECT @fWeekBeginKey = MIN(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr AND fiscal_month = @f_mon AND fiscal_week = @f_wk
SELECT @fWeekEndKey = @dateKey
SELECT @fMonthBeginKey = MIN(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr AND fiscal_month = @f_mon
SELECT @fMonthEndKey = MAX(id) FROM [Utility].[Calendar] WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr AND fiscal_month = @f_mon
SELECT @fQtrBeginKey = MIN(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr
SELECT @fQtrEndKey = MAX(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr
SELECT @fYearBeginKey = MIN(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr
SELECT @fYearEndKey = MAX(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr
PRINT 'fYR = ' + CAST(@fYearBeginKey AS VARCHAR(4)) + ' - ' + CAST(@fYearEndKey AS VARCHAR(4))
PRINT 'fQTR = ' + CAST(@fQtrBeginKey AS VARCHAR(4)) + ' - ' + CAST(@fQtrEndKey AS VARCHAR(4))
PRINT 'fMON = ' + CAST(@fMonthBeginKey AS VARCHAR(4)) + ' - ' + CAST(@fMonthEndKey AS VARCHAR(4))
PRINT 'fWK = ' + CAST(@fWeekBeginKey AS VARCHAR(4)) + ' - ' + CAST(@fWeekEndKey AS VARCHAR(4))
SELECT cal.date, cal.fiscal_year,
/* NumOrders */
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key = @dateKey) as TotalNumOrdersDay,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey) as TotalNumOrdersWeek,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey) as TotalNumOrdersMon,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey) as TotalNumOrdersQtr,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey) as TotalNumOrdersYear,
/* OrdersGross */
(SELECT SUM(item_qty * item_price) FROM FactOrder WHERE date_purchased_key = @dateKey) as TotalOrdersGrossDay,
(SELECT SUM(item_qty * item_price) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey) as TotalOrdersGrossWeek,
(SELECT SUM(item_qty * item_price) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey) as TotalOrdersGrossMon,
(SELECT SUM(item_qty * item_price) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey) as TotalOrdersGrossQtr,
(SELECT SUM(item_qty * item_price) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey) as TotalOrdersGrossYear,
/* ItemsSold */
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key = @dateKey) as TotalItemsSoldDay,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey) as TotalItemsSoldWeek,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey) as TotalItemsSoldMon,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey) as TotalItemsSoldQtr,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey) as TotalItemsSoldYear,
/* NumPromosRedeemed */
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key = @dateKey AND promo_disc_code IS NOT NULL) as TotalPromosRedeemedDay,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey AND promo_disc_code IS NOT NULL) as TotalPromosRedeemedWeek,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey AND promo_disc_code IS NOT NULL) as TotalPromosRedeemedMon,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey AND promo_disc_code IS NOT NULL) as TotalPromosRedeemedQtr,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey AND promo_disc_code IS NOT NULL) as TotalPromosRedeemedYear,
/* PromoDiscount */
(SELECT DISTINCT SUM(promo_disc_amt) OVER (PARTITION BY NULL) totalPromo FROM FactOrder WHERE [promo_disc_code] IS NOT NULL AND date_purchased_key = @dateKey GROUP BY order_key,promo_disc_amt) as TotalPromoDiscDay,
(SELECT DISTINCT SUM(promo_disc_amt) OVER (PARTITION BY NULL) totalPromo FROM FactOrder WHERE [promo_disc_code] IS NOT NULL AND date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey GROUP BY order_key,promo_disc_amt) as TotalPromoDiscWeek,
(SELECT DISTINCT SUM(promo_disc_amt) OVER (PARTITION BY NULL) totalPromo FROM FactOrder WHERE [promo_disc_code] IS NOT NULL AND date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey GROUP BY order_key,promo_disc_amt) as TotalPromoDiscMon,
(SELECT DISTINCT SUM(promo_disc_amt) OVER (PARTITION BY NULL) totalPromo FROM FactOrder WHERE [promo_disc_code] IS NOT NULL AND date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey GROUP BY order_key,promo_disc_amt) as TotalPromoDiscQtr,
(SELECT DISTINCT SUM(promo_disc_amt) OVER (PARTITION BY NULL) totalPromo FROM FactOrder WHERE [promo_disc_code] IS NOT NULL AND date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey GROUP BY order_key,promo_disc_amt) as TotalPromoDiscYear,
/* NumEmployeeDiscounts */
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key = @dateKey AND staff_disc_code IS NOT NULL) as TotalEmployeeDiscountsDay,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey AND staff_disc_code IS NOT NULL) as TotalEmployeeDiscountsWeek,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey AND staff_disc_code IS NOT NULL) as TotalEmployeeDiscountsMon,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey AND staff_disc_code IS NOT NULL) as TotalEmployeeDiscountsQtr,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey AND staff_disc_code IS NOT NULL) as TotalEmployeeDiscountsYear,
/* EmployeeDiscount */
(SELECT DISTINCT SUM(staff_disc_amt) OVER (PARTITION BY NULL) totalStaff FROM FactOrder WHERE [staff_disc_code] IS NOT NULL AND date_purchased_key = @dateKey GROUP BY order_key,staff_disc_amt) as TotalStaffDiscDay,
(SELECT DISTINCT SUM(staff_disc_amt) OVER (PARTITION BY NULL) totalStaff FROM FactOrder WHERE [staff_disc_code] IS NOT NULL AND date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey GROUP BY order_key,staff_disc_amt) as TotalStaffDiscWeek,
(SELECT DISTINCT SUM(staff_disc_amt) OVER (PARTITION BY NULL) totalStaff FROM FactOrder WHERE [staff_disc_code] IS NOT NULL AND date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey GROUP BY order_key,staff_disc_amt) as TotalStaffDiscMon,
(SELECT DISTINCT SUM(staff_disc_amt) OVER (PARTITION BY NULL) totalStaff FROM FactOrder WHERE [staff_disc_code] IS NOT NULL AND date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey GROUP BY order_key,staff_disc_amt) as TotalStaffDiscQtr,
(SELECT DISTINCT SUM(staff_disc_amt) OVER (PARTITION BY NULL) totalStaff FROM FactOrder WHERE [staff_disc_code] IS NOT NULL AND date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey GROUP BY order_key,staff_disc_amt) as TotalStaffDiscYear,
/* NumGiftCards */
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key = @dateKey AND gift_card_code IS NOT NULL) as TotalGiftCardsDay,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey AND gift_card_code IS NOT NULL) as TotalGiftCardsWeek,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey AND gift_card_code IS NOT NULL) as TotalGiftCardsMon,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey AND gift_card_code IS NOT NULL) as TotalGiftCardsQtr,
(SELECT COUNT(DISTINCT order_key) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey AND gift_card_code IS NOT NULL) as TotalGiftCardsYear,
/* GiftCardAmt */
(SELECT DISTINCT SUM(gift_card_amt) OVER (PARTITION BY NULL) totalGC FROM FactOrder WHERE [gift_card_code] IS NOT NULL AND date_purchased_key = @dateKey GROUP BY order_key,gift_card_amt) as TotalGiftCardAmtDay,
(SELECT DISTINCT SUM(gift_card_amt) OVER (PARTITION BY NULL) totalGC FROM FactOrder WHERE [gift_card_code] IS NOT NULL AND date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey GROUP BY order_key,gift_card_amt) as TotalGiftCardAmtWeek,
(SELECT DISTINCT SUM(gift_card_amt) OVER (PARTITION BY NULL) totalGC FROM FactOrder WHERE [gift_card_code] IS NOT NULL AND date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey GROUP BY order_key,gift_card_amt) as TotalGiftCardAmtMon,
(SELECT DISTINCT SUM(gift_card_amt) OVER (PARTITION BY NULL) totalGC FROM FactOrder WHERE [gift_card_code] IS NOT NULL AND date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey GROUP BY order_key,gift_card_amt) as TotalGiftCardAmtQtr,
(SELECT DISTINCT SUM(gift_card_amt) OVER (PARTITION BY NULL) totalGC FROM FactOrder WHERE [gift_card_code] IS NOT NULL AND date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey GROUP BY order_key,gift_card_amt) as TotalGiftCardAmtYear,
/* FirstPurchase */
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key = @dateKey AND first_purchase = 1) as TotalFirstPurchaseDay,
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey AND first_purchase = 1) as TotalFirstPurchaseWeek,
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey AND first_purchase = 1) as TotalFirstPurchaseMon,
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey AND first_purchase = 1) as TotalFirstPurchaseQtr,
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey AND first_purchase = 1) as TotalFirstPurchaseYear,
/* NumFirstPurchases */
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key = @dateKey AND [first_purchase] = 1) as NumFirstPurchasesDay,
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey AND [first_purchase] = 1) as NumFirstPurchasesWeek,
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey AND [first_purchase] = 1) as NumFirstPurchasesMon,
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey AND [first_purchase] = 1) as NumFirstPurchasesQtr,
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey AND [first_purchase] = 1) as NumFirstPurchasesYear,
/* RepeatPurchases */
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key = @dateKey AND first_purchase = 0) as TotalRptPurchaseDay,
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey AND first_purchase = 0) as TotalRptPurchaseWeek,
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey AND first_purchase = 0) as TotalRptPurchaseMon,
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey AND first_purchase = 0) as TotalRptPurchaseQtr,
(SELECT SUM(ext_price) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey AND first_purchase = 0) as TotalRptPurchaseYear,
/* NumRepeatPurchases */
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key = @dateKey AND [first_purchase] = 0) as NumRptPurchasesDay,
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey AND [first_purchase] = 0) as NumRptPurchasesWeek,
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey AND [first_purchase] = 0) as NumRptPurchasesMon,
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey AND [first_purchase] = 0) as NumRptPurchasesQtr,
(SELECT COUNT(DISTINCT order_key) FROM [FactOrder] WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey AND [first_purchase] = 0) as NumRptPurchasesYear,
(SELECT COUNT(DISTINCT customer_id) FROM DimCustomer) as NumMembers,
(SELECT COUNT(DISTINCT customer_id) FROM DimCustomer WHERE is_shapematch_member = 1) as NumSMMembers,
/* PlanSalesGoal */
(SELECT sales_goal FROM FactPlanDataSales WHERE date_key = @dateKey) as PlanToDateDay,
(SELECT SUM(sales_goal) FROM FactPlanDataSales WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) as PlanToDateWeek,
(SELECT SUM(sales_goal) FROM FactPlanDataSales WHERE date_key >= @fMonthBeginKey AND date_key <= @fMonthEndKey) as PlanToDateMon,
(SELECT SUM(sales_goal) FROM FactPlanDataSales WHERE date_key >= @fQtrBeginKey AND date_key <= @fQtrEndKey) as PlanToDateQtr,
(SELECT SUM(sales_goal) FROM FactPlanDataSales WHERE date_key >= @fYearBeginKey AND date_key <= @fYearEndKey) as PlanToDateYear,
/* PlanMembershipGoals*/
(SELECT membership_goal FROM FactPlanDataMembership WHERE fiscal_month = @f_mon AND fiscal_year = @f_yr) AS MembershipGoalMonth,
(SELECT sm_membership_goal FROM FactPlanDataMembership WHERE fiscal_month = @f_mon AND fiscal_year = @f_yr) AS SMMembershipGoalMonth,
/* TotalVisits */
(SELECT total_visits FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS TotalVisitsDay,
(SELECT SUM(total_visits) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS TotalVisitsWeek,
(SELECT SUM(total_visits) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS TotalVisitsMon,
(SELECT SUM(total_visits) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS TotalVisitsQtr,
(SELECT SUM(total_visits) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS TotalVisitsYear,
/* UniqueVisitors */
(SELECT unique_visitors FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS UniqueVisitorsDay,
(SELECT SUM(unique_visitors) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS UniqueVisitorsWeek,
(SELECT SUM(unique_visitors) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS UniqueVisitorsMon,
(SELECT SUM(unique_visitors) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS UniqueVisitorsQtr,
(SELECT SUM(unique_visitors) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS UniqueVisitorsYear,
/* Hits */
(SELECT hits FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS HitsDay,
(SELECT SUM(hits) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS HitsWeek,
(SELECT SUM(hits) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS HitsMon,
(SELECT SUM(hits) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS HitsQtr,
(SELECT SUM(hits) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS HitsYear,
/* NewMembers */
(SELECT new_members FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS NewMembersDay,
(SELECT SUM(new_members) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS NewMembersWeek,
(SELECT SUM(new_members) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS NewMembersMon,
(SELECT SUM(new_members) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS NewMembersQtr,
(SELECT SUM(new_members) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS NewMembersYear,
/* NewSMMembers */
(SELECT new_sm_members FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS NewSMMembersDay,
(SELECT SUM(new_sm_members) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS NewSMMembersWeek,
(SELECT SUM(new_sm_members) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS NewSMMembersMon,
(SELECT SUM(new_sm_members) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS NewSMMembersQtr,
(SELECT SUM(new_sm_members) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS NewSMMembersYear,
/* MemberLogins */
(SELECT member_logins FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS MemberLoginsDay,
(SELECT SUM(member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS MemberLoginsWeek,
(SELECT SUM(member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS MemberLoginsMon,
(SELECT SUM(member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS MemberLoginsQtr,
(SELECT SUM(member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS MemberLoginsYear,
/* SMMemberLogins */
(SELECT sm_member_logins FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS SMMemberLoginsDay,
(SELECT SUM(sm_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS SMMemberLoginsWeek,
(SELECT SUM(sm_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS SMMemberLoginsMon,
(SELECT SUM(sm_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS SMMemberLoginsQtr,
(SELECT SUM(sm_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS SMMemberLoginsYear,
/* Tracked NewMembers */
(SELECT t_new_members FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS TrackedNewMembersDay,
(SELECT SUM(t_new_members) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS TrackedNewMembersWeek,
(SELECT SUM(t_new_members) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS TrackedNewMembersMon,
(SELECT SUM(t_new_members) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS TrackedNewMembersQtr,
(SELECT SUM(t_new_members) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS TrackedNewMembersYear,
/* Tracked NewSMMembers */
(SELECT t_new_sm_members FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS TrackedNewSMMembersDay,
(SELECT SUM(t_new_sm_members) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS TrackedNewSMMembersWeek,
(SELECT SUM(t_new_sm_members) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS TrackedNewSMMembersMon,
(SELECT SUM(t_new_sm_members) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS TrackedNewSMMembersQtr,
(SELECT SUM(t_new_sm_members) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS TrackedNewSMMembersYear,
/* Tracked MemberLogins */
(SELECT t_member_logins FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS TrackedMemberLoginsDay,
(SELECT SUM(t_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS TrackedMemberLoginsWeek,
(SELECT SUM(t_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS TrackedMemberLoginsMon,
(SELECT SUM(t_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS TrackedMemberLoginsQtr,
(SELECT SUM(t_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS TrackedMemberLoginsYear,
/* Tracked SMMemberLogins */
(SELECT t_sm_member_logins FROM [FactVisitorMetrics] WHERE date_key = @dateKey) AS TrackedSMMemberLoginsDay,
(SELECT SUM(t_sm_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey) AS TrackedSMMemberLoginsWeek,
(SELECT SUM(t_sm_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fMonthBeginKey AND [date_key] <= @fMonthEndKey) AS TrackedSMMemberLoginsMon,
(SELECT SUM(t_sm_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fQtrBeginKey AND [date_key] <= @fQtrEndKey) AS TrackedSMMemberLoginsQtr,
(SELECT SUM(t_sm_member_logins) FROM [FactVisitorMetrics] WHERE date_key >= @fYearBeginKey AND [date_key] <= @fYearEndKey) AS TrackedSMMemberLoginsYear
FROM Utility.Calendar AS cal
WHERE cal.id = @dateKey
The data is mostly integers.
As you can see, there are 5 different WHERE clauses for each set of statements. What I was hoping to do was write 5 different statements, one for each WHERE clause, and then have the data I am gathering/aggregating just be columns of the appropriate SELECT. But, I need to "union" the columns from the 5 statements into a single row, much like what this SP returns.
Does that make more sense?
Cheers,
Chris
-
As an example, see if this helps
/* ItemsSold */(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key = @dateKey) as TotalItemsSoldDay,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey) as TotalItemsSoldWeek,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey) as TotalItemsSoldMon,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey) as TotalItemsSoldQtr,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey) as TotalItemsSoldYear
Can be written as this
/* ItemsSold */SELECT SUM(CASE WHEN date_purchased_key = @dateKey THEN item_qty ELSE 0 END) as TotalItemsSoldDay,
SUM(CASE WHEN date_purchased_key >= @fWeekBeginKey
AND date_purchased_key <= @fWeekEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldWeek,
SUM(CASE WHEN date_purchased_key >= @fMonthBeginKey
AND [date_purchased_key] <= @fMonthEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldMon,
SUM(CASE WHEN date_purchased_key >= @fQtrBeginKey
AND [date_purchased_key] <= @fQtrEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldQtr,
SUM(CASE WHEN date_purchased_key >= @fYearBeginKey
AND [date_purchased_key] <= @fYearEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldYear
FROM FactOrder
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Yes, Mark has it. You should be able to convert all of them like that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Cool, guys! That's what I was looking for, I think.
Thanks for the help. Much obliged.
Cheers,
Chris
-
Ok, finally had a chance to look at this and digest it.
It seems that Mark's solution will still leave me with the same number of statements, meaning I will still be doing the same 5 WHERE conditions for each column I want to have in the solution set.
Isn't it somehow possible to make 5 statements, one for each WHERE condition, and concatenate (this is where I was saying "union") the results so that I get the same single, wide row?
Something to the effect of:
SELECT SUM(item_qty) AS TotalItemsSoldDay, SUM(item_qty * item_price) AS TotalOrdersGrossDay, ... FROM FactOrder WHERE date_purchased_key = @dateKey
SELECT SUM(item_qty) AS TotalItemsSoldWeek, SUM(item_qty * item_price) AS TotalOrdersGrossWeek, ... FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey
SELECT SUM(item_qty) AS TotalItemsSoldMon, SUM(item_qty * item_price) AS TotalOrdersGrossMon, ... FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey
SELECT SUM(item_qty) AS TotalItemsSoldQtr, SUM(item_qty * item_price) AS TotalOrdersGrossQtr, ... FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey
SELECT SUM(item_qty) AS TotalItemsSoldYear, SUM(item_qty * item_price) AS TotalOrdersGrossYear, ... FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey
The above works fine, but then I have other conditions that add criteria to the WHERE clause, so I would need another set of 5 statements to handle those permutations, like this:
SELECT COUNT(DISTINCT order_key) AS TotalPromosRedeemedDay FROM FactOrder WHERE date_purchased_key = @dateKey AND promo_disc_code IS NOT NULL
SELECT COUNT(DISTINCT order_key) AS TotalPromosRedeemedWeek FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey AND promo_disc_code IS NOT NULL
SELECT COUNT(DISTINCT order_key) AS TotalPromosRedeemedMon FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey AND promo_disc_code IS NOT NULL
SELECT COUNT(DISTINCT order_key) AS TotalPromosRedeemedQte FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey AND promo_disc_code IS NOT NULL
SELECT COUNT(DISTINCT order_key) AS TotalPromosRedeemedYear FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey AND promo_disc_code IS NOT NULL
but how do I get them all into that elusive single row?
Thanks,
Chris
-
Marks's approach reduces your number of where clauses from 125+ to 25+. Since each of those where clauses probably results in a Scan, this will lower your scan count by a factor of five, and probably increase your performance by a similar amount.
Try it before you knock it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Mark (6/28/2008)
As an example, see if this helps/* ItemsSold */(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key = @dateKey) as TotalItemsSoldDay,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey) as TotalItemsSoldWeek,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fMonthBeginKey AND [date_purchased_key] <= @fMonthEndKey) as TotalItemsSoldMon,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fQtrBeginKey AND [date_purchased_key] <= @fQtrEndKey) as TotalItemsSoldQtr,
(SELECT SUM(item_qty) FROM FactOrder WHERE date_purchased_key >= @fYearBeginKey AND [date_purchased_key] <= @fYearEndKey) as TotalItemsSoldYear
Can be written as this
/* ItemsSold */SELECT SUM(CASE WHEN date_purchased_key = @dateKey THEN item_qty ELSE 0 END) as TotalItemsSoldDay,
SUM(CASE WHEN date_purchased_key >= @fWeekBeginKey
AND date_purchased_key <= @fWeekEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldWeek,
SUM(CASE WHEN date_purchased_key >= @fMonthBeginKey
AND [date_purchased_key] <= @fMonthEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldMon,
SUM(CASE WHEN date_purchased_key >= @fQtrBeginKey
AND [date_purchased_key] <= @fQtrEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldQtr,
SUM(CASE WHEN date_purchased_key >= @fYearBeginKey
AND [date_purchased_key] <= @fYearEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldYear
FROM FactOrder
The only problem with the above is that this will include rows from FactOrder that were not included in the original queries. You still have to add a where clause to this statement. Before you can add the where clause - you need to identify the minimum beginning date and maximum end date. If we know for sure that we are using calendar quarters - then we can be fairly certain that the earliest beginning date is @fYearBeginKey and the ending date would be @fYearEndKey. That would modify the above to:
/* ItemsSold */SELECT SUM(CASE WHEN date_purchased_key = @dateKey THEN item_qty ELSE 0 END) as TotalItemsSoldDay,
SUM(CASE WHEN date_purchased_key >= @fWeekBeginKey
AND date_purchased_key <= @fWeekEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldWeek,
SUM(CASE WHEN date_purchased_key >= @fMonthBeginKey
AND [date_purchased_key] <= @fMonthEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldMon,
SUM(CASE WHEN date_purchased_key >= @fQtrBeginKey
AND [date_purchased_key] <= @fQtrEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldQtr,
SUM(CASE WHEN date_purchased_key >= @fYearBeginKey
AND [date_purchased_key] <= @fYearEndKey THEN item_qty ELSE 0 END) as TotalItemsSoldYear
FROM FactOrder
WHERE [date_purchased_key] >= @fYearBeginKey
AND [date_purchased_key <= @fYearEndKey
But, this does not solve the OP's problem - which is to get everything into a single row resultset.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
The more I think about this, the more I am convinced that you are going down the wrong path for how to create this report. You really shouldn't be trying to put all data into a single row.
Not only will that be a lot harder up front, any changes (e.g. adding new counters) is going to require modifying the stored procedure, modifying the report to handle the new counter columns and publishing the new report.
I would highly recommend that you re-evaluate how you are going to build this report and consider using a single row for each counter. This way, if you add a new counter - all you have to do is modifying the stored procedure and the report will handle the new row.
If you return the data for the report as something like:
CalDate Year CounterType TotDay TotWeek TotMonth TotQuarter TotYear
2008-06-01 2008 NumOrders 1 1 1 1 1
2008-06-01 2008 OrdersGross 1 1 1 1 1
...
2008-06-01 2008 Hits 1 1 1 1 1
2008-06-01 2008 NewMembers 1 1 1 1 1
...
You can then easily show that report in a simple tabular format for each counter type. To build the set of data, you can do the following:
ALTER PROCEDURE [dbo].[DORMainSelect]
@RptDate datetime
AS
/* $Header: dbo.DORMainSelect.prc,v1.0 04/12/2007 */
/********************************************************************
*
* PROCEDURE NAME: DORMainSelect
*
* Copyright 2008 myShape, Inc.
*
* CHANGE HISTORY: Created 6/22/08 @ 11:25pm CB
*
* CALLED FROM: SSRS
*
* DATABASE(S) USED: myShapeDW
*
* DESCRIPTION: Aggregates various data for Daily Order Report
*into a single row that servers as the basis for
*the report.
*
* INPUT PARAMETERS
* @RptDatedatetime
*
* OUTPUT PARAMETERS[NONE]
*
***************************************************************************/
DECLARE @f_yr int
DECLARE @f_qtr int
DECLARE @f_mon int
DECLARE @f_wk int
DECLARE @dateKey INT
DECLARE @fWeekBeginKey INT
DECLARE @fWeekEndKey INT
DECLARE @fMonthBeginKey INT
DECLARE @fMonthEndKey INT
DECLARE @fQtrBeginKey INT
DECLARE @fQtrEndKey INT
DECLARE @fYearBeginKey INT
DECLARE @fYearEndKey INT
SELECT @dateKey = id, @f_yr = fiscal_year, @f_qtr = fiscal_quarter, @f_mon = fiscal_month, @f_wk = fiscal_week FROM Utility.Calendar WHERE [date] = @RptDate
PRINT 'fYR = ' + CAST(@f_yr AS VARCHAR(4))
PRINT 'fQTR = ' + CAST(@f_qtr AS VARCHAR(4))
PRINT 'fMON = ' + CAST(@f_mon AS VARCHAR(4))
PRINT 'fWK = ' + CAST(@f_wk AS VARCHAR(4))
PRINT 'fDAY = ' + CAST(@dateKey AS VARCHAR(4))
SELECT @fWeekBeginKey = MIN(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr AND fiscal_month = @f_mon AND fiscal_week = @f_wk
SELECT @fWeekEndKey = @dateKey
SELECT @fMonthBeginKey = MIN(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr AND fiscal_month = @f_mon
SELECT @fMonthEndKey = MAX(id) FROM [Utility].[Calendar] WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr AND fiscal_month = @f_mon
SELECT @fQtrBeginKey = MIN(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr
SELECT @fQtrEndKey = MAX(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr AND fiscal_quarter = @f_qtr
SELECT @fYearBeginKey = MIN(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr
SELECT @fYearEndKey = MAX(id) FROM Utility.Calendar WHERE fiscal_year = @f_yr
PRINT 'fYR = ' + CAST(@fYearBeginKey AS VARCHAR(4)) + ' - ' + CAST(@fYearEndKey AS VARCHAR(4))
PRINT 'fQTR = ' + CAST(@fQtrBeginKey AS VARCHAR(4)) + ' - ' + CAST(@fQtrEndKey AS VARCHAR(4))
PRINT 'fMON = ' + CAST(@fMonthBeginKey AS VARCHAR(4)) + ' - ' + CAST(@fMonthEndKey AS VARCHAR(4))
PRINT 'fWK = ' + CAST(@fWeekBeginKey AS VARCHAR(4)) + ' - ' + CAST(@fWeekEndKey AS VARCHAR(4))
DECLARE @calDatedatetime
,@fiscalYearint;
-- Setup the calendar date and fiscal year variables
SELECT @calDate = cal.date
,@fiscalYear = cal.fiscal_year
FROM Utility.Calendar AS cal
WHERE cal.id = @dateKey;
/* NumOrders */
SELECT @calDate AS CalendarDate
,@fiscalYear AS FiscalYear
,'NumOrders' AS CounterType
,SUM(CASE WHEN date_purchased_key = @dateKey THEN 1 ELSE 0 END) AS TotalDay
,SUM(CASE WHEN date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey THEN 1 ELSE 0 END) AS TotalWeek
,SUM(CASE WHEN date_purchased_key >= @fMonthBeginKey AND date_purchased_key <= @fMonthEndKey THEN 1 ELSE 0 END) AS TotalMonth
,SUM(CASE WHEN date_purchased_key >= @fQtrBeginKey AND date_purchased_key <= @fQtrEndKey THEN 1 ELSE 0 END) AS TotalQuarter
,SUM(CASE WHEN date_purchased_key >= @fYearBeginKey AND date_purchased_key <= @fYearEndKey THEN 1 ELSE 0 END) AS TotalYear
FROM dbo.FactOrder AS fo
WHERE date_purchased_key >= @fYearBeginKey
AND date_purchased_key <= @fYearEndKey
UNION ALL/* OrdersGross */
SELECT @calDate AS CalendarDate
,@fiscalYear AS FiscalYear
,'OrdersGross' AS CounterType
,SUM(CASE WHEN date_purchased_key = @dateKey THEN item_qty * item_price ELSE 0 END) AS TotalDay
,SUM(CASE WHEN date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey THEN item_qty * item_price ELSE 0 END) AS TotalWeek
,SUM(CASE WHEN date_purchased_key >= @fMonthBeginKey AND date_purchased_key <= @fMonthEndKey THEN item_qty * item_price ELSE 0 END) AS TotalMonth
,SUM(CASE WHEN date_purchased_key >= @fQtrBeginKey AND date_purchased_key <= @fQtrEndKey THEN item_qty * item_price ELSE 0 END) AS TotalQuarter
,SUM(CASE WHEN date_purchased_key >= @fYearBeginKey AND date_purchased_key <= @fYearEndKey THEN 1item_qty * item_price ELSE 0 END) AS TotalYear
FROM dbo.FactOrder
WHERE date_purchased_key >= @fYearBeginKey
AND date_purchased_key <= @fYearEndKey
UNION ALL/* ItemsSold */
SELECT @calDate AS CalendarDate
,@fiscalYear AS FiscalYear
,'OrdersGross' AS CounterType
,SUM(CASE WHEN date_purchased_key = @dateKey THEN item_qty ELSE 0 END) AS TotalDay
,SUM(CASE WHEN date_purchased_key >= @fWeekBeginKey AND date_purchased_key <= @fWeekEndKey THEN item_qty ELSE 0 END) AS TotalWeek
,SUM(CASE WHEN date_purchased_key >= @fMonthBeginKey AND date_purchased_key <= @fMonthEndKey THEN item_qty ELSE 0 END) AS TotalMonth
,SUM(CASE WHEN date_purchased_key >= @fQtrBeginKey AND date_purchased_key <= @fQtrEndKey THEN item_qty ELSE 0 END) AS TotalQuarter
,SUM(CASE WHEN date_purchased_key >= @fYearBeginKey AND date_purchased_key <= @fYearEndKey THEN item_qty ELSE 0 END) AS TotalYear
FROM dbo.FactOrder
WHERE date_purchased_key >= @fYearBeginKey
AND date_purchased_key <= @fYearEndKey
/*
Other counter types - I REMOVED SEVERAL HERE
*/
UNION ALL/* TotalVisits */
SELECT @calDate AS CalendarDate
,@fiscalYear AS FiscalYear
,'TotalVisits' AS CounterType
,SUM(CASE WHEN date_key = @dateKey THEN total_visits ELSE 0 END) AS TotalDay
,SUM(CASE WHEN date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey THEN total_visits ELSE 0 END) AS TotalWeek
,SUM(CASE WHEN date_key >= @fMonthBeginKey AND date_key <= @fMonthEndKey THEN total_visits ELSE 0 END) AS TotalMonth
,SUM(CASE WHEN date_key >= @fQtrBeginKey AND date_key <= @fQtrEndKey THEN total_visits ELSE 0 END) AS TotalQuarter
,SUM(CASE WHEN date_key >= @fYearBeginKey AND date_key <= @fYearEndKey THEN total_visits ELSE 0 END) AS TotalYear
FROM dbo.FactVisitorMetrics
WHERE date_purchased_key >= @fYearBeginKey
AND date_purchased_key <= @fYearEndKey
UNION ALL/* UniqueVisitors */
SELECT @calDate AS CalendarDate
,@fiscalYear AS FiscalYear
,'UniqueVisitors' AS CounterType
,SUM(CASE WHEN date_key = @dateKey THEN unique_visitors ELSE 0 END) AS TotalDay
,SUM(CASE WHEN date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey THEN unique_visitors ELSE 0 END) AS TotalWeek
,SUM(CASE WHEN date_key >= @fMonthBeginKey AND date_key <= @fMonthEndKey THEN unique_visitors ELSE 0 END) AS TotalMonth
,SUM(CASE WHEN date_key >= @fQtrBeginKey AND date_key <= @fQtrEndKey THEN unique_visitors ELSE 0 END) AS TotalQuarter
,SUM(CASE WHEN date_key >= @fYearBeginKey AND date_key <= @fYearEndKey THEN unique_visitors ELSE 0 END) AS TotalYear
FROM dbo.FactVisitorMetrics
WHERE date_purchased_key >= @fYearBeginKey
AND date_purchased_key <= @fYearEndKey
UNION ALL/* Hits */
SELECT @calDate AS CalendarDate
,@fiscalYear AS FiscalYear
,'Hits' AS CounterType
,SUM(CASE WHEN date_key = @dateKey THEN hits ELSE 0 END) AS TotalDay
,SUM(CASE WHEN date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey THEN hits ELSE 0 END) AS TotalWeek
,SUM(CASE WHEN date_key >= @fMonthBeginKey AND date_key <= @fMonthEndKey THEN hits ELSE 0 END) AS TotalMonth
,SUM(CASE WHEN date_key >= @fQtrBeginKey AND date_key <= @fQtrEndKey THEN hits ELSE 0 END) AS TotalQuarter
,SUM(CASE WHEN date_key >= @fYearBeginKey AND date_key <= @fYearEndKey THEN hits ELSE 0 END) AS TotalYear
FROM dbo.FactVisitorMetrics
WHERE date_purchased_key >= @fYearBeginKey
AND date_purchased_key <= @fYearEndKey
UNION ALL/* NewMembers */
SELECT @calDate AS CalendarDate
,@fiscalYear AS FiscalYear
,'NewMembers' AS CounterType
,SUM(CASE WHEN date_key = @dateKey THEN new_members ELSE 0 END) AS TotalDay
,SUM(CASE WHEN date_key >= @fWeekBeginKey AND date_key <= @fWeekEndKey THEN new_members ELSE 0 END) AS TotalWeek
,SUM(CASE WHEN date_key >= @fMonthBeginKey AND date_key <= @fMonthEndKey THEN new_members ELSE 0 END) AS TotalMonth
,SUM(CASE WHEN date_key >= @fQtrBeginKey AND date_key <= @fQtrEndKey THEN new_members ELSE 0 END) AS TotalQuarter
,SUM(CASE WHEN date_key >= @fYearBeginKey AND date_key <= @fYearEndKey THEN new_members ELSE 0 END) AS TotalYear
FROM dbo.FactVisitorMetrics
WHERE date_purchased_key >= @fYearBeginKey
AND date_purchased_key <= @fYearEndKey
/*
More counter types added here - again, I removed a lot of them
*/
This will not perform any better than what you now have, but will probably be a lot easier to generate the report.
Well - it is an idea and something to consider...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi all,
Thanks for the response, and your willingness to work on this thing with me. I greatly appreciate it!
Jeffrey, this is the sort of thing I was thinking I might have been missing, and I agree with you on the maintenance and extensibility. Let me have a look at your code and make sure I understand it and can implement.
Much obliged,
Chris
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply