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

Dates Without Times

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

Various Times From Various Dates

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

Job Execution Dates-Times Calculation (Past & Fut)

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

SQL Query Load Times

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

Excessive Query Execution Times

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

Sql Query Fails At Specific Times

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

Query Analyzer Cannot Connect, Times Out

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

Baffling Query Return Times

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

SQL Datasource Running The Same Query Mutliple Times

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

Employee Attendance With In/out Times (was Query Help Needed...)

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

How To Run Insert Query For Required No.of Times With Different Parameters...?

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

Transact SQL :: Running Query For Multiple Times Through The Day?

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

SQL Server 2008 :: Iterate Query Using A Loop As Many As 5 Times Max?

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

InSQL 7.1 - Count How Many Times A Tag Equals 1 Over A Specified Time Period Query....

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

SQL Server 2008 :: Left Joins And Query Plan Compile Times

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

Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc

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

Between Dates Query - HELP!!!

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

How To Query Dates

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

SQL Query And Dates

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

Query Between Dates

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

How To Do This Between Dates Query?

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

Query Question/help With Dates

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

Need Help With Query That Compares Dates

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

SQL Query For Data Between 2 Dates

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

SQL QUERY Help Please.. (Comparing Dates)

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

SQL Query Or SP? Order Value Between Two Dates

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

SQL Query Usings Dates In A VBA Sub

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

Query For Distinct Dates

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

Query Which Should Return All The Dates Between 2

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

Query Help Please - Consecutive Dates

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

Query For Getting Missing Dates In Months

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

Searching Dates In Query Analyzer

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







Copyrights 2005-15 www.BigResource.com, All rights reserved