Hi,
how can i group data using specified date like for example per month.
here is my sample data.
fielddate | fieldnumber
january 1, 2007 | 1
january 5, 2007 | 2
February 2, 2007| 3
March 4, 2007 | 3
March 5, 2007 | 4
i want to sum all fieldnumber by month
result will be:
resultdate | resultnumber
january 2007 | 3
February 2007 | 3
March 2007 | 7
thnx for the help in advance.
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
Most of the data is in one table.
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
If I have a date column and want to return a columns that sum by each month of the year, what is the best way to do that?
Example
Date Amount
1/3/2007 10
1/7/2007 15
3/4/2007 8
3/21/2007 19
5/33/2007 12
9/6/2007 5
12/8/20007 4
12/12/2007 10
Return:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Amount 25 0 27 0 12 0 0 0 5 0 0 14
Hi All,
I have an orders table which has a filed called OrderTime which is the exact time when the order was received ? I need to write the following queries
1. Get number of orders per year
2. Get number of orders per month (obviously if a month (for example, november) is in a different year, its to be in a different row)
3. Get number of orders per hour
Note: If there are no orders in a year etc, it should return a row with 0.
hi
I tried below but it doesnt work
select date_field from tbl group by DAY(date_field)
I have a table that i want to query and group the records by themonth, what SQL stanment would i use?Somthing like this? apart from i know that the "Group BY (MM/YYYY)"wont work !:)Select Date,NameFROM TableGroup by (MM/YYYY)Does this make sense?Willa
View 1 Replies View RelatedI 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.
View 1 Replies View Relatedhello, how can I query dates using "between" function but grouped by months? for example:
QUERY:
FROM: 15/DIC/2007 TO: 15/FEB/2008
RESULT:
DECEMBER-2007 --- $49,535
JANUARY-2008 --- $45,352
FEBRUARY-2008 --- $52.345
Thanks in advance-!
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.
Any ideas?
I would like to generate the report for finance office to pay the salary for each staff.
I have some mass data in SQL server
STAFFCODE TIME TYPE
101 31/1/1900 09:00:00 IN
101 1/2/1900 03:02:00 OUT
102 31/1/1900 09:00:00 IN
102 31/1/1900 19:23:00 OUT
I would like to calculate each staff each month working minutes.
MONTH STAFFCODE WORKING_MINS
JAN/1900 101 900
JAN/1900 102 623
FEB/1900 101 182
Hi,
I have the following query which is working fine, but its returning a row for each day.
I'd like to modify it somehow so that we had the same data returned, but 1 row for each month. Can anyone offer some insight ?
Any help is much appreciated !!
thanks once again,
mike123
CREATE PROCEDURE [dbo].[select_Stats_LoginHistory]
(
@numDays int
)
AS SET NOCOUNT ON
SELECT CONVERT(varchar(10),LL.loginDate,112) as loginDate,
COUNT(LL.userID) AS TotalLogins,
COUNT(DISTINCT LL.userID) AS TotalLogins_Unique
FROM tblLogins_Log LL WITH (NOLOCK)
WHERE DateDiff(dd, LL.loginDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),LL.loginDate,112)
ORDER BY loginDate DESC
table structure below
CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,
[code]...
How can I get the result orderd by the month number?
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
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
Ok i dont know how to ask this question but here it goes.
I am pulling monthly data for suppliers showing ontime, early and late deliveries
let say month of Jan. they had
supplier month stat qty
A Jan early 5
A Jan late 1
A Jan ontime 20
A feb late 50
A feb ontime 5
I need to retrieve the data but as you can see since feb did not have any early I am missing the data
how can I create a line
A feb early 0
but remember that i have multiple customers and multiple months.
All I can think of is saveing the base data then run a cursor to inser the lines as needed
following table global_usage
ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
select * from tbl where nmonth between datepart(mm,DATEADD(month, -2, getdate())) and datepart(mm,getdate())
this query is returning my data between 1(jan) and 3 (mar)
if i change my query to get all datas between nov to mar
select * from tbl where nmonth between datepart(mm,DATEADD(month, -4, getdate())) and datepart(mm,getdate())
this query is not returning data since the month between 11 to 3
Guys,Help me out to correct this syntax.
Hi,
Writing a report that displays total hours, etc for each day in a given month and I'm having trouble comking up w/ an elegant solution. Tables are set up like this:
table1 (id, start_date,end_date,submitter)
table2( id,table1_id,start_time,end_time, etc)
Each record in table2 is a child to a parent record in table1 and there may be multiple children for each parent. The "start_time" and "end_time" fields are datatype "datetime" and are stored with actual times (not 12:00:00 AM"). I need the report to display the values for each day in a gridview, etc so I can act upon them from the "RowDataBound" sub (more math, etc). The problem that I am running into is that for records that have multiple children for a given day, I need to sum those results and display the summed data as a single row within the GV - can't seem to work that out - I keep getting every child record displaying as its own row in the GV.
Any thoughts? I'm sure it's probably fairly simple but I am still a beginner...thanks as always
Hi All,
I wanna pull a 12 month rolling data and donno how to do that, can anybody help ?
Problem:
lets say if i run the query today i should get data between oct-3-2006 and oct-3-2007, my main colums is monthyear which is date datatype.
Appreciate your help.
Thanks
Chinna
I posted this question last night and thought I had an answer. I have a date field. I want to be able to filter records by the month of the date. To do this, I pass the integer of the month. But I also want to be able to return all the records if no month integer is passed. So functionally,select * from table where (MONTH([AD ENDS]) = @month)will return all records where [Ad Ends] is in January if @month = 1 and all records if @month is empty. The solution I got last night was to use select * from table where (MONTH([AD ENDS]) = ISNULL(@month, MONTH([AD ENDS]))) If @month is null, all records are returned. I was focused on another aspect of the page when this was posted. It worked in the designer, so I thought I was set. This morning I realized I don't know how to pass a null variable in a querystring. Since a querystring is a string, it probably can't be done. Another suggestions was to change this programmatically. But is there a way to do this in the dataset? I'm using SQL Server 2005, a strongly typed dataset and the designer.Diane The answer i got last night was to
View 4 Replies View RelatedI have a series of daily data and I am looking to filter for month end business days and create a sum.
This piece of code gets what I need, arbitrarily, for the 7th day of every month. How can I amend the code so that rather than 7, I have the last business day of every month?
select FROM_DATE, PORTFOLIO, SUM(VALUES) from MyTable
where DATEPART(day,FROM_DATE) = 7
group by FROM_DATE, PORTFOLIO
I have the following code for last business day of month (from a previous question raised on these forums), but when I set DATEPART(day,FROM_DATE) equal to the following piece of code, I get no results.
CASE DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1))%7
WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Saturday subtract one day
WHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)) -- if Sunday subtract two days
ELSE DATEADD(mm,DATEDIFF(mm, 0, FROM_DATE),-1)
END
What query should i use to generate a data of all days on last month, for example, if today is june so last month would be may.
View 3 Replies View RelatedI have a query that pulls data by date so that I have every day of the month but I would like to group it by month so that I only have one entry per month. Can that be done?
View 2 Replies View RelatedHi,
Can anyone tell me how to get data for yesterday, last month, and last year?
As a example, I wanna know how many loans disbursed for yesterday(not for last 24 hours).
It may be smthing like this. But when I run this one , it gave me a syntax error.
WHERE DATEDIFF(dd,Account.CommencedOnDate,GetDate()) = 1
can anyone help me out pls?
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
Hi,
I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month >= 6 AND R.Year = 2006)
Group By R.Year , R.Month )
Union
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month <=3 AND R.Year = 2008)
Group By R.Year , R.Month )
I am in seach of a query where in I can provide month, year and client name and fetch last available comments from the table.
Client,Month,Year and Comments are columns in that table.
For Ex: If i pass client as A, month as 7 and year as 2015, I should get comments for client A, month July and year 2015 if available.
If data not available, it must go to June month and so on until it finds comments.Also when month is Jan, if query is going back, year also should get changed.
Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.
we can show it if its only for previous day. In this case it is like this;
cdtable.SubmittedDate = GETDATE () - 1
But I dont find function like GETMONTH() or smthing.
Thanks
Hi All,
would like to make a report with sales figures per month even is there are no sales figures that month.
Report should still show the month.
Any help to get me started in the wright direction much appreciated
edwin
hi,
Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.
we can show it if its only for previous day. In this case it is like this;
cdtable.SubmittedDate = GETDATE () - 1
But I dont find function like GETMONTH() or smthing.
Thanks
Hi,
Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?
Thanks.