|
Hello friends,
I need query which will check user supplied date range is in between the existing table startdate and enddate.
if any of the date of user supplied date range is in between the tables start date and end date,it should retrun that record from table.
for example user supply date range is from 1 may 2012 to 5 may 2012.
then query must check that
1 may 2005
2 may 2005
3 may 2005
4 may 2005
5 may 2005
(all dates) is in between startdate and enddate of existing table.
please reply...Thanx in advance...
|
|
|
|
|
select * from tablename where startdate>=@startDate and enddate<=@endDate
Initialize parameters
@startDate and
@endDate with values
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Blue_Boy wrote: where startdate>=@startDate and enddate<=@endDate
Probably not a good idea. One end should be inclusive and the other exclusive, for example.
where startdate>=@startDate and enddate<@endDate
The reason for this is that for a range check you do not want the same value to show up twice in two 'different' ranges.
|
|
|
|
|
SELECT *
FROM tablename
where insertdate BETWEEN @startDate and @endDate
this is simple but it won't contain @endDate in result set.
For that you need to use DATEADD(datepart, number, date) fuction
Since your query will be
SELECT *
FROM tablename
WHERE insertdate BETWEEN @startDate and DATEADD(d,1,@endDate)
Thanks,
Vishal K
|
|
|
|
|
Instead of usign Between Operator, you should use FromDate >= @FromDate
AND EndDate < DateAdd(D, 1, @EndDate )
(It always perform the accurate process, specially in case when Date Column accepts dates also)
Remember EndDate must work with LessThan and FromDate performs on GreaterThanEqualTo
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
I have knowledge in
Basic SQL queries.
SP
function
view
Triggers
shall i chose my career as sql developer side
Thanks & Regards
Arul.R
"The greatest lesson of life is that you are responsible for your life."
modified 30-Apr-12 3:09am.
|
|
|
|
|
Sorry, but this is a technical forum, it's unlikely we can offer useful career guidance. You need to decide these things for yourself based on your knowledge, experience and the job vacancies open to you.
Binding 100,000 items to a list box can be just silly regardless of what pattern you are following. Jeremy Likness
|
|
|
|
|
Yes sure..being a newbie to technology..you can opt whatever strikes you better.
SQL is also a good option.
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
You need to do whatever it is you enjoy. Do not let the market determine what you decide on. It is ever changing.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
well, It is also a good option and later on you can master other skills as well like SSIS,SSRS etc..
regards
Vatsa
www.objectiveprogramming.com
|
|
|
|
|
I want to learn complicated store procedure. I have needed some table and different and complicated syntax of store procedure as example where I show result to execute syntax.
|
|
|
|
|
And you question is what?
First you need a complicated data structure to work with, then you need a detailed requirement and then you need to work through examples, read books and learn TSQL (or the flavour of the database you choose to work with).
Good luck!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
See here[^] for some useful sources.
Binding 100,000 items to a list box can be just silly regardless of what pattern you are following. Jeremy Likness
|
|
|
|
|
Get the AdventureWorks database if you are using SQL Server. Then get any book on T-SQL or Stored Procedures and start to work.
|
|
|
|
|
|
Assume @Code is a three character prefix, ie: AUS
Why does this break:
DECLARE @TEMP VARCHAR(7) =
(SELECT TOP 1 CustCode
FROM Customer
WHERE LEFT(CustCode, 3) = @Code)
with the following error?
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
I suspect there are more than 1 record matching your @code filter and you are trying to stuff more than 1 record into @Temp.
Nope - thats not it, you have top 1. It works for me on sql server 2008 R2. I used this
DECLARE
@Code VARCHAR(3)
SET @Code = '103'
DECLARE @TEMP VARCHAR(7) =
(SELECT TOP 1 filename
FROM dbo.ImportFiles
WHERE LEFT(filename, 3) = @Code)
SELECT @Temp
Which gives me the first 7 characters of the filename (all the file names start with 103). Changing @Code returns null
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for looking Mycroft - seems the issue is a calculated field (CustCode) that is part of the table definition.
CREATE TABLE Customer(
...
CustCode AS udf_GetCustCode(@Name),
...
I think this needs to be calculated as a udf during the INSERT.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Andy_L_J wrote: seems the issue is a calculated field (CustCode) that is part of the table definition.
Probably fired off by a trigger - triggers how to completely f*** your database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a table that stores transactions for income and expenditure. the fields of the table is shown below
transid, accountcredited, accountdebited, date, amount. Every transaction has double entry; it debits one account and credit another.
The accountcredited and accountdebited are linked to accounts table(the fields for accounts table are accid, accountno, balance)
The problem is I cannot figure out sql statement generate running balance
Thanks in advance
|
|
|
|
|
Try a search [^] this question has been answered so many times before.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I guess you have to use SUM and GROUP BY, by the way your question is not enough clear.
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi
Need to know how to connect to Oracle (11g) database from Log4net.config (using appenders) file. As I need to wright the logs to the Oracle (11g) database when any user logon to the web application.
Note: I am succesfully able to wright the logs to the text files but not able to wright the logs to the Oracle (11g) database.
Any help is greatly appreciated.
Regards
Bala
|
|
|
|
|
Try this[^].
Bastard Programmer from Hell 
|
|
|
|
|
As part of my degree I recently submitted an interim paper and one of the feedback comments from the tutor was along the lines of "consider asking the opinions of others on the database, it is not cheating as long as you properly reference your source". Fair point I thought so here I am.
In my submission I had written about the two options I had considered for the database model and why I had elected to choose the one I did. So the question arises from this, what other options are there or what would your preferred approach be?
Basically, the project is looking at migrating an existing file based process across to a web solution, and part of the system relates to the daily reporting of end of period figures and status.
At the end of each daily reporting 24 hour period, a whole bunch of items are recorded, and these are recorded for more that one site, and not every site records the same metrics, but the majority of them are all the same.
As it stands at present there are approximately 30 parameters recorded, some numerical, some textual.
The first option I considered was row per site per period, with each column representing a metric (I will just call them param, but in reality they have meaningful names).
<id><site><period-end-date><param-n><param-n+1><param-n+2>............etc.
The second option was splitting the KPIs into groups, and have table per group with one master record identifying the site and period in another table.
Master Table
<id><site><period-end-date><KPI-group-A-ID><KPI-Group-B-ID><KPI-Group-C-ID>
KPI Group A Table
<id><param-n><param-n+1><param-n+2>............etc.
KPI Group B Table
<id><param-n><param-n+1><param-n+2>............etc.
KPI Group C Table
<id><param-n><param-n+1><param-n+2>............etc.
I originally have coded the models using code first approach (I'm using EF and MVC3) using option 2, as thought it would be easier to manage and allow for easier scaling, and appears to work well.
The part I am adding in at the moment is a losses breakdown for period, so each site might have multiple entries in a loss table linked to the Master Record in a one-to-many.
So the question is, particularly to any DBA's or architects, how would you or what suggestions would you have on how best to build the database to meet the needs, allowing for potentially more kpi parameters or groups to be added in the future.
Any thoughts greatly appreciated.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
