Query - Between To Dates And Between To Times
Jul 24, 2001
Hi there, I'm a little bit stumped on this one.
I have a column in a table that records when the date and time of an event took place.
Table Name: Chronicle
Column Name: Created (of type DateTime)
I would like to select the Chronicle records that are between two dates.
(e.g. 1 May 2001 and 20 May 2001)
And I would like to select those records that are between two times.
(e.g. 6:00am and 1:00pm)
Does anyone know how to do this or have any pointers for me?
I can see it would be easier if I had the date in one column and the time in the other.
Can it be done without doing that?
cheers,
Hamish
Hamish Norton
hamish@liftrider.com
View 1 Replies
ADVERTISEMENT
Jul 27, 1998
Anyone know of an easy way to work with datetime columns ignoring the time part.
I want to "group by" the date so it is vital that the time part goes.
But I still want to work with the resulting date as a date (i.e. use date functions) so I would prefer to keep the datetime data type.
View 1 Replies
View Related
Aug 29, 2006
Jeff writes "I am trying to write a query to retrieve information that was processed between two times of the day (2:00 pm to 6:00 pm) from a date range... I am perplexed; I am new to SQL and am trying not to become frustrated, but this seems to be beyond my skill level. Any guidence would be greatly appreciated!"
View 8 Replies
View Related
Jan 29, 2008
I have a tricky question to Microsoft SQL Server 2000/2005. I have failed to find a solution to the problem yet and others have failed too. So I thought that I throw it around here, because the fact that a lot of knowledgeable DBAs hang around in this forum.
I am looking for a script/stored procedure that is able to show me upcoming job executions for a selected date/time range based on the current settings for the jobs configured on the Database server.
Example
Date/Time From: 2/16/2007 00:00 a.m. (Friday)
Date/Time To: 2/17/2007 12:00 p.m. (Saturday)
A. Job 1 / Schedule 1 = Week Days, 1:00 a.m.
B. Job 1 / Schedule 2 = Saturdays, 10:00 a.m.
C. Job 2 / Schedule 1 = hourly, between 10:00 a.m. and 1:00 p.m.
D. Job 3 / Schedule 1 = every 3rd Saturday of the month, 5:00 a.m.
E. Job 4 / Schedule 1 = every 1st Friday of the month, 8:00 a.m.
The routine I have in mind would not return Job E, because 2/16/2007 is the 3rd Friday of the month and not the first.
Job A: 2/16/2007 01:00 a.m.
Job B: 2/16/2007 10:00 a.m.
Job B: 2/16/2007 11:00 a.m.
Job B: 2/16/2007 12:00 p.m.
Job B: 2/16/2007 01:00 p.m.
Job D: 2/17/2007 05:00 a.m.
Job A: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 11:00 a.m.
Job B: 2/17/2007 12:00 p.m.
Output
It needs to return the Job ID, the Job Name, the Schedule ID, the Date and the Time.
Disabled Jobs and Schedules are by default excluded from the selection, but an option to include or exclude those would be a bonus.
Information such as the min, max and average execution time would be great too.
Notes
The schedules of Job D and Job B overlap as you can see in my example above.
This happens only once per month though. I have over 20 jobs with sometimes very frequent execution times, like every 5 minutes or every 20 minutes and jobs that run hourly, daily, weekdays only, weekends only, monthly once etc.
Purpose
I want to do two things.
I want to determine where jobs overlap, not just by start date/time, but also by average run time and maximum run time.
I also want to be able to generate a report that shows me what should have been done and what was actually done by the jobs (note on the site, 6 of the jobs create new jobs on the fly for other database servers and this is sometimes not happening properly, without getting any error message. The volume of jobs makes the manual search like a search for a needle in a haystack.)
Findings so far:
I did some digging myself and found following stored procedures that do some of the steps that I need and involved tables for the calculation.
Stored procedures:
- sp_get_schedule_description (in db: msdb) (undocumented stored procedure)
- sp_add_schedule (in db: msdb) http://msdn2.microsoft.com/en-us/library/ms187320.aspx
Tables:
- msdb.sysjobs
- msdb.sysjobschedules
- msdb.sysjobhistory
It is not a problem to determine the next execution of a job, but that is not what I need, anyway.
The problem is that it does not help you to determine all upcoming execution times, if the selected timeframe is long enough that SQL Server executes the job more than once.
There is no way around using the sysjobschedules table and calculate the execution dates and times based on the configured settings. See the Stored procedure: sp_get_schedule_description.
That one breaks down nicely the settings as documented for sp_add_schedule at
http://msdn2.microsoft.com/en-us/library/ms187320.aspx, but it does not allow the determination of the exact upcoming dates and times when the job is supposed to be executed.
Another Example
If there is only one job scheduled to run
1) every 5 minutes,
2) on every weekday
3) between 1:30pm and 2:00pm
You would get the following results
1) start date/time 7/7/2007 12:00pm, end date/time 7/8/2007 2:00pm
nothing, because the 7/7/2007 and 7/8/2007 are on the weekend
2) start date/time 7/5/2007 12:00pm, end date/time 7/5/2007 1:45pm
7/5/2007 1:30pm
7/5/2007 1:35pm
7/5/2007 1:40pm
7/5/2007 1:45pm
3) start date/time 7/5/2007 1:45pm, end date/time 7/6/2007 3:00pm
7/5/2007 1:45pm
7/5/2007 1:50pm
7/5/2007 1:55pm
7/5/2007 2:00pm
7/6/2007 1:30pm
7/6/2007 1:35pm
7/6/2007 1:40pm
7/6/2007 1:45pm
7/6/2007 1:50pm
7/6/2007 1:55pm
7/6/2007 2:00pm
Now SQL has a lot more configuration options for the scheduler.
And don't forget that you can have more than one schedule record for any single job, including no-schedule record (which would not interest me).
Autom. when SQL starts freq_type=64
Starts when CPU idle freq_type=128
One Time On Date mm/dd/yyyy at time: hh:mm:ss am/pmfreq_type=1
or
Recurring
Occurs
Daily freq_type=4
Every x day(s)freq_interval=x
Weekly freq_type=8
Every x week(s) onfreq_recurrence_factor=x
Mo [ ], Tu [ ], We [ ], Th [ ],
Fr [ ], Sa [ ], Su [ ],
1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday. Examples: Su and Mo enabled = 3 (1 (Su) + 2 (Mo)), Mo, We and Fr enabled = 42 (2 (Mo) + 8 (We) + 32 (Fr))
Monthly freq_type=16
Day X of every Y month(s)freq_interval=X
freq_recurrence_factor=Y
or
The 1st,2nd,3rd,4th,LAST WEEKDAYfreq_type=32
of every Y month(s)
freq_relative_interval=1,2,4 (3rd),8 (4th),16(last)
freq_interval= 1=Su,2=Mo,3=Tu,4=We,5=Th,6=Fr,7=Sa,8=Day,9=Weekday,10=Weekend day
freq_recurrence_factor=Y
Occurs Once at hh:mm:ss AM/PMfreq_subday_type=0x1
or
occurs Every X Hours/Minutes
Starting: hh:mm:ss A/PM freq_subday_type=0x4 (minutes) or 0x8 (hours)
Ending: hh:mm:ss A/PMfreq_subday_interval=X
active_start_time
active_end_time
Start Date mm/dd/yyy End Date mm/dd/yyyyactive_start_date
oractive_end_date
No End Dateactive_end_date=99991231
Does anybody has a script that does that or several individual scripts that would have to be combined to do what I want to do?
Thanks. I appreciate it.
Cheers!
Carsten Cumbrowski
http://www.sqlhunt.com/
View 14 Replies
View Related
Feb 10, 2006
I have an VB.NET web app which performs a fairly complicated SQL query. It seems in the morning, the 1st time the page is loaded (and query executed) it takes up to 10-15 seconds to complete loading. Sometimes it even times out. However anytime after that, the page loads up (even from another computer) in about 4-5 seconds. Can someone explain the reason for this and how I might fix the load times in the morning?
Thanks
View 1 Replies
View Related
Mar 16, 1999
We recently upgraded from SQL 6.5 to SQL 7. I have a few .sql files that were each running around 5 - 8 minutes under 6.5. These same files now each take over 30 minutes to run. Has anybody had problems with their queries taking longer to run under 7.0? These files are quite large and are comprised of 3 - 4 batches with several queries in each batch. If anybody has any thoughts on the cause please let me know.
Thanks in advance.
View 1 Replies
View Related
Dec 11, 2003
Hi,
I'm in an unfortunate situation. We are posting information using a stored procedure to an outside SQL server connected to through a System DSN on our server (win 2003 server) using php's ODBC functions (we never had any luck connecting directly to the SQL server using php's mssql functions).
Everything is working fine, we can connect, send querys, etc ... but between 1am and 10am we recieve errors when trying to execute queries (though we can connect fine).
Whoops ... forgot to get the error returned before it turned 10am ... I can post it tomorrow
I think the database is being locked, but unfortunately I know very little about MS SQL server
The people who's database we are connecting to are not being helpful ... I was hoping I could get some suggestions on what would be the cause.
Anything you can suggest would be a huge help! Thanks!
- Joe
View 2 Replies
View Related
Nov 19, 2004
We are having a problem with Query Analyzer not connecting to SQL Server anymore. Sometimes it will, sometimes it won't. Sometimes when it does, then you click on the databases drop-down, it may take a long time to return. Likewise, it may take a long time to open the object browser, or it may open without the database info but with just the "Common Objects" info.
All of this used to work just fine.
HELP
View 2 Replies
View Related
Feb 11, 2008
Simple version first:
The basic question is:
what would cause? this query takes minutes or times out:
select xx,xx,xx,xx
from view_xxx
where field in
(
select distinct xxx from table inner join table.....
)
the inner query
select distinct xxx from table inner join table..... takes less than a second
then if I take the results of that query and do this:
select xx,xx,xx,xx
from view_xxx
where field in
(
52193,23523,25233... and so on
)
this later query takes 2 seconds... What is the difference? For more details see the detailed version.
-Robert
now a more details version:
On a SQL2000 machine...
What would cause this query to take forever, or even timeout:
Select distinct VTR.Control_ID from View_ToolResults VTR
Where VTR.Control_ID in
(
------ NOTE INNER QUERY TAKES LESS THAN 1 SECOND
SELECT ORD.CONTROL_ID
FROM Orders ORD
INNER JOIN Providers PR ON PR.SUBJECT_ID = ORD.SUBJECT_ID and PR.SV_OFFICE_ID = ORD.SV_OFFICE_ID and PR.Source_ID = ORD.Source_ID
INNER JOIN VisitTypes VT ON ORD.VISIT_TYPE_ID = VT.VISIT_TYPE_ID and ORD.Source_ID = VT.Source_ID
INNER JOIN HealthPlans HP ON HP.MARKET_ID = ORD.MARKET_ID and HP.Source_ID = ORD.Source_ID
INNER JOIN HPGroups HPG ON HP.MARKET_ID = HPG.MARKET_ID AND HP.SOURCE_ID = HPG.SOURCE_ID WHERE (ORD.Status is not null AND ORD.Status <>'E')
AND HPG.GR_ID in
(
SELECT GR_ID from UserHPGroups Where User_ID= 7
)
AND ORD.RELEASED_DATE between '01/01/2001' and '02/11/2008'
and ORD.RECEIVED_DATE between '07/01/2007' and
'12/31/2007' and ORD.MARKET_ID= 2699431
)
as mentioned above the inner query takes less than 1 second. So if I take the results from that query and do a select like this:
Select distinct VTR.Control_ID from View_ToolResults VTR
Where VTR.Control_ID in
(55836
,55631
,55331
,50992
,51101
,51196
,51500
,52269
,52366
,52384
,52429
,52518
,53117
,53993
,53994
,53995
,54027
,54128
,54192
,54264
,54291
,54292
,54324
,54379
,54578
,54706
,54917
,52622
,54354
,55965
,55410
,55461)
It takes 2 seconds.....
WHAT!!!!!>???!?!?! i don't understand... what is the difference?
The field in all cases is declared as Numeric(10,0)... I've tried to convert to int. I've tried to do temp tables... it doesn't help.
View 10 Replies
View Related
Jan 22, 2008
I have multiple SQLDatasources on multiple pages. When I call the Select command for the datasource the query is run multiple times. I was wondering if anyone had any problems like this. The data is being read into listboxes. If you need any more info or have any specific questions, feel free to ask.
View 6 Replies
View Related
Apr 8, 2006
Dear Friends,
I am in problem & have to solve one query.
I have a one table with the employee time in & time out data, employee can go out & come in fequently in a day.
I want to know that how much time every emp have attend in the company per day.
Kindly, do reply as soon as possible.
I am enclosing data defination in txt file along with the data in the MS Excel file.
Thanx ....
View 7 Replies
View Related
Apr 22, 2008
Hi All,
I have two tables in my database.
I want to insert date and no.of hours worked on that day for a particular project.
So in a week if end user enters 7 dates and hrs for each day......
i have to insert those values into the table against one project only.
Can any one please help me out how to run insert query for 7 times (in a week) with different parameters
Thanks,
Praveen
View 5 Replies
View Related
Jun 12, 2015
I want  the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in jobÂ
Â
INSERT INTO [dbo].[Audit_Active]
([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName])
 SELECT  Â
--DISTINCT Â
p.SPID,
p.LogiName,
p.HostName, Â Â
[Code] ....
View 8 Replies
View Related
Mar 20, 2015
If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:
db1.trn'
End
I want to iterate this query using a loop as many as 5 times max.
View 3 Replies
View Related
Aug 2, 2007
Need help to create a query to count how many times over a specified time the tag(s) equal the value of 1.
Thanks
Gary
View 3 Replies
View Related
Mar 8, 2015
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
View 9 Replies
View Related
Aug 15, 2007
Hi,
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
Thank you,
Michael Tzoanos
View 4 Replies
View Related
Oct 10, 2006
Hi,I have an SQl query that will list the results if it lies between two dates, for example;SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2This returns all results where Date1 and Date2 fall between the Current DateWhat i am looking for is a way to replace the { fn Now() } with a date of my choice.For example;SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2However this does not work.Has anyone any ideas why this may be ??Thanks in advanceAndrew Vint
View 8 Replies
View Related
Mar 16, 2004
How can I query date only in a where clause and not include the time part? When I use the where below if the time part of the date entry has past then the rows are not returned.
WHERE A.Selected=1 AND A.EndDate Is Null OR A.Enddate >= GETDATE()
Thanks,
View 1 Replies
View Related
Aug 3, 2004
Hi, im trying to write a sql query to check if dates are in the db,
ias it is it is telling me the dates are there and their not. any help would be great.
George
here's what i have...
SQL2 = "SELECT * FROM reservations WHERE" _
& "(arvdate >=#" & request("arvdate") & "# AND endate <= #" & request("endate") & "#) " _
& "OR " _
& "(arvdate >=#" & request("arvdate") & "# AND endate >= #" & request("endate") & "#) " _
& " AND idrent = " & idrent _
& " AND confirmation = 1" _
& " ORDER BY arvdate"
View 3 Replies
View Related
Oct 23, 2007
I have two tables: Sales, Consigners
The Sales table includes: date, description, price, consigner
The Consignment table includes: lastday, consigner
I need a query that will display all rows in Sales assuming Sales.date is less than or equal to Consigner.lastday. The .consigner fields need to be joined.
I have this and it isn't working:
SELECT DISTINCT [Sales Data].co1, [Sales Data].amount, [Sales Data].description, Consignment_Data.date AS Expr1
FROM Consignment_Data INNER JOIN [Sales Data] ON Consignment_Data.co1 = [Sales Data].co1
WHERE ((([Sales_Data].[date])=[Consignment_Data].[date]));
This is for MS Access 2003. Can anyone help me? I would appreciate some advice, as my code isn't working at all
View 7 Replies
View Related
Sep 6, 2006
Hi,
I have a query as follows:
SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded')
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)
the results look like this
WeekRetailTotal
MonthRetailOrderNo
SalesWeek
SalesYear
£397.55
8
3
2002
etc etc for each week in a year and then it goes onto the next year.
What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.
I've tried to do a WHERE DATEPART(wk, OrderDate) > @StartDate AND DATEPART(wk, OrderDate)
< @EndDate AND YEar(OrderDate) > @StartYear AND YEAR(OrderDate) < @EndYear
But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.
I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.
Any ideas?
Thanks
View 4 Replies
View Related
Sep 30, 2007
Hello,
I have a sql query that I am requireing to get records on date parameters :
SELECT * FROM table WHERE StartDate >= 2007/09/30 07:00:00 AND EndDate <= 2007/09/30 09:00:00
I have records with StartDate 2007/09/30 08:00:00, 2007/09/30 08:30:00 which this query does not select.
I am trying to select all StartDate which fall between 2007/09/30 07:00:00 AND 2007/09/30 09:00:00.
Any suggestions on the query design
Thanks
Peter
View 6 Replies
View Related
Dec 11, 2007
I have figured out how to work with dates in SQL, but don't know how to put it all together to completea sql query I am working on. Here are some examples of the basic MS SQL date routines I know:
SELECT DATEDIFF(day, '11-2-2007 11:11:11', '12-4-2007 11:11:11') -- Difference between two dates.
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] -- Get's today's date
Now back to my problem....I have the following Query that must be modified so that the filter criteria is based upon the"StartDate" being greater than today's date. Can someone please help me out.
SELECT StartDate, BuildingAddres, TotalSquareFeet, FinishDateFROM dbo.ConstructionTable
Note that the date passed in for Start date will be in a format like the following '11-2-2007 09:1:11'.
So if I were to pseudo code out the query it would be something like the following, where the pseudo codeis in parenthesis.
SELECT StartDate, BuildingAddres, TotalSquareFeet, FinishDateFROM dbo.ConstructionTable (WHERE StartDate > Todays Date)
View 1 Replies
View Related
May 17, 2005
Hi.,
Can anyone please assist me in getting the following SQL Query to work. It returns no errors but also returns no records even though there is actually data in my database for that period.
Dim DCriteria as StringDCriteria = "StartDate <= " & EDateDCriteria = DCriteria & " AND EndDate >= " & SDatestrSQL = "SELECT * FROM vwLeavePlan WHERE " & DCriteria
This results in the follwoing SQL Statement:
strSQL = SELECT * FROM vwLeavePlan WHERE StartDate <= 31/05/2005 AND EndDate >= 01/05/2005
Regards.
Peter
View 14 Replies
View Related
Dec 1, 1998
hi,
i would like to create an SQL query, part of which involves comparing dates.
i have something like
Select ID, CONVERT(varchar(10), StartDate, 101), Name from Table WHERE StartDate .....
how can i compare the StartDate to something i have in the form of a string? for example, i have
Date = 12/12/1998. and i would like to select where the StartDate in the DB = '12/12/1998'.
i'd appreciate all the help i can get.
thanks,
sb
View 1 Replies
View Related
Feb 7, 2008
Hi all,
Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!
Table Structure (abridged, relevant columns):
Orders:
Code:
[Orders](
[OrderID] [int] IDENTITY(1,1)
[OrderDateTime] [datetime]
[OrderSiteID] [nvarchar](255)
[OrderOffline] [bit]
[OrderSentToWP] [bit]
[OrderReceivedFromWP] [bit]
[OrderAuthorised] [bit]
[OrderCancelled] [bit]
[OrderApproved] [bit]
[OrderFraud] [bit]
[OrderDispatched] [bit]
OrderItems:
Code:
[OrderItems](
[OrderID] [int]
[ProductID] [nvarchar](255)
[Quantity] [int]
[Price] [real]
[Weight] [real]
Products:
Code:
[Products](
[ProductID] [uniqueidentifier]
[ProductCode] [nvarchar](255)
[ProductTitle] [nvarchar](255)
Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.
I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.
So far I have this:
Code:
SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID
WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND
(Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND
(Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid'
GROUP BY Products.ProductCode, Products.ProductTitle
Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again
I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!
Thanks in advance to anyone that looks at this for me.
Also, any recommended books/sites to learn this kind of query?
Richard
View 4 Replies
View Related
Jul 19, 2007
Hi My first post :-)
I am trying to build a VBA Sub that uses a MS SQL query, this is fine it is just I am having trouble building the query to do what I need.
I need to be able to do a between month and years, so if I have a VBA routine that constructs a query from variables. Those variable will be a month and a year.
so I can return a count of all records that were logged between the month and year.
My poorly constructed query is below.
SELECT [DateRaised]
FROM HD_Call
WHERE DateRaised BETWEEN DATEADD([MONTH], -1, GETDATE()) AND DATEADD([MONTH],0, GETDATE())
AND DateRaised BETWEEN DATEADD([YEAR], -2, GETDATE()) AND DATEADD([YEAR],0, GETDATE())
AND [completed] = 1
AND [Assignee] LIKE '%'
AND [CallType] LIKE '%'
Order By 1
Thanks in advance
View 4 Replies
View Related
Sep 4, 2014
I have a need to only find distinct dates in which a worker worked in the factory using TSQL
Code:
RowNumber workerstartDate workerenddate
1 2012-08-08 2012-10-10
2 2012-08-10 2012-08-31
3 2012-09-05 2012-09-15
4 2012-10-15 2012-12-19
5 2013-01-02 2013-03-14
62013-03-15 2013-05-23
Basically, I am looking for the above to look like this
Code:
rownumberworkerstartDate workerenddate
12012-08-08 2012-10-10
42012-10-15 2012-12-19
52013-01-02 2013-03-14
62013-03-15 2013-05-23
View 2 Replies
View Related
Jul 20, 2006
i want a query which returns all the date between 2 dates . its like an calender.....for example i selected 2-1-2006(dd-mm-yyyy) to 18-03-2006 ....it should returns like this
2-1-2006
3-1-2006
4-1-2006
.
.
.
16-03-2006
17-03-2006
18-03-2006
View 1 Replies
View Related
Jul 23, 2005
Hello,Can someone please help me with a query?The table looks like this:BookedRooms===========CustomerID RoomID BookDateID1 1 200507011 1 200507021 1 200507031 1 200507091 1 200507101 1 200507111 1 20050712Desired result:CUSTOMER STAYS==============CustomerID RoomID ArriveDateID DepartDateID1 1 20050701 200507031 1 20050709 20050712Basically, this is for a hotel reservation system. Charges varynightly, and customer changes (shortening/extending stay, changingrooms, etc) happen quite often. Therefore, the entire stay is bookedas a series of nights.The length of the stay is never known, so it needs to be derived viathe Arrive and Depart Dates, based on the entries in the table.Notice, customers often stay in the same room, but with gaps between,so a simple MIN and MAX doesn't work. The output needs to showconsecutive nights grouped together, only.I've researched this quite a bit, but I just can't seem to make itwork.Any help would greatly be appreciated.Thanks!
View 15 Replies
View Related
Feb 5, 2008
I have a table like FK_ID, Value, Date (here FK_ID is foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this
View 2 Replies
View Related
Dec 7, 2007
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
Can anyone help me with this code. I am having a SCD (Stupid Coder Day) and can't seem to do anything that is scalable - like accounting for leap years (Feb issue) and the fact that Scenario # 2 above is between 2 different calender years.
Your help is much appreciated.
JJOSHI
View 3 Replies
View Related
