Looking to improve performance of the following code.
It basically generates future days for each dog. So there is a dog table and a day table with every day.
These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.
All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.
How to make the last query as fast as possible.
IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL DROP TABLE dbo.[AllDates] CREATE TABLE dbo.[AllDates] ( [Date] date not null PRIMARY KEY ) ;WITH Dates AS
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
In my new job I have to administer an existing SQL-database with approx. 50 tables. In this database are no joins :confused: defined between the tables. We use a Visual Basic 6 application to create a GUI and within this VB6 app. there are several SELECT statements to retrieve the required data. In these SELECT statements are all the INNER and OUTER JOINS between the tables defined. My question: is this a correct way to work with or is it better to create all the JOINs between the tables on the database itself? Or should I create different views and define the JOINs in there? My main concern is the speed to retrieve data and second the required time to administer this database.
Basically I need to get the SUM of the sum of three columns and all three columns have nulls. To make it more complicated, the result set must return the top 20 in order desc as well.
I keep facing different issues whether I try and use Coalesce, IsNull, Sum, count, anything. My query never returns anything but 0 or NULL regardless of if I am trying to build a CTE or just use a query.
So I'm using Col A to get the TOP 20 in order (which is fine) but also trying to add together the sums of Col A + Col B + Col C for each of the twenty rows...
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
My question : How Do I bring all rows from TblB and matching single row from TblA (I have more than one sessionID in TblB and only one Unique SessionID in TblA).
The Select query I was using is
SELECT PageViews.ID AS ID, PageDetailView.VisitID, PageViews.PageAccessed, PageDetailView.PageAccessed AS Expr1, PageViews.QueryString, PageDetailView.QueryString AS Expr2, PageViews.Referer, PageViews.SessionID, PageDetailView.SessionID AS Expr3, PageDetailView.PdtID, PageDetailView.Pcode, PageDetailView.CustID, PageDetailView.OrdTot, PageViews.[Date] FROM PageViews RIGHT OUTER JOIN PageDetailView ON PageViews.SessionID = PageDetailView.SessionID ORDER BY PageViews.ID DESC
I have a basic sql statement, where I have a usersID, and I want to joing that usersID to another table in another database to get the users first and last names. How do I join across databases... each with a different connection string? Here's what I want.. Select usersID from tableA in databaseA, and usersFirstName, usersLastName from table B in database B where the usersID from tableA = the usersID in tableb.
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr, FROM plan2008 GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr FROM fsp_auftrag GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
The following returns all base tables within the database of type "varchar":
Code: SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb' AND DATA_TYPE IN('varchar')" AND TABLE_NAME IN( SELECT TABLE_NAME FROM mydb.information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')
What I then want to do is... For each of these results:
Code: select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME] WHERE ID = 'test'
Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?
Values of two columns in two different tables--presentation usingselectHi Everyone,i have two tables in the database . One is called address tableand one is adressPhone Table. Below is the sample of those two tablesAddresscol1 col2 col3X 12 13y 15 19z 18 10create table address(col1 varchar(20),col2 int, col3 int)insert into address values ('x',12,13)insert into address values ('y',15,19)insert into address values ('z',18,10)AddressPhoneCol4 Col5 Col613 213-455-9876 113 415-564-6546 213 543-987-5677 319 678-555-2222 1create table addressphone(col4 int, col5 varchar(50),col6 int)insert into addressphone values(13,'213-455-9876',1)insert into addressphone values(13,'415-564-6546',2)insert into addressphone values(13,'543-987-5677',3)insert into addressphone values(19,'678-555-2222',1)I have to display something like thisx 12 13 213-455-9876 415-564-6546 543-987-5677y 15 19 678-555-2222 NULL NULLSo there is one to many relationship between address andaddressPhone table where address.col3 = addressphone.col4I don't know how to write the query to get the phone numbers thathas he same id in the same row like I displyed above.I did something like this, but this is not workingselect col1,col2,col3, (select col5 from addressPhone where col6=1),(select col5 from addressPhone where col6=2), (select col5 fromaddressPhone where col6=3),from address table inner join addressPhoneon address.col3=addressphone.col4above is not working because it is complaining that a subquerycannot return multiple results. Col6 in the addressphone table isthe phone type ike business phone,mobile phone or home phone. It is possible that there are two phonenumbers for business phone.Please let me know how can I write this query.Any help will be greatly appreciated.Thanks
Card number and date of borrowers earliest loan for all borrowers who had a loan before the 03/Jan/2004 OR who borrowed a book published before 1920
So far my query looks like this but it is bringing back date out after 03/Jan
select cardno, min(l.dateout) from loan l right outer join book b on l.isbn = b.isbn where b.yearpublished < '1920' or l.dateout < '03/Jan/2004' group by cardno order by cardno;
I need to join 2 tables but the join needs to account for 2 seperate columns.
for example:
select a. type a. prod_code a. prod_type b. division
from table1 a left join table2 b on a. prod_code = b. prod_code and a. prod_type = b. prod_type
The issue is that you may have only the prod_code or prod_type and null value for the other in table1.
Ideally I want it to check for both then if 1 isn't available then it draws the division of the available. having both or one or the other determines the division it falls under.
WE have a query which pulls revenue by country and client for the last 3 years. Right now we have each year being reported in separate columns but we would like to have the revenues for each year for each client to appear on one row. Below is the current query we have setup.
SELECT p.country_code, p.local_client_code, wwc.local_client_name, case when pr.fiscal_year = 2015 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2015 Revenue',
I have a query that has a left join with a large partitioned table. The partitioned table has 10s of millions of records, and each partition has about 100,000 records.
The left join is part of an insert that gets a column from the partitioned table, if the column exists. The query contains the partition ID and all other joined columns are part of a non-clustered index.
Through the profiler, I found that there were millions of reads and the execution plan was giving me a table scan on the partitioned table.
I changed the query to do the insert followed by an update with inner join. That did the trick, but it worries me that SQL Server 2014 behaves differently from 2012 or 2008R2, which can make upgrading very time consuming.
What I want to achieve is to get values from period1 till period04 and used the lasted value to code the value of accoutperiod, if value is from period1 then code it as 01, period2 as 02, period03 as 03 and period04 as 04. So the output should be like this
Within the LinkingID, there are duplicates in ID1 and ID2 but just in opposite columns. I have been trying to figure out a way to remove these set based. It doesn't matter which duplicate is removed. Essentially these are just endpoints and I don't care which side they are on. The solution must recognize the duplicates and not just remove based on every 2nd row.
I have created row level security on two views and adding these two views to particular role.Today I have got an requirement that, middle level managers shouldn't see the all the columns. So I have created another role for Middle level managers and assign securables as those two views with selected columns by grant, and map all the middle level managers to this role. I thought my job is done. But these managers uses this view on SSAS(tabular model) and Excel, In those applications they are not able to load the data.
Later I come to know we can't use -- select * from ViewA ( in viewA I have restristced few columns in the role level) Work around is creating another view and assigning to the role. But how can we achieve column level security to implement this in either SSAS/SSRS/EXCEL?
The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.
Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;
Msg 156, Level 15, State 1, Line 315 Incorrect syntax near the keyword 'select'. Msg 102, Level 15, State 1, Line 315 Incorrect syntax near ')'.
adding or moving ')' or '(' are not working.......
select * into #temp from #source pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value select * from #temp order by .........
versus
select * into #temp from #source pivot ( avg(value) for drive in (select distinct(column) from #source)) as value
I'm trying to capture Column Statistics Profile as if I was using SSIS data profiling task. I do not have this option and would like to see how I could go about capturing the min max and avg of all numeric columns within a database.
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that -- two queries against a columnstore index that each execute in batch mode -- will continue to execute in batch mode when inner joined. -- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows IF OBJECT_ID('dbo.IntCount') IS NOT NULL DROP FUNCTION dbo.IntCount;
I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths. eg..
Input 1:
Table A: (below are the fields and their respective values, not all fields will have values) ----------- KSCHL - ZIC0 (KEY) KOTABNR - 521 (KEY) MATNR KUNNR-->1234567890 LIFNR VKORG-->a234 PRCTR KUNRE-->4355325363 LIFRE-->88390234 PRODH
Table BIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)
Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif then after concat the value should be like below:
12345678904355325363a234 88390234
Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..
I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...
I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
LEFT JOIN Status As S on S.LoanID = L.LoanID LEFT JOIN Borrower B on B.LoanID = L.LoanID LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID where S.PrimStat = '1' and B.Deceased = '0'
I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters. Currently am using the below function and getting the parameters by each in select statement as mentioned below.
select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1, [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2 CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex] ( @CustomProp varchar(max),
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.