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?
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?
Hi every one, I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview. How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
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.....
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.
I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.
In my reports I am extracting the data of number of people joined in all the weeks of the year. And in one of reports I have to extract the data of the number of people joined until the last week from the first week. I am trying out all the logics but nothing is working for me as such. Can any one help me with this issue??????
i have some classes that I want to group by month/year (note:i dont need the day of the month) how do i wirte my sql so it only gives me the dictinct groups month/year of the classes I have so that it comes out like so.. 11/2006 12/2006 1/2007 3/2007 i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value. dbo.classgiven.classdate date of the class.thank you all SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/" + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode FROM dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND (dbo.classT.coned IS NOT NULL)", conNorthwind )
I need a Select sentence that return me the first week of the month for a given week.
For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).
I only need give a week number of the year and then returns the week number of the first week of that month.
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.
Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. W01 represents the week of the year from W01 through W53, and D represents the day of the week with 1 = Monday through 7 = Sunday.
The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day, first date+60, and first date+364 for each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK GO create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK ( @Datedatetime ) returnsvarchar(10) as /* Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. */ begin
declare @YearWeekDayOfWeekvarchar(10)
Select --Format to form YYYY-W01-D @YearWeekDayOfWeek = convert(varchar(4),year(dateadd(dd,7,a.YearStart)))+'-W'+ right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) + '-'+convert(varchar(1),(datediff(dd,a.YearStart,@Date)%7)+1) from ( select YearStart = -- Case finds start of year case whenNextYrStart <= @date thenNextYrStart whenCurrYrStart <= @date thenCurrYrStart elsePriorYrStart end from ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aaa.Jan4))/7)*7,-53690), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aaa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aaa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4= dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aaa ) aa ) a
return @YearWeekDayOfWeek
end go
-- Execute function on first day, first day+60, -- and first day+364 for years from 1990 to 2030.
select DT= convert(varchar(10),DT,121), YR_START_DT = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT), YR_START_DT_60 = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60), YR_START_DT_365 = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364) from ( select DT = getdate()union all select DT = convert(datetime,'1990/01/01') union all select DT = convert(datetime,'1990/12/31') union all select DT = convert(datetime,'1991/12/30') union all select DT = convert(datetime,'1993/01/04') union all select DT = convert(datetime,'1994/01/03') union all select DT = convert(datetime,'1995/01/02') union all select DT = convert(datetime,'1996/01/01') union all select DT = convert(datetime,'1996/12/30') union all select DT = convert(datetime,'1997/12/29') union all select DT = convert(datetime,'1999/01/04') union all select DT = convert(datetime,'2000/01/03') union all select DT = convert(datetime,'2001/01/01') union all select DT = convert(datetime,'2001/12/31') union all select DT = convert(datetime,'2002/12/30') union all select DT = convert(datetime,'2003/12/29') union all select DT = convert(datetime,'2005/01/03') union all select DT = convert(datetime,'2006/01/02') union all select DT = convert(datetime,'2007/01/01') union all select DT = convert(datetime,'2007/12/31') union all select DT = convert(datetime,'2008/12/29') union all select DT = convert(datetime,'2010/01/04') union all select DT = convert(datetime,'2011/01/03') union all select DT = convert(datetime,'2012/01/02') union all select DT = convert(datetime,'2012/12/31') union all select DT = convert(datetime,'2013/12/30') union all select DT = convert(datetime,'2014/12/29') union all select DT = convert(datetime,'2016/01/04') union all select DT = convert(datetime,'2017/01/02') union all select DT = convert(datetime,'2018/01/01') union all select DT = convert(datetime,'2018/12/31') union all select DT = convert(datetime,'2019/12/30') union all select DT = convert(datetime,'2021/01/04') union all select DT = convert(datetime,'2022/01/03') union all select DT = convert(datetime,'2023/01/02') union all select DT = convert(datetime,'2024/01/01') union all select DT = convert(datetime,'2024/12/30') union all select DT = convert(datetime,'2025/12/29') union all select DT = convert(datetime,'2027/01/04') union all select DT = convert(datetime,'2028/01/03') union all select DT = convert(datetime,'2029/01/01') union all select DT = convert(datetime,'2029/12/31') union all select DT = convert(datetime,'2030/12/30') ) a
I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no, isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr', isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May', isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun', isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
[Code] ....
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June .... 100 2012 100 $23 $30 $400 100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
And the resultant string is: dd/mm/yyyy, for example 30/08/2004. But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
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 ?
I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.
The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.
I've tried evaluating the parts of the episode date seperately like:
where (epimonth>=10 and epiyear=2003) or (epimonth<=2 and epiyear=2004)
and that works, but what happens when someone wants to see from 10/2002 to 2/2004?
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.
This function returns the ISO 8601 week of the year for the date passed. The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day of each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_WEEK_OF_YEAR go create function dbo.F_ISO_WEEK_OF_YEAR ( @Datedatetime ) returnsint as /* Function F_ISO_WEEK_OF_YEAR returns the ISO 8601 week of the year for the date passed. */ begin
declare @WeekOfYearint
select -- Compute week of year as (days since start of year/7)+1 -- Division by 7 gives whole weeks since start of year. -- Adding 1 starts week number at 1, instead of zero. @WeekOfYear = (datediff(dd, -- Case finds start of year case whenNextYrStart <= @date thenNextYrStart whenCurrYrStart <= @date thenCurrYrStart elsePriorYrStart end,@date)/7)+1 from ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4= dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aa ) a
return @WeekOfYear
end go
-- Execute function on first day of first week of year from 1990 to 2030 select DT, ISO_WEEK_OF_YEAR = dbo.F_ISO_WEEK_OF_YEAR(a.DT) from ( select DT = getdate()union all select DT = convert(datetime,'1990/01/01') union all select DT = convert(datetime,'1990/12/31') union all select DT = convert(datetime,'1991/12/30') union all select DT = convert(datetime,'1993/01/04') union all select DT = convert(datetime,'1994/01/03') union all select DT = convert(datetime,'1995/01/02') union all select DT = convert(datetime,'1996/01/01') union all select DT = convert(datetime,'1996/12/30') union all select DT = convert(datetime,'1997/12/29') union all select DT = convert(datetime,'1999/01/04') union all select DT = convert(datetime,'2000/01/03') union all select DT = convert(datetime,'2001/01/01') union all select DT = convert(datetime,'2001/12/31') union all select DT = convert(datetime,'2002/12/30') union all select DT = convert(datetime,'2003/12/29') union all select DT = convert(datetime,'2005/01/03') union all select DT = convert(datetime,'2006/01/02') union all select DT = convert(datetime,'2007/01/01') union all select DT = convert(datetime,'2007/12/31') union all select DT = convert(datetime,'2008/12/29') union all select DT = convert(datetime,'2010/01/04') union all select DT = convert(datetime,'2011/01/03') union all select DT = convert(datetime,'2012/01/02') union all select DT = convert(datetime,'2012/12/31') union all select DT = convert(datetime,'2013/12/30') union all select DT = convert(datetime,'2014/12/29') union all select DT = convert(datetime,'2016/01/04') union all select DT = convert(datetime,'2017/01/02') union all select DT = convert(datetime,'2018/01/01') union all select DT = convert(datetime,'2018/12/31') union all select DT = convert(datetime,'2019/12/30') union all select DT = convert(datetime,'2021/01/04') union all select DT = convert(datetime,'2022/01/03') union all select DT = convert(datetime,'2023/01/02') union all select DT = convert(datetime,'2024/01/01') union all select DT = convert(datetime,'2024/12/30') union all select DT = convert(datetime,'2025/12/29') union all select DT = convert(datetime,'2027/01/04') union all select DT = convert(datetime,'2028/01/03') union all select DT = convert(datetime,'2029/01/01') union all select DT = convert(datetime,'2029/12/31') union all select DT = convert(datetime,'2030/12/30') ) a
Does anyone know how I can get last day of month if I pass a function a given month and and given year. @Month = 2 @Year = 2004 The result I would need is 29 because there are 29 in the month of February in the 2004. Any help on this is greatly appreciated. Kellie
Hi Guys, Can anyone please let me know How to find the first day of the week, if I know the weekno and the year. For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week for the above query the date will be eg:26/06/2003
I really appreciate your Help
Thanks:confused: Find the Date if I know the week and year
Hi, I have a Serial No which has a length as 14.For eg IL010730123456. IL01 is The Default Code. 07 is the current year and 30 is the week of the year. 123456 is Serial. How shoud I find The Year and week with the help of this serial no ie The First day of the 30th week of 2007.Is it Possible?
Plz Help me with an appropriate solution. Thanks.....
I am trying to calculate the number of the week within a month of a given date. in other words, given a date I need to find out whether it's the 1st week or 2nd or 3rd or 4th or 5th week of that given month.
for instance let's call my function "weekOfMonth()":
weekOfMonth(4/19/2004) should return 4 (it's the fourth week of april) weekOfMonth(4/7/2004) should return 2 (it's the second week of april) etc ..
Running a SQL 7 system on a Windows 2000 server using Access 2000 on clientmachines as a front end. System administrator currently reboots the serveronce a month. Yesterday we had some weird thing with the database whereusers were getting ODBC errors when trying to access it. Rebooted theserver, everything was fine. Suggested to the sa that he reboot the serveronce a week. He said he already does it once a month, and that's sufficient.My POV is that: a) doing it once a week might prevent situations such as theone yesterday; b) even without situations like the one yesterday,performance may be degrading over the course of the month, without our beingaware of it, and rebooting once a week might help performance.Any thoughts on this?Thanks!Neil
