I have two tables a stock table and a price table and I want to select the correct price for the Stock Date.
Problem is sometimes there is a promotion date in the price table between the live dates so the rows double up. Below is my sql but I get over 17,000 rows when it should be about 16,964.
I will post up the table and data.
SELECT a.[Company]
,a.[ProductID]
,a.[ColourSize]
,a.[StockDate]
,a.[Quantity]
,b.[Ticket Price]
FROM[Stock_Ledger] a
LEFT OUTER JOIN [Product_Prices] b
ON a.[Company] = b.[Company]
AND a.[ProductID] = b.[ProductID]
AND a.[ColourSize] = b.[ColourSize]
AND a.[StockDate] BETWEEN b.[StartDate] AND b.[EndDate]
ORDER BY a.[StockDate],a.[ProductID],a.[ColourSize]
I need to be able to select records based on dates that are before a certain date. That bit's easy, my problem is that this certain date needs to be the current date. Any body be able to help?
Hello Forum, I am have posted questions regarding selecting a series of dates and am still having trouble doing so. I have been using vwd to build a website, and now would like to retrieve data from access and display a weeks worth of information at one time from selecting a column of information from the data base. I have used this section of code to do the required function. 'SELECT OrderId, CustomerId, Price FROM Orders WHERE OrderedDate BETWEEN DATEADD(d, -7, GETDATE()) AND GETDATE(), I have replaced these variables with my own, and keep getting an error message 'Undefined function 'GETDATE' in expression', I have tried many different functions to get it to work. Any ideas? Thanks Forum, Tim p.s. thank you todd for replying to my first question!
I am having a problem in outputting the last two dates that a bill has been sent out.
Background: A customers recieves a bill 4 times a year. The dates for issuing these bills are not fixed and I dont know the date that bills are usually sent out.
The database that I am using stores all of the dates that a bill would have been sent out.
Issue: A customer recently requested to see the dates of last 2 bills along with their value.
I know that you can view a customer last date by using the following statement.
Select max(issuedate)LastBill from statements
but i don't know how i could output the last 2 bills that were issued
HiI have a table in my database called 'tblUsers'It contains usernames and a few columns of data.One of them in 'birthday'it is in the format dd/mm (ie 28/04 is 28th of april)it is always 5 characters longIts data type is char in my db.Now I try this<asp:SqlDataSource ID="SqlDataSource8" runat="server" ConnectionString="<%$ ConnectionStrings:IntranetConnectionString %>" SelectCommand="SELECT DisplayName FROM [tblUsers] WHERE ([Birthday] = @ShortDate)"> <SelectParameters> <asp:ControlParameter ControlID="Calendar1" Name="ShortDate" PropertyName="SelectedDate" Type="string" /> </SelectParameters> </asp:SqlDataSource>where the selected date from a calendar control is used.Now it works when the table has a Date column which is off type DateTime but how would I modify it to work like I want?Thanks!
For some reason this is just not "clicking" with me and the longer I stare at it the more I overcomplicate things and then I just confuse myself further...
Provided this relation showing where "pkey" and "skey" met on "ServiceDate":
I need to select out a list of distinct pkeys and skeys only when there was no meeting between the two in November 2005. In other words, this query would produce only one record - the starred record - when run against this sample table. This is because even though, for example, skey 124 / pkey 2 has an entry outside the desired range, it also has one inside the desired range. The same goes for the record outside our range for 123 and 2 - it also has a record inside our range.
At this point, I've come to the conclusion that I can first select all the distinct pkeys and skeys where the servicedate is not in 11/2005 then join it to a selection of distinct pkeys and skeys where the servicedate IS in the desired date range. Does that seem like the most straightforward way through this?
I don't get the impression that this is that complicated a problem, but it's one of those deals where I goofed up somewhere along the line, and now I think I'm really overthinking the problem, so I'd be much obliged if someone could give me a hearty slap to clear the ol' noggin.
Hi, I was wondering if anyone can help me write a SELECT statement to return all records (rows) based on whether they fall within two dates. For example, consider this table:
Code Snippet --DROP TABLE #Records CREATE TABLE #Records (RecordID INT PRIMARY KEY NOT NULL, StartDate DATETIME, EndDate DATETIME) INSERT INTO #Records VALUES (1, '2008-05-01', '2008-05-12') INSERT INTO #Records VALUES (2, '2008-05-08', '2008-05-12') INSERT INTO #Records VALUES (3, '2008-05-19', '2008-05-22') INSERT INTO #Records VALUES (4, '2008-05-22', '2008-05-23') INSERT INTO #Records VALUES (5, '2008-05-26', '2008-06-01') INSERT INTO #Records VALUES (6, '2008-05-28', '2008-06-01') SELECT * FROM #Records
I want to return the RecordID for any row which span this week. I've tried the simple approach of using WHERE GETDATE() BETWEEN StartDate AND EndDate but this doesn't produce the desired results. For example, RecordID 4 spans this current week but is not returned (obviously) with this approach.
The idea is to show which events (rows) are coming up this week and next week. Eventually the data will be used to populate an SSRS report.
I have a large table (2+Million records, 2000+ new per day). If I want to count the records in one month, I could do :-
Select Count(*) from Table1 where Added_Date between '1999-12-01' and '1999-12-31'
or
Select Count(*) from Table1 where Added_Date >= '1999-12-01' and Added_Date <= '1999-12-31'
For these two above, I get the same result.
HOWEVER!!
If I then do:-
Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00' and '1999-12-31 23:59:59' or use the >= and <= operators, the result is larger.
If I then do :-
Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00.000' and '1999-12-31 23:59:59.999' or use the >= and <= operators, the result is larger still but i believe this including more rows than there actually is?.
I can get over the problem programatically using Convert to truncate to a day, month or year however, I don't understand why the results are different.
Any Ideas as to why this is happening, A'm I being stupid!!!!!!!
(PS Added_Date has a non_clustered index which has been rebuilt twice and DBCC finds no errors)
Hello. I need to create a page with asp 3 and sql server 2000 that lists all the records in a table that match a date criterion, but when doing so I get an error "Error Converting datetime from character string"... I've tried this versions of the query:
Code:
SELECT (...) CONVERT(DATETIME,PAYMENTS_RECEIVED.PYR_DATE,103), (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE BETWEEN '20/03/2006' AND '21/03/2006')
and...
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME,'20/03/2006',103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME,'21/03/2006',103))
I also tried adding " 00:00:00 a.m." to the dates to be converted in the second piece of code. If I recall correctly, "103" is the convertion code meaning "dd/mm/yyyy". That is, I expect people to type in a date like "27/03/2006" and get the table records from that date.
Weirdest thing of all is that, when using the query builder of a sqlsource control in Visual Studio Web Developer Express 2005 this following query works just fine as I expect:
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME, '20/03/2006', 103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME, ' 21 / 03 / 2006 ', 103))
Both the server and the DB are the same in all cases, but in the first two I run the query from a (working of other queries) vbscript asp 3 page.
I know that similar posts exist already (I've searched for this before posting), but I can't fix it, at least not with the usual answer on this one (that seems to be using the convert function as in the first piece of code). Thanks in advance!!!
tblCustomers contains a CustomerID that is unique to each customer.
tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.
tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.
tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.
What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.
For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.
I’ve tried several different queries but here is an example:
SELECT tblMachines.MachineID,ServiceMachines.ServiceDate FROM tblMachines LEFT JOIN (SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo ) ServicedMachines ON tblMachines.MachineID=ServicedMachines.MachineID WHERE YEAR(ServiceDate) != '2014'
I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.
I have three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)
I would like to get the most recent date within a given year per each EMP? For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.
SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year FROM EMP_DATE DTE join EMP_INFO EMP on EMP.ID = DTE.ID join YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
On my SSRS report, I use a date parameter to let the user select a date with the little calendar tool. When a date is selected, I have a small bit of coding run to convert the date to text, because when the report first fires up, the field is populated with "1/1/1900," and I need that turned into a blank character to let the report pull up all rows. So far, all well and good.If someone picks a date, then the report will filter the data on that selected date. Works fine.
But, it appears there is no way to get the calendar tool to go back to allowing ALL dates - so that all records are pulled - except by manually typing in, or selecting it with the tool, 1/1/1900.If I try to clear the field, causing it to use '' as a WHERE criteria (WHERE AdmitDate LIKE '%' + @AdmitDateTxt + '%'), it repopulates the field with the last selected date. So, I guess the question is, how does one tell the calendar tool for picking dates for a date parameter to reset back such that all records are pulled, not just those for a single date, without being required to type in, 1/1/1900? Or, is there some way of telling the date parameter to select all dates?
What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:
DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment) ,( ((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE
[Code]....
The query works with the following exceptions:
My issues are:
1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.
2. Avoid weekend dates. I need the query to display only weekdays
3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.
Does anyone know how to select rows by their position in a table. I need to be able to e.g return the 2,000th - 2,200th rows as a set. I will have many concurrent clients connecting to this table so I do not want to use cursors. The table is 800,000 in length. Any ideas people?
Hi, I want to select the 8 most saled products from large orders table... the problem is that when i use the "distinct" sentence (something like this- "SELECT TOP 8 distinct id, products, productid FROM tbl_orders ORDER BY id") I get back the distinct of any columns.... (and any ID is distinct, of course), but if i don't include the id's in the distinct sentence, i can't order by id's. can i get the last orders, only by distinct product, and not by distinct id, and order them by the id's?
I have a temporary table that contain 1000 rows. I want to insert records from 900 to 1000 into another temporary table. Is ther any query that will do this in SQL server. With cursors I can do that but I need one shot query. Please give me some suggestions.
I using this query but it is not returning any rows. select * into #t2 from #t1 t1 where not exists( select top 900 * from #t1 t2)
I have a table stock, on the table I have company_name, stock_value, sector. Now I want to get top five rows from this table depending on the five highest value of stock_value and for each sector. The query has to run on both oracle DB and Microsoft SQL Server with top priority MSSQL. I will appreciate if you help me on this
I know I can use "First" to specify a number of rows to return from a query but is it possible for the number of rows returned to be based on a parameter, something like this:
SELECT FIRST @someNumber name, age FROM friends WHERE age > @ageInput
Hi experts,I have been trying to limit the table rows in the following situation,any suggestions will be appreciated.we have table called tempTb has columns id, c_id, c_name, rating, datecolumns.id is an identity column.date is a datetime column, the rest are varchar datatype.Here is the table structure with sample data,idc_idc_nameratingdate1aoamer onli11/1/20022aoamer onli13/1/20023aoamer onli16/1/20024aoamer onli39/1/20025aoamer onli312/1/20026aoamer onli33/1/20037aoamer onli36/1/20038aoamer onli39/1/20039aoamer onli212/1/200310aoamer onli16/1/200411aoamer onli112/1/200412xyxabs yasd11/1/200213xyxabs yasd23/1/200214xyxabs yasd26/1/200215xyxabs yasd29/1/200216xyxabs yasd112/1/200217xyxabs yasd13/1/200318xyxabs yasd36/1/200319xyxabs yasd39/1/200320xyxabs yasd212/1/200321xyxabs yasd16/1/200422xyxabs yasd112/1/2004[color=blue]>From this table I need to select the rows with rating changes only,[/color]i.e if two or three consecutive rows have same rating only the firstrow should be selected.the selection should look like...idc_idc_nameratingdate1aoamer onli11/1/20024aoamer onli39/1/20029aoamer onli212/1/200310aoamer onli16/1/200412xyxabs yasd11/1/200213xyxabs yasd23/1/200216xyxabs yasd112/1/200218xyxabs yasd36/1/200320xyxabs yasd212/1/200321xyxabs yasd16/1/2004I was trying to do this by self-joining the table like....select t1.* from tempTb t1, tempTb t2where t1.id!=t2.id,t1.c_id=t2.c_id,t1.c_name=t2.c_name,t1.rating!=t2.rating.But this is generating cartesian products,I have tried some other combinations after where clause with date colmnwtc,but none seems to give the required result.so if anybody can guide me in the right direction I would appreciateit.Thanks alot,Remote
Hello All,I have this table:CREATE TABLE [dbo].[tbl_TESTING] ([ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,[FNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[LASTNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[MOVEDINYR] [int] NULL ,[MOVEDOUTYR] [int] NULL) ON [PRIMARY]GOwith these records:INSERT INTO tbl_TESTINGVALUES('JAMES', 'TAYLOR', '1995', '2000')INSERT INTO tbl_TESTINGVALUES('JAMES', 'TAYLOR', '1994', '2005')What I would like to do is be able to select FIRSTNAME, LASTNAME,MIN(MOVEDINYR), MAX(MOVEDOUTYR) for JAMES TAYLOR e.g.FIRSTNAME=JAMESLASTNAME=TAYLORMOVEDINYR=1994MOVEDOUTYR=2005Some sql syntax help appreciated,thanks in advance!
hi!I have two tables with same structure. I need to run a select query thatwill return only the rowsfrom one table that are not in the other.In MySQL it would beselect * from table1 MINUS select * from table2.but in MsSql I can not find the apropriate way to do this.Any Help?Zvonko
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
