How to write sql query that will filter between two dates (user input in text box1 and text box2) in the below sql statement .
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1 UNION ALL SELECT 'Table2', SUM(a) - SUM(b) FROM table2 UNION ALL SELECT 'Table3', SUM(a) - SUM(b) FROM table3
SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
I want to filter a table for data between two dates.
Start Date and End Date are the two dates.
The query,
1. should not filter based on date if the dates are not provided.
2. Should give all records on or after the start date if only the start date is given.
3.Should give all records on or before the end date if only the end date is given.
4. Should give all records on or between the start and end dates if both dates are given.
I tried something like this.
@startDate datetime =NULL ,
@endDate datetime = NULL
AS
BEGINSET NOCOUNT ON;
SELECT * FROM TABLE
WHERE ((TICKET.CREATED_DATE >=@startDate AND TICKET.CREATED_DATE<=@endDate ) OR (@startDate IS NULL)OR (@endDate IS NULL)OR(@startDate IS NULL AND @endDate IS NULL))
Not working could someone give me the correct query.
Thanks
i want to filter the data on specified dates. (between dates).
SELECT a.UserID, b.Name, b.EmployeeNum,b.Department, MIN(a.TransactionTime) AS TransactionTime,a.TransactionTime as TR_Date,
min(CONVERT(CHAR(20), TransactionTime, 113)) As TransactionDateTime
FROM NitgenAccessManager.dbo.NGAC_AUTHLOG AS a INNER JOIN NitgenAccessManager.dbo.NGAC_USERINFO AS b
ON a.UserID = b.ID
WHERE (a.FunctionKey = ' ')
[code]...
I am facing an issue in TABLIX FILTER . I have a table having a column with dates like
01Feb2015
03Jun2014
I need to add a filter in SSRS tablix to get the dates < 01Mar2015
How can I add it in tablix filter as if i directly use my column name and filter value as 01Mar2015 it is not giving results
Secondly, I have another column with date like : 2015-02-04 00:00:00.000
I need to filter the same in another tablix for dates < 01-Mar-2015
I tried this in expression to convert it into string
=Format(Fields!Last_Date.Value, "YYYY-MM-DD")
And used 2015-03-01 in filter but it did not work
If I dont use format, ssrs gives error that tablix cannot compare datetime and string...
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies View RelatedHi,
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday
FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate
WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Any advice will be greatly appreciated!
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
ADDIS
Q1
Jan
1
1
2008
NULL
NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev
FROM dbo.EntityDef INNER JOIN
dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN
dbo.CurrentDate INNER JOIN
dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate
WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')
ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate
I appreciate all your help
Thanks
Hello I am working in an advertising company and I have to find all records who have advertised 7 days back but if one person has advertised more than once means for different products, his name should come only once. How can I filter this. I am totally new to this language I can write simple query but don’t know how to filter it.
Name IndividualID Product Date
A 1 x 2007-01-27.23.59.59.777
A 1 y 2007-01-28.00.00.00.000
b 2 xx 2007-01-28.00.00.00.000
A 1 z 2007-01-28.00.00.00.000
b 2 yy 2007-01-28.00.00.00.000
A 1 p 2007-01-28.00.00.00.000
c 3 m 2007-01-28.00.00.00.000
d 1 n 2007-01-28.00.00.00.000
A 1 s 2007-01-28.00.00.00.000
Hi,
I have a supllier named K O'Neill and i want to filter on his name in my supplier table . Is this possible ?
When I try Vend Name = 'K O%Neill' it does not work.
Can anyone help?
Thanks.
I need a query that returns every n record from the database and the last record.
For every n-th is easy, but I need a
WHERE Index % n = 0
but I need a last one too.
Any Help.
Hi I am wandering if it is possible to achieve the following:
I am using Reporting Services and Reporting services I got a report with a text parameter.
I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...
I am doing the following:
Filter([DIM Product].[Product].ALLMEMBERS,
,IIF(INSTR(@Product, "*") > 0
,[DIM Product].[Product].currentmember.name = Replace(@Product, "%", "")
,INSTR([DIM Product].[Product].currentmember.name, @Product)
)
)
The problem ist that the function Replace does not work!
Does someone know how to do it?
What is the syntax for using IN as a filter? in SQL it would be IN( 'A', 'B', 'C') but this does not work in Reporting services.
View 4 Replies View RelatedIn a SQLDataSource, the following line throws an error as an undefined function. FilterExpression='left$(lname,1) = "D"'
Isn't this a standard VB function and why does it produce an error. How do I fix?
Thanks
Hi,
I am new in framework 2 and I can't find a way to filter the sqldatasource.
I have an sqldatasource control that retrive data from data base-"Select * from myTable"
I set the fiterExpression property-sqlDataSource1.FilterExpression="ID='" + strID + "' " ;
I don't know how to continue from here.If I bound the sqlDataSource1 to a control like gridView it works good and I see the filter oparation. but I want to get the result set in the code and loop threw it like I did with ver 1.1 with sqldataReader:
While sqlDatareader1.Read { myCode ... }
How can I do it with sqlDataSource ?
Thanks,
David
I have two tables A and B. I want to insert data into tables C and D based on join between A and B (A.column = B.column). What is the best way to accomplish this type of task?
IE-------> If column = xyz then insert into table C
IF column = abc then insert into table D
ELSE do_not_insert
Please help.
Regards
Ziggy
I have been running traces in SQL Profiler and have not been able to get the filtering to work correctly. I am interested in capturing only statements that use CPU and have setup the filter for CPU > 0. It still returns all of the rows that have Null. Is there any way of filtering out the rows that have nunll for CPU?
Thanks,
Ken Nicholson
I'm an MDX newbie.. with some help I was able to create a query that returns accurate results. All I need to do now is filter for Completed Work value of greater than 0.
I have tried to use the filter wizard in VS2005, but I have never gotten the desired results.
Platform Info:
SQL Server 2005 Standard
VS2005
---------Start Statement----------------------------------------------------------
WITH
MEMBER [Measures].[Completed Work by WI on dt1] AS
(
[Assigned To].[Person].CurrentMember,
[Work Item].[System_Id].CurrentMember,
[Date].[Year Week Date].[Date].&[2006-04-23T00:00:00],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work by WI on dt2] AS
(
[Assigned To].[Person].CurrentMember,
[Work Item].[System_Id].CurrentMember,
[Date].[Year Week Date].[Date].&[2006-04-29T00:00:00],
[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]
)
MEMBER [Measures].[Completed Work] AS
[Measures].[Completed Work by WI on dt2] - [Measures].[Completed Work by WI on dt1]
SELECT
NON empty
{
[Measures].[Completed Work]
}
ON COLUMNS,
NON EMPTY
{
([Assigned To].[Person].[Person],[Work Item].[System_Id].[System_Id],[Work Item].[System_Title].[System_Title])
}
ON ROWS
FROM
[Team System]
----------End Statement---------------------------------------------------
I got a field called Date#, datatype = datetime, default value = getdate().
So after i insert a record, the Date# will auto record the system date and the time. And my MS SQL Server show: 6/9/2004 12:24:35 PM
However i am not able to filter my record by date. The statement i tried are:
SELECT * from mytable WHERE Date# = '6/9/2004 12:24:35 PM'
SELECT * from mytable WHERE Date# = "6/9/2004 12:24:35 PM"
SELECT * from mytable WHERE Date# = 6/9/2004 12:24:35 PM
SELECT * from mytable WHERE Date# = '6/9/2004'
Pls help me,
Thanks a million
HI all friends
I have a Fact table like this
DIMENSSIONSMEASURES
------------------------------
idempidprodcutsalessalesws
I just want make the next query with MDX
SELECT sales
FROM myFactTable
WHERE salesws > 0
Note that salesws is a measure not a dimmension
How it would be in MDX? I need a lot of help
Regards!
Hi,
I need to filter my table to return data between 18:30 and 7:00
I am not sure how to do the half hour and span over midnight.
I think this might work for 18:00 and 7:00.
SELECT *
FROM MyTable
WHERE datePart(hour, Timestamp) Between 18 AND 24 Or datePart(hour, Timestamp) Between 00 AND 7
I would appreciate any help!
Thanks
I have a table with a varchar in it.
Inside the var char it contains data like:
Variable.[32] + Variable.[432] * (Variable.[333] / Const.[21])
What I am trying to do is search for all records that are using variable 333
I was trying:
SELECT * FROM my_table WHERE data_field LIKE '%Variable.[333]%'
However, that returning nothing.
I am going to guess that the [ ] need to be escape or something, but I don't know if thats really the issue.
Doing:
SELECT * FROM my_table WHERE data_field LIKE '%Variable%'
Works.
Thanks in advance,
Rich
I have an sql script that has 2 main blocks of Where filter which I'd call as Filter1 and Filter2 as follows:
Select
DisplayColumns...
From
InterestingTables
Where
(Filter1) --W1
AND --W2
NOT --W3
(Filter2) --W4
Note that Filter1 and Filter2 are composite filters (inner filters nested; it shouldn't matter as long as I have the outer parenthesis as above, right?).
Now, say SetF1ExF2 is the result set of the sql script above.
Additionally, let's say I obtain the result set SetF1 by having W2 to W4 commented out and SetF1AndF2 comes from commenting out W3.
Shouldn't the following hold: SetF1AndF2 = SetF1 - SetF1ExF2
I am having a discrepancy between the values with SetF1AndF2 < SetF1 - SetF1ExF2.
TIA.
I've got an MDX query I need to modify, but not quite sure how to proceed. I essentially know most of tye syntax, but am at a loss as to where the components of the syntax should appear in the query. Here is the original query:
Code:
WITH
MEMBER [Employee].[FTE vs FL].[CF Total] AS AGGREGATE([Client Facing Disciplines])
MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[Trailing 12 Months] AS AGGREGATE(LASTPERIODS(60, STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
MEMBER [Time].[Year - Half Year - Quarter - Month - Date].[YTD] AS AGGREGATE(YTD(STRTOMEMBER(@SelectedMonthEx, CONSTRAINED)))
[Code] ....
This is how I'm sure the two lines that say what should be excluded, I'm just not sure where they should go. I believe a FILTER statement is what I need, but not sure how to format it.
[Employee].[FTE vs FL].[Discipline].&[LOYALTY],
[Employee].[FTE vs FL].[Discipline].[Department].&[OLSON PR Chicago]
MDX is definitely not my strong point.
Hi ,
account_desc_out,Analysis_Desc_out,Sub_Analysis_Desc_out,debit_out,
credit_out,sub_Analysis_Code_out,Analysis_Code_out
-----------------------------------------------
11066-0001-01 GEN 6633 0.00000000 160.000000006633GEN
11066-0001-01 GEN 6633 160.00000000 0.000000006633GEN
When i run the below query the o/p is comming as above:actually no record should come bcz debit amt and credit amt should knock off .can anyone correct the below query:
select
account_code_desc as'Account_Desc_out',
analysis_code_Desc as'Analysis_Desc_out',
subanalysis_code_desc as 'Sub_Analysis_Desc_out',
case when drcr_flag='dr' then sum(isnull(base_amount,0)) else 0.00 end as 'debit_out',
case when drcr_flag='cr' then sum(isnull(base_amount,0)) else 0.00 end as 'credit_out',
subanalysis_code as 'sub_Analysis_Code_out',
analysis_code as 'Analysis_Code_out'
from Analysis_stg_tb as stg with (NOLOCK)
where subanalysis_code_desc='6633-Goverdhan S'
group by subanalysis_code,analysis_code,
account_code_desc,analysis_code_desc,subanalysis_code_desc,drcr_flag
having sum(case when drcr_flag='cr' then isnull(base_amount,0) else 0.00 end) -
sum(case when drcr_flag='dr' then isnull(base_amount,0) else 0.00 end)<>0
order by Account_Desc_out,Analysis_Desc_out,Sub_Analysis_Desc_out
Is there a difference if I use filter in the join instead in where clause:
1)
select s.*
from students s
join courses c
on s.ssn = c.ssn
and c.type = 'fresher'
2)
select s.*
from students s
join courses c
on s.ssn = c.ssn
where c.type = 'fresher'
Thanks...
Hi every one,
I want to filter application name in sql profiler, so I write SQL% (for every one who connects by sql qery analyzer) in "application name" filter box but it doesn't work correctly,and shows all applications. Why it works incorrect?
Thank You
Kini
Ok I am experimenting with the data in this database and trying to determine the best way to write this query. I need to find patients that visited in 2005 or 2006 but have not come back since. At first I was doing it looking at patients that have only came in one time ever and that being in 2005 and 2006 but I think I may be missing some patients. I want to narrow it to patients that came in during 2005 and 2006 but have not come back since but I think I am just getting an unduplicated list of patients that visited during those years. Am I on track to do this with the query I have written and if so any ideas?
With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth
from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
where year(b.create_timestamp) IN (2005,2006)
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth)tmp
/*where tmp.countenc <=1*/
)
select description,mstr_list_item_desc, count(*)
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,year(b.create_timestamp)as YOS,f.mstr_list_item_desc
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
cross apply(select top 1 payer_name,payer_id
from person_payer
where person_id = tmp.person_id
order by payer_id) d
join payer_mstr e on d.payer_id = e.payer_id
join mstr_lists f on e.financial_class = f.mstr_list_item_id
where c.description = 'Leon MD, Enrique' /*and
year(b.create_timestamp) IN (2005,2006)*/
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp,
f.mstr_list_item_dessc)tmp2
group by description,mstr_list_item_desc
Thanks in Advance!
Sherri