How to speed up the query in PostgreSQL

I have DB in PostgreSQL with a big data (now it is somewhere around 46 GB and the db will keep growing). I created indexes on often used columns and adapted the config file:

shared_buffers = 1GB
temp_buffers = 256MB
work_mem = 512MB

But this query is still slow:

select distinct us_category_id as cat, count(h_user_id) as res from web_hits 
inner join users on h_user_id = us_id 
where (h_datetime)::date = ('2015-06-26')::date and us_category_id != ''
group by us_category_id

Explain Analyze:

HashAggregate (cost=2870958.72..2870958.93 rows=21 width=9) (actual time=899141.683..899141.683 rows=0 loops=1)

Group Key: users.us_category_id, count(web_hits.h_user_id)
-> HashAggregate (cost=2870958.41..2870958.62 rows=21 width=9) (actual time=899141.681..899141.681 rows=0 loops=1)

Group Key: users.us_category_id
 -> Hash Join (cost=5974.98..2869632.11 rows=265259 width=9) (actual time=899141.679..899141.679 rows=0 loops=1)

Hash Cond: ((web_hits.h_user_id)::text = (users.us_id)::text)
-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)
-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)
Filter: ((h_datetime)::date = '2015-06-26'::date)

Rows Removed by Filter: 55051918
-> Hash (cost=4292.99..4292.99 rows=134559 width=10) (never executed)
-> Seq Scan on users (cost=0.00..4292.99 rows=134559 width=10) (never executed)
Filter: ((us_category_id)::text <> ''::text)

"Planning time: 1.309 ms"
"Execution time: 899141.789 ms"

Date is changed. How can I speed up the query?

Table and index creating

CREATE TABLE web_hits (
  h_id integer NOT NULL DEFAULT nextval('w_h_seq'::regclass),
  h_user_id character varying,
  h_datetime timestamp without time zone,
  h_db_id character varying,
  h_voc_prefix character varying,
  ...
  h_bot_chek integer, -- 1-бот...
  CONSTRAINT w_h_pk PRIMARY KEY (h_id)
);
ALTER TABLE web_hits OWNER TO postgres;
COMMENT ON COLUMN web_hits.h_bot_chek IS '1-бот, 0-не бот';

CREATE INDEX h_datetime ON web_hits (h_datetime);
CREATE INDEX h_db_index ON web_hits (h_db_id COLLATE pg_catalog."default");
CREATE INDEX h_pref_index ON web_hits (h_voc_prefix COLLATE pg_catalog."default" text_pattern_ops);
CREATE INDEX h_user_index ON web_hits (h_user_id text_pattern_ops);

 CREATE TABLE users (
  us_id character varying NOT NULL,
  us_category_id character varying,
  ...
  CONSTRAINT user_pk PRIMARY KEY (us_id),
  CONSTRAINT cities_users_fk FOREIGN KEY (us_city_home)
      REFERENCES cities (city_id),
  CONSTRAINT countries_users_fk FOREIGN KEY (us_country_home)
      REFERENCES countries (country_id),
  CONSTRAINT organizations_users_fk FOREIGN KEY (us_institution_id)
      REFERENCES organizations (org_id),
  CONSTRAINT specialities_users_fk FOREIGN KEY (us_speciality_id)
      REFERENCES specialities (speciality_id),
  CONSTRAINT us_affiliation FOREIGN KEY (us_org_id)
      REFERENCES organizations (org_id),
  CONSTRAINT us_category FOREIGN KEY (us_category_id)
      REFERENCES categories (cat_id),
  CONSTRAINT us_reading_room FOREIGN KEY (us_reading_room_id)
      REFERENCES reading_rooms (rr_id)
);
ALTER TABLE users OWNER TO sveta;
COMMENT ON COLUMN users.us_type IS '0-аноним, 1-читатель, 2-удаленный';

CREATE INDEX us_cat_index ON users (us_category_id);
CREATE INDEX us_user_index ON users (us_id text_pattern_ops);

No response from server : faulty php script?

So I'm continuing to work on this login.php script

<?php

$link = new MySQLi (localhost, Maelhann, userpassword, Logindata2256 ) ;

if ( mysqli_connect_error() ){

$logmessage = 'MySQL error : ' . mysqli_connect_error() ; 
die('could not connect to database');
}

$Email = $_POST['Email'];
$Password = $_POST['Password'];

 if ( int mysqli_result::$num_rows() == 1 ){

$_SESSION["Email"];
$_SESSION["Password"];
header("location:Newaccount.html");
 }
else{
echo "wrong username or password" ;

}

?>

but all I get when trying to log in is a blank page... any Idea where I could have failed?

How can I make a pseudo-dynamic Stored procedure / CTE

I need to write a sql query where I have a group of students who are in a specific program and display their completion status for each course in the program. The problem is that the program is a parameter to go into the query, and each time I run it (with a different program) each program has a different set of courses associated with it.

So, from a table that relates Students to programs. (StuPRograms) Many to many relationship between Programs and Courses, and then I get a result from another table (Enrollments) that joins Students to Courses. The rows are students while the columns are pivots using the courses, and the cells are the result of if that student has completed the course.

I could do a normal SQL Select with piles of calculated columns. (the columns being the student, the name of the first course, the status of the student in the first course, name of 2nd course, status in 2nd course.. etc..) but that would be crazy slow.

I've used TSQL to generate the data with a dynamic pivot, but because it has dynamic columns I can't put it in a Stored Procedure. (Because the SP needs to generate a constant schema.) So, I'm thinking if I limit my results to only look at a 10 course columns that would give it a static schema, so I can put it in a stored proc.. Effectively I'm looking for a better performing solution then to do 20 column sql statements for each StuProgram row returned.

MySQL Order by + Group by drastically slow down the query

I have the following query that, despite its complexity, performs well in the following shape:

SELECT 

`listings`.*, 
`auth_agents`.*, 
`symbols`.`code` AS `symbol_code`,
`symbols`.`text` AS `symbol_text`,
`symbols`.`position` AS `symbol_position`,
GROUP_CONCAT(DISTINCT IFNULL(`categories_countries`.`translation`, `c`.`name`) ORDER BY CASE WHEN `c`.`category_id` = 106 THEN 1 ELSE `c`.`category_id` END) as `category_names`,
`pv_1`.`value` AS `transaction_type_name`,
`pv_2`.`value` AS `property_type_name`,
`listings_statuses`.`name` AS `status`,
CONCAT(`pv_3`.`value`, 'property/', `listings`.`slug`, '/', `listings`.`listing_id`) AS `url`,
price_range(`listings`.`price_sales_min`, `listings`.`price_sales`, `symbols`.`code`, `symbols`.`position`, `listings`.`listings_price_qualifier_id`) AS `price_sales_range`,
price_range(`listings`.`price_lettings_min`, `listings`.`price_lettings`, `symbols`.`code`, `symbols`.`position`, `listings`.`listings_price_qualifier_id`) AS `price_lettings_range`,
summary(`listings`.`description`, `listings`.`summary`) AS `summary`,
short_title(`categories`.`name`, `categories`.`title`, `pv_1`.`value`, `listings`.`city`, `listings`.`region`, `listings`.`area`) AS `short_title`,
IF(`listings`.`listings_price_qualifier_id` = 2, TRUE, FALSE) AS `POA`,
IF((SELECT `listings_categories`.`listing_id` FROM `listings_categories` WHERE `listings_categories`.`listing_id` = `listings`.`listing_id` AND `listings_categories`.`category_id` = 124 LIMIT 1) IS NOT NULL, TRUE, FALSE) AS `serviced_office`,

GROUP_CONCAT(DISTINCT CONCAT_WS('|', `auth_agents_staff`.`auth_agents_staff_id`, `auth_staff`.`f_name`, `auth_staff`.`l_name`, `auth_staff`.`photo`)) AS `agents_staff`,
(SELECT CONCAT('http://ift.tt/1GIT2RV', `listings`.`auth_agent_id`, '/', `listings_media`.`filename`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` ORDER BY `default` DESC LIMIT 1) AS `image`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 1) AS `image_count`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 3) AS `pdf_count`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 4) AS `floor_plan_count`,


distance(`listings`.`lat`, `listings`.`lng`, 51.5073500, -0.1277600) AS `distance` 

FROM `listings` 
JOIN `listings_coords` ON `listings_coords`.`listing_id` = `listings`.`listing_id` 

JOIN `auth_agents` ON `listings`.`auth_agent_id` = `auth_agents`.`auth_agent_id` 
JOIN `branches` USING (`branch_id`) 
LEFT JOIN `listings_auth_agents_staff` ON `listings_auth_agents_staff`.`listing_id` = `listings`.`listing_id` 
LEFT JOIN `auth_agents_staff` ON `listings_auth_agents_staff`.`listing_id` IS NOT NULL AND `listings_auth_agents_staff`.`auth_agents_staff_id` = `auth_agents_staff`.`auth_agents_staff_id` 
LEFT JOIN `auth` AS `auth_staff` ON `listings_auth_agents_staff`.`listing_id` IS NOT NULL AND `auth_agents_staff`.`auth_agents_staff_id` IS NOT NULL AND 
`auth_staff`.`auth_id` = `auth_agents_staff`.`auth_id` 

JOIN `parameters_values` AS `pv_1` 
ON `pv_1`.`parameter_id` = 2 AND `pv_1`.`country_id` = 223 AND `pv_1`.`key` = `listings`.`transaction_type` 

JOIN `parameters_values` AS `pv_2` 
ON `pv_2`.`parameter_id` = 1 AND `pv_2`.`country_id` = 223 AND `pv_2`.`key` = `listings`.`property_type` 

JOIN `parameters_values` AS `pv_3` 
ON `pv_3`.`parameter_id` = 5 AND `pv_2`.`country_id` = 223 

JOIN `countries` ON `listings`.`country_id` = `countries`.`country_id` 
JOIN `countries_symbols` ON `countries`.`country_id` = `countries_symbols`.`country_id` 
JOIN `symbols` ON `countries_symbols`.`symbol_id` = `symbols`.`symbol_id` 

JOIN `listings_units` ON `listings`.`listings_unit_id` = `listings_units`.`listings_unit_id` 

JOIN `listings_units` AS `user_units` ON `user_units`.`listings_unit_id` = 2 


JOIN `listings_statuses` ON `listings`.`listings_status_id` = `listings_statuses`.`listings_status_id` 
LEFT JOIN `listings_statuses_countries` ON `listings_statuses`.`listings_status_id` = `listings_statuses_countries`.`listings_status_id` 
AND `listings_statuses_countries`.`country_id` = '223' 

JOIN `listings_categories` ON `listings_categories`.`listing_id` = `listings`.`listing_id`  
JOIN `categories` ON `categories`.`category_id` = `listings_categories`.`category_id` 

JOIN `listings_categories` AS `lc` ON `lc`.`listing_id` = `listings`.`listing_id`  
JOIN `categories` AS `c` ON `c`.`category_id` = `lc`.`category_id` 
LEFT JOIN `categories_countries` ON `c`.`category_id` = `categories_countries`.`category_id` AND `categories_countries`.`country_id` = '223' 

LEFT JOIN `listings_lettings`  
ON (`listings`.`transaction_type` = 2 OR `listings`.`transaction_type` = 3) AND `listings_lettings`.`listing_id` = `listings`.`listing_id` 
LEFT JOIN `listings_lettings_rent_frequencies`  
ON (`listings`.`transaction_type` = 2 OR `listings`.`transaction_type` = 3) AND `listings_lettings_rent_frequencies`.`listings_lettings_rent_frequency_id` = `listings_lettings`.`listings_lettings_rent_frequency_id` 

LEFT JOIN `listings_lettings_rent_frequencies` AS `llrf` ON `llrf`.`listings_lettings_rent_frequency_id` = 2 
WHERE `listings`.`active` = 1 AND `listings`.`published_flag` = 1 AND 
`listings`.`listings_status_id` = 1 AND
`listings`.`lat` BETWEEN 51.333436956522 AND 51.681263043478 AND 
`listings`.`lng` BETWEEN -0.40717687879372 AND 0.15165687879372 AND 
st_within(`listings_coords`.`coords`, @poly) AND 

IF (`listings`.`transaction_type` = 2 || `listings`.`transaction_type` = 3, 
`listings`.`price_lettings` * `listings_lettings_rent_frequencies`.`months` >= `llrf`.`months` * 500,
TRUE
) AND 
IF(`listings_units`.`meters` IS NULL, TRUE, `listings`.`max_size` * `listings_units`.`meters` >= 100 * `user_units`.`meters`) AND 
`listings`.`country_id` = 223 

GROUP BY `listings`.`listing_id`  
ORDER BY `listings`.`created_at` 
LIMIT 10; 

As you can see, the first query grabs the polygon which then is used in polygon search through spatial function. The query executes in aprox 0.06sec.

However adding any ORDER BY clause results in a drastic performance drop and ends up executing in 17 seconds.

Now - I've done some research and I know this might be to do with either clustered indexes or just adding the right indexing in general, but after hours spent trying to either return just the ID's and then run another query to get the data or simply getting rid of the GROUP BY and removing the joins (which seemed to have worked at first but then another performance issues appeared when I joined a few tables to do a price search) - I gave up.

I tried to run a query without grouping and then group the results, like:

SELECT * FROM ( *the query* ) AS results GROUP BY listing_id

But I got pretty much the same results.

Is there a way to optimise the query so I could do both grouping and sorting?

Here is the EXPLAIN:

    |  1 | PRIMARY            | countries                          | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | Using index; Using temporary; Using filesort                              |
    |  1 | PRIMARY            | user_units                         | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_statuses                  | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_statuses_countries        | const  | PRIMARY,country_id                                                                | PRIMARY                              | 8       | const,const                                                      |    1 | Using index                                                               |
    |  1 | PRIMARY            | countries_symbols                  | ref    | PRIMARY,symbol_id                                                                 | PRIMARY                              | 4       | const                                                            |    1 | Using index                                                               |
    |  1 | PRIMARY            | symbols                            | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.countries_symbols.symbol_id                           |    1 | NULL                                                                      |
    |  1 | PRIMARY            | pv_2                               | ref    | parameter_id,country_id                                                           | parameter_id                         | 8       | const,const                                                      |    2 | NULL                                                                      |
    |  1 | PRIMARY            | pv_1                               | ref    | parameter_id,country_id                                                           | parameter_id                         | 8       | const,const                                                      |    3 | NULL                                                                      |
    |  1 | PRIMARY            | pv_3                               | ref    | parameter_id                                                                      | parameter_id                         | 4       | const                                                            |    4 | NULL                                                                      |
    |  1 | PRIMARY            | listings                           | range  | SEE BELOW                                                                         | compound                             | 20      | NULL                                                             | 2594 | Using index condition; Using where; Using join buffer (Block Nested Loop) |
    |  1 | PRIMARY            | listings_units                     | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.listings_unit_id                             |    1 | Using where                                                               |
    |  1 | PRIMARY            | listings_coords                    | eq_ref | PRIMARY,coords                                                                    | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using where                                                               |
    |  1 | PRIMARY            | auth_agents                        | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.auth_agent_id                                |    1 | NULL                                                                      |
    |  1 | PRIMARY            | branches                           | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.branch_id                                    |    1 | Using index                                                               |
    |  1 | PRIMARY            | listings_categories                | ref    | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | lc                                 | ref    | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | listings_auth_agents_staff         | ref    | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | categories                         | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings_categories.category_id                       |    1 | NULL                                                                      |
    |  1 | PRIMARY            | c                                  | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.lc.category_id                                        |    1 | NULL                                                                      |
    |  1 | PRIMARY            | auth_agents_staff                  | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings_auth_agents_staff.auth_agents_staff_id       |    1 | Using where                                                               |
    |  1 | PRIMARY            | auth_staff                         | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.auth_agents_staff.auth_id                             |    1 | Using where                                                               |
    |  1 | PRIMARY            | categories_countries               | eq_ref | PRIMARY,categories_countries_country_id_foreign                                   | PRIMARY                              | 8       | cp_staging.lc.category_id,const                                  |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_lettings                  | ref    | listings_lettings_listing_id_foreign                                              | listings_lettings_listing_id_foreign | 4       | cp_staging.listings.listing_id                                   |    1 | Using where                                                               |
    |  1 | PRIMARY            | listings_lettings_rent_frequencies | eq_ref | PRIMARY,listings_lettings_rent_frequencies_id_months                              | PRIMARY                              | 4       | cp_staging.listings_lettings.listings_lettings_rent_frequency_id |    1 | Using where                                                               |
    |  1 | PRIMARY            | llrf                               | const  | PRIMARY,listings_lettings_rent_frequencies_id_months                              | PRIMARY                              | 4       | const                                                            |    1 | Using where                                                               |
    |  5 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  4 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  3 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  2 | DEPENDENT SUBQUERY | listings_categories                | eq_ref | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 8       | func,const                                                       |    1 | Using index                                                               |

5th column value for listings table (pulled out so that format of above table could be preserved) PRIMARY,listings_former_id_unique,listings_price_sales_index,listings_price_sales_min_index,listings_price_lettings_index,listings_price_lettings_min_index,listings_country_id_foreign,listings_agent_id_foreign,listings_listings_status_id_foreign,listings_listings_price_qualifier_id_foreign,listings_listings_unit_id_foreign,listings_listings_tenure_type_id_foreign,agents_staff_id,listings_listings_class_id_foreign_idx,listings_listings_location_type_id_foreign_idx,listings_listings_branch_id_foreign_idx,latlng,listings_city,listings_created_at,listings_country_created_at,listings_transaction_type,listings_listing_id_transaction_type,listings_listings_coords_id,listings_created_at_listing_id,compound

How to open a URL in an SSRS report in a new window? While the URLs keep changing in every record

I want to be able to open links in a new window in my SSRS report. The problem is these links are not the only things present in the record, and there is other data present. I keep seeing examples where they are considering only a link to be present in the record with no other data.

Also the data in the record includes HTML tags and I checked the option that says "HTML - Interpret HTML tags as styles" on the placeholder properties window.

Example of data in the record:

Hello! Welcome to google, the most used search engine. 
Click on www.google.com to go the website

So when I click on the www.google.com URL, it should open in a new window.

Edit: The URLs keep changing in every record.

I want more example on Optimization SQL Query

I want more example on Optimization SQL Query

"Advanced" SQL tutorial (t sql preferred)

So, I've recently started learning SQL, but I've hit a bit of a roadblock: nothing seems to be offered online besides the most basic topics. I used the tutorial at khan academy and GalaXQL but there doesn't really seem to be a site with more advanced problems than those contained within the previous two sites. Does anyone know of a place I can get used to more advanced queries / challenge myself further? I'm working within a T SQL environment, so anything related to that would be great, but I'll be happy with anything I can get.

OrientDB Time Search Query

In OrientDB I have setup a time series using this use case. However, instead of appending my Vertex as an embedded list to the respective hour I have opted to just create an edge from the hour to the time dependent Vertex.

For arguments sake lets say that each hour has up to 60 time Vertex each identified by a timestamp. This means I can perform the following query to obtain a specific desired Vertex:

SELECT FROM ( SELECT expand( month[5].day[12].hour[0].out() ) FROM Year WHERE year = 2015) WHERE timestamp = 1434146922

The first part of this question is whether there are any advantages/disadvantages to storing the timestamp as a property of the Vertex (as above query) versus storing it as the edge (or an edge property). In which case I think the query would be:

SELECT expand( month[5].day[12].hour[0].out('1434146922') ) FROM Year WHERE year = 2015

Although this looks more elegant I have 2 concerns; the overhead of creating new edge types. The flexibility if you don't actually know the exact timestamp.

The second part of the question relates to the fact that once I have isolated the individual Vertex based on time, this is only the head of a hierarchal tree of Vertex.

I could of course get the @rid from the above query and then construct a new query but I was wondering how I could adapt the above to do it all in one. For example assume there is a boolean property called active in all the hierarchal Vertexes. How can I get all the Vertex that are true?

Unable to grant Postgres permissions.

I'm using the following set of commands on Postgres 9.1 that has tables in abc.onlySchema.

create user myuser with password '1234';
create role mygroup inherit;

grant all privileges on database abc to mygroup;
grant all privileges on all tables in schema onlySchema to mygroup;
grant all privileges on all sequences in schema onlySchema to mygroup;
grant all privileges on all functions in schema onlySchema to mygroup;

grant mygroup to myuser;

The commands are entered by superuser myadmin. When I log in as myuser, I'm unable to select from tables I know exist and the command \dt returns nothing. Set role mygroup changes nothing. Am I missing some magic command that tells Postgres that I was serious when I issued the above commands. Perhaps a FLUSH PRIVILEGES-like command.

Btw, when I issue \dp as my myadmin user, the following appears {...,myuser=arwdDxt/myadmin} and I have set search_path = onlySchema; AND alter role myuser set search_path to onlySchema;. Still nothing.

Extracting event order from SQL table

I have a SQL table similar to this:

+----+-------+--------+-------+-------+
| rd | ts_in | ts_out | sens  | devid |
+----+-------+--------+-------+-------+
|  1 |     0 |   1577 |     2 |    2  |
|  1 |   229 |   1549 |     1 |    2  |
|  1 |   123 |   1322 |     0 |    2  |
+----+-------+--------+-------+-------+

Where a data logger ends up storing it's data.

This data logger is stored underground and it have two inductive devices that detect when a car walks on top of it, one on it's beginning, another on it's end. It also have a device between the two sensors which weights the car axis on top of it.

When an inductive device detects a car is on top of it, the datalogger takes a timestamp of it's internal clock (ts_in) and waits until the sensor is deasserted, by then it takes another snapshot (ts_out) and it inserts a line on the table containing the datalogger id (devid), the sensor which triggered (1 = front, 2 = rear) the event, and the related timestamps. It also sets the rd field to a per-sensor unique counter (but it's not a per-device, as you can notice in the example).

The weight device also have similar behavior but it stores sens=0 and other data not present in the example (but it's ts_in and ts_out works the same).

One can think of I would always get data like this: sens 1 -> sens 0 -> sens 2 if the vehicle goes in some direction and: sens 2 -> sens 0 -> sens 1 if the vehicle goes in the opposite direction, but there are caveats:

a) the sensors may fail. The weight doesn't, so sometimes I can get only a sens 0 event, other times it gets it and one of the sensors but not the other.

b) I need to match the events around a sens0 entry: if I have a sens0 entry with a ts_out > sens1_tsout && sens2_tsout, I know those sens1/sens2 entries can't belong to this one.

In order for this information to be useful to me, I need to do a self join on the table and extract the following information:

• I need do determine, for every sens0 entries, if there's a pair of matching sens1 & 2 entries for which it's ts_out < sens0_tsout, and I also need to get the ts_in of the first sensor that hits (something in mysql like LEAST(a.ts_in, a.ts_out) AND GREATEST(a.ts_out, b.ts_out).

What I expect in output is something like (the column order is unimportant):

+------| --------- | ------------+
|devid | direction |  sens0_data |
+------| --------- | ------------+
| 2    |  *text*   |  *weigth*   |
+------| --------- | ------------+

Where text could be something like:

"forward" (i.e. tsin_sens11 > tsin_sens2 && tsout_sens2 > tsout_sens1);

"backward" (i.e.tsin_sens12 > tsin_sens1 && tsout_sens1 > tsout_sens2) "forward_rewind" (i.e. tsin_sens1 > tsin_sens2 && tsout_sens2 < tsout_sens1);

"backward_rewind" (i.e. tsin_sens2 > tsin_sens1 && tsout_sens1 < tsout_sens2);

NULL (i.e. there's no lines maching the above conditions, inclusive the case where there's no matching sens1/2 lines where ts_out < sens0 ts_out).

Basically, I need to prove, for every sens0 line, that I either:

• Have a nearby (in terms of ts_in & ts_out) pair of sens = 1 and sens = 2 lines;

• Having those, that the car entered on one direction (which I must differentiate) and left on the other, or that it went backwards.

• That, absent those conditions, I only have the sens = 0 data and NULL for the direction field.

Is that feasible in two or three inner joins or should I take a multiple query step?

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. - error

I got problem with second question, becasue i receive error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.". Can you help me?

Select P.Name, P.ProductCategoryID, PC.Name, Max(P.ListPrice) as MaxPrice
from SalesLT.Product as P
join SalesLT.ProductCategory as PC
on PC.ProductCategoryID=P.ProductCategoryID
where P.ListPrice=
    (select Max(ListPrice)
    from SalesLT.Product as P2
    where P2.ProductCategoryID=P.ProductCategoryID)
group by P.Name, P.ProductCategoryID, PC.Name
order by MaxPrice desc;

go

with sale_cte as 
    (Select PC.Name, P.ProductCategoryID, P.Listprice,
    ROW_NUMBER() over(partition by PC.Name order by P.Listprice) as RN
    from SalesLT.Product as P
    join SalesLT.ProductCategory as PC
    on PC.ProductCategoryID=P.ProductCategoryID)
Select PC.Name,
    (select *
    from sale_cte)
from SalesLT.ProductCategory as PC
wher RN=1

How to setup MS SQL for Lightswitch intrinsic deploy - exception occurred when building the database for the application

Question Sign in to vote 0 Sign in to vote VS2013 Community html project, SQL2014 Standard db

Does anyone have a good walkthrough of deploying with an intrinsic db? Deployment with external db works fine, but not with intrinsic.

F5 build works fine on localhost with external or intrinsic db, server deploy to IIS/SQL works fine with external db... just not with intrinsic db...

None of the docs I have found are real detailed about how to setup sql server to handle the intrinsic deployment.

Created the sql project, selected that in LS app properties. Do I create a DB on the SQL server or does LS do that? The Publish dialog on the database step says the admin account will be used to "create and update" the db. Have tried sql admin account with and without specifying a target db. Can the user account be the same as the admin account? Tried that both ways.

getting a count from 1 table given an input from another

I have 3 tables, filer_info, filer_persent, and persent_email connected via filer_info.filer_info_id=filer_persent.filer_info_id and filer_persent.persent_info_id=persent_email.persent_info_id

I want to find all rows where I have multiple type PRIMARY in the persent_email table (ie count > 1). And the only thing I want to return in the query is filer_info.filer_ident and the count.

This gives me every row, but I only want the data where filer_ident > 1 in the returned rows.

       select * from filer_info f
       inner join filer_persent fp on f.filer_info_id=fp.filer_info_id 
       inner join persent_email p on fp.persent_info_id=p.persent_info_id
       where fp.filer_persent_kind_cd = 'FILER' and     p.persent_email_kind_cd='PRIMARY'
       order by f.filer_ident

Way to eliminate DISTINCT in this query

This query returns the correct SUM, but its duplicating itself:

SELECT [GrandTotal] = (total1 + total2)
FROM (SELECT(SELECT SUM([Total]) [total1] FROM [ooc].[RentalRevenue2] WHERE [CheckOut] >= '2015-01-01' AND [CheckOut] < '2015-06-30' AND [CampaignName] NOT LIKE '%Non-Cancellable%') as total1,
            (SELECT SUM([Total]) [total2] FROM [ooc].[Revenue2] WHERE [DateConfirmationReceived] >= '2015-01-01' AND [DateConfirmationReceived] < '2015-06-30' AND [CampaignName] LIKE '%Non-Cancellable%') as total2
      FROM [ooc].[Revenue2]) T 

I can eliminate this behavior by adding DISTINCT.

SELECT DISTINCT [GrandTotal] = (total1 + total2)
FROM (SELECT(SELECT SUM([Total]) [total1] FROM [ooc].[RentalRevenue2] WHERE [CheckOut] >= '2015-01-01' AND [CheckOut] < '2015-06-30' AND [CampaignName] NOT LIKE '%Non-Cancellable%') as total1,
            (SELECT SUM([Total]) [total2] FROM [ooc].[Revenue2] WHERE [DateConfirmationReceived] >= '2015-01-01' AND [DateConfirmationReceived] < '2015-06-30' AND [CampaignName] LIKE '%Non-Cancellable%') as total2
      FROM [ooc].[Revenue2]) T 

I don't think this is the correct way of executing this SUM query.

SQL Select - How to show multiple columns based on field value?

I have been trying to put together an SQL query that shows one line for each record with the values from another field broken out into their own columns. How would I be able to show multiple columns and a single row for each record?

I have a table with data similar to the following sample:

+--------------+------------+---------------+
| Employee_Num | Client_Num | Deduction_Num |
+--------------+------------+---------------+
|         1305 |       1000 |             1 |
|         1305 |       1000 |            30 |
|         1312 |       1000 |             1 |
|         1320 |       1000 |             1 |
|         1320 |       1000 |            30 |
|         1323 |       1000 |            30 |
|         1323 |       1000 |             1 |
+--------------+------------+---------------+

I have attempted a union but the results still show multiple records for each employee. Here's what I have tried thus far:

SELECT Employee_Num, Client_Num, Deduction_1, Deduction_30
FROM ( SELECT 
    Employee_Num,
    Client_Num, 
    Deduction_Num AS Deduction_1, 
    Deduction_Num AS Deduction_30
    FROM Employee_Deductions
    WHERE client_num = 1000
    AND Deduction_Num IN (1) 
UNION

    SELECT 
    Employee_Num,
    Client_Num, 
    Deduction_Num AS Deduction_1, 
    Deduction_Num AS Deduction_30
    FROM Employee_Deductions
    WHERE Client_Num, = 1000
    AND Deduction_Num IN (30)  
) AS Datum

WHERE Client_Num = 1000
ORDER BY Employee_Num

I would like this to be the desired result:

+--------------+------------+-------------+--------------+
| Employee_Num | Client_Num | Deduction_1 | Deduction_30 |
+--------------+------------+-------------+--------------+
|         1305 |       1000 |           1 |           30 |
|         1312 |       1000 |           1 |              |
|         1320 |       1000 |           1 |           30 |
|         1323 |       1000 |           1 |           30 |
+--------------+------------+-------------+--------------+

Any help would be appreciated.

Preferred way of data upgrade considering abstraction of data storage format

The core question is how to properly execute upgrade of data while applications versions differ in matter of data representation? E.g. for databases it is often to use SQL scripts to modify persisted data model. I can easily imagine similar way using XSL for XML and so on. But I would like to create abstraction of data persistence model in that way that upgrade method does not consider way of storing data - it is suffice that it can read previous version data and save new ones.

I tried doing such thing in C# like loading previous version of assembly containing data definition classes through reflection while having current version deployed with application. It is quite complex and tricky solution. Other option is to keep previous model classes redundantly with some version based naming conversion - this solution is easier as it gives full access to types at compile time.

My idea asking this question is, what is the proper solution of such upgrades of data?

Get start date of week using datepart

I have a table with 2 columns - Date, ItemsSold. I have the following query

Select datepart(week, [Date]) as WeekNo, SUM([ItemsSold]) as Total From [table] 
group by datepart(week, [Date])

This gives the following output

WeekNo   Total
3         20
4         30

What I actually need is an output as follows

WeekOf          Total
11 Jan 2015      20
18 Jan 2015      30

How do I get the start date of the week that datepart returns?

SQL Server Security Logins

On our Development and test servers I have added a windows authentication login for a new employee. I need this login to work for all databases on the server and to allow for them to be able to create, add, etc. I know if I set the server role to sysadmin it will allow all the permissions but is this the proper way to set up the user? Should there be some other role that I should map the user to and grant permissions through that role?

Translate plain sql to sql alchemy

I have this generic query, that i want to reproduce in sql alchemy

select id, last_year, ((atr_a * 0.3 + atr_b * 0.3 + atr_c * 0.3) / 0.9) as rating 
from analytics_product 
ORDER BY rating DESC, year DESC

I have a relation of each product can have an entry in the rating table.

class Review_product(db.Model):
    __tablename__ = 'review_product'
    id = db.Column(db.Integer, primary_key=True)
    last_year = db.Column(db.SmallInteger) 
    analytics = db.relationship('Review_product', backref='review_product',  lazy='joined', uselist=False)

class Analytic_product(db.Model):
    __tablename__ = 'analytics_product'
    id = db.Column(db.Integer, primary_key=True)
    review_id = db.Column(db.Integer, db.ForeignKey('review_product.id'), nullable=False)
    atr_a = db.Column(db.Integer)
    atr_b = db.Column(db.Integer)
    atr_c = db.Column(db.Integer)

How can i generate the same output as the query above using these models? Please not that i don't have the last_year in the analytics table.

SQL Requests against local SQL database with android simulator

This is kind of a weird question, so I'm open to suggestions.

I'm working on an Android app (using Xamarin, although I don't think that's relevant here other than to tell you that it's in c#). I would like to make some sql queries from the Android app to get and update some information on a sql server. I think I've figured out how to do this properly - unless there is some special case for android, I mainly followed this tutorial: http://ift.tt/1R0aEE0

Assuming that works, my issue has to do with testing it. I'm running the application on the android simulator, and the sql database is running locally. However, it appears that the simulator is not able to talk to the local database (which would make sense) since I'm getting:

System.Data.SqlClient.SqlException: Server does not exist or connection refused. ---> Mono.Data.Tds.Protocol.TdsInternalException: Server does not exist or connection refused. ---> System.Net.Sockets.SocketException: Could not resolve host 'HOST'

So, my question is: is there a way for me to test my android application's sql functionality using a local sql database and the android simulator? If not, what's the simplest way I could test this functionality?