Grouping Months Into Quarters
Aug 14, 2012
I have the following query, which is producing the results, which i need.
SELECT
*
FROM
(
SELECT
o.FDMSAccountNo,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
SUM(hst_sales_amt) AS 'Total'
[Code] ....
However I want to create an additional four columns, which gives me the quarterly sales amount
E.g.
FDMSAccount no 20120101 20120201 20120301
878743915885 NULL10000 35000
So the quarter sales amount for fdmsaccount no 878743915885 is 45000...
View 1 Replies
ADVERTISEMENT
Feb 28, 2007
Hi
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!!
Kind regards
Carel Greaves
View 3 Replies
View Related
Jun 25, 2007
Hi agian. Another probs. I need help again....
DECLARE @quarter varchar(10), @year int
SET @quarter = 'First' --First, Second, Third, Fourth
SET @year = 2007
table1 - hrEmpPaySumm
empno period1 sss_ee sss_er
1 1/1/2007 500 100
1 3/1/2007 600 200
1 3/16/2007 700 300
2 4/1/2007 800 400
3 4/1/2007 900 500
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."
View 4 Replies
View Related
Nov 20, 2015
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
View 10 Replies
View Related
Oct 25, 2006
Hi,
i get a list of Quarters that look like following as an input to my proc.
'Q306/Q106/Q406/Q405'
I need to find out whihc one of the above is the least Quarter value.
In the above example it is 'Q405'
The Quarter values that come into the proc keep changing...It could be even 1 or 2 or 3 Quarters etc...
Is there any datatype called Quarter in SQL Server.It shows Quarter as a key word...
Can some one please help on this....
View 9 Replies
View Related
Mar 27, 2008
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
View 5 Replies
View Related
Aug 26, 2015
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' ;
So, is my calculation correct?
View 2 Replies
View Related
Nov 26, 2007
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:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
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_]
View 1 Replies
View Related
Nov 20, 2006
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
View 7 Replies
View Related
Mar 21, 2008
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.
Avg
(
[Time].[Month].CurrentMember.Lag(3) :
[Time].[Month].CurrentMember,
[Measures].[A1_Avg]
)
View 4 Replies
View Related
Jan 23, 2008
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.
View 2 Replies
View Related
Oct 10, 2006
Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...
View 3 Replies
View Related
Nov 20, 2006
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
View 1 Replies
View Related
Aug 3, 2007
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.
View 2 Replies
View Related
Sep 16, 2004
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.
View 1 Replies
View Related
Jan 18, 2005
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
View 10 Replies
View Related
May 13, 2008
Hi All,
I need to display the rent not paid months in the year. How can I form the query?
Help me.
Kamal.
View 7 Replies
View Related
May 24, 2008
Hi Friends,
i need a Month name between 2 dates...
Example :
If i give start date(MM/dd/yyyy) is 03/20/2008
and End Date(MM/dd/yyyy) is 05/24/2008)
The Output Should be
March,
April,
May
How can i get this...
Thank you...
View 1 Replies
View Related
Jun 9, 2004
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
any help would be greatly appreciated
Thank you
Stephen
View 11 Replies
View Related
Mar 19, 2007
I have a View there I need my criteria to select 13 months from previous month.
Example. This is 3/1/2007 - I need to query 2/1/2007 to 2/1/2006.
View 7 Replies
View Related
Jun 24, 2007
Hi all.
I have probs on grouping by...
Sample data...
table - hrEmpPayrollSummary
empno period1 period2 pagibig_share
1 1/1/2007 1/15/2007 2000
1 1/16/2007 1/30/2007 5000
1 2/1/2007 2/16/2007 6000
2 1/1/2007 1/15/2007 8000
3 1/16/2007 1/28/2007 9000
3 12/1/2007 1/15/2007 10000
3 12/1/2007 12/15/2007 10000
expected result...
result for the year 2007
empno january february march april may june july august september october november december
1 7000 6000 0 0 0 0 0 0 0 0 0 0
2 8000 0 0 0 0 0 0 0 0 0 0 0
3 9000 0 0 0 0 0 0 0 0 0 0 10000
-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."
View 13 Replies
View Related
Dec 14, 2007
Hi i need a sql stored procedure below, this returns 2 columns of data, what i now need is to display the months as columns
ie.
TotalQty - OL_St_Code - Jan - Feb - March
then each month column shows a TotalQty for that month, do i need a query of query? if so how would i go about this
select count(OL_Qty)as TotalQty, OL_St_Code
from dbo.X_TBL_ORDER_LINEWEB LW
LEFT JOIN dbo.X_TBL_ORDERWEB OW ON OW.O_ID = LW.OL_O_ID
WHERE MONTH(O_Date) Between '11' AND '12'
group by Month(O_Date), OL_St_Code
View 19 Replies
View Related
Jul 20, 2005
How can I get the date of the first day of the month of3 months ago in one statement ?(And with no time in it ??)Arno de Jong, The Netherlands.
View 1 Replies
View Related
Jan 31, 2008
Hi,
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
Thx.
View 4 Replies
View Related
May 8, 2008
Hi,
I want to select data from a table for last 13 months.
Can anyone tell me how to write the T-sql code for this?
The table looks like this.
FileMonth Type
2007-04-01 00:00:00.000 Total NULL 199886109.65
2007-04-01 00:00:00.000 Total Line 170724936.92
2007-05-01 00:00:00.000 Total Loan 29161172.73
2007-05-01 00:00:00.000 InstLend NULL 49780163.49
2007-06-01 00:00:00.000 InstLend Line 42450387.44
...........
So I wanna pull data from 04/01/07 to 04/01/08.
So I wanna write a query some thing like this.
Select *
from table1
where Filemonth BETWEEN PreviousMonth AND Last13thMonth
Thanks
View 1 Replies
View Related
Apr 16, 2008
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.
Code Snippet
select datediff(month, '4-30-2006', '4-1-2008');
Result: 24
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?
View 7 Replies
View Related
Apr 3, 2008
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?
View 4 Replies
View Related
May 9, 2008
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!
View 9 Replies
View Related
Oct 19, 2005
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..........
View 2 Replies
View Related
Nov 13, 2000
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?
Thanks
Luiz Lucasi
View 2 Replies
View Related
Jun 15, 2005
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
View 1 Replies
View Related
Nov 29, 2004
Hi,
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.
I am using SQL Server
Can anyone point me in the right diretion?
Thanks
View 1 Replies
View Related
Aug 5, 2004
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 ?
Thanks in advance
Dave
View 3 Replies
View Related
