Query By Quarter

May 19, 2004

Hello, everyone:

There is a table named INCOME that has INCOME column for each day and DATE column starting from Aug. 29 1980. How to calculate income summary by each quarter? Thanks.

ZYT

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Rollover Prior Quarter Data To New Quarter In A Table - Output Identity Values

Jun 5, 2014

I have a process to rollover prior quarter data to new quarter in a table.

For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )

1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1

Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like

1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
4,'today',2014,2
5,'tomorrow,2014,2
6,'friday',2014,2

Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )
Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.

Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like

1,4
2,5
3,6

I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.

View 9 Replies View Related

Sql For Financial Reporting Periods This Month, Last Month, This Quarter, Last Quarter, This Year, Last Year

Oct 26, 2006

Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine  for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas? 

View 8 Replies View Related

Calculate Quarter

Jan 28, 2007

Hi,

I need to retrieve both Mont and Quarter from an MS SQL server field containing a date as ''01/01/2007 12:10:00'.

Retrieving the Month is working properly, but getting some sql error with the Quarter function.

I will appreciate if someone can indicate me how should I do in order to retrieve the Quarter from a date.

To retrieve the month, I am using:

& "MONTH(STOCK.VALUEDATE) AS 'Month'"


To retrieve the Quarter, I am trying (but getting error):

& "QUARTER(STOCK.VALUEDATE) AS Quarter"
Thanks,

Aldo.

View 3 Replies View Related

Last Day Of Current Quarter + I Month

Feb 12, 2006

Hi there everbody,

i am trying to get the last day of the Q + 1 month

the first part i have succeeded (dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,getdate() )+1, 0))) ,but how do i add 1 more month ?

would appreciate any suggestion

regards

Yoav

View 2 Replies View Related

All Sales Reported After Quarter End

Apr 19, 2007

It's been a long time since I've built tough queries and need some help.

I need to report all sales that happened within a quarter but were reported after the quarter end.

Quarters

Q1 - 1/1 thru 3/31
Q2 - 4/1 thru 6/30
Q3 - 7/1 thru 9/30
Q4 - 10/1 thru 12/31

Sample Data

ticketid salespersonid saledate sale_entereddate
1234 bsmith 1/1/2006 2/1/2006
1235 jgarcia 3/31/2006 4/1/2006
1236 bsmith 1/1/2006 2/3/2006
1237 jdoe 6/23/2006 7/1/2006
1238 bsmith 8/5/2006 8/6/2006
1239 bsmith 10/1/2006 1/1/2007

View 4 Replies View Related

Group By Financial Quarter

Dec 4, 2007

I have a transaction file with Company_code,Gl_code,Amount,Transaction_Date

I want to sum the amount based on a Company_Code,Glcode for every financial quarter(01/04/2007-30/06/2007,01/07/2007-30/09/2007 and so on).

Expected Output

Company_Code,Gl_code,Quarter_1,Amount_1,Quarter_2,Amount_2,Quarter_3,Amount_3,Quarter_4,Amount_4

Regards

Nirene

View 6 Replies View Related

Quarter() Function In SSRS

Aug 1, 2007

I am uisng VS 2005 to build reports for SQL 2005. The problem is with getting the Quarter() function, mentioned in the BOL, to work in the report builder. When I build an expression and try to use the function as noted in the BOL I receive an error stating that the name "Quarter" is not declaired. When I use the expression builder to build the expression the function is not available in the builder, which is probably why I get the error.

I am assuming there is some sort of service pack of hot fix I need to apply to my VS but have not been able to locate which one. I have verified that I have all of the recent service packs applied for VS and SQL server 2005 but I am still unable to see/use this function.

Any help would be greatly appreciated. I need this function for my financial reports.

View 1 Replies View Related

Problem With QUARTER(date)

Apr 4, 2008

Hi,

I am using QUARTER(startdate) formula to retrieve the quarter of a specific datetime and to store in my calculated field in dataset. But while running its showing "error: [BC30451] Name 'QUARTER' is not declared".

Can anyone show me some light on this problem?

View 5 Replies View Related

Analysis :: Get Max Quarter Of A Year MDX?

Aug 18, 2015

There is a way to get the non empty max calendar quarter of the year and the last year. For example, the max calendar quarter of the last year should be 4, and in this moment the max quarter of this year should be 3.

I am building a report in SSRS and trying to avoid query the DWH database. I want to run every query against the cube.

View 3 Replies View Related

Start Of The Week Of Current Quarter

Oct 2, 2014

I am trying to always get the start of the week of the current quarter in my criteria

This is the statement for the current quarter
Dateadd(qq, Datediff(qq,0,GetDate()), 0)

This is the statement for the current week
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

How to calculate from the start of the week of the current quarter...

View 1 Replies View Related

Accumulate Months By Quarter Measurement

Mar 24, 2014

I need accumulate the months by Quarter measurement. For example we're in March now and I need put these code in Where clause like:

Where MyDate > DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) ...

here DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) makes Apr of last year included because that is the first month in 2nd Quarter of last year. But in next month we're in Apr and I need make -11 as -12 in above code to include Apr of last year. And same reason if we're in Feb. 2014 then I need change -11 to -10.

So how can I calculate this to make the number fit into the code or some other way to get the same effect? I need get all last 4 quarters based on current month (include current Quarter).

View 2 Replies View Related

Sum By Week / Month / Quarter / Year

Aug 20, 2014

I am trying to group counts by week,month,quarter, year for a particular activity type and I'm having issues.Here's my code so far:

SELECT
distinct
EmailAddressID,
emailaddress,
SUM(CASE WHEN [ActivityDate] >= DATEADD(WEEK, DATEDIFF(WEEK, 0, @DT), 0)
THEN
SUM(CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END) END AS WeekTotalOpens
FROM EmailActivity
WHERE DATEPART(YEAR, [ActivityDate]) = DATEPART(YEAR, @DT)
GROUP BY EmailAddressID,emailaddress
Desired Output:
EmailAddressId EmailAddress WeekTotalOpens MonthTotalOpens etc. then WeekTotalClicks and so on....

SQL doesn't seem to like the sub-aggregate. What is the best way to approach?

View 2 Replies View Related

Determine Quarter End And Beginning Dates

Jul 23, 2005

Hello,I have a query that I would like to schedule in DTS. The criteria ofthis query checks for records in the table that are within the currentquarter. Here is what I have.WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'I would like to dynamically generate the Quarter End and QuarterBeginning dates within my where clause based on the date that DTWSpackage is being executed on. Can anyone show me how this can beaccomplished?Thank You.

View 3 Replies View Related

Reporting Services :: End Date Of Last Quarter

Jun 10, 2015

I am using the following expression to calculate the last day of the last quarter

=dateadd("s",-1,dateadd("q",datediff("q","1/1/1900",today()),"1/1/1900"))

It returns 3/31/2015 11:59:59 PM

What needs to change so that it return 3/31/2015 12:00:00 AM

View 4 Replies View Related

Getting Year/quarter/month/day In The Right Order (was Dates)

Feb 28, 2005

I have a program that calls queries (OLAP system) the system includes a dimension of date: Year, Quater, Month, Week

When the result appears in the table, it is not in order? Only the year is in oredr and after that each heirachy is wrong and not in order....not sure how to do this!!!

any help would be grateful!!! not sure what I should be looking at.....

View 14 Replies View Related

Char(4) Or Integer For Year / Quarter Field

Sep 3, 2007

Hello,

what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?

Both are part of a composite index.

Thanks
Silas

View 6 Replies View Related

T-SQL (SS2K8) :: Get First Day Previous Quarter From Todays Date

Aug 22, 2014

How do I get first day of last month of previous quarter from today's date? I know my question is little confusing. I need to get 06/01/2014 using t-sql.

View 2 Replies View Related

T-SQL (SS2K8) :: Get Rows Based On Current Quarter

Sep 3, 2014

I am working on a report and the data source is Teradata. now I have situation where I want to get order id details based on the current quarter and year I am posting this same data. For TD related queries I do not where to post.

ACCT_ID ACCT_NMORD_NBRORD_DT ORD_AMT_USD
595709114ASDASD444447/28/2014 546
2224809440ASDASD444445/2/2012 546
1724031572ASDASD444446/22/2011 546
1702887651ASDASD444447/3/2014 546
1724020508ASDASD444447/16/2012 546
1148151895ASDASD444449/18/2013 546
2125154824ASDASD444449/2/2014 546
1503552723ASDASD4444412/20/2011 546
2224689808ASDASD4444410/4/2010 546
931387698ASDASD4444412/31/2010 546

View 4 Replies View Related

SQL 2012 :: Measuring Server Uptime Over A Quarter

Sep 29, 2014

Here is a brief description what I am actually looking at. As we all have SLA's to understand how much uptime/downtime we can afford maybe per year/per quarter. I am keenly interested in finding out the way of calculating the sql server uptime. I googled for this and didn't find an appropriate solution that can justify my needs.

I am looking at a way that can give me a historical view of the uptime (possibly aggregated over time), considering all the facts for e.g I am not considering the maintenance that we do for keeping our servers up to date which includes patching and stuff, instead I am more focused on the historical view that for e.g if my manager asks me to give him a report stating the uptime for all the sql servers that we have for the current quarter.

Hence, I would basically some kind of script wherein I am storing the history somewhere and at a later date if my manager asks me to give a quarterly uptime report I can pull out that aggregated data, and generate a pie chart or something from that data to show the uptime and downtime for the same. I don't want to use 3rd party tool and I know there are quite a few, but company won't afford it.

View 0 Replies View Related

SQL Server 2008 :: Querying Last Quarter Data

Feb 26, 2015

We have this query that pulls number of days worked from the current Quarter to Date.

(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'
FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd))
AS VARCHAR) AS daysworked, ActivityId AS totalcalls
FROM PhoneCall AS p
WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR,
DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb)
AS [Days Worked],

