I would like to generate a report based on age from an existing table. All I have is birthdate column to subtract from getdate(). I would like to create age bracket columns. The example below shows that there is 150 people aged 0-4 years old, 75 people aged 5-9, 225 people aged 10-14, etc. Would I use a case statement? Would I use the COALESCE function? I can come up with the calculations and the columns, I just can't fuse the values of my made up columns to result in one row. I need help, please....
here's a sample of what I want the query output to look like:
0-4 5-9 10-14 15-17
----------------------
150 75 225 35
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Hello,I have a table that lists a number of available time slots for a party venue. I want a user to select a particular time slot and have the query show results for that time slot plus two time slots before and two time slots after the user's selection. If the user selects a time slot that is the first of the day, I still want that user to be shown 5 results including the one he selected. The same goes for the last time slot of the day. Can anyone help me with the SQL statement? Thank you very much in advance! Mark
I need to run reports from our database that stores IP addresses when a software license is used. So, I need to know how to run SQL queries to retrieve certain IP addresses from specified ranges. For example:
192.168.1.0 - 192.168.2.255
192.168.3.11 - 192.168.7.128
10.10.10.8 - 10.10.12.255
What commands would help me accomplish pulling all IP addresses in a range. I know I would have to run multiple reports for each range. I tried using the "between" function and "less than/greater than" functions, but I received mixed results. For example:
SELECT * FROM testtable WHERE ip LIKE '192.168.[1-2].[0-255]'
I also tried -
SELECT * FROM testtable WHERE ip>'192.168.1.0' and ip<'192.168.2.255'
Any help would be greatly appreciated! Thanks in advance!
I have a FileConsumptionDetail table which has [DataTime] every 30 minutes. Also, I have a schedule table with has range time and with certain condition.
Now I would like to query against these 2 tables what is the best approach to handle to do this due the process load is around 20,000 records. The end of result is sum of the Value1 of FileConsumptionDetail with the relevat schedule. I can probably using cursor and loop the 20,000 records of the data and check one by one whether that value is applied on that schedule table. Overkill .. I guess?
IsMonday - IsSunday is basically a flag for each day if it's a daily type of schedule. IsJan - isDec is a flag for each month if it's a monthly type of schedule.
Regarding 2nd table design, i am not sure this is a best approach though and I am happy to change if it's easy to query.
Hope this clear and I am appreciated your comment.
I have a table called Event, this table has two fields called StartDate and FinishDate. On my application I want the user to be able to search for all the events for a certain date range. I am having some trouble getting my head around the code and was wondering if someone could offer some advice.
Some more details... the user can enter a from date, a to date or both. I can have separate sql code for each situation. The event.startdate and event.finishdate are never null and may contain a time value.
Hi,I have a query which works for one day:SELECT SOME_COL AS something, SOME_COL2 AS something2 FROM myTableWHERE DATE = '2007-05-11' AND SOME_STAT 1Returnssomething something 21 23 4How do I get this to work for a date range (e.g. DATE '2007-05-09')where I get:date something something22007-05-09 1 22007-05-09 3 42007-05-10 1 22007-05-10 3 42007-05-11 1 22007-05-11 3 4Thanks in advance!
Hi,Need help in optimizing a query in SQL Server.Following is the problem statement.There are two tables;1st table (t1) has a KEY ( char(8) ) column, with a clustered index.this is not the primary key. The table can have billions of records;in test environment, we are having 3,000,000 records2nd table (t2) has two columns a from_Range and to_Range, bothchar(8). this table has lesser number of records, in thousands.Clustered index is on the primary key.However there is no relation whatsoever between the KEY and thefrom/to range.We need to find matching records where Key is found between the fromand to range :select t1.id, t2.id from t1, t2where t1.KEY between t2.from_range and t2.to_range( The ids form part of primary keys in both tables. )The plan shows a loop, with t1 using clustered index of KEY and t2using clustered index of the primary key.This query is taking around 14 seconds on SQL server 2000 in win2kprowith P4 and 512 MB RAM.Is there any way this can be reducd to a subsecond performance ? Thisquery forms the core of most of the processing, and any reduction herewill have recursive effect all over.Thanks in advance,roy.
I use oledb (ACCESS database) in my application. i want to build a query to retrieve the number of Bookings from my Booking table where the appointment_date_time.timeOfADay is in range of 9am-12pm or 14pm- 7pm, that is (>= 9 and <12) or (>= 14 and < 17). Please help to build the query,
I found some query sample like:
select * from tblstudents where classID='1' and studentstartdate between ('2004-12-03') and ('2004-12-12')
or
WHERE DateField BETWEEN @StartDate AND @EndDate
But I dont want to search year and month and day, i just want to search the actual hour of a day. i am stuck with the syntax, please help
I need SQL to determine what the date range for the previous month was, ie Start date 11/01/01 for 11/30/01. The results will populate a drop down box for user queries.
I have two columns, where I have the start and stop numbers (and each of them ordered asc). I would like to get a query that will tell me the missing range.
For example, after the first row, the second row is now 2617 and 3775. However, I would like to know the missing values, i.e. 2297 for start and 2616 for stop and so on as we go down the series. Thanks in advance to any help provided!
hi, i'm using sql sserver 2005 and i have a time that i need to change to a datetime query (from the start of the day to the end of the day), whats the syntax for that? thx
how to add a date range filter to my database query. I want to be able to enter a date into ("E,6") and have VBA pull data >,= to that date range according to my "OPENED_DATE" dates. My code is pasted below:
If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?
For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3
This is a query that I am trying to work on:
Code: SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*) FROM UPValue v INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired WHERE v.Year = 2012 and LeaseType = 2 group by v.District, v.PropClass, YearAquired order by v.District, v.PropClass
Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.
On a new project i need to create possible ranges between a specific interval.
suppose i have this main product:
main product : LY E67F
Also, in first level i have a table classify by Group depending on Intensity.
table group Group intensity AA 1120 AB 1400 BA 1800 BB 2240 CA 2800
I need to create these diferent options:
1 option : AAAB or AABA or AABB or AACA 2 option : ABBA or ABBB or ABCA 3 option : BABB or BACA or BBCA or beginning from the end 1.option CABB or CABA... or beginning from the middle 1.option BBBA or BBAB and so on.
I fact, i need to find all available options possibles to build article code, like a matrix.
I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.
(Select SUM(Amt), R.Month AS Month ,R.Year AS Year FROM Receipt R WHERE (R.Month >= 6 AND R.Year = 2006) Group By R.Year , R.Month ) Union (Select SUM(Amt), R.Month AS Month ,R.Year AS Year FROM Receipt R WHERE (R.Month <=3 AND R.Year = 2008) Group By R.Year , R.Month )
This one is making my head hurt! Trying to figure out how to query for records between date range. The records have a start_date and an end_date field. The end_date field maybe null.
For example, say you wanted to see the records of everyone checked into a hotel during a given date range. You need to account for the people that checked in before you @start_date parameter and may check out after your @end_date parameter.
fyi- As for the null end_date field, think of this as they have checked in and not sure when they will checkout yet.
I have a DB with the folloing fields... ArticleID, ArticleTitle, PublishDate(DateTime Field), EndPublishDate(DateTime Field).... What I want to do is show in a list view only the articles where the date is on or after the PublishDate and before or on the EndPubishDate. I thought I could simply do something like this SELECT ArticleTitle, PublishDate, PublishEndDate, PublishedFROM UserArticlesWHERE (Published = @Published) AND (PublishDate >= @PublishDate) AND (PublishEndDate <= @PublishEndDate)
Well I can't... can someone explain how I can do this please... I understand the the PublishDate and EndPublsihDate need a variable... The info is in the DB I just don't know how to do what I need to.
I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.
The data looks like: ABCD-001869202 ABCD-001869203 ABCD-001869204 ABCD-001869205 ABCD-001869209 ABCD-0018692010 ABCD-0018692011 ABCD-001869309 ABCD-001869310 ABCD-001869311 ABCD-001869312 ABCD-001869313 ABCD-001869314
Desired out put:
From To ABCD-001869202 ABCD-001869205 ABCD-001869209 ABCD-0018692011 ABCD-001869309 ABCD-001869314
I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:
SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher INTO #tempVoucher FROM LEDGERJOURNALTRANS where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'
I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:
SELECT * FROM TRANSACTION_HISTORY WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))
Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day.
select shop ,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span ,sum(case when datediff(dd,yourdatetimefield,getdate())=0
I have a table of errors with a DateTime field for when the error occurred. I want to query the table for a given date range omitting the time portion. What is the most efficient way to perform this query?
Hi, I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.
My Issue: I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.
Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.
My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null) CustomerID (FK, int, null) PaymentDate (datetime, null) UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
