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).
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.
I am trying some replication sample.I create a table with thousands of records in the publisher side. as I create a subscription(the subscription database is on a remote machine), the whole table is created on the remote database.
I wanted to measure the performence as: 1. how much time was taken in filling the whole table in the subscriber side? 2. If i insert some 10000 records on publisher side, I want to measure, how much time was taken in inserting the same records on the subscriber?
How do I measure this ? Can I use some Log reader stuff.
The below code works fine to measure the difference in days between two dates. However, there is an additional business requirement to subtract week-ends, and holidays, from the equation. Any ideas on how to accomplish this task, and leverage the below, existing code? Thanks in advance! (SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "
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?
I have a log table in which alarm events are logged (a many relation to a main table) where EventId is the foreign key in the log table.
In this table all events related to a given incident (EventID) are logged, but it is specifically two eventtypes which is of interest, thats the "into alarm" represented by an integer value "0" in the column "EventTypeId" and the "restored represented by an integer value of "2". Each state of change has a timestamp of datetime.For each incident (EventId) there can be multiple "into alarm" and "restore" rows, there can also be other events which has no interests. Only the timediff between each "0" and "2" accumulated is of interest.
There's one more little twitch, it can occure that when the query is run, the "2" restored state is not yet present, the query should not return a result for that "0" into alarm when the "2" restored is not present, unless there was a complete alarm/restore session.
CREATE TABLE [dbo].[AlarmHistoryLog]( [EventId] [int] NOT NULL, [EventSeq] [int] IDENTITY(1,1) NOT NULL, [EventTypeId] [int] NOT NULL, [EventTime] [datetime] NOT NULL, [EventDescription] [nvarchar](250) NOT NULL, [Comment] [nvarchar](4000) NULL, [UserID] [int] NULL, [ReceiverID] [int] NULL ) ON [PRIMARY]
I have some transactions with the same card number that needs to add value amount to its existing balance. For example:
Card Number Balance Amount Issue Date Issue Branch. 4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.
Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?
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.
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.
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".
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.
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?
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.
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.....
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.
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.
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.
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.
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.
