I have database with three tables Accounts, Results, and ClosedOrders. All are connected through AccountID PK/FK.
I got a wonderful select statement that gives me the latest Results for each Account.
WITH cte AS
(
SELECT
Accounts.AccountID,
Accounts.AccountName,
Results.ResultTime AS LastUpdated,
[Code] ....
I've been struggling to extend this with two more columns from the ClosedOrders table. How to add columns to the this view? Basically what I need is this:
SELECT SUM([Lots]) AS Longs
FROM [DEV].[dbo].[ClosedOrders]
WHERE OrderTypeID = 0;
SELECT SUM([Lots]) AS Shorts
FROM [DEV].[dbo].[ClosedOrders]
WHERE OrderTypeID = 1;
But it has to "join" the CTE somehow so that I get the correct answer for each Account row.
I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my output ok but when I try to receive that output from write another select statement then its show error.
Msg 102, Level 15, State 1, Line 276 Incorrect syntax near ';'. Msg 102, Level 15, State 1, Line 315 Incorrect syntax near ')'. Msg 156, Level 15, State 1, Line 351 Incorrect syntax near the keyword 'as'.
I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
select computer, count(*) as MissedCount from WInUpdates_Neededreq WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE()) group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database. The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records. I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered). I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these). I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes. I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem. So my question is: Is it normal for such a type of read query to take 90 seconds to complete? Is there anything I could do to speed it up. Any other thoughts? Thanks
I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.
I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].
I have a table where I need to concatenate all values into one field separated by a comma. If the field is null display a blank value. This is my table structure and example output
Create Table #read ( id int ,field1 varchar(100) ,field2 varchar(100) ,field3 varchar(100) ,field4 varchar(100)
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and if the column has a string "d" then replace with "D" . I can write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz after the update: BcDxyz
So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE WHEN [AddressType] = 'M' THEN [MailingAddress].[Address1] [MailingAddress].[Address2] [MailingAddress].[City] [MailingAddress].[State] [MailingAddress].[Zipcode] WHEN [AddressType] = 'D' THEN [DefaultAddress].[Address1] [DefaultAddress].[Address2] [DefaultAddress].[City] [DefaultAddress].[State] [DefaultAddress].[Zipcode]
Select ID, FIRST(forename), FIRST(surname) from table1 GROUP BY ID;
I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.
There are records like:
ID forename surname -------------------------------- 1 John Kormack 1 James Dope 2 Erin Dupes 3 Will Hugh 3 Walter Heisenberg
As a part of DBA, I used to execute various SQL files. Most of the time, it is like a manual effort to execute the files individually.
I am looking to automate the process, like a single click to execute all the .SQL files.
The main hurdle I have is, some files needs to be executed in A1 database, some in B1 database and some other SQL files need to be executed in C1 database. In this scenario, I need to pass the DBName information to the powershell query dynamically.
My design for this requirement is, say each .SQL file need to contain a template like
@DBName = 'your Database name' @Executeon = 'When to execute'
In this case, the powershell first need to read the SQL file and finds the value for @DBName and replace it in the powershell query and execute the SQL files automatically.
Is it feasible ? Or any other alternate easier way to proceed.
Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
I have following query. Now I want to change this in such a way that if Condition 1 satisfies then it should not go to next condition ,if not then should go to next. Similarly the same format till condition 4.
select * from table where IND = 'Y' and ( (STATE = @STATE and LOB =@LOB and COMPANY=@COMPANY)--Condition 1 OR (STATE = @STATE and LOB =@LOB)--Condition 2 OR (STATE = @STATE and COMPANY=@COMPANY)--Condition 3 OR (STATE = @STATE)--Condition 4 )
Hi, Recently I have been trying to write a script in order to redo this entire database, it's actually only about rectifying column and table names as well as a few erroneous relations, etc. The idea is that the actual data is okay it's just the organization that is completely messed up (spaces in column and table names, etc.)
Anyway my problem is this, a part of this script is about mass-renaming columns and tables the code is as follows in the Query Analyzer:
When I barely analyze the code (Ctrl+F5) it gives me the following error: Serveur : Msg 170, Niveau 15, État 1, Ligne 2 Ligne 2 : syntaxe incorrecte vers 'sp_rename'.
Which would roughly translate into: Server : Msg 170, Level 15, State 1, Line 2 Line 2 : Incorrect syntax near 'sp_rename'
When I execute it, it gives the same error. Why does it do that when i give it the two lines while if i enter the lines one by one it works just fine. How is a SCRIPT supposed to work if I have to "baby-feed" every statement separately.
PS: i am working on SQL Server 2000 (in Query Analyzer menu ?/About .. it says SQL Version 8.00.194
create table #task(TaskId bigint unique, Name varchar(2000)) insert into #task values(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>') insert into #task values(2, 'Text Text Text <<Name>> Text Text Text Text <<Company>> Text Text Text <<Salary>> Text Text Text')
[Code] ....
Now I need to create an inline function who resolve the task name with appropriate values and return me the resolved task name
select * from fn_TaskResolver(1, 'Text Text Text Text Text Text Text <<Name>> Text Text Text <<Salary>>')
I try this function but its return multiple rows as i just want to return one row. as I have big data set so i don't want to use scaler or Multi Line function.
create function fn_TaskResolver(@TaskId bigint, @name varchar(2000) Return table as return
