Hey Gurus,
I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).
A million thanks in advance.
Hi, I was wondering wich is teh easiest way to solve this:
I have one data set that comes from a sql query with many columns.
I have another data set that comes from a txt file that I am reading.
Both data set have one column in common (for example ID).
In the sql data set, I have many rows and I want to keep only the ones that have the same ID from the data set of my txt file. It looks like a inner join if we talk in sql terms.
So, which is the easiest way to solve this?
Thanks!
I am sharing one sql query and o/p:
select distinct case
when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
when LastExecutionResult = '17023' then 'User cancelled installation'
when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
[Code] .....
The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)
Comment Machine Name
Application will be Installed once machine is Online 4
Application will be Installed once machine is Online 12
Application will be Installed once machine is Online 42
Application will be Installed once machine is Online 120
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
I need o/p like below:in single line
Application will be Installed once machine is Online 178
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies View RelatedHow can I add a group number to the following query?
For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):
RN ReportDate Category Division TotalBalance
-------------------- ---------- ------------------------------ ------------------------------ ---------------------
1 2015-06-08 Field Sales CA BDM 299743154.3912
2 2015-06-07 Field Sales CA BDM 299765954.0354
3 2015-06-01 Field Sales CA BDM 297902654.4172
1 2015-06-08 Key Accounts Life Office 49954981.74
2 2015-06-07 Key Accounts Life Office 50016989.22
3 2015-06-01 Key Accounts Life Office 50169967.26
4 2015-05-31 Key Accounts Life Office 50169918.01
Becomes
GN RN ReportDate Category Division TotalBalance
-------------------------- ---------- ------------------------------ ------------------------------ ---------------------
1 1 2015-06-08 Field Sales CA BDM 299743154.3912
1 2 2015-06-07 Field Sales CA BDM 299765954.0354
1 3 2015-06-01 Field Sales CA BDM 297902654.4172
2 1 2015-06-08 Key Accounts Life Office 49954981.74
2 2 2015-06-07 Key Accounts Life Office 50016989.22
2 3 2015-06-01 Key Accounts Life Office 50169967.26
2 4 2015-05-31 Key Accounts Life Office 50169918.01
i.e. each combination of Category+Division results in a new GN.
The query is:
selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN'
, ReportDate
, Category
, Division
, sum(BalanceGBP) as 'TotalBalance'
FROM FlowsAndOpenings
group by ReportDate, Category, Division
order by Category, Division, RN
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty
--------------------------
Package A | 15
Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
Is there any program similar to phpMyAdmin for MS SQL servers?
Thanks in advance
I have a function that uses the following statement in it
SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref
WHERE (src_terrier.datadate = @dt_src_date) AND
(src_terrier.Areacode = @chr_div) AND
(src_centre_list.Portfolio_no = @vch_portfolio_no) AND
(src_centre_list.propcat = @vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)
The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows
src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.
How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.
Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.
Anybody confused by that , cause I am!
Regards
hello there.
is there any case for getting the directory/file structure on the ReportServer?
something similar to "DirectoryInfo".
i'm using the webservice
best would be putting the url (e.g. "http://localhos/reportserver/reportgroup1") to a function and get at least an array of filenames in this folder back.
is there any way to solve this?
thanks in advance, tobias
Hi!
I'm about to write some code that generate TSQLdoc.
For that I would like to show dependencies.
What will the best way to get actual info about
which procedures use this proc
which procedures or functions do this proc or func use?
Thanks
I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.
View 2 Replies View RelatedIs there anything similar to AWR/statspack report in SQL Server 2005.
View 4 Replies View RelatedHi All,
Is there any difference between the two queries given below..I am not able to find any but am not sure. Kindly help.
A)
select a.* from( select top 1 hs.last_modified, hs.price, hs.revision_date
from history hs where hs.last_modified < '06-Jan-2008' order by hs.last_modified desc)a
order by a.revision_date desc
B)
select hs.last_modified, hs.price, hs.revision_date
from history hs where hs.last_modified < '06-Jan-2008'
order by hs.last_modified desc, hs.revision_date desc
Thanks,
Pradeep.D
Hi,
I need something like a sequence in a datafield of my table. (unique number in the table) is a uniqueidentifier the right thing to choose? and if yes, how to I insert a new value into the that table:
a) from the SQL Enterprise Manager?
b) from a JAVA program using jdbc?
thanks for all hints.
I have three fields in a table say [F1, F2 & F3]. I need to fetch anyone of these three fields which has the maximum value between them.
In Simple words i'm looking for some function which is similar to COALESCE function which returns the first NOT NULL value of the fields that were passed as arguments.
FYI I'm using SQL Server 7.0 which does not supports UDF's
Earlier response appreciated
Thanks and Regards
Chandru
Folks, i have to create four fields in every user table within my database:
CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]
There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!
declare @name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @name
IF @@fetch_status = 0
BEGIN
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_date [DATETIME]
GO
ALTER TABLE @name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur
I also want that if one column for a table exists; the other columns should be created rather than it quits.
Howdy!
finding is queries to find duplicate data.Basically I have a picklist table in a database and I have discovered that there are what looks like duplicate data (because the name is the same) but there is a different number on the end, as you can see from an example below.
NO, ENTRY
24, John Doe|26|05768
24, John Doe|26|5768
Do you know if there is a sql query that can be ran against this table that will look through the ENTRY column and select fields that are similar and not duplicates (duplicates can't exist due to PK constraints)
I have the below in a table - TABLEA
Ref, Date, TIME, Code, Minutes
01117,2012-01-02, 541,BASIC,240.0
01117,2012-01-02, 541,BASIC,105.0
And I am trying to insert this into another table TABLEB but it wont allow as I am getting a duplicates error because of the unique Indexing on the table.
Ref,Date,Time,Code
Ideally if I could run a query on TABLEA so that it would merge and sum the minutes where REF,Date,TIME,CODE are the same.
i.e., the above would become
Ref , Date , TIME, Code, Minutes
01117,2012-01-02, 541,BASIC,345.0
Is this possible?
Another option that would work for me is the TIME column info isnt required to remain at 541.
If there was a count increment on the rows it would allow the import to rum.
ie if the above became
Ref, Date, TIME, Code, Minutes
01117,2012-01-02, 1,BASIC,240.0
01117,2012-01-02 ,2BASIC,105.0
It would also import correctly.
The first option is which I would prefer.
Table One is an older database and has the column employee id, which would always consist of first letter of the last name an underscore and a numeric value. So for example, data looks like
employeeID
R_12345678
S_5555555
T_777777
U_7777
Our new data structure simply removed the letter & underscore.
employeeID
12345678
5555555
777777
7777
Now my question is, how could I join on oldDB.employeeID to newDB.employeeID since the data is very similar, but not exactly the same?
Im trying this simple query, where if a book has the same ID but different names it should be shown in one row and not two...
Now it is showing
- 1 AuthorDude
- 1 AuthorGirl
- 2 Authorblabla
While it should show
- 1 AuthorDude, AuthorGirl
- 2 Authorblabla
SELECT a.book_id, b.Name
from BOOK_SALES a
left outer join
(
select Book_id,is_primary,
STUFF((select ', ' + Name from BOOK_CONTRIBUTOR B where B.BOOK_ID = A.BOOK_ID
for xml path('')),1,1,'') Name
from BOOK_CONTRIBUTOR A
group by Book_id,IS_PRIMARY
) b on a.BOOK_ID = b.BOOK_ID
When i do a inner join, it would just show many Book_ID`s, but with the authors combined.
[URL] ....
i like to write a sql query to find out similar columns in different tables.
i have something like this. but my logic is wrong.
select *
from information_schema.columns a
join information_schema.columns b
on a.column_name = b.column_name
and a.table_name <> b.table_name
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.
Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.
I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.
I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.
How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?
Thanks in advance.
I did a successful Insert to a SQL Server today in .NET VS2005.
But before I insert I figure I better make sure the part does not allready exist in the table.
Can a check the command to determine if it has a boolan value of False and if so to INSERT as before? Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "Select PartNumber From Pricing Where PartNumber = '" & var0 & "'"
'myCommand.CommandText = "INSERT INTO Pricing (PartNumber, ListPrice, PartDescription, ProductClassCode, ProductClassDescription, ProductFamilyCode, ProductFamilyDescription, ProductLineCode, ProductLineDescription) VALUES('" & var0 & "', " & var1 & ", '" & var2 & "', '" & var3 & "', '" & var4 & "', " & var5 & ", '" & var6 & "', '" & var7 & "', '" & var8 & "')"
myCommand.Connection = con
con.Open()
myCommand.ExecuteNonQuery()
con.Close()
I would like to write a fun or stored procedure to do some operation. It require me to know that what category is currently belong to certain people(people_table: category_table1 to Many)However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.Question:1. Is there any way to hold the collection of result(like array)?2. Also, how to determine to use fun or stored procedure?(Since a integer is need to return by them)Thx
View 4 Replies View RelatedHi all,
I have a table with the list of tables I need to drop. So basically before droping those tables I need to disable the FK and PK constraints.
So I want to spool out the out of this script.
SELECT 'ALTER TABLE ' +
QUOTENAME( c.TABLE_NAME ) +
' NOCHECK CONSTRAINT ' +
QUOTENAME( c.CONSTRAINT_NAME ) AS ALTER_SCRIPT
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
Is there a way to store the output of this script in a .sql file so that I could execute it.
Any thoughts will help!
Thank you!
Does any one have information on any good Access Sites that are similar to this one, which could be helpful in completing some task. Thanks.
View 1 Replies View RelatedHi
I have a mysql query in my php script like
UNIX_TIMESTAMP() - UNIX_TIMESTAMP(sessioncreated) as sessionspan .
What is the equivalent of above query in mssql. I need the same query in mssql. Is there any function that does the same action in mssql2000.
Thanks in Advance.
stranger
I have two SQL 6.5 servers running independently. Both servers have almost identical databases. For example, we create purchase order in SQL server A. And post the same purchase order # for it's PO Receipt and PO Inspection in SQL server B. How can I apply all transactions entered from SQL server A to SQL server B, and vise versa at end of the day. How can I get help or training for writing codes for manipulating transaction log?
View 1 Replies View RelatedIs there a function to match simalar words
say for example 'tom' will match 'tomy'
Thanks
Hi,
I am looking for a tool that is similar to SQL Impact (Quest). Quest has discontinued the tool.
This tool should be able to detect all database object dependencies for SQL Server, Sybase and Oracle. The objects should include tables, views, stored procedures, indexes and other objects. This should also detect DB object dependencies in front end applications as well.
Any suggestions are greatly appreciated...
Thanks!
Hi,I am working on SQL Server.
I have a table with columns that need to be broken down. I nees to break this table:
TelNo1 TelNo2 TelNo3
555-44-33 555-43-88 555-46-89
into:
Tel_Number
555-44-33
555-43-88
555-46-89
I tried using the union statement but i just did'nt succed. Can anyone help me,please
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.
Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50
Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15
[code]....