sql,ms-access,ms-access-2007 , Add 1 to datediff result if time pass 14:30 or 2:30 PM

Add 1 to datediff result if time pass 14:30 or 2:30 PM


Tag: sql,ms-access,ms-access-2007

Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30.

My query

SELECT reservations.customerid,
       DateDiff("d",reservations.checkin_date,Now()) AS Due_nights
FROM reservations

Am using MS-Access DB


Could be:

    DateDiff("d",reservations.checkin_date, Date()) 
        + Abs(DateDiff("s", #14:30#, Time()) > 0)AS Due_nights


How to join result of a Group by Query to another table

I have the following Group By SQL: SELECT s.Login_Name, COUNT(s.s1CIDNumber) FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name I want to know how I can join this result set to another table(tblUsers) to add user_id to the result set (tblSurvey and tblUsers have 1-1 relationship on Login_Name) I tried the following: Select...

Entity Framework code-first: querying a view with no primary key

Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify. But for views not having primary key is it still possible to query. I try to find but always Entity Framework...

Default the year based on month value

I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...

T-SQL Ordering a Recursive Query - Parent/Child Structure

I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels. For example I could create a task (this is the parent), then create a sub-task from this and then...

Convert AWK command to sqlite query

I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...

I need help setting the RecordSource of a Report within a VBA Function

Ok, so I've been looking around here for a few days (and a few other sites) and while I have modified my code a good bit to find a solution, it still doesn't work. I have four source queries, and I'd like to set the record source of my report...

Select Statement on Two different views

I couldn't find the answer to my specific question anywhere. Is it possible to select from two different views? For example my code looks something like this right. select view1.col1, view1.col2, view1.col3 from dbo.view1 inner join ~~~~~ inner join ~~~~~ but I want to include a column from a different...

Title search in SQL With replacement of noice words [on hold]

I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

mysql_real_escape_string creates \ in server only not in local

When I use mysql_real_escape_string in my localhost and I output the result of html in a table I have no problem. But when I use it on my server it outputs even the \ This is how I use it: $_GETVARS['txtEmpNum'] = mysql_real_escape_string($_GETVARS['txtEmpNum']); $_GETVARS['txtLName'] = mysql_real_escape_string($_GETVARS['txtLName']); $_GETVARS['txtFName'] = mysql_real_escape_string($_GETVARS['txtFName']); $varSQL...

How do I find records in one table that do exist in another but based on a date?

I want to find all the dealers who haven't had an order in 2015 yet. I know this query doesn't work but I thought it might be helpful to understand what I want to do. In this example I want to get just "Bob" as a result. He is the...

Using Sum in If in Mysql

I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...

SQL Repeated Condition in Two Tables

I am a beginner to SQL, and I am having trouble getting rid of repeated code. I have a condition "AccountId=20" that I use twice in a SQL query. The SQL looks something like: DELETE FROM Table_A WHERE FunctionId IN (Select FunctionId FROM Table_B WHERE AccountId=20); DELETE FROM Table_B WHERE...

Select count Columns group by No

Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...

Looping distinct values from one table through another without a join

I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...

Pull information from SQL database and getting login errors

I am creating a very small, simple CRM for a company, they require the function to be able to view the last 25 orders via the dashboard. The orders are added via a Order-add form within the CRM. When adding the following code to the CRM I get an error:...

Matplotlib: Plot the result of an SQL query

from sqlalchemy import create_engine import _mssql from matplotlib import pyplot as plt engine = create_engine('mssql+pymssql://**:[email protected]:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') for row in result: print row connection.close() The above code generates an array: (54ca686d0189607081dbda85', 4174469) (551c21150189601fb08b6b64', 182) (552391ee0189601fb08b6b73', 237304) (5469f3ec0189606b1b25bcc0',...

Fastest way to add a grouping column which divides the result per 4 rows

If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

Can someone explain to me how this statement is an exclude?

Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

Get unique row by single column where duplicates exist

I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...

Merging two tables into new table by ID and date

Let' say I have two tables: table 1: col a | col b | col c 1 | 62215 | 21 1 | 62015 | 22 2 | 62215 | 23 2 | 51315 | 24 and table 2: col a | col b| col f 1 | 62015| z...

SQL: overcoming no ORDER BY in nested query

I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...

SQL Group By multiple categories

This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...

Add 1 to datediff result if time pass 14:30 or 2:30 PM

Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...

SQL Multiple LIKE Statements

I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

Recursive Lag Column Calculation in SQL

I am trying to write a procedure that inserts calculated table data into another table. The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not...

Select all none null values from all columns in a table

I am having an issue going through column by column and getting a count of all the non-null values in each column. I think issue is how I am getting the column names. In the last select statement where I am trying to select all of the values in each...

Take thousand value in SQL

I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...

Retrieve Values As Column

I've the following table in my PHPMYADMIN The desired output is headline                              impressions              clicks Buy A new Iphone                 ...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

for-loop add columns using SQL in MS Access

I am trying to add n columns to a table, like in this example of code where n = 10: Sub toto() Dim db As Database, i As Integer Set db = CurrentDb() For i = 1 To i = 10 db.Execute " ALTER TABLE time_series " _ & "ADD...

left join table, find both null and match value

I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

Improving work with SQL DataTime

I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...

SQL Customized search with special characters

I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

timestamp SQL to Excel

If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

oracle sql error Case When Then Else

SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

Retrieve data from one table and insert into another table

I am trying to retrieve data from one table and then insert it into another table. This is a a sample of the first table in which there is the following data. tb1 is the table which consists of data. The two columns Manager and TeamLeader basically means for example...

Is it possible to index views in Apache Solr

Let me first give you an example. I have two tables -table1 and table2. table1 has a field id_table2, which is a foreign key and references one of the fields in table2. So, when I want to scan table1, I make a query like: SELECT t1.attr_1_, t1.attr_2_, t2.attr_3_ FROM table1...

Purging Database - Count purged/not-purged tables

So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

Query how often an event occurred at a given time

[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...

Is there a better way to write this query involving a self select?

I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

MySQL: Select several rows based on several keys on a given column

I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...

Foreign key in C#

I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

SQL varchar variable inserts question mark

I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529‏' select @A Results: R2300529? any explanation? i'm using SQL server 2012....

How to delete only the table relationed

I know how to create the migrations with laravel 5.1 and I have the following code public function up() { Schema::create('articulos', function (Blueprint $table) { $table->increments('id'); $table->string('title'); $table->string('content'); $table->integer('categoria_id')->unsigned(); $table->integer('creador_id')->unsigned(); $table->string('slug', 32); $table->timestamps(); }); Schema::table('articulos', function($table) { $table->foreign('categoria_id')->references('id')->on('categorias');...

Inner Join 3 tables pl/sql

Basically i want to inner join 3 tables and the case is understood by the query itself as follows. Tables: A has 2 columns column 1 and column 2 B has 2 columns column 3 and column 4 C has 3 columns column 5,column 6 and column 7 Query: select...

How to select next row after select in SQL Server?

I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...

SQL only one cell from column can have X value

Is there a way to ensure, when you design a table - SQL, MySQL -, that only one cell from column X is true, the rest being automatically false? When you change the value of a different cell to true, all other cells should automatically become false. A radio button...

How can I create missing date records for each employee based off a limited calendar?

I have two tables, one an abbreviated calendar: create table dbo.calendar ( [date] datetime ) insert into dbo.calendar values ('20150101 00:00:00 AM') -- 1/1/15 insert into dbo.calendar values ('20150102 00:00:00 AM') -- 1/2/15 insert into dbo.calendar values ('20150103 00:00:00 AM') -- 1/3/15 insert into dbo.calendar values ('20150104 00:00:00 AM') --...