We have this query that pulls number of days worked from the current Quarter to Date.
(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'
FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd))
AS VARCHAR) AS daysworked, ActivityId AS totalcalls
FROM PhoneCall AS p
WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR,
DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb)
AS [Days Worked],
I need changing it to bring up LAST Quarter's data.
Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed ) Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.
Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like
1,4 2,5 3,6
I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data MessageContentType | BodySegment xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT ID,MsgContentType BODYSEGMENT, BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan FROM s
When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"
Concat MsgContentType Column BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).
Below is the sample script.
DECLARE @XMLVAR XML SET @XMLVAR = '<?xml version="1.0"?> <POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'
SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME ,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Error: Msg 7372, Level 16, State 4, Line 1 Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723 Linked Server Script: /****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true' I've tried searching the Internet for solution but yielded no results. Can anyone help?
Hi, I have a matrix report with three data points 1. Inventory 2. Occupancy 3. Absorption
They are grouped in columns by Year and the data is returned by the query at Quarter granularity
My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters
So, for 2006
Want Q4 data for Inventory, sum of all 4 quarters for Absorption
For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption
How would I (or could I) do this in a Matrix Report - or is there a better way ?
care session quarter Q1-15 Q2-15 Q3-14 Q3-15 Q4-14
I am using this [care session quarter] column in the group by clause to achieve but no success.IF I use date column in the select clause and Group by clause then it comes correctly but groups by all dates which is not required.
Ideally I want show only quarter aggregates. The [Date Dimension] table has the column [care session quarter] which stores all the quarters of years along with dates for each day. i..e I have all columns in [Date Dimension] table as shown below
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
Here is a brief description what I am actually looking at. As we all have SLA's to understand how much uptime/downtime we can afford maybe per year/per quarter. I am keenly interested in finding out the way of calculating the sql server uptime. I googled for this and didn't find an appropriate solution that can justify my needs.
I am looking at a way that can give me a historical view of the uptime (possibly aggregated over time), considering all the facts for e.g I am not considering the maintenance that we do for keeping our servers up to date which includes patching and stuff, instead I am more focused on the historical view that for e.g if my manager asks me to give him a report stating the uptime for all the sql servers that we have for the current quarter.
Hence, I would basically some kind of script wherein I am storing the history somewhere and at a later date if my manager asks me to give a quarterly uptime report I can pull out that aggregated data, and generate a pie chart or something from that data to show the uptime and downtime for the same. I don't want to use 3rd party tool and I know there are quite a few, but company won't afford it.
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column: thisisavalue thisisanothervalue thisisanothervalueagain shortval
replaced with xxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx xxxxxxx
My current function is replacing the data like this: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hey all. I have a query where I am basically querying an organizational chart. The table storing this information is basically a two column table of parent/child pairs. So you might have:
Querying this table should return all child columns that flow up to the top, so querying 1 would return 1, 2, 3, 4, 6, 7, 8, 10, 11.
I used a sample from MSDN (http://www.sqljunkies.com/Article/D7CAED46-CCAC-4FF7-B528-B2E9A274B71C.scuk) that does just this, except that when querying a value that returns more than 1000 records - I am experiencing way too long response times. The MSDN sample uses temp tables and inserts values into a temp table as it moves through the records and finds a match.
Anyone have an ideas on another way to accomplish the same thing? This is an important part of my security model as users that login should only have access to data that falls within their parent/child heirarchy.
Hi all, I've just started playing with data importing from Excel.
Since I know how to do linked servers, that's where I started. I created a link, querried the spreadsheet and got some strange results where data appearing in the spreadsheet returns as NULL in QA.
So, I noticed that I could use OPENDATASOURCE and OPENROWSET and tried each of them. They're returning the same data (in different column orders) also with strangely intermittant NULL values instead of the data that I see in the source spreadsheet.
I bet that this is something easy once you know what's going on. Anyone know what's going on?
Not that I think it'll matter, but the queries I'm using for these three cases are:
select * from EXCEL_A...EPIQ202$ where SUBSCRIBER_ID = '50664582103'
select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=e:EPIQ202.xls;Extended Properties=Excel 8.0')...EPIQ202$
select* from openrowset( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:EPIQ202.xls', 'SELECT * FROM [EPIQ202$]' )
Is there a way to query the name of the physical data file and/or log file associated with a given database. For example if I were to do an alter database: ALTER DATABASE X MODIFY FILE (NAME = xxxxx, size=10MB)
Is there something I can type in to extract the file name associated with 'X' or do I have to know it implicitly?
Hi, I am using visual web developer 2005 express edition and Microsoft SQL 2005 to develop a fast-food ordering website and I am having trouble in retrieving data from a database table into a form. Can some one please teach me the way to write the syntax for retrieving or selecting a inserted value from the database? I have only know the syntax to insert data value from a form which is something like this: 'Create a New Connection to our daabase Dim test As SqlDataSource = New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("Connectionstring1").ToString 'This is the SQL Insert Command test.InsertCommand = "Insert into Customer([Initial],[Correspondent_Name],[Correspondent_No],[Payment_Type]) VALUES (@Initial,@Correspondent_Name,@Correspondent_No,@Payment_Type)" 'Each of this insert a value into the appropriate command test.InsertParameters.Add("Initial", DropDownList4.SelectedValue) 'This is the selected "Initial" DropDownList value test.InsertParameters.Add("Correspondent_Name", TextBox10.Text) 'This is the Correspondent_Name value test.InsertParameters.Add("Correspondent_No", TextBox3.Text) 'This is correspondent_no value test.InsertParameters.Add("Payment_Type", "Pay upon delivery") 'This indicates that the payment is made with credit card test.Insert() But I have no idea the syntax to retrieve a data. Please help me with this matter as it is important to me. Thanks in advance!
Using a code snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by a snippet from the xml clob that I am pulling from.
select * from (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag from ws_transactions Where Wtr_Service_Tag In ('20458749610')
[Code] ....
This query is only able to pull the first value in the list.
I have two questions...
[1]How can I edit this query to pull all of the list items when there are more than 1? I have another field, in a separate table, that I can pull from to get that number.
[2]This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc. The complicated part is that they want an admin to be able to modify/add/delete the set of properties. So for example, they could add the attribute "size." Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item. I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid. However, what would the query be to do this? I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")
I am working on a project, which involves displaying trends of certain aggregate values over time. For example, suppose we want to display how the number of active and inactive users changed over time.
One issue is how to store historical data. First of all, should I create a separate database for each historical snapshot or should I use one database for all snapshots? Second, our database size is a couple of gigabytes and replicating the entire database on a daily basis is not feasible. An alternative solution is to back up aggregate values, but how do I back up results of aggregate queries, where the user can specify a date range in the WHERE-clause? Another solution is to create fact tables from our relational schema and back those up.
Another issue is how to query historical data. Using multiple databases to store historical snapshots makes it harder to query.
As you can see there are several design alternatives and I would like to know how this sort of problem is generally solved in the industry. Does SQL Server provide any support for solving this problem?
I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either. The only way I know that works is to script the creation of all tables then export and import. This does work. How can I get the 'entire' database, structure and data, from 2008 to 2005? ThanksSQL newbie.
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
We are using push subscription using transactional replication. Is there a recommended value for retention period on distributionDb? We are using default value of 72 hrs and recently we saw an issue where data was not replicated with an error that subscription was inactive. When I searched, I fid that it is related to the retention period setting on distribution DB.
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I have a report that summarizes hospital readmissions. Some months may only have a female or male patient that is readmitted but, I want to show both months either way.
And I always know the root ID from the first record on "table" dummy (generated with a common table expression), in this case it's ID 1, but from here, how to process this for any level of depth ?
It might be an old question but wanted to see, if we have any latest techniques (other than bcp).
SELECT Field1, Field2 FROM MyTable
If I want to export the output of the above query to a csv on a network folder? I would like to avoid usage of SSIS package or BCP (as user needs to get additional rights to execute bcp).
