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?
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:
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.
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
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
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
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')
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
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?
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
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.
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....
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.
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?
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.
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:
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.
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.
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?
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)
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.
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.
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?
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 ?
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,
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 )
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.
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??
