select empid,year(orderdate) from [Sales].[Orders] WHERE custid = 71
group by empid,year(orderdate)
sample data resultset:
empid orderdate
1 2006
1 2007
1 2008
why is the year() gets only one year when worked with groupby. when empid =1 the year 2007 has 2 records and 2008 has 2 records in first select and when I used year(orderdate) in secong select only one year for each is selected.
I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.
Using SQL 2000 Report Designer & Visual Studio 2003 and a report for MS CRM 3.0
I need to group by Year + Month, like now would be "2008 02". Am using the FilteredOpportunity.estimatedclosedate. I have tried using YEAR(FilteredOpportunity.estimatedclosedate)+MONTH( FilteredOpportunity.estimatedclosedate), but does not work. Also YEAR( FilteredOpportunity.estimatedclosedate.Value). I don't store "2008 02" type data in the CRM.
I'm looking for a function that will return an INTEGER that computes the number of days between two dates based on a 360-calendar year instead of using just the datediff function which is actual days.
If anybody knows where I could find something like this, I would be greatly appreciated.
Does this site have a library of user-defined sql functions? Or, does somebody have a site that you know of where there are a bunch?
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
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 ?
Below is the code. It gives 2 errors. 1 @StartDate must be defined (it is a report parameter) 2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field) Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5)) DECLARE @sowners nvarchar(5) DECLARE @mydate datetime DECLARE @sstatus nvarchar(10) DECLARE @sjtype nvarchar(5) DECLARE @chardate nvarchar(7) DECLARE @Jmonth Int DECLARE @Jyear Int DECLARE @Jcharyear nvarchar(4) DECLARE @Jcharmonth Nvarchar(2) DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype FROM Requirements WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate)) OPEN JobCur FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype WHILE @@FETCH_STATUS = 0 BEGIN SET @Jyear = (Year(@mydate)) SET @Jmonth = (Month(@mydate)) SET @Jcharyear = CAST(@Jyear,4) SET @Jcharmonth = CAST(@Jmonth,2) IF Len(Jcharmonth) = 1 BEGIN SET Jcharmonth = '0' + Jcharmonth END IF @Jmonth >= 10 BEGIN SET @chardate = @Jyear + '/' + @Jmonth END IF @Jmonth < 10 BEGIN SET @chardate = @Jyear + '/0' + @Jmonth END INSERT INTO @t (Owner, cdate, status, jtype) VALUES (@sowners,@chardate,@sstatus,@sjtype) FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype END CLOSE jobcur DEALLOCATE jobcur BEGIN SELECT * FROM @t END
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
I have a user defined function in datebase SQL 2000. function looks like create function Getcurrentdate(@month int, @day int) returns smalldatetimebegin declare date1 as smalldatetime--get current year --convert month, day and year into current date. then return return date1end my problem was , after using getDate(). I get error meassage which is "can'not use getDate() inside user function"How can I get current year in the user defined function. Thanks
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012' when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013' when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014' when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015' when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016' when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017' End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
SELECT Node_ID,Day,Operation, AA,BB FROM (SELECT CASE WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA, CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
hay all, i'm trying to pass parameters to a stored procedure ..and i keep getting this error "Procedure or function 'selectFromView' expects parameter '@Year', which was not supplied." this is the procedure implementation : ALTER PROCEDURE dbo.selectFromView
@Year as varchar(10), @Country as varchar(10), @Family as varchar(10), @Manu as varchar(10), @Status as varchar(10), @Type as varchar(10), @Operator as varchar(10)
AS SELECT * FROM ViewofAll WHERE ProductionYear = @Year and CountryName = @Country and Family = @Family and Manufacturer = @Manu and Status = @Status and Type = @Type and OperatorName = @Operator
RETURN and below how i call the procedure : Dim connection As SqlConnection connection = New SqlConnection() connection.ConnectionString = "Data Source=BLACKIRIS3SQLEXPRESS;Initial Catalog=Aircrafts;Integrated Security=True" connection.Open() Dim command As SqlCommand command = New SqlCommand("selectFromView", connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add(New SqlParameter("@Year", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input command.Parameters.Add(New SqlParameter("@Country", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input command.Parameters.Add(New SqlParameter("@Family", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input command.Parameters.Add(New SqlParameter("@Manu", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input 'Stored prcedure parameters command.Parameters.Add(New SqlParameter("@Status", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input command.Parameters.Add(New SqlParameter("@Type", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input command.Parameters.Add(New SqlParameter("@Operator", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input command.Parameters.Item("@Year").Value = myArray(0) command.Parameters.Item("@Country").Value = myArray(1) command.Parameters.Item("@Family").Value = myArray(2) command.Parameters.Item("@Manu").Value = myArray(3) command.Parameters.Item("@Status").Value = myArray(4) command.Parameters.Item("@Type").Value = myArray(5) command.Parameters.Item("@Operator").Value = myArray(5) DSGrid.SelectCommand = command.CommandText With DSGrid .DataBind() End With i'm not really familiar with stored procedures ...any one can help me plz ? thanx in advance .
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date) RETURNS date AS BEGIN; DECLARE @Result date; SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101) RETURN @Result; END;
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
I have 3 Field on my table. ID (int PK),Name(nvarchar(50),PubDate(DateTime). "PubDate" may have values between "1/1/1996" "1/1/2007" NOw How could I group Table Records base on Year of the Field "PubDate"-1996,1997,...,2007- As I think, First I must Retrieve each Record Year suppose Record1 (ID=1 Name "Something" PubDate=2007) and then List and Group them. But I don't whether this function Is available in MSSQL 2005.
how do i use group by for the below query, am getting duplicate results for the query i execute, am not sure how do i use group by for this. pls.help.
SELECT C.USER_ID, A.comm_Name, B.UU_TITLE, FILENAME_URL=(Case When UU_Type = 'U' Then UU_url else UU_Filename End), C.User_FirstName + ' ' + C.User_LastName AS Author, Convert(CHAR(10),UU_TimeStamp,101) as DateCreated, Convert(CHAR(10),UU_LastModified,101) as DateModified, uu_AssocAuID, DOWNLOADS = (select count(*) from Vportaldevstats.[dbo].stats_download A where A.stat_AuID = b.UU_AssocAuID ) FROM vportaldev.dbo.community A INNER JOIN vportaldev.[dbo].user_upload B ON A.comm_CreatorUserID = B.UU_USERID INNER JOIN vportaldev.[dbo].[user] C ON C.user_id = B.UU_USERID
I would like to know if that the SUM() function can use more than 1 group in the scope portion. Example is as below:
Sum(Fields.Hour.Value, ("grp1", "grp2")) This would help as I am doing a report using a matrix report. I need to do a calculation in the measure area so that it will calculate base on the row and column grouping. Is this possible?
can anyone tell me what is the use of group by in rs tables and how to use group by in tables (when we have to use )...if possible please gimme some examples..
I want to run a much larget SQL statement, but for examples sake this is a good starting point
Code: Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?
How can I group my data to show up like the following? I need to get the wr, sample and the most current date in MS Access. 0805393, 01, 5/16/08 0805393, 02, 5/16/08 0805393, 03, 5/17/08
I tried Max(Y), and also grouping them and it's driving me crazy!... Thanks
I'm trying to update a varchar field using SUM. I keep getting the error that the sub query returns more than one value.
UPDATE CIRSUB_M SET TRM_DMO = SUBSTRING(TRM_DMO,1,11) + (SELECT CAST(SUM(COPIES) AS VARCHAR(5)) FROM CIRSUB_M WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T') GROUP BY PUB_CDE, DNR_NBR) WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')
Example
PUB_CDE DNR_NBR COPIES TRM_DMO THN 000000092637 100 A THN 000000092637 200 B THN 000000082455 100 A THN 000000082455 200 B THN 000000051779 100 A
select bicycle_shop.bicycle_shop_id, bicycle_shop.company_name, order_.unit_price from order_ inner join bicycle_shop on order_.order_serial_number = bicycle_shop.order_serial_number order by order_.unit_price DESC;
this query will bring up a few records for each shop, of the one or more bikes they bought and each bike's price, how do i have these come out to a sum(of the price), so that each bike shop will have a sum of revenue. someone told me i need to use group by and the sum function but i dont know how
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?
I have two queries that give me the total sales amount for the current year, and the last year.
SELECT SUM([Sales (LCY)]) FROM [$Cust_ Ledger Entry] cle LEFT OUTER JOIN dw.dim.FiscalDate fd ON fd.CalendarDate = cle.[Posting Date] WHERE [Customer No_] = '10135' AND fd.CalendarYear = '2013'
[Code] ....
I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.
This is my query without the columns I need:
SELECT c.CustomerNumber ,c.Name ,c.ChainName ,c.PaymentTermsCode ,cle.CreditLimit AS 'CreditLimit' ,SUM(cle.Amount) AS 'Amount'
