Select Rows Based On Multiple Date Conditions
Jun 5, 2008
Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.
SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.
Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?
Thanks,
Marc
View 2 Replies
ADVERTISEMENT
Apr 7, 2015
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
View 1 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Sep 6, 2015
I have a table like the following (with much more data, but the concept is the same) with Dates and Actions for People and a column called Action with beginning Dates and end dates.
(I attached a picture because I could not figure out how to Format it)
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
I need the query to be like this. The idea is to have the query grab the next 'END' not all Ends, which my attempts have done i.e. I get not just the closest end to the begin date, but ALL Ends with the same Person.
I Need it to look like this:
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
There can be different People so the query Needs to return the beginning and end rows for the Person in sequential order.I can't figure out how to select only the 'next' end. My query always gets 'end' values that have a 'begin'. I
View 8 Replies
View Related
Jun 18, 2015
I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:
SELECT 'Older_Than_30Days' =
CASE
WHEN open.date >= 30 THEN '1'
ELSE '0"
END
My problem is the WHEN.
View 6 Replies
View Related
May 12, 2008
Select c.Plan, c.ClaimNumber
from tbFLags c inner join tbMembers m
On c.Claim = m.HistoryNum
where c.Plan = 'J318' and c.Paymon = c.Rmon and c.Rmon = '2008-03-01'
Now I want to add these into this statement, what should be done.
Members meeting any of the 3 sets of criteria should not be selected
1) tbFlags.Hosp='1'
2) tbFlags.RD='1' OR tbCMSFlags.RAType in ('D', 'I2')
3) Deceased = tbMembers.DOD is not null.
View 27 Replies
View Related
Feb 6, 2015
I have the got below T SQL query to fetch the matching record
DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
[Code] .....
-- result - 'Orange'
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
-- result - 'Orange'
View 4 Replies
View Related
Jan 10, 2008
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
Thanks for whatever information you can provide.
View 7 Replies
View Related
Mar 17, 2015
Here's my current SQL:
SELECT
RN_TEST_ID AS 'Test ID',
MAX(RN_EXECUTION_DATE) AS 'Last Execution Date',
MAX(RN_EXECUTION_TIME) AS 'Execution Time',
RN_DURATION AS 'Run Duration'
FROM RUN
[code]....
Here's a sample of data returned:
Test IDLast Execution DateExecution TimeRun Duration
86722/9/2015 0:0012:08:16180
86822/9/2015 0:0011:29:06181
86842/9/2015 0:0008:29:17119
105252/3/2015 0:0019:03:4089
105252/3/2015 0:0019:10:13305
106682/3/2015 0:0018:55:43103
106682/6/2015 0:0018:10:50123
114572/3/2015 0:0011:40:0726
What I need are two things:
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
View 6 Replies
View Related
Mar 21, 2006
Hello again,
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag
1111296.54
3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id
100000001
200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag
1111296.54
3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id
100000001
(Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
Thanks, and im happy to clarify if needed.
View 1 Replies
View Related
Sep 6, 2011
I have a table with two columns refid and name and it has the following values
1 tom
1 jim
2 bob
1 bob
I need a resultset that would have the following values
1 tom, jim, bob
2 bob
I have tried couple of things one being:
DECLARE @namelist VARCHAR(1000)
SELECT @namelist = COALESCE(@namelist +', ' ,'') + name FROM sales where refid = 1
SELECT @namelist
But I am looking for a resultset with a unique refid and all the names comma separated for that refid.
View 2 Replies
View Related
Sep 5, 2014
I need to split a row into multiple rows based on multiple column values.
time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00
I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.
row 1
time_in 10:00
time_out 12:00
row 2
time_in 13:00
time_out 14:00
row 3
time_in 15:00
time_out 16:00
View 2 Replies
View Related
Oct 2, 2014
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
Category Example
IDAmazonCategoryIDAmazonCategoryNameFeePercentageStartPriceEndPrice
1apsAllDepartments0.150.000.00
2instant-videoAmazonInstantVideo0.000.000.00
3appliancesAppliances0.150.000.00
Product Example
1Product1Electronics9.99
2Product3Electronics99.99
3Product2Electronics599.99
Raw SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
I use Microsoft SQL 2008
View 2 Replies
View Related
Aug 16, 2014
I need to get 3 rows per set based on a date given (must be this date) but I want the rows to be based on this date as...
1 ( row where the date from the date column is the next date after given date )
0 ( row where the date is the closest date prior to the date given )
-1 ( prior to the date at 0 )
And add a column with the relative number.
** The dates for the same name and item will never repeat.
For example, a set of rows...
Row ID, Name, Item, Number, Date
1 Andy, Item1, 12030, 2014-06-30
2 Andy, Item1, 62030, 2014-03-31
3 Andy, Item1, 30300, 2013-12-31
4 Andy, Item1, 40030, 2013-10-31
5 Andy, Item1, 50030, 2013-08-30
6 John, Item2, 50240, 2014-04-30
7 John, Item2, 41400, 2014-03-31
8 John, Item2, 40509, 2014-01-31
9 Andy, Item2, 24004, 2014-03-31
10 Andy, Item2, 20144, 2013-12-31
11 Andy, Item2, 20450, 2013-09-30
12 Andy, Item2, 25515, 2013-06-30
If I have 2014-03-15 as the date and search for 'Andy', I expect...
Row ID, Item, Date, Relative Date
2, Item1, 2014-03-31, 1
3, Item1, 2013-12-31, 0
4, Item1, 2013-10-31, -1
9, Item2, 2014-03-31, 1
10, Item2, 2013-12-31, 0
11, Item2, 2013-09-30, -1
This is what I'm using which I have no issues switching if necessary...
DATEDIFF( quarter, 2014-03-31, date )
date BETWEEN DATEADD( quarter, -1, '20140315' ) AND
DATEADD( day, 1 ( DATEADD ( quarter, 2, '20140315' ) )
which returns...
Row ID, Item, Date, Relative Date
2, Item1, 2014-06-30, 1
3, Item1, 2014-03-31, 0
4, Item1, 2013-12-31, -1
9, Item2, 2014-03-31, 0
10, Item2, 2013-12-31, -1
Not sure if date math is the best option here. Perhaps using row_number() in some way?
I'm trying to avoid having to process the entire table programmatically given the size of the data set.
View 8 Replies
View Related
Feb 5, 2007
I'm dealing with a problem.
The record information example
DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600
what i want is for every half hour between start and end a record
10.30 action1
11.00 action1
11.30 action1
how can i create this, i'm a little stuck on this
View 2 Replies
View Related
Apr 12, 2007
Hello All
I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.
I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE rptArFLSalesByCustItemized_sp
@custId pCustID,
@dateFrom datetime,
@dateThru datetime,
@itemIdFrom pItemId,
@itemIdThru pItemId
as
set nocount on
-- define some variables for previous year
declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
-- set defaults
SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem))
SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem))
SET @LYfrom=DATEADD(YEAR,-1,@dateFrom)
SET @LYthru=DATEADD(YEAR, -1, @dateThru)
-- create small temp table to hold customer info
Create Table #tmpArCustInfo
(
custId pCustID,
custName VARCHAR (30),
)
-- populate customer temp table with info
Insert into #tmpArCustInfo
select custId, custName
from tblArCust
WHERE custId = @custId
-- create a temp table to hold the Data for each Item
Create Table #tmpArSalesItemized
(
itemId pItemId,
productLine VARCHAR (12),
pLineDesc VARCHAR (35),
descr VARCHAR (35),
LYQtySold int,
LYTDQtySold int,
QtySold int,
LYTDsales money,
totalSales money,
LastInvDate datetime,
)
-- populate the temp table with all of the inventory items
insert into #tmpArSalesItemized
select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL
from tblInItem ii, tblInProductLine ip
where ip.productLine = ii.productLine
AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
-- update table with this years quantities
update #tmpArSalesItemized
SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd
where TransId IN (select TransId from tblArHistHeader where custId = @custId)
AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru)
AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru
GROUP BY hd.partId
)
-- Return the temp tables results
select * from #tmpArSalesItemized, #tmpArCustInfo
drop table #tmpArSalesItemized, #tmpArCustInfo
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.
Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.
Thanks
Jamie
View 1 Replies
View Related
Jul 17, 2013
I have a table of attributes set up as follows:
ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
View 5 Replies
View Related
Aug 28, 2007
Hi,
I have the following scenario :
CustomerDetail
customerid
customername
status
app_no
[status = 0 means customer virtually deleted]
CustomerArchive
archiveno [autoincrement]
customerid
customername
status
At the end of the month, I have to physically delete customers. I have written two stored procs:
proc1
create proc spoc_startdeletion
as
declare @app_no int
select @app_no = (select app_no from customerdetail where status=0)
EXEC spoc_insertcustomerarchive @app_no
-- After transferrin, physically delete
delete from customerdetail where status=0
proc2
create proc spoc_insertcustomerarchive
@app_no int
as
insert into customerarchive(customerid,customername,status)
select customerid,customername,status from customerdetail where app_no = @app_no
It works fine if there is only one row with status=0, however the problem is that when there are multiple rows in customerdetail with status=0, it returns 'Subquery returned more than one value'
How can i transfer multiple rows one by one from the customerdetail to customerarchive and then delete the rows once they are transferred.
Vidkshi
View 15 Replies
View Related
Aug 31, 2015
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
View 2 Replies
View Related
Aug 20, 2006
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
View 7 Replies
View Related
Nov 21, 2007
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId - SiteId
1 - 5
1 - 9
1 - 16
1 - 18
1 - 20
1 - 27
1 - 31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
View 10 Replies
View Related
Sep 22, 2015
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
View 2 Replies
View Related
Apr 18, 2014
--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware
From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.
-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))
[Code] ......
View 2 Replies
View Related
Sep 13, 2006
Hi
I would like to get records from a table and present a result set based upon the search fields
the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,
they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.
Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?
Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage
adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg
View 12 Replies
View Related
Feb 19, 2004
i am not getting a result back when i run the query below.
select * from users where DateCreated = '2004-02-19'
so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00
how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?
View 5 Replies
View Related
Sep 12, 2012
I have the following table:
Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind
I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.
So i would want the following from above:
Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind
I'm having trouble with the syntax need to accomplish this.
View 2 Replies
View Related
Sep 23, 2012
Product Date
--------------------------------------------------------
pd0 2012-08-11 18:45:55.780
Pd1 2012-08-11 18:55:17.020
pd2 2012-08-11 19:06:58.623
pd3 2012-08-18 12:00:01.193
pd4 2012-08-25 12:13:04.077
pd5 2012-08-25 17:28:30.347
pd6 2012-08-25 18:23:16.473
pd7 2012-09-18 18:29:58.360
I want select the product based on from date and to date.
For Example
I want the select the product date in between 2012-08-11 to 2012-08-18
Note:dont check the time.
I want the query for select product based on only date not depend upon time
View 6 Replies
View Related
Dec 3, 2012
I want to select the latest single value based on ProductID. The value should be the last value inserted from the Date column.
I want to use this as part of a query to delete this value when a button is slected in ASP.Net C#.
Here is my code which does not work
select basketID, SessionID, UserID, MAX(Date), productID from ShoppingBasket
where userID = '1'
group by productID, basketID, SessionID, UserID
View 2 Replies
View Related
Jan 30, 2015
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
View 1 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Jun 2, 2015
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)
View 5 Replies
View Related
Jun 2, 2008
I'm very green with SQL so I could do with some advice please.
I need to pull some data from a table based on the year portion of a datetime field, so far I've got this...
USE MfgSys803
SELECT orderdate, ordernum FROM orderhed
WHERE ((SELECT CONVERT(VARCHAR(4),GETDATE(),111)) = (SELECT CONVERT(VARCHAR(4),ORDERDATE,111) FROM orderhed))
... the field 'orderdate' is the datetime. The purpose of the WHERE statement is to get the current year fromt he system and then compare this to the current year of the field 'orderdate'.
Unfortunately I get the error...
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
... the 'SELECT CONVERT' portions of the WHERE work fine on thier own but I can't use them together.
Hopefully this makes sense, thanks :)
View 7 Replies
View Related
Apr 17, 2008
Hi! I'm new to SQL and have a question...
I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens.
Basically my SELECT statement looks something like this, then is followed by an If... Else statement
SELECT * FROM TableName
WHERE LastName = varLastName
AND FirstName = varFirstName
AND Address = varAddress
If RecordSet.EOF = True Then
'Item Not Found, add new record
'code to add new record......
Else
'Item Found, do nothing
End If
RecordSet.Update
RecordSet.Close
Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??
View 5 Replies
View Related
