but instead putting 31, I'd like to pull the totaldays from the actual_date using the Extract function so I try this
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(to_date('05/01/13','dd/mm/rr'))) as AVGPERDAY,
dd.month_number
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.month_number = 1
GROUP BY dd.month_number;
The result is nice, but now when I change the date with the dd.actual_date it gives error
SELECT sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) as AVGPERDAY,
dd.month_number
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.month_number = 1
GROUP BY dd.month_number;
Error at Command Line : 1 Column : 53
Error report -
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
I have a daily report which each morning generates monthly information for the current month which was implemented in December. Everything was working correctly untill January 1st. On the 1st the report generated blank since it was suppose to generate 1-31 Dec but but the currently month was Jan, so it failed. How do I program it so if it is the 1st of a month generates the previous month but still would generate current month but while in the current month? Any help is appreciated.
SELECT GETDATE() - 1 AS rptdate, Errors.WTG_ID, lookup.Phase, Errors.STATUS_TYPE, Errors.STATUS_CODE, STATUS_CODES.STATUS_DEF, Errors.TIME_STAMP, Errors.ANSI_TIME, lookup.WTG_TYPE, Errors.POSITION FROM Errors INNER JOIN lookup ON Errors.WTG_ID = lookup.WTG_id RIGHT OUTER JOIN STATUS_CODES ON Errors.STATUS_CODE = STATUS_CODES.STATUS_CODE AND lookup.WTG_TYPE = STATUS_CODES.WTG_TYPE WHERE (STATUS_CODES.STATUS_DEF IS NOT NULL) AND (Errors.TIME_STAMP BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) ORDER BY Errors.WTG_ID, Errors.TIME_STAMP, position
I want to select data from a cube using an MDX statement and show the data on a graph report.
I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.
Then set the 'Date' Column to the x-axis and the Value column to the y-axis.
The user also must have the option to not show certain periods (Switch of daily and weekly)
My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.
---- Code
SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,
NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,
[Measures].[ValueAfterLogic],DESC)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS
FROM [Workplace])
WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])
Currently, I have a report that takes two parameters: Â StartDate and EndDate. Â
I would like to schedule the report to run on a Daily, Weekly, Monthly or Yearly basis, but this doesn't work too well with StartDate and EndDate because the parameter is static. Â What is the most elegant way to implement this change?
How to create a stored procedure, or many stored procedures for generating a monthly sales report in our company.
We have two tables:
ITEM_SALES which consists of: Item_ID Name Store Sales_Date Sales_Price Quantity
And then
ITEM_DISCOUNT which consists of: Item_ID Name Store Sales_Price Date_From Date_To
Explanation: After each month, our different stores will send us a report on which discounts they had. These discounts can vary from one, to many days and therefor we have the Date_From, Date_to.
To make this sales report, i need a procedure that first fetches all sales in ITEM_SALES, then checks all the discounts in ITEM_DISCOUNT and overwrites the ones that have similar Item_ID, Name, and Store for a selected period.
Example: So if a item originally had a sales_price on 99,- and then had a discount sales_price to 79,- for 2014-01-02 to 2014-01-10 it has to be overwritten for that period so the report shows the right numbers.
I am making a stored procedure for monthly sales. In the stored procedure we have a Discount. This discount can be fetched from three different tables. If the discount is not in id.rabatt, it should fetch from dp.rabatt, if its not there, it should fetch from ds.rabatt. So the first two ones can be empty, while the last one always has a discount..
Im having big trouble designing the WHEN part of the procedure.
CASE ( when Isnull(id.rabatt, Isnull(u.rabatt, id.rabatt)) then.. when Isnull(dp.rabatt, Isnull(x.rabatt, id.rabatt)) then.. when Isnull(ds.rabatt, Isnull(y.rabatt, id.rabatt)) then.. end) AS 'Discount',
The reason i have to use Isnull is that inside each Discount table, i also have two different discounts, one that lasts forever(2999) and one that have a selected period. Like i show here:
LEFT OUTER JOIN discount AS id ON id.identifiers = isa.identifiers AND id.store = BV.name AND id.from_date <= isa.sales_date AND id.to_date >= isa.sales_date AND id.to_date < '2999-01-01' LEFT OUTER JOIN discount AS u ON u.identifiers = isa.identifiers AND u.to_date = '2999-01-01'
The two others tables are designed in similar ways
Now, I want to obtain monthly sales from the data in the two tables by passing in a Year parameter. How to develop such a stored procedure? I have no idea where to get started. I was thinking to call a SELECT statement on each month. But, things trouble me are:
1. how to loop through each month to make a SELECT query for each month for a given Year? Use a cursor or what? 2. how to determine month boundary for a given year and construct the where clause on OrderDate using the month boundary for the SELECT query ? What happens if it is a leap year? 3. how to stop processing for the rest of the year when last order month is done?
I'm creating 15 reports based on one data source. Once I created, I'm supposed to run this on daily and monthly basis. I'm going to use snapshot option in Report Manager. Is it fine? Do I need to create a data base too?
Also when I change the target URL address in the report property box, can I deploy it to any other server? Do I have to change the settings of report configuration manager too, inorder to publish reports on another server(not in my local machine)
I'm trying to develop a query that provides sales data by Customer.GroupCode in monthly columns as depicted below:
GrpCd JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOT Film 5,000 15,000 20,000 Aero Elct 3,000 950 3,950 Desg Edu 150 150
Here€™s a simplified version of the DDL: CREATE TABLE invchead ( invoicenum int NULL , invoicedate datetime NULL , invoiceamt decimal(16, 2) NULL , custnum int NULL )
The query below gets me close but it gives me gives me one row for each customer. So if I have 5 customers with the same group code, I get 5 rows for that group code. I need to modify it or come up with a different approach that gives me only one row for each GroupCode.
SELECT distinct c.Name, c.GroupCode, (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=1) JAN, (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=2) FEB, ...... (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate)) TOT
FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum) WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008'
Grateful for any advice that will get me closer to accomplishing this.
We have 9 shop installed POS system and how to get the 10 shop of daily sales transaction by use of different shop IP Address? I don't want to repeat typing 9 time for change IP address as well.
select * from xsoheader inner join xsodetail.memonum = xsoheader.memonum where xshopcode='%00*'
Hi all, I'm new to MDX and am getting very confused with a script.
I'm running into problems breaking down monthly measures to daily values. If I have a monthly measure of 50, I would like to divide it by the number of days in the month to come up with daily values.
I believe I have set the proper granularity for the measurement relationship against the time dimension and have added the following script to my MDX script:
SELECT [Measures].[Measurement Objective] ON 0, MTD([Date].[Calendar].[Date].&[20070207]) ON 1 FROM [Cube]
Everything looks good. The query returns seven rows, each with a properly scaled version of the monthly measurement. However, when I write the following query to return a single MTD value:
SELECT [Measures].[Measurement Objective] ON 0 FROM [Cube] WHERE MTD([Date].[Calendar].[Date].&[20070207])
It doesn't work. It gives me the error:
The MDX Function CURRENTMEMBER failed because the coordinate for the 'Calendar Year' attribute contains a set
I'm sure this is just a matter of me misunderstanding MDX. Any help would be appreciate. Thanks, Richard
I m Maran. I am trying to write a SQL Query to retrieve the following report format. But I'm not sure how to go about it.
Input values:
Starting Date: 09/14/2007 End Date: 12/06/2007
Monthly Report :
Start Date - End Date - Number of companies 09/14/2007 - 09/30/2007 1 10/01/2007 - 10/31/2007 0 11/01/2007 - 11/30/2007 4 12/01/2007 - 12/06/2007 0
Please its very urgent, Plz do the needful help. Actually this same report format i was posted already and got some methodology, but its not satisfied my requirements :( :(
I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.
I have created a database table in MSSQL 2000 like this
[empcode] [leave_date] [type] [reason] 100 2008-12-29 00:00:00.000 T Tour 100 2008-12-30 00:00:00.000 T Tour 101 2008-12-31 00:00:00.000 CL Casual Leave 102 2009-01-01 00:00:00.000 R Restricted holiday 100 2009-01-02 00:00:00.000 T Tour
This table contains only leave details.... but i need to create monthly attendance report such as below
empcode 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 ............. 100 P P P P S P T CL P P P S P P T ............ 101 P T R R S R R T CL P P S P P P.............. 102 P P P P S P P P P T T S CL P P P............
I want to display the reports in weekly format suppose
today is sept 27 2006, so i know from datepart(weekday,..) its value is 4 and end of this week is sept 30 2006 and again next week will start like that....also search should be monthly...
my report looks like Weekly Report (09/27 - 10/12) Week====09/27-09/30======10/01-10/07======10/08-10/12 Sales======50===============100===============80
I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months? In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months.
Hi Guys, I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen (or the stored procedure) so i need some help for SQL Gurus. Following are the details: 1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month) 2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day 3. There are around 500 employees data im dealing with 4. The user will select any given department and employee's data for the respective department has to be displayed for any given month 5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen 6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee 7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in the attendance table, we are dealing with approximately half a million attendance records. Thanks and waiting for your suggestions...
I am using SQL Server 2005 with Reporting Services (Using the Visual side - not direct code)
I am having problems understanding the dates. eg where to put them,
I want a report that runs on the 1st day of the month for the previous month. I know you can set up something in subscriptions but then how do I get my report header to say from .......to...............
I have been through the AW reports but can't see what I need.
Happy if someone wants to direct me to somewhere that has date examples.
I need to calculate the success rate of our OS Patch deployments. the data from system is stored in SQL with corresponding states (installed, missing, ...)
I would need to provide monthly report that shows how successful the deployment was for particular patchgroup. I have the following 2 dummy tables (just used as example)
table5 ==> Table containing patch groups + patches table6 ==> Table with machines names, patches and patch state
select * from table5 pgrouppatch GROUP1PATCH1 GROUP1PATCH2 GROUP1PATCH3 GROUP1PATCH4
[Code] ....
Result would be pgroup install missing group1 80% 20% group2 50% 50% group3 55% 45%
Ideally I would like to do this in T-SQL but if necessary can also do this in .NET Function. Only mention this but both Patchgroup and machines are dynamic each month can be different.
below are the sql scripts to create tables and populate with data
CREATE TABLE [dbo].[table5]( [pgroup] [varchar](128) NOT NULL, [patch] [nvarchar](128) NOT NULL ) CREATE TABLE [dbo].[table6]( [machinename] [varchar](128) NOT NULL,
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123 Children:Â Sale ID # 456, Sale ID #789, Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
Trying to generate a daily KPI report from our SQL server. I do not have access to write any functions. In the "start date" and "end date" section what numerical value would I enter to give me the equivalent of CURRENT_DATE ()?
I have been trying to figure this out for a while. I have a report that should run at 9AM, 12 Noon and 2PM every weekday. Due to the complexity of the report, I'm using snapshots. Since the lowest gap is 2 hours, I currently have it configured to create the snapshot every two hours. Since this is running all day, my servers are taking a lot of strain. In the report manager help, it suggests creating three daily schedules for the report. I cannot find a way to create more than one schedule for a report. Please let me know how I can do this. Any help will be highly appreciated.
I am creating a sales report that needs to display Sales statistics for a selected month grouped by Material. Also, it needs to display the same stats for the selected month of the previous year. Finally, it needs to display Year To Date Statistics for the current year and previous year up to and including the selected month.
Currently, I am using 3 queries to do this. The first one gets the statistics the current month grouped by material. The others sprocs get their corresponding information by me passing in the material number and the month and doing the appropriate sum. So, essentially, for a single line of the report, 3 queries are being done. The problem arises in that sometimes there may be as many as 300 materils displayed in a given month, which amounts to alot of db activity and a long delay loading the report.
Is there anyway to get this information in one swoop using some fancy aggregation?
Trying to get a report to run daily between certain hours. I can set a start time and an "end date" in Report Manager, but as far as I can tell, I can't say run hourly every day from X to Y. am I missing something?
If this isn't doable from RS, can I just find the associated job in the Agent and change the schedule of that job?
Hi, Is there a way to write a stored procedure to get weekly report for 5 weeks?I currently use a stored procedure with 5 select statement to get the result for each week, but I was wondering it there is a way to do that with only one statementthanks
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
The results were, respectively: 701,68; 2120,60...; -669,441; and  finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17 test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.