I am trying to create a SELECT statement that would allow my users to type in a date parameter like 6/25/04. My SELECT statement would then pull all entries for that date. The problem I am running into is that it seems SQL wants the date to be parameterized as between 6/25/04 and 6/25/04 11:59:30 PM. Is there any way around that? Again I would like my users to simply enter 6/25/04 and have all entries pulled. Thanks for any help.
I have the following Select SQL Statement in which I get the count of the 'Code' column based upon a criteria and Group By clause:BEGIN SELECT Code, COUNT(Code)as exprCount1a FROM dbo.[Test] WHERE Section = '1' and Item = 'a' GROUP BY Code ORDER BY Code END The results of the statement: Code | exprCount1a 1 22 44 1 I would like the following results: Code | exprCount1a 1 22 4 3 04 1 Note: Code ' 3 ' doesn't have any rows that meet the select count statement criteria but I still need to populate ' 0 ' in the results. Thank you in advance
I have the following query that should return the most recent FormNote entry for a work order where the note begins with "KPI". However if someone decides to a more recent note, it selects that one, even if it doesn't begin with "KPI".
I would like it to return the most recent record that ALSO begins with "KPI". How can I correct this?
Select wh.worknumber, wh.date_created, wh.itemcode, wn.TextEntry as [Notes] from worksorderhdr wh left join
(select ID, WorksOrder,[CreationDate], TextEntry from ( select ROW_NUMBER()over(partition by worksorder order by [CreationDate] desc) OID,* from FormNotes )orders where orders.OID=1 ) wn on wn.WorksOrder = wh.worknumber where TextEntry like 'KPI%'
Sample results below, see line 5 - this record should not have been selected as there is a record beginning with "KPI" for that work order, but it is dated before this one.
worknumber date_created itemcode Notes -------------------- ----------------------- -------------------- ----------------------------------------------------------------------------------- HU-DN-004385 2014-07-21 16:15:00 4261 KPI Hyd oil leak repaired HU-DN-004707 2014-08-06 11:39:00 8005 KPI Valve replaced on day 2. HU-DN-004889 2014-08-19 15:44:00 9275A KPI Repaired in 2 days - m/c working HU-DN-004923 2014-08-22 14:23:00 4261 KPI New tracks fitted HU-DN-005162 2014-09-12 15:04:00 9360A Mechlock key delivered to site - m/c working HU-DN-005170 2014-09-15 12:07:00 2130A KPI 28.10.14 Metlock fitted
Hello: I need assistance writing a SELECT statement. I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched. For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997. Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate. One way of doing this that I've already tried is: SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997' In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss. Is there a better way of doing this than with the UNION operator? Thank you
I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT
Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).
Any suggestions on the best way to write this would be valued.
I'm trying to write a select statement that will show me the total payments, last payment date, and last payment amount for each client. I get results but it is all payments. Can anyone help me with this?
Thank you,
Here is what I have tried:
SELECT dbo.tblClients.Client_ID, Sum(dbo.tblPaymentReceipts.[Amount Paid]) AS SumOfAmtPaid, MAX(dbo.tblPaymentReceipts.[Date]) AS LastPaymentDate, dbo.tblPaymentReceipts.[Amount Paid] INTO #temp_UNPaymentsA FROM dbo.tblPayments INNER JOIN dbo.tblPaymentReceipts ON dbo.tblPayment.Pay_ID = dbo.tblPaymentReceipts.Pay_ID WHERE (dbo.tblPaymentReceipts.[Date] BETWEEN '1/1/2001' AND '12/31/2003') GROUP BY dbo.tblPayments.Pay_ID, dbo.tblPaymentReceipts.[Amount Paid]
Select * FROM #temp_UNPaymentsA GROUP BY Client_ID, SumOfAmtPaid, LastPaymentDate, [Amount Paid] HAVING SUM(SumOfAmtPaid) BETWEEN 0 AND 1000
SELECT Selskabsnummer, Plademærkenummer, [Fra dato], isnull([Til dato], getdate()) as [Til dato], Bemærkning FROM Gramex_DW.dbo.Plademærkelinie WITH (NOLOCK) WHERE convert(int, Plademærkenummer) = 1 ORDER BY isnull([Til dato], getdate()) ASC
I'm looking for a way to replace the [Fra dato] field with '1900-01-01' if the date earlier than this date. E.g. instances of 1800-01-01 exist.
I'm not looking for an update to the table, but merely a way to manipulate the output, as the front-end I'm developing can't handle pre 1900-01-01 date formats, and I would prefer formatting the data before it reaches he front-end as it would be easier to handle.
I need to convert a date like 08/1/2009 to 0809 I also need to show currency as 100.00 and not 100.0000 How can I do these in a select statement? SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment I appreciate any help!
Dim Command As New SqlClient.SqlCommand("SELECT * FROM CUSTOMER_MEET WHERE (MT_WEEK = CURRENT WEEK OF THE YEAR) AND (MT_DATE = TODAY'S DATE)", conn).
My problem here is how to code today's date in a format like this - October, 09 2007. The MT_DATE column on the database table has Varchar datatype - Varchar(21).
Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null)) @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much!
If I run the following select statment against the appropriate table it returns the duplilcate records in the result set. However, from this list I want to add an additional select statement embedded into the query that will actually return only those records with the most current syscreated date.
Example of script I'm using---
select cmp_fadd1, syscreated, cmp_name, cmp_code from cicmpy where cmp_fadd1 in (select cmp_fadd1 from cicmpy group by cmp_fadd1 having count(1) = 2) order by cmp_fadd1,syscreated desc
The results is:
Address Syscreated date Customer 1622 ONTARIO AVENUE 2005-06-15 22:19:45.000 RELIABLE PARTSLTD 1622 ONTARIO AVENUE 2004-01-22 18:10:05.000 RELIABLE PARTS LTD PEI CENTER 2006-01-05 22:03:50.000 P.G. ENERGY PEI CENTER 2004-01-22 17:57:56.000 P.G. ENERGY
From this I want to be able to select ONLY those records with the most current syscreated date or those records with 2005-06-15 and 2006-01-05
Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.
select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date) from master.sys.databases a left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id where c.type='D'
I have to produce a report, but not sure of the best way to get the required results
Aim - to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]
E.g.
10 Fdmsaccountno in Jan
Those 10 Fdmsaccountno vary in [Retail_amount]
I have 5 fdmsaccountno which are between %0 & £5 2fdmaccounno beterrn £6& £10 3 fdmsaccountno £10>
My query is
SELECT [FDMSAccountNo] ,[Month_end_date] ,[Retail_amount] FROM [FDMS].[dbo].[Fact_Fee_History] where [Fee_Sequence_Number] = '32r' and Month_end_date between '2013-01-01' and '2013-12-01'
I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.
I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:
UPDATE xLegHdr SET SMiles = IF Dist<250 THEN Round(Dist-25)*1.15) ELSE Round(Dist-40)*1.15) END IF
I have different codes and I want to specify different date of birth range based on selected code in the parameters.
DECLARE @CODE VARCHAR(5) = 'FXE' SELECT * FROM TABLE WHERE CODE = 'FXE' and AND DATE_OF_BIRTH BETWEENDATEADD(month,-15,GETDATE()) AND DATEADD(year,-1,dateadd(YEAR,-0,cast(GETDATE()asdate))))
How I can do for one code but I cant get my way around bringing another code 'WHY' which has a different date of birth range. Basically I want a user to be able to change a code in the parameter and display results based on the parameter selected.
Ehhm,great!! Could you tell me what is going wrong with the stored procedure below? I can't get any results! (the 'ap_dateIn' field in the sql table 'tbl_1' has "dd/mm/yy hh:mms" smalldatetime format)
This seems really simple so I'm suprised I'm having so much trouble. Perhaps I'm just overthinking the whole issue. I'm trying to write a query that will extract a list of Users from a table that have logged in within the last 90 days. Sounds easy huh? I'm still stumped!Here's the basics of the attempted query:SELECT UserNameFROM UsersWHERE (LastLoggedInDate<= DATEPART(dd, GETDATE() - 90))Thanks!
I am facing strange problem in SQL Server Query Analyzer, I am writing following query: Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/02/2001'
Query failed & gives following error: Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
But if I write same query in this way: Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '2001-02-28' OR Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/Feb/2001'
It works fine! Can anybody explain me why it is so???
Ok... Here is what I want to to: I have a number of tables, but this query concerns only two of them: Employee, Disposition, with a one to many relationship on the phone number.. Employee has personal info, and dispostion is a large table that keeps track of transactions. There is a unque_id for every transaction.
I want to pull some information. A date is assciated with each trans action on the disposition table. I want to be able to pull up all the unique employee phone numbers from a disposition table but only the latest date they made a transaction. My result should be only one phone nbr, one transaction number, and the date of the last transaction, per employee phne_nbr. But I can't seem to be able to pull this.
This query gets me the max date called per phne_nbr:
select 'Max'= max(dte_call), phne_nbr into TestTable from Disposition group by phne_nbr
The problem arises when I need to get the unique_id for that transaction, as I need it for other queries...The code below does not work...I get duplicate records and so forth.
select 'Max'= max(dte_call), phne_nbr, unique_id into TestTable from Disposition group by phne_nbr, unique_id
This shouldn't be that hard!! (((I tried a variation usin select distinct with the filed I wanted to be distinct in () otherfield, other field... but the distinct is not limited to the field in (), it gets the distinct record for the entire select ))) I must be missing something simple here. Can anyone help!!!
I am using BETWEEN '02/01/2007' AND '2/01/2006' in the criteria of a VIEW and have tried <= '02/01/2007' AND >='2/01/2006' but both are not filtering the dates correctly. Is there another way? What am I missing?
I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .
Create Table Test (Tsq INT IDENTITY (1,1), Data Varchar (150), ts datetime, Tpkt_type int) insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')
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?
I would like to write 1 proc that can take additional criteria if its sent in. An example is:
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = @VENDOR and Sitecode = @SITECODE and PackageType = @PACKAGETYPE )HB on HA.VendorPackageId = HB.VendorPackageId and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 )
Multiple @COMPONENTTYPE, @VALIDITEMTYPE,@ITEMVALUE can be sent in. Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_2 and ValidItemType = @VALIDITEMTYPE_2 and ItemValue = @ITEMVALUE_2 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_3 and ValidItemType = @VALIDITEMTYPE_3 and ItemValue = @ITEMVALUE_3 )
Ignoring the 2nd 2 selects if @COMPONENTTYPE_2, @VALIDITEMTYPE_2,@ITEMVALUE_2 and @COMPONENTTYPE_3, @VALIDITEMTYPE_3,@ITEMVALUE_3 are = ''
I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set:
SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......
WHERE CostFlag = @InputParameter
GROUP BY Dept, Project
The code above works great - but only for a single column. I need to return a dataset like this: Dept Project Cost_Mean Cost_Range D1 D1P1 495 135 D1 D1P2 960 70 D1 D1P3 1375 105
