I'm trying to create a chart with monthly comparisons, but when i throw my months into the chart it groups them under one another instead of next to one another. Please Help!!
expected result... if @quarter = 'First' AND @year = 2007 - returns all data of jan,feb,mar of 2007 empno month1 sss_ee1 sss_er1 month2 sss_ee2 sss_er2 month3 sss_ee3 sss_er3 1 January 500 100 Feb 0 0 March 1100 500 2 January 0 0 Feb 0 0 March 0 0 3 January 0 0 Feb 0 0 March 0 0
if @quarter = 'Second' AND @year = 2007 - returns all data of apr,may,jun of 2007 empno month1 sss_ee1 sss_er1 month2 sss_ee2 sss_er2 month3 sss_ee3 sss_er3 1 April 0 0 May 0 0 June 0 0 2 April 800 400 May 0 0 June 0 0 3 April 900 500 May 0 0 June 0 0
and so on....
-Thanks
-Ron-
"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."
I want past two and next two quarters from the present quarterLike now its 4th quarter i should get 3rd and 2nd quarter and next two quarters that is 1st and 2nd also this is my table and when the year changes data is not fetched correctly
I would like to add a calculation to provide an annualized Gross Amount value. Â Most solutions I have seen are for annualizing down to the day. Â For instance, if today is the 123rd day of the year, the calculation would be Amount / 123 * 365. Â What I want to do is annualize the amount based only on completed periods. Â Today is 8/26. Â
The last completed quarter ended on 6/30. Â Therefore I want to basically take the sum of Q1 and Q2, divide by two, and multiply by four to get an annualized amount based on completed quarters. Â Eventually I want to do something similar with months.
So here's what I have so far. Â In my DSV my DimDate is actually based on a view. Â The view has a Date column with the actual date, all of the normal fields you would expect in a DimDate table, and two columns that look like this:
CASE WHEN [Date] < DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0) THEN 'Y' ELSE 'N' END AS CompletedQuarter,
[Code] ....
Note that for dates between 1/1 and 3/31 the CompletedQuarterCount will be 0. Â I want any annualized amount to be 0 in that case because I only want to use completed quarters in my calculation.
I added both to my Pay Date dimension (which uses vwDimDate from the DSV) in the cube. Â I have tried the calculation below and in my Excel pivot table I'm getting blanks for the calculated field.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross Amount Annualized by Pay Date Quarter] AS SUM( IIF([Pay Date].[Pay Date Completed Quarter Count] > 0, [Gross Amount] / [Pay Date].[Pay Date Completed Quarter Count] * 4, 0) ), FORMAT_STRING = "Currency", VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Claim' ;
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
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 ?
