September 11, 2009 at 10:30 am
SELECT
SCD.SALESREP_NAME AccountOwner,
CA.ACCOUNT_NUMBER AccountID,
CA.CUSTOMER_ACCOUNT_NAME AccountDescription,
ORD.NET_AMOUNT,
ORD.NET_UNITS,
ORD.ORDER_DATE,
TD.STANDARD_DATE_TEXT,
--Current Year MTD Sales:Sum of ORD.NET_AMOUNT month-to-date //This i wrote and cat i am usimg because standard date text is vachar.//
"Current Year MTD Sales"=
SUM(CASE
WHEN
TD.STANDARD_DATE_TEXT >=
cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)), 0) AS varchar(25))
AND
TD.STANDARD_DATE_TEXT
< cast( DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) + 1, 0)AS VARCHAR(25))
THENORD.NET_AMOUNT
ELSE0
END)
--Last Year MTD Sales:Sum of ORD.NET_AMOUNT for last year’s month
--Variance $ MTD:Current MTD Sales - Last Year MTD Sales
--Variance % MTD:(Current MTD Sales - Last Year MTD Sales) / Last Year MTD Sales
--Current Year YTD Sales:Sum of ORD.NET_AMOUNT from the first to the current date
--Last Year YTD Sales:Sum of ORD.NET_AMOUNT from the first to the current date (last year)
--Variance $ YTD:Current YTD Sales - Last Year YTD Sales
--Variance % YTD:(Current YTD Sales - Last Year YTD Sales) / Last Year YTD Sales
FROM
CUSTOMER_ACCOUNT_DIMENSION CA
INNER JOIN
ORDER_FACT ORD ON CA.CUSTOMER_ACCOUNT_ID = ORD.CUSTOMER_ACCOUNT_ID
INNER JOIN
SALESREP_CSR_DIMENSION SCD ON ORD.SALESREP_CSR_ID = SCD.SALESREP_CSR_ID
INNER JOIN
TIME_DIMENSION TD ON ORD.ORDER_DATE_ID = TD.TIME_DIMENSION_ID
Thanks Help appreciated.
September 11, 2009 at 10:33 am
It would help if you could also post the DDL for the table(s), sample data, expected results based on the sample data.
If you need help with this, please read and following the instructions in the first artiled referenced below in my signature block.
September 11, 2009 at 5:00 pm
Since you failed to provide DDL, sample data, and expected results, I had to guess at what you wanted. I also had to use the AdventureWorks database.
The following code should give you an idea how to get all of the columns that I think that you need. I don't want to put any further work into this until you have provided the requested info.
I also had to hard-code "Today", because the AdventureWorks DB is several years out of date by now.
DECLARE @Today AS Datetime
SET @Today = '2004-07-31' -- The Max Order Date in AdventureWorks
;
WITH CustSumm AS (
SELECT CustomerID
, Year(OrderDate) AS OrderYear
, Sum(
CASE
WHEN Month(OrderDate) = Month(@Today)
THEN TotalDue
ELSE 0
END
) AS PMTD
, Sum(
CASE
WHEN DatePart(y, OrderDate) = DateAdd(Year, DateDiff(Year, 0, @Today) - 1, 0 )
GROUP BY CustomerID, Year(OrderDate)
)
SELECT *
FROM CustSumm AS a
LEFT OUTER JOIN CustSumm AS b
ON a.CustomerID = b.CustomerID
AND a.OrderYear = b.OrderYear + 1
WHERE a.OrderYear = Year(@Today)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 14, 2009 at 9:48 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CUSTOMER_ACCOUNT_DIMENSION](
[CUSTOMER_ACCOUNT_ID] [int] NOT NULL,
[REF_CUST_ACCOUNT_ID] [float] NULL,
[REF_PARTY_ID] [float] NULL,
[CUSTOMER_ACCOUNT_NAME] [varchar](100) NULL,
[ACCOUNT_NUMBER] [varchar](30) NULL,
[PARTY_NUMBER] [varchar](30) NULL,
[SALES_CODE] [varchar](30) NULL,
[SALES_CODE_DESCR] [varchar](80) NULL,
[INDEPENDENT_CUSTOMER_IND] [varchar](1) NULL,
[CUST_ACCOUNT_STATUS_CODE] [varchar](1) NULL,
[CUST_ACCOUNT_STATUS_DESCR] [varchar](80) NULL,
[OBJECT_VERSION_NUMBER] [float] NULL,
[ORIG_SYSTEM_REFERENCE_CUST] [varchar](100) NULL,
[ORIG_SYSTEM_REFERENCE_PAR] [varchar](100) NULL,
[CUST_ACCOUNT_CATEGORY] [varchar](80) NULL,
[SALES_ACTIVITY_CURRENT_IND] [varchar](1) NULL,
[SALES_ACTIVITY_PRIOR_IND] [varchar](1) NULL,
[LIFE_TO_DATE_NET_SALES] [varchar](25) NULL,
[EMPLOYEE_ID_REQUIRED] [varchar](10) NULL,
[ENROLLER_FLAG] [varchar](10) NULL,
[IIM_ORG_ID] [float] NULL,
[PO_REQUIRED] [varchar](10) NULL,
[COST_CENTER_REQUIRED] [varchar](10) NULL,
[COST_CENTER_VALUE] [varchar](25) NULL,
[ADDRESS_LINE1] [varchar](240) NULL,
[ADDRESS_LINE2] [varchar](240) NULL,
[ADDRESS_LINE3] [varchar](240) NULL,
[CITY] [varchar](60) NULL,
[STATE_ABBREV] [varchar](60) NULL,
[COUNTRY] [varchar](60) NULL,
[POSTAL_CODE] [varchar](60) NULL,
[SALES_TAX_GEOCODE] [varchar](30) NULL,
[PROVINCE] [varchar](60) NULL,
[COUNTY] [varchar](60) NULL,
[LAST_UPDATE_DATE_SS] [datetime] NULL,
[LAST_UPDATE_DATE_DW] [datetime] NULL,
[COLLECTOR_CATEGORY] [varchar](30) NULL,
[COLLECTOR_NAME] [varchar](30) NULL,
[REF_OES_ACCOUNT_OWNER_ID] [varchar](150) NULL,
CONSTRAINT [PK_CUSTOMER_ACCOUNT_DIMENSION] PRIMARY KEY CLUSTERED
(
[CUSTOMER_ACCOUNT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
*************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ORDER_FACT](
[ORDER_FACT_ID] [int] NOT NULL,
[REF_OES_HEADER_ID] [float] NULL,
[REF_OES_LINE_ID] [float] NULL,
[REF_ORDER_FACT_ID] [int] NULL,
[OES_CURSOR_SOURCE] [varchar](20) NULL,
[ORG_ID] [float] NULL,
[ORDER_NUMBER] [float] NULL,
[ORDER_LINE_NUMBER] [float] NULL,
[ORDER_TYPE_ID] [int] NULL,
[ENROLLMENT_IND] [varchar](1) NULL,
[ENROLLMENT_TYPE] [varchar](10) NULL,
[LINE_TYPE_ID] [int] NULL,
[ORDERED_DATE] [datetime] NULL,
[HEADER_CREATED_BY] [float] NULL,
[HEADER_CREATION_DATE] [datetime] NULL,
[LINE_CREATED_BY] [float] NULL,
[LINE_CREATION_DATE] [datetime] NULL,
[BOOKED_DATE] [datetime] NULL,
[HEADER_CANCELLED_IND] [varchar](1) NULL,
[LINE_CANCELLED_IND] [varchar](1) NULL,
[ORDER_FLOW_STATUS] [varchar](30) NULL,
[CUSTOMER_PO_NUMBER] [varchar](50) NULL,
[LINE_CATEGORY_CODE] [varchar](30) NULL,
[SALES_UNITS] [int] NULL,
[RETURN_UNITS] [int] NULL,
[NET_UNITS] [int] NULL,
[SALES_AMOUNT] [money] NULL,
[RETURN_AMOUNT] [money] NULL,
[REBATE_AMOUNT] [money] NULL,
[NET_AMOUNT] [money] NULL,
[SHIPPING_METHOD_ID] [int] NULL,
[PRICE_LIST_ID] [int] NULL,
[PAYMENT_TERMS_ID] [int] NULL,
[ORDER_SOURCE_ID] [int] NULL,
[CUSTOMER_ACCOUNT_ID] [int] NULL,
[CUSTOMER_SITE_USE_ID_BT] [int] NULL,
[CUSTOMER_SITE_USE_ID_ST] [int] NULL,
[STUDENT_DIMENSION_OES_ID] [int] NULL,
[ORDER_DATE] [datetime] NULL,
[ORDER_DATE_ID] [int] NULL,
[SALESREP_CSR_ID] [int] NULL,
[PRODUCT_ID] [int] NULL,
[STUDENT_RELATIONSHIP_ID] [int] NULL,
[ENROLLER_ID] [int] NULL,
[COST_CENTER_ID] [int] NULL,
[GL_CHART_ID] [int] NULL,
[TRACKING_NUMBER] [varchar](30) NULL,
[LAST_UPDATE_SS] [datetime] NULL,
[LAST_UPDATE_DATE_DW] [datetime] NULL,
[TAX_AMOUNT] [money] NULL,
[INVOICE_ID] [int] NULL,
[HEADER_ORIG_SYS_DOCUMENT_REF] [varchar](50) NULL,
[FREIGHT_AMOUNT] [money] NULL,
[REFUND_TYPE_ID] [int] NULL,
[PRODUCT_PRICING_ID] [int] NULL,
[UNIT_SELLING_PRICE] [float] NULL,
[INVOICE_TRX_TYPE_ID] [int] NULL,
[ENROLLMENT_STATUS_ID] [int] NULL,
[CLASS_DIMENSION_OES_ID] [int] NULL,
[SESSION_ID] [int] NULL,
[REF_IIM_LIST_PRICE] [float] NULL,
[REF_IIM_ITEM_PRICE] [float] NULL,
[REF_IIM_PROMOTION_DISCOUNT] [float] NULL,
[REF_IIM_LOYALTY_DISCOUNT] [float] NULL,
[REF_IIM_ORDER_ID] [float] NULL,
[REF_IIM_TOTAL_LINE_ITEM_PRICE] [float] NULL,
[REF_IIM_QUANTITY] [float] NULL,
[ENROLLER_NAME] [varchar](240) NULL,
[ENROLLER_PARTY_ID] [varchar](240) NULL,
[ORG_CONTACT_ID] [int] NULL,
[RETURN_REASON_CODE] [varchar](30) NULL,
[INVENTORY_ITEM_ID] [int] NULL,
[ORDER_TYPE] [varchar](30) NULL,
[ACCOUNT_OWNER_ID] [int] NULL,
CONSTRAINT [PK_ORDER_FACT] PRIMARY KEY CLUSTERED
(
[ORDER_FACT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
************************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TIME_DIMENSION](
[TIME_DIMENSION_ID] [int] NOT NULL,
[STANDARD_DATE] [datetime] NOT NULL,
[JULIAN_DATE] [int] NULL,
[ACCOUNTING_PERIOD] [varchar](6) NULL,
[DAY_TEXT] [varchar](10) NULL,
[DAY_NUMBER_OF_WEEK] [int] NULL,
[MONTH_TEXT] [varchar](10) NULL,
[MONTH_NUMBER_OF_YEAR] [int] NULL,
[WEEK_NUMBER_OF_YEAR] [int] NULL,
[YEAR_NUMBER] [int] NULL,
[CALENDAR_DOM_NUMBER] [int] NULL,
[LAST_CALENDAR_DOM_IND] [varchar](1) NULL,
[TOTAL_CALENDAR_DAYS_IN_MONTH] [int] NULL,
[CALENDAR_DOY_NUMBER] [int] NULL,
[CALENDAR_QUARTER] [varchar](2) NULL,
[COMPANY_HOLIDAY_IND] [varchar](1) NULL,
[WEEKDAY_IND] [varchar](1) NULL,
[BUSINESS_DAY_IND] [varchar](1) NULL,
[BUSINESS_DAY_COUNTER] [int] NULL,
[BUSINESS_DAY_OF_MONTH] [int] NULL,
[LAST_BUSINESS_DOM_IND] [varchar](1) NULL,
[TOTAL_BUSN_DAYS_IN_MONTH] [int] NULL,
[BUSINESS_DAY_OF_YEAR] [int] NULL,
[SELLING_SEASON_TRADE_PUB] [varchar](20) NULL,
[SELLING_SEASON_RE_PUB] [varchar](20) NULL,
[SELLING_SEASON_KPS] [varchar](20) NULL,
[SELLING_SEASON_DFS] [varchar](20) NULL,
[STANDARD_DATE_TEXT] [varchar](10) NULL,
CONSTRAINT [PK_TIME_DIMENSION] PRIMARY KEY CLUSTERED
(
[TIME_DIMENSION_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
*************************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SALESREP_CSR_DIMENSION](
[SALESREP_CSR_ID] [int] NOT NULL,
[GROUP_NAME] [varchar](60) NULL,
[REF_OES_SALESREP_ID] [int] NULL,
[SALESREP_NAME] [varchar](100) NULL,
[STATUS] [varchar](30) NULL,
[START_DATE_ACTIVE] [datetime] NULL,
[END_DATE_ACTIVE] [datetime] NULL,
[SALESREP_NUMBER] [varchar](30) NULL,
[REF_OES_PERSON_ID] [int] NULL,
CONSTRAINT [PK_SALESREP_CSR_DIMENSION] PRIMARY KEY CLUSTERED
(
[SALESREP_CSR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
September 14, 2009 at 10:41 am
Now all that is missing is the sample data and expected results.
September 14, 2009 at 11:02 am
Sorry sir i cannot give data. attached is the ouput document.
And the query which i wrote is :
SELECT
SCD.SALESREP_NAME AccountOwner
,CA.ACCOUNT_NUMBER AccountID
,CA.CUSTOMER_ACCOUNT_NAME AccountDescription
,ORD.NET_AMOUNT
,"Current Year MTD Sales" = SUM (case when TD.MONTH_NUMBER_OF_YEAR = month(getdate()) and TD.YEAR_NUMBER = year(getdate()) THEN ORD.NET_AMOUNT ELSE 0.00 END)
,"Last Year MTD Sales" = SUM (case when TD.MONTH_NUMBER_OF_YEAR = month(getdate()) and TD.YEAR_NUMBER = year(getdate()) - 1 THEN ORD.NET_AMOUNT ELSE 0.00 END)
,"Current Year YTD Sales" = SUM (case when TD.MONTH_NUMBER_OF_YEAR <= month(getdate()) and TD.YEAR_NUMBER = year(getdate()) THEN ORD.NET_AMOUNT ELSE 0.00 END)
,"Last Year YTD Sales" = SUM (case when TD.MONTH_NUMBER_OF_YEAR = YEAR(GETDATE())-1
AND TD.MONTH_NUMBER_OF_YEAR <= MONTH(GETDATE())
AND CA.ACCOUNT_NUMBER =332039
group by
SCD.SALESREP_NAME
,CA.ACCOUNT_NUMBER
,CA.CUSTOMER_ACCOUNT_NAME
,ORD.NET_AMOUNT
September 14, 2009 at 11:12 am
Not asking for actual data. You should be able to create a data set that is representative of your actual data that also reflects the problem you are trying to solve. From that data set you would then show us what the expected results should be.
Sorry, but as a volunteer I simply don't have the time to create sample data for you and hope I have created something the correctly meets your requirements.
September 14, 2009 at 11:23 am
Attached is the sample data.
September 14, 2009 at 11:30 am
srathna77 (9/14/2009)
Sorry sir i cannot give data. attached is the ouput document.
As Lynn mentioned, we only want *sample* data. So if you have a Name (Like the one in the screenshots of your example output, not sure if that information is test information or real info, you may want to check into that/remove it) like 'Sally Noble', you can change it to 'Jane Doe', a social security number such as '384-38-9639'(<-- example only, not real) becomes '123-12-1234', so on and so forth.
You may decide that it's not worth your time to create all this sample data. Unfortunately, we usually come to the same conclusion in regards to our own time, which leaves you without an answer.
[Edit] The sample data should be in a format that allows us to one click insert it into your tables. With that sample data, we'd have to go in and manually insert all the quotes, union all statements, selects, etc. etc. The article in Lynn's signature (in mine as well) gives examples of how we need it.
September 14, 2009 at 11:37 am
Well, you did provide us with data, but unfortunately it isn't in a readily consummable format and I really don't have time to put it into a usable format at the moment. I may have time tonight, but not making any promises.
You may want to read the first article I have referenced below in my signature block regarding asking for assistance. The instructions in that article show you how best to post data for best results.
September 14, 2009 at 12:41 pm
Attached sample data in excel files. Please see sample.rar file for excel files
Thank you.
September 14, 2009 at 2:11 pm
srathna77 (9/14/2009)
Attached sample data in excel files. Please see sample.rar file for excel filesThank you.
Nope, still requires more work on our part. Have you read the article you were asked to read? The sample data really needs to be provided in a readily consummable fashion, like this:
insert into dbo.mytable (columnlist)
select data element 1, data element 2,... union all
select ...
When provided like that, all we have to do is cut/paste/execute in SSMS.
September 14, 2009 at 3:09 pm
Attached sample data in given format.
September 14, 2009 at 3:34 pm
srathna77 (9/14/2009)
Attached sample data in given format.
I have one last question, is one row of data in each table really representative of the problem we are trying to help you solve?
September 15, 2009 at 9:01 am
Somehow i did the query can you please have a look at that .
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT
SR.SALESREP_NAME AS "Account Owner"
,CA.ACCOUNT_NUMBER AS "Account ID"
,CA.CUSTOMER_ACCOUNT_NAME AS "Account Description"
,ORDP.[Current Year MTD Sales]
,ORDP.[Last Year MTD Sales]
,"Variance $ MTD" = ORDP.[Current Year MTD Sales] - ORDP.[Last Year MTD Sales]
,"Variance % MTD" = (ORDP.[Current Year MTD Sales] - ORDP.[Last Year MTD Sales]) / ORDP.[Last Year MTD Sales]
,ORDP.[Current Year YTD Sales]
,ORDP.[Last Year YTD Sales]
,"Variance $ YTD" = ORDP.[Current Year YTD Sales] - ORDP.[Last Year YTD Sales]
,"Variance % YTD" =(ORDP.[Current Year YTD Sales] - ORDP.[Last Year YTD Sales]) / ORDP.[Last Year YTD Sales]
FROM
SALESREP_CSR_DIMENSION SR
INNER JOIN ORDER_FACT ORD ON SR.SALESREP_CSR_ID = ORD.ACCOUNT_OWNER_ID
INNER JOIN TIME_DIMENSION TD ON ORD.ORDER_DATE_ID = TD.TIME_DIMENSION_ID
INNER JOIN CUSTOMER_ACCOUNT_DIMENSION CA ON ORD.CUSTOMER_ACCOUNT_ID = CA.CUSTOMER_ACCOUNT_ID
INNER JOIN (
SELECT CA.ACCOUNT_NUMBER,
"Current Year MTD Sales" = SUM (case when TD.MONTH_NUMBER_OF_YEAR = month(getdate()) and TD.YEAR_NUMBER = year(getdate()) THEN ORD.NET_AMOUNT ELSE 0.00 END)
,"Last Year MTD Sales" = SUM (case when TD.MONTH_NUMBER_OF_YEAR = month(getdate()) and TD.YEAR_NUMBER = year(getdate()) - 1 THEN ORD.NET_AMOUNT ELSE 0.00 END)
,"Current Year YTD Sales" = SUM (case when TD.MONTH_NUMBER_OF_YEAR <= month(getdate()) and TD.YEAR_NUMBER = year(getdate()) THEN ORD.NET_AMOUNT ELSE 0.00 END)
,"Last Year YTD Sales" = SUM (case when TD.MONTH_NUMBER_OF_YEAR = YEAR(GETDATE())-1
AND TD.MONTH_NUMBER_OF_YEAR <= MONTH(GETDATE())
group by
SCD.SALESREP_NAME
,CA.ACCOUNT_NUMBER
,CA.CUSTOMER_ACCOUNT_NAME
,ORD.NET_AMOUNT)
ORDP
ON CA.ACCOUNT_NUMBER = ORDP.ACCOUNT_NUMBER
GO
Thank you
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply