I want to modify the following SQL Query to replace the hard-coded date with 5 years back from the current month (The Current Month minus 5 years).
SELECT FirstName, LastName, LatestHireDate, COUNT(*) as "Latest Hire Date"
FROM dbo.EMPLOYEE
WHERE LatestHireDate > '2013-08-01' and ActiveResourceFlag = 'TRUE'
GROUP BY LatestHireDate, FirstName, LastName;
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
Hi All !
I want to show row groups as hierarchy levels and need the
sub total values belongs to each group and sub group levels. But the
most important point is that my top next top group (from child to
parent ) is not static its dynamic.i.e for a diffrent senario my under
displayed example can have Universe--->Earth as parent for Australia and USA.
eg:
1.Australia
|-------sydney
|-------Melbourne
2.USA
|------North US
|------North US(1)
|------North US(2)
|------South US
|------South US(1)
|------South US(2)
Can I get some help from anybody for making a dynamic row groups in the matrix.
Waiting for a kind help.
Regards,
I'm new to these forums. If this is not the right place to post this question, please let me know where I should ask. :)
Anyway, I have a report that is grouped something like this:
Team
Location
Score
TEAM 1
#Loc#
#Total#
Home
10
Away
14
Away
8
NULL
0
NULL
0
Home
14
TEAM 2
#Loc#
#Total#
Home
10
Away
14
Home
19
NULL
0
Home
14
TEAM 3
#Loc#
#Total#
Away
7
Away
12
For each team grouping, the header columns needs to perform an action on the grouped records to determine the text.
The #Total# header is easy -- here I want a total of the points ( =Sum(Fields!Points.Value) ). No problem.
The #Loc# header is the problem. What I want to do is to count the number of "Home" values in the group (so Team 1 location reads "Home (2 of 6)", and Team 2 location reads "Home (3 of 5)"). If there are no "Home" values, I want to use the "Away" value (so Team 3 location reads "Away (2 of 2)").
Can someone tell me how I can create a custom code function that will allow me to iterate through the values of the grouped records from the group header textbox? I assume I need to be able to create a custom aggregate function of some kind, but I don't quite know where to begin.
Thanks for your help.
Joe
Hi, I am currently trying to create a report the dynamicaly groups from parameters. The grouping part works fine but I need to show the parameter label name or the field referenced by a parameter.
=iif(Parameters!Param1.Value="P",Parameters!Param2.Label,Fields(Parameters!Param1.Value).Value)
This is the expression I have written to do this. The problem seems to be is with the true part of the iif statement. The false displays fine in the group header. The Parameters!Param2.Label also displays if used on its own.
This is the error that is fires back when I run the report.
The Value expression for the textbox €˜textbox4€™ contains an error: The expression referenced a non-existing field in the fields collection.
I am quite new to SSRS and I am using VS2005 pro.
Thanks
D
Hello all!
Does anybody know, if it is possilbe to dynamically change grouping in a table report?
I mean, is it possible to provide the enduser the capability to group data in a table on whatever column he would like?
The second question is related:
Is it possible to make a usersort on a grouped table, in such way, that ALL the rows would be sorted ascended regardless of the group they belong to? (For now the only way I see to do this is to give the enduser possibility to turn off grouping manually)
Hi,
I have a SSRS 2005 report with a table and several groupings. These groupings are made visible with toggle items.
I am trying to determine how to make the associated group headings (entered within table header) visible or not visible depending on the visibility state of the groupings.
Example: (initial state with group1 only visible)
Group1 Heading Count Sum
+Group1 999 999
(click + sign to make visible group 2)
Group1 Heading Group2 Heading Count Sum
-Group1 999 999
+Group2 888 888
etc.. for remaining groups
I've tried toggling the table header column the same way I did the group, but the group scope isn't available so I don't know what to use. (Manually putting it in causes report error.)
I don't know what visibility expression to use because I can't figure out what report item I can look at to determine the group's visibility "status" in order to make an expression.
Any ideas?
Note: I did see a similar post from sadsac, but the "answer" didn't address this.
Thanks
i have a query
select mydate,lastname from users
group by mydate,lastname
now the only thing is mydate is a datetime fields but i want the results to group by just the date for each lastname entered.
How do i do this?
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
==========================================
1000| Software| 2,000.00 | 01 | 2005
==========================================
1000| Software| 2,000.00 | 01 | 2005
==========================================
1000| Software| 5,000.00 | 02 | 2005
==========================================
1000| Software| 5,000.00 | 02 | 2005
==========================================
1001| Hardware| 10,000.00 | 02 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description | Hardware | Software | Total
=======================================
Opening Balance | 0.00 | 4,000.00 | 4,000.00
Period 02 | 10,000.00 | 10,000.00 |20,000.00
=======================================
Closing Balance | 10,000.00 | 14,000.00 |24,000.00
The above report has 4 columns, with 1st & last column being a calculated field.
Notice that the Opening Balance description is a sum of all amount under period 01,with software having a total Opening Balance of 4,000.00 (2,000.00 + 2,000.00).
The amount for both Hardware & software are group by period 02, and a Closing Balance is derived by adding the sum of Period 01 & Period 02.Total column is a summation of
both Hardware & Software amount.
Guys, hope someone out there can help me with the sql command for the above report?
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Software| 2,000.00 | 01 | 2005
===================================================
1000| Software| 2,000.00 | 01 | 2005
===================================================
1000| Software| 5,000.00 | 02 | 2005
===================================================
1000| Software| 5,000.00 | 02 | 2005
==================================================
1001| Hardware| 10,000.00 | 02 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description | Hardware | Software | Total
====================================================
Opening Balance | 0.00 | 4,000.00 | 4,000.00
Period 02 | 10,000.00 | 10,000.00 |20,000.00
====================================================
Closing Balance | 10,000.00 | 14,000.00 |24,000.00
The above report has 4 columns, with 1st & last column being a calculated field.
Notice that the Opening Balance description is a sum of all amount under period 01,
with software having a total Opening Balance of 4,000.00 (2,000.00 + 2,000.00).
The amount for both Hardware & software are group by period 02, and a Closing Balance
is derived by adding the sum of Period 01 & Period 02.Total column is a summation of
both Hardware & Software amount.
Guys, hope someone out there can help me with the sql command for the above report?
I have a table with a datetime field and a username. the following gives me a count of the individual users reagrdless of how many entries there are for them.
SELECT COUNT(DISTINCT Log_Username) AS Expr1
FROM tblUserLog
However, I also want to have a total of distinct users for each date - how do I do this ?
TIA
hi all..
my group by clause has to select date.. problem is it differentiate
2006-01-23 15:45:34.000 and 2006-01-23 15:46:55.000 ..
As we all know, for sure we need to group all the 23 jan date into one without bothering the time, so how do i let the query pick only one line for date 23 jan?? thankss in advance...
~~~Focus on problem, not solution~~~
Hello SQL Gurus,
Here is my challenge...
I have a database with a table that has a date field in numerical format, ie. 20071231 (YYYYMMDD).
the table lists Invoice numbers, their date, value, customerID, etc.
what would the query look like that shows the sum of value of invoices, for a customer, grouped by each month.
so a recordset that looks like
month, customerid, sum(invoicevalue)
200708, 113, $1000
200709, 113, $1111
200710, 113, $1010
I cannot execute a dynamic SQL function when I convert a datetime to a string:
declare @date_key datetime
set @date_key = '5/1/08'
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI'
,'Execute dbFinance..spCalc ''' + convert(varchar(20), @date_key, 101) +'''')
The code below produces the exact same text but DOES work:
select * from OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes; Integrated Security=SSPI','Execute dbFinance..spCalc ''05/01/2008''')
Hi,I have a datetime column named dtDateTime.its format is "Oct 27 2006 12:00:00 "I want to group by only date part of it and countmy code is$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),count(convert(varchar,J1708Data.dtDateTime,120))FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleIdWHERE (J1708Data.iPidId = 303) AND(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')AND (Vehicle.sDescription = $VehicleID)GROUP BY convert(varchar,J1708Data.dtDateTime,120)";However, convert part, group by part doesnt' work at all.(i couldn't check count part)can you find where's the problem?Thx.
View 2 Replies View RelatedI am creating a query in MS Access. My table looks like this:
Wrk, Sample, Y
0805393, 01, 5/16/08
0805393, 02, 5/16/08
0805393, 03, 5/16/08
0805393, 03, 5/17/08
0805393, 03, 5/17/08
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
Hello,
I am curious if you can create a subscription for a report that resides on reports manager with a dynamic date? We have a couple of reports on reports manager that require a date value and I tried to use getdate() for the value but it will not except it. Is there a way to have a subscription do something like this so the end user doesn't have to change the date parameter each time? Thanks in advance.
John
I am trying to to a transaction count (per day) running a stored procedure that does a group by date. The problem is that since the date has a time stamp (I assume) it sees each date as a different group even if it's the same day. Is there a way to format the date in the stored procedure so that it sees all of the transactions on the same day as one or is there another way to do this.
Select count(recordid),transactiondatefrom sometable group by transactiondate
Thanks
in advance
Hello
I have a Table
Code Name Gender DateJoined
Now when i Group by Name i get
Name Male Female
jasldk 1 2
But the problem i have is i need to filter the records based on Date joined, but i cant include the DateJoined in my Group by clause ( i am making the Groupby As View) and since my view dont contain the date i couldnt use that as view to use in my application.
Is there any other way i can achieve this?
Thanks in advance
'
With Regards
Hi,I have a tricky SQL query problem that I'm having probs with.I have a table which resembles something like thisDate | Price1 | Price2 | Price301 Jan 2006 | 100 | 100 | 10002 Jan 2006 | 100 | 100 | 10003 Jan 2006 | 100 | 100 | 10004 Jan 2006 | 115 | 100 | 10005 Jan 2006 | 115 | 100 | 10006 Jan 2006 | 115 | 115 | 11507 Jan 2006 | 115 | 100 | 10008 Jan 2006 | 100 | 100 | 10009 Jan 2006 | 100 | 100 | 100and I want to write a query/view that will return this
Quote:
hi all,i got is table:Id StartDate EndDatea 19/03/2001 18/03/2002a 19/03/2002 18/04/2002*b 13/08/2000 12/08/2001b 13/08/2001 12/08/2002b 13/08/2002 10/07/2002*Sort command and groupins i am ok but i need to select only the records thathas the latest enddate. (See *)any ideas? thanks in advancerashid
View 1 Replies View RelatedI have 1 table named BookMe,
with fields
$Date, $Div, $Name
I want to select the top N number of Div's (ie highest Div value) per distinct Date.
i know how to get the distinct date like this:
SELECT DISTINCT $Date FROM BookMe ORDER BY $Date
How do I combine a TOP keyword to get the TOP n rows per distinct date???
Thx!
I'm trying to show the max date from a field by group. I've tried Max(OrderedDate) but it still shows me all the dates from the group field I only want to see the last date. I'm using report builder FE, which I can't modify the BE. Any ideas I can get this to work?
View 1 Replies View RelatedI am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this
declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)
But this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month. I am having a bit of trouble formatting, how to accomplish this.
Hi,
I have a parent package which accepts date as input. I can configure Set Values of Execute Package Utility with hard coded date value and it works fine. My question is how do I configure Set Values to accept dynamic date value or current date value (may be using GetDate()) ?
Thanks in advance.
Prabha
Hello,
I have an OLEDB Source component with a Oracle OLEDB connection manager.
In my SQL statement I must do something like this ...
SELECT * FROM OracleTable
WHERE convert(datetime, OracleDate) = parameter
I've dynamicaly build the statement like this : http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
Unfortunately this doesn't work because the variable is a datetime value and not a string.
Maybe another way is to store the date in a table in my SQL database and include it in my Oracle SQL statement ? This would mean that there are multiple connection managers being used in one SQL statement ... I have no idea if this would be possible ...
Any other options ?
Hi all,
I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.
I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.
I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply:
The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)
Thanks in advance.
Looking to see if thier is a better way to find the last record entered in a group of records.
What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.
Table example using simplied data.
PolId
CoveragId
EffDate
Status
Limit1
2
1
9/7/2007
a
10000
2
2
9/7/2007
a
150000
2
2
10/1/2007
a
200000
3
1
9/7/2007
a
10000
The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.
The current query returns a single value. from a single table.
Current code used in the select protion on a larger query. bpi = basicpolicyInformation.
( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)
Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.
How to select the max row/date per group. I am planing to do at report design level(i.e, trying to put filter condition in group properties).
View 2 Replies View RelatedHi all,
hopefully someone can suggest the best way of implementing the problem i am trying to resolve. We have a table which contains rows relating to tests run on our product. This table is populated from an SSIS job which parses CSV files.
There are multiple rows per serial number relating to multiple tests. The only tests i am interested in are the ones with an ID of T120. Here is the query i have so far which should make it a little easier to explain:
SELECT [SerialNumber]
,Param1
,[TimeStamp]
FROM [Build Efficiency System].[dbo].[SSIS_SCANNERDATA_TBL]
WHERE Test = 'T120'
GROUP BY SerialNumber, Param1, [TimeStamp]
ORDER BY SerialNumber
What i have above is fine to a point. The problem i am encountering is that in test T120 it specifies a part which can be be one of about 6 in field Param1. If during testing there is a problem with the part then it is replaced and the test run a second time up until the whole product passes the test. The query above returns all instances of replacements so i may have the out put as follows:
SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03
What i really need is to only list the last part that is installed, hence the one with the latest timestamp:
SerialNumber Param1 TimeStamp
0 Part1 15/03/07
0 Part2 16/03/07
0 Part3 15/03/03
Can someone please help me to alter the above query so that it will show only those Param1 fields that have the latest date for each part.
Many thanks in advance,
Grant
I am working on FTP TASK in SSIS Package. i have to get files from FTP that file names are like 20141110.txt. i want to download any particular date file from ftp. How to i set expression in Remote path?
View 3 Replies View RelatedI am attempting to create a subscription that will send a report whose name would have been formatted to the following: ReportName_MMDDYYYY.
I know this can be done by creating a Windows File Share subscription type, but in this case, I would need to use E-Mail.
In this scenario, the user will receive the email and included, the file formatted as described above.
Is there a way to get it done ?
I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.
Here is my query
SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4,
[8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8,
[12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12,
[4/1/2008 12:00:00 AM] AS Month13, Engineer
FROM (SELECT Client, DollarsBilled, SlipDates, Engineer
FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM],
[6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM],
[12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt