I am using the following query in a view to retrieve the latest 24 hourly records for a site.This returns 24 hourly records for the last day of measurements at a Site.This works great. However, I now need to retrieve the latest hourly records from the current hour. For example, hours will run from 00:00 to 23:00 and if the query is executed at 15:00, I will return only hourly records for 00:00 to 15:00 etc. I believe I need to filter the result set or modify the query to exclude records greater than the current hour.
I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?
if now the system time is 2014-12-23 23:45:345 . then I want to calculate the sales amount with total and get current date and current hour filter by hh:00-hh:59 , my desire output is like below :
Current time:23:50 run this query Date Time amount 2014-12-23 23:40 $10 2014-12-23 23:01 $5 2014-12-23 23:39 $5 Total :$20
I am trying to break down the content based of hourly basis. It works fine when there are values for that specific hour but if there are entries or values for a specific hour then it returns null instead of 0. How not to get null instead get zero.
Here is the code below:
With temp_exp As (Select pl.state,Cast(signeddate As date) As signatureDate, signeddate As DoneTime From contract c with(nolock) where c.signeddate>DateAdd(Day, Datediff(Day,0, GetDate()), 0) ) Select Sum(Case When CONVERT(varchar(8),DoneTime,108) Between '07:00:00' And '07:59:59' Then 1 Else 0 End) '8AM',
I am looking to write a query that returns all records Inserted in the last hour.
The problem, as I see it, is that the column I need to refer to is a VARCHAR() datatype. Can I convert from varchar (example 14:04:31)to a time value and calculate from this ?
I would like to subtract 1 hour from current_timestamp or similar, so that the query dynamically changed.
I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...
I have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.
The resulting table for two days would look something like: Time Hits 1/1/01 12:00 1 1/1/01 1:00 23 1/1/01 2:00 54 1/2/01 1:00 15 1/2/01 2:00 14
I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.
I have a table in SQLCE with ProductCode, Age, Rate, Gender, EffectiveDate. I need to get the list of records with the latest effectivedate not later than the current date for the combination of the other fields.
I have a problem with a date in my sql view, I need the records from the 3 last months but without the current month, if I execute my view right now I have the records from January to april but I just need from January to march, I must have always the 3 previous month but I don't know how I can do it
Hi all I've been having a really difficult time finding out how to run a query which selects records from an SQL database for the current logged on user. I've tried a whole manner of different approaches but here is where I am at now: <script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) If Not Page.IsPostBack Then ds_meobservingcolleagues.SelectParameters(0).DefaultValue = User.Identity.Name End If End Sub </script>
This is not resulting in any error. But it is not returning the records that it should be either. Please can anyone help? I'm sure it shouldn't be this difficult to do
My question is concerned with the three columns below (customerID, RepairDate, CompletedRepair (Yes or No). The column name "CompletedRepair " is blank initially. I need to update the CompletedRepair column with this logic below:
- A customer comes to our store to fix their car, if we fix their problem on the first time and they don�t return later for this same issue, then the �CompletedRepair column = Y
- If a customer needs to come back to our store to re-fix the same issue within 7 days windows based on the RepairDate on the previous transaction then �On the last return transaction: CompletedRepair = Y (example: RepairDate =6/12/2006) �On all previous transactions: CompletedRepair = N (example: RepairDate =6/8/2006, 6/9/2006, 6/10/2006)
- If a customer needs to come back to our store to re-fix the same issue but out of the 7 days windows based on the RepairDate then �On the last return transaction: CompletedRepair = Y (RepairDate =6/12/2006) �On the previous transaction: CompletedRepair = Y (RepairDate =6/1/2006)
Every time customer comes to for car repair shop for a new issue or an old issue, we create a new repair transaction in our SQL db. The update on the "CompletedRepair " column will be run every day. Today's records will be run against with last 7 days records (based on Repair Date) to check when customer has been really fixed: the last fix counted Y, the previous fix counted as N but comparison in only 7 days. In other words, a repair today is considered as a completed repair when comparing with last 7 day repairs but it might become not a completed repair if this same customer would come back within next 7 days for the same issue.
The CompletedRepair column is dynamic column and is updated daily by using the logic above.
Below is the expected outcome after we update the Completed Repair column:
CustomerID Repair DateCompleted Repair
ab1 06/12/06 Y ab1 05/28/06 Y ab1 05/18/06 Y ab105/15/06 N ab1 05/12/06 N
Initially 5/12/06 had Y, when 5/15/06 transaction came, it took the Y and made the 5/12/06 become N. The 5/18/06 transaction did the same to 5/15/06 transaction, made itself Y and converted 5/15/06 into N. The 5/28/06 is Y because comparing with 5/18/06, it is out of 7 days window. The 6/12/2006 is Y because comparing with 5/28/06, it is out of 7 days window.
ab2 06/02/06 Y ab2 05/28/06 N ab2 04/19/06 Y ab2 04/14/06 N
The 4/14/06 transaction initially was Y, it became N when new transaction on 4/19/06 came. Same thing with transactions on 5/28/06 and 6/2/06
ab3 05/11/06 Y ab3 03/29/06 Y ab3 03/23/06 N ab3 03/12/06 Y
The 3/23/06 was Y, when new transaction on 3/29/06 came, it became N and the new transaction is Y. The 5/11/06 is Y because comparing back to 3/29/06, they are out of 7 days window.
ab4 05/11/06 Y
This ab4 customer came to fix her car only one time and don't come back. We supposed the fix was sucessfully and so we mark the CompletedRepair as Y.
I think that I would need to use SQL cursor or case statement for this but I really don't know how to start. Please advice and help me out. Any ideas and suggestion are really appreciated! If you need more information, please let me know!
I was using Type 2 for one of our Fact table.... and need to put a flag to know which one is the Current record... I couldn't able to figure how to implement logic in the merge statement... This is an example Query ....I was using like this for my fact table...
Basically I need to track CustomerName and City... So I need a Currentflag (Y) for latest record....
MERGE INTO [dbo].[TargetCustomer] AS TRG USING [dbo].[MyCustomers] AS SRC ON TRG.[CustomerID] = SRC.[CustomerID] AND TRG.[CustomerName]=SRC.[CustomerName] AND TRG.[City]=SRC.[City]
I need to return the previous row value if it is negative in current row. For example, in the below table for ID=7 i need the value 1305(ID=4) since 6,5 are negative values.
I have some my below requirment to loading some last year and currnet year records for some ID's in my table,
We have to load the ID's that are active at the end of the year for the prior year and ID's that are active as of today for the current year.Here is the scenario when the ID is currently terminated but active at the end of the prior year and the record is not in the table.so, we didn’t load the count for the prior year
Here prior year is 2015-2015 and Current year is 2015-2016
CREATE TABLE remp_year (ID INT, STATUS NVARCHAR(100) NULL, START_DATE DATE NULL, END_DATE DATE NULL, date_year nvarchar(10) NULL)INSERT INTO remp_year VALUES (10,'Active','2015-05-26','2015-12-31','2015-2016');
[Code] ...
Here ID 20 and 50 for terminated records is the prior year records so it should count for the last year and those are active in this year those will count for this year.
Is there a simple command that can be executed to check if the current user has sysadmin privs? I just want to check to see if I have sysadmin privs and if so then execute a command, if not do nothing in .Net code. I just want to do this check once and set a variable in the .net code.
There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'
Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.
ID Date Price Status 1 11/12/2015 100 Low Value
2 11/12/2015 160 Ignore 3 11/12/2015 100 High Value
create table testmaster ( ID int, Threshold int ) create table testdetail ( ID int, Date varchar(20), Price float )
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX) @sql = ' insert into target select from table_1_currentdate a LEFT JOIN Table_2_currentdate-1 b on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
Apologies for the simplicity of the question, but it reflects mycapabilities! I have the following sample fields coming from differenttables:LocationTimeDate (timestamp)DataI need to return the average of Data per Location per HOUR.Thanks.
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29 if we pass year =2015 and month=09 then we have to get 2015-09-29 if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
I am trying to get hourly data on a table which was working fine on mysql but not on sql. how to use this in sql server managmenmt studio, i receive 'time' is not a recognized function name.and curdate() is not recognise function.
select [pa_number], [pa_surname] ,[pa_forename], sum(time(datetime) >= '07:00:00' and time(datetime) < '08:00:00') as '7.00-8.00 AM', sum(time(datetime) >= '08:00:00' and time(datetime) < '09:00:00') as '8.00-9.00 AM', sum(time(datetime) >= '09:00:00' and time(datetime) < '10:00:00') as '9.00-10.00 AM ', sum(time(datetime) >= '10:00:00' and time(datetime) < '11:00:00') as '10.00-11.00 AM',
HiHow can I create a job in sql agent to create a new snapshot every hour?I have, for eg a T-SQL that does it manually.create database Snapshotter_snap_20070418_1821 on( name = Snapshotter, filename ='c: empSnapshotter_snap_20070418_1821.ss')as snapshot of SnapshotterNow, what I do NOT want, is to only have one copy, but rather to do thisevery hour or two through out the day - and keep the old copies for sometime. (In that case, a DROP database, and a CREATE database <generic name>is easy).Any help appreciated,M
First time here so please bear with me.Set up a DTS package to export data to an excel sheet on an hourlybasis. Problem is, it keeps appending to the same excel sheet.Any idea how to prevent that. All I want to accomplish is that everyhour, the latest data is in the excel sheet and the previous data isdeleted.Thanks in advance!
I have a big table which has fields(ID, RecordDate, Value). The records were 15 minutes interval, start at yyyy-mm-dd 00:00:00 and end at yyyy-mm-dd 23:45:00 for each day. Some records may be missing.how can I do the query:if the every 15 min records exists in the day, do sum(value) for the day else do something else.I would like see difference between SQL 2008 and 2012.
