I have created a tabular report. Some of the columns I have are titled "AdjustedHistory1", "AdjustedHistory2" and so on. I also have columns titled "AdjustedForecast1", "AdjustedForecast2", and so on. I want a column to be able to retrieve the current date and thus display the information "July" (i.e. the current month). Next, I want "AdjustedHistory1" to be June, "Adjusted History2" to be May, and so on. Then I want "AdjustedForecast1" to be August, "AdjustedForecast2" to be September, and so on. The trick is, I need these fields to auto-update; meaning so that when the current month is August, "AdjustedHistory1" is July, "AdjustedHistory2" is June, "AdjustedForecast1" is September, and "AdjustedForecast2" is October.
I already read a lot about the inscope-function and how it is used to display variances over time periods. But I don't know where to start, as there is no tutorial how to setup this functionality. What I want to display within the report is the following:
Code Snippet
Months ProductGroup Article Year 1 2 3 4 5 .... Bicycles 1020 2007 1500 2000 etc. 2008 3000 3000 Var. abs. 1500 1000 Var. % 100% 50% 1025 2007 0 1000 2008 500 1200 Var. abs. 500 200 Var. % 500% 20% Motorcycles etc.
I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.
I am attaching a sample of what I would like to accomplish (months are columns).
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
I need to get all the data based on the user selection of the months. So, I will provide a dropdown will all list of the months. When user pick the month, for example January, then I will show all the invoice on January. How to get the month of the year in the where clause in T-SQL? TIA
I have a question i am confused very much with i am doing on MTD Average , i really need some help with code.
the logic behind the code is
3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.
This code does not actually gives me the exact value, please advise on any correction to the code , or a new way to write the code.
Hi, I am trying to determine the amount of months between today and adate stored in the database. But I cannot seem to figure out get thedifference between the two dates. Thanks in advance.
Hi All, I provided the client with the dropdownlist which lists all the months (january - december). This input will be used to filter in the invoiceDT. How can I write the query where clause to compare the invoiceDT (mm/dd/yyyy) into this input which is only number 1 through 12? TIA
I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths. But it is minusing days not months. @TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.
I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.
Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.
SELECT K.TRANS_TYPE,K.COMPANY,K.DIVISION,K.SALES_FORCE,K.SALES_LINE,K.SALES_REGION,K.SALES_ZONE,K.FORECAST_ DATE,C.STATISTICS_PERIOD,K.PRODUCT_NUMBER, K.CUSTOMER_CODE,K.WAREHOUSE,0,0,0,K.FORECAST_QTY FROM KPI_SIGNOFF AS K inner join ROUNDED_CALENDAR as C on K.FORECAST_DATE between C.START_DATE and C.END_DATE
Hi this is the code that I am using to populate a table let us call new_table. However what I would like to do is get the max forecast date and get the statistics_period for that max forecast date.
Once I get that I would set that as my current statistics period.
Then I want to create a new column in the same new_table call stat_period-1, which would be the one period back.
Please note that the statistics period is like the months 1 – 12. Does not go higher that 12 and does not go lower than 1.
So for stat_period-1 when statistics period is equal to 1 it would have to be set to 12.
This is what I want but do not know how to place this into the code, please help me with this. THanks
I am wondering if someone maybe able to help me, I am needing to order my data via months in the calendar sense not alphabetically, below is what I currently have, but it only does it alphabetically.
select to_char(created,'yyyy-Mon'), matdesc, count(*) from test group by to_char(created,'yyyy-Mon'), matdesc order by to_char(created,'yyyy-Mon') desc
I need to count last 3 months records. The query I'm trying to use was based on last 8 days. How can I use this query in months and not days? Or should I use a new query?
Code Snippet SELECT COUNT(Column1) AS [Nº de Cartões], Column2 AS [Tipo de Cartão], Column4 AS Periodicidade FROM main_client_file WHERE (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 3), 0)) AND (Column9 <= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 1), 0)) GROUP BY Column2, Column4
Hi, I've just noticed that there is a proc in my database using a person's age in a where clause but it seems to be rounding the age. The idea is select records where the age (using the person's birthdate) is between @xMonths and @yMonths.
If 4/30/06 is the birthdate and 4/1/08 is today, it needs to be 23 months. Can someone explain how it determines the month boundaries and how to exclude partial months?
I have a start date saved and an end date saved but when I query I only want to return the amount of months that have elapsed between the two, any idea?
I have a database with multiple items each day. I'm looking to extract monthly information from this data. So I'm thinking that I would like to have a drop down list with each available month in the list. How would I extract which months (for multiple years) have data from a datatype = smalldatetime? I'm using c# Thanks!
Hi,I have 2 tables created. One is the Sale Persons (PersonID, PersonName) and the other is the Sales Detail (PersonID, Month, Year, TotalSales)PersonID | PersonName PersonID | Month | Year | Total Sales================== ==================================ID1 | Sally ID1 | 1 | 2005 | 1000ID2 | David ID2 | 2 | 2005 | 1500Is it possible to write in a SQL statement to return all the sales person & the total sales for the 12 months for a particular year (even though some month data are not in the table)I would like the result to be like the following:PersonID | Month | Year | Total Sales===================================ID1 | 1 | 2005 | 1000ID1 | 2 | 2005 | 0ID1 | 3 | 2005 | 0(For month 4 - 12) Total Sales will be 0 too as no records exist in the first placeID2 | 1 | 2005 | 0ID2 | 2 | 2005 | 1500..........
I have a database of the table payments and I want to create another table with the olds payments (18 months past) a have a field with the payment date date_pay. How I can do that?
I have written the following query which returns the number of orders received grouped by the year and month: SELECT DATEPART(yyyy, order_placeddate) AS year, DATEPART(mm, order_placeddate) AS month, count(order_id) AS orders FROM orders GROUP BY DATEPART(yyyy, order_placeddate), DATEPART(mm, order_placeddate) ORDER BY year, month year month orders ---- ----- ------ 2004 6 17 2004 7 37 2004 8 30 2004 9 42 2004 10 34 2004 11 46 2005 1 25 2005 2 7 2005 4 1 The obvious problem with the above is that it misses out the months that have no orders, i.e. December, March, May, etc.
Is there a way I can amend my query so that it shows all months regardless of whether any orders were placed?
I have thought about trying to LEFT OUTER JOIN the above to a table that has rows with values of 1 – 12, but I’m not convinced this is the answer... and I don’t really know how to do it!
Do let me know if any of the above is unclear – what I’m after is the following: year month orders ---- ----- ------ 2004 6 17 2004 7 37 2004 8 30 2004 9 42 2004 10 34 2004 11 46 2004 12 0 2005 1 25 2005 2 7 2005 3 0 2005 4 1 2005 5 0 Many thanks
I need a select statement with the condition that the date (a timestamp with format yyyy-mm-dd) is either equal to or longer than 14 months from today's date.
Hi, i am trying to create a t-sql statement that will retrieve last months data (ie. if i run the query on 9th August, i only want to retrieve Julys data, 1st Sept will retrieve all of Augusts data etc). The query will be used once a month to populate a table, can anyone advise me on the correct where clause to use ?
This code displays records by the the month they where recorded and displays the months as names (January...) How can I do the Same for Days (Mon, Tues) and Hours (9.00,10.00)
Select DATENAME(mm,dDate) as Month, count(iStatID) as Total_Po from ListingStats Group by DATENAME(mm,dDate) Order by DATENAME(mm,dDate)
I am attempting to find a moving average for gas production per gas well over a 3 month period. I also would like to only keep the records in which the average of the previous 3 months is less than or equal to 90. My table roughly looks as such:
Code: OperatorID RRCID Year Month Gas Production 88626113200813 88626113200829 88626113200835 88626113200845 88626113200855 88626113200885 88626113200895 886261132008104 886261132008116 886261132008126 88626113200916 8862611420081752 8862611420082697 8862611420083673 8862611420084706 8862611420085701 8862611420088730 8862611420089716 88626114200810717 88626114200811680
I would like to find the average of the last 3 months, or last 3 records.