I need changing it to bring up LAST Quarter's data.

View 1 Replies View Related

Transact SQL :: Convert YYYYQ To Quarter End Date

May 14, 2015

I have a text field which displays quarter using YYYYQ (i.e. 20142, 20151, etc...)

I need to convert the field to a datetime representing the last day of that quarter.  For example 20142 would be 06-30-2014 and 20151 would be 03-31-2015.

View 7 Replies View Related

Transact SQL :: How To Get Four Quarter End Dates Based On Given Date

Jun 3, 2015

I have a simple following table which is having only one date column.

CREATE TABLE TEST_DATE
(
InputDate DATE
)
GO
INSERT INTO TEST_DATE VALUES('01-01-2015')
INSERT INTO TEST_DATE VALUES('06-25-2015')
INSERT INTO TEST_DATE VALUES('11-23-2014')
GO
SELECT * FROM TEST_DATE;

And the expected out put would be as follows:

I want to derive a Four Quarter End Date based on Date selected.

For Example if i select 01-01-2015 then
First Quarter End Date would be Previous Quarter End Date
Second Quarter End Date would be Current Quarter End Date
Third Quarter End Date would be Next Quarter End Date
Fourth Quarter End Date would be Next +1 Quarter End Date Like that

View 9 Replies View Related

SQL Server 2008 :: How To Sum Values Based On Quarter And Month

Mar 20, 2015

We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....

We will be having date column values as 201401,201402,.....

How can we implement in sql sever performance should be good.

View 1 Replies View Related

SQL Server 2008 :: Rounding To Nearest Quarter Hour?

Aug 12, 2015

I am getting the time difference between two dates using

DATEDIFF(second,Information.[Start Time],Information.[End Time]) / 60.00 / 60.00 AS hours,

My output looks like
1.33
0.17
1.50
etc

I'd like to round to the nearest quarter hour

1.50
0.25
.150
etc

View 4 Replies View Related

Reporting Services :: Round Time To Quarter Hour

Jun 2, 2011

SSRS 2008 R2. I am writing a report that uses a DB4 database. Because of this many SQL commands that I normally use are not available and I don't know what commands I can use. Because of this I am doing much of my calculations inside of SSRS.

I need to round a time field to the nearest quarter hour inside of SSRS.

For example:
8:12 becomes 8:15
2:30 becomes 2:30
1:57 becomes 2:00
4:07 becomes 4:00

View 9 Replies View Related

For Each Year Group In Matrix Return Last Quarter's Data

Sep 12, 2007

Hi, I have a matrix report with three data points
1. Inventory
2. Occupancy
3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

View 6 Replies View Related

Data Warehousing :: Order By Not Working For Quarter Years

Sep 30, 2015

Expected

[care session quarter]
Q3-14
Q4-14
Q1-15
Q2-15
Q3-15

Currently the output am getting shown below

care session quarter
Q1-15
Q2-15
Q3-14
Q3-15
Q4-14

I am using this [care session quarter] column in the group by clause to achieve but no success.IF I use date column in  the select clause and Group by clause then it comes correctly but groups by all dates which is not required.

Ideally I want show only quarter aggregates. The [Date Dimension] table has the column [care session quarter]  which stores all the quarters of years along with dates for each day. i..e I have all columns in [Date Dimension] table as shown below

Column_name
DATE_KEY
temp_date
DATE_NAME
DATE_WEEKDAY_NAME
DATE_IS_WORKDAY
DATE_NUMBER_IN_WEEK

[Code] ....

View 2 Replies View Related

Calculating Average Count By Day / Week / Month / Quarter / Year

Aug 18, 2014

I need developing a query to get the average count by the following:

Day - use daily info for the last ??? days

Weekly - average
- Add all days and divide by 7
- As of Saturday midnight

Monthly - average
- Add all days and divide by days in the month
- As of last save on last day of month

Quarter - average
- Add all days and divide by number of days in the quarter
- As of last day of quarter

Year - average
I don't have requirements for year as of yet.

How can I get the avery count per these timeframes?

View 7 Replies View Related

Transact SQL :: Cumulative Values Quarter And Half Yearly Wise

Nov 23, 2015

Having table like below. Here want to cumulative the values quarter and half yearly wise...

declare @table table 
(month varchar(10),
value int)
insert into @table values('apr' ,100 )
insert into @table values('may' ,200 )
insert into @table values('jun' ,300 )

[Code] ....

Like wise the data should added...

View 3 Replies View Related

Reporting Services :: Adding A Quarter Column Every Three Months In A Report In SSRS?

Sep 15, 2015

I want to create a report of our customers displaying the monthly sales totals and display the Quarter totals at the end of  every third month, e.g.

Customer       Jan Feb Mar Quarter1  Apr May Jun
Quarter2 Jul  Aug...

I've created the report with the Quarter totals and the far right end, but I can place my finger on how to do it every three months.

My data exists in one table. Here is the query:

SELECT customer
, customercode
, city
, state
, salesperson
, invoiceYear
, invoiceMonth
, CAST(SUM(netSalesValue + discValue) / 1000 AS decimal(10, 1)) AS grossSales

[code]....

My query results are as follows:

Customer Name | 0000305 | Orange | CA | SalesPerson Name | 2015 | 1 | 8.4 | Q1 | True

View 2 Replies View Related

Analysis :: How To Find Quarter / Year / Month / Semester Start And End Dates In MDX

Sep 24, 2015

I have a date dimension set in the SSAS Cube. I have been trying get quarter,year,month,semester start and end dates using ClosingPeriod() and OpeningPeriod() functions but not getting the exact value. How the get correct dates for a given date.

View 2 Replies View Related

Reporting Services :: Range Bar Chart - X Axis Dates Label Format Need To Be Converted To Quarter

Sep 4, 2014

I have created range bar chart and I am not able to achieve the following tasks.

1. Change X-axis Label Format to Quarter:

I have x-axis with dates and y axis of project groups. I have changed x-axis interval type = month and interval=3.
   
Set the Maximum =  Max(ProjectEndDate) and Minimum = Min(ProjectStartDate).

Now my chart showing 3 months x-axis interval dates in mm/dd/yyyy format. I want to change this interval date format to Quarter. The problem is LabelsFormat property is not recognize  the "=Q or q or quarter" and also not accepting the expressions. How can I achieve this?

2. Placing series side by side when it is not overlapping

I want to place the same group series side by side only when the previous project end date is less than next project start date, otherwise place the next project to next row. How can I achieve this?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved