selecting table data in hierarchical XML .
Here is the sample table DDL and data
Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int
[code]....
I am trying to print Companies with less than 100 employees for all dates.Here's my table structure
Create table CompanyEmployeeArchive(
Company varchar(100) not null, Employees int, Dateinserted date)
Insert into CompanyEmployeeArchive values('Microsoft',1001,'2015-01-01')
Insert into CompanyEmployeeArchive values('Microsoft',1050,'2015-02-01')
Insert into CompanyEmployeeArchive values('Microsoft',1600,'2015-03-01')
Insert into CompanyEmployeeArchive values('IBM',10,'2015-01-01')
Insert into CompanyEmployeeArchive values('IBM',80,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',90,'2015-01-01')
Insert into CompanyEmployeeArchive values('Apple',900,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',1000,'2015-03-01')
I want companies that have employees less than 100 for all dates i.e. Only IBM. Apple has < 100 employees only on one month.Select Company, dateinserted, employees from CompanyEmployeeArchive group by company,dateinserted,employees having employees < 100 order by company, dateinserted this query lists Apple too. How can I change the query so Apple does not show up in the list.
I have below two table and i want ot generate the xml out put as mentioned below.
create table Candidates(eno int,ResumeText varchar(30), Email varchar(30),Active varchar(30),postalcode varchar(30),country int)
insert into Candidates
select 1,'Test','ee@ee.com','Active yes','888888',2
union all
select 2,'Test','ee@ee.com','Active yes','888888',6
[code]...
Looking at an execution plan today I noticed something I've not seen before. The plan includes a non clustered index seek, followed by a RID lookup on the heap. The output list for the index seek contains the expected "Bmk" column (in this case "Bmk1473"), but also includes "IsBaseRow1475". This isn't a column from the table.
View 3 Replies View RelatedI have the following code below where I need to have all of the query results output into a .csv file to use in a VBA macro. The issue I am running into is that the data is not deliminating correctly and my rows are being shifted incorrectly. Any better way of out putting the results into a .csv file with a common delimiter.
-- Declare the variables
DECLARE @CMD VARCHAR(4000),
@DelCMD VARCHAR(4000),
@HEADERCMD VARCHAR(4000),
@Combine VARCHAR(4000),
@Path VARCHAR(4000),
@COLUMNS VARCHAR(4000)
[Code] ...
Output from query (please post in a text editor. The line starting with (only ) should be on line 1 after 20 pks and is shifted to a new line.):
557898^1^9885E25^80082^9.0 CM GLASS FIBER PADS 20PKS
(only 12 pks in stock that will ship today)
^12.00000000^.00000000^18.32000000^219.84000000000^28.30000000
^339.60000000000^9.98000000^35.2650176678445^DR9146322^0^
[Code] ....
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
If I use msdb..sp_send_dbmail or save query results as text (using sqlcmd) and include the column headers I get the dashed separator line.
e.g.
custID, name
------, ------
1,bob
2,jamesI would like this
custID, name
1, bob
2 ,james
I found this method [URL] ....
::
sqlcmd -E -S (local) -d myDB -W -w 1024 -s "," -i "SELECT * FROM tblCust" | findstr /V /C:- /B > C: emp.csv
Can the same result be achieved sending as attachment with dbmail?
EXEC msdb..sp_send_dbmail @attach_query_result_as_file = 1I don't want to have to add column names as part of the query
Change the query to return column headers in resultset
SELECT 'CustID' as f1, 'name' as f2
UNION ALL
SELECT CAST(CustID as Varchar(10)), name FROM tblCustand set
msdb..sp_send_dbmail @query_result_header = 0
I have a sql query that gets the count of exams held in each month.
Below is the code that I have used.
select Examid, count(*) as CumCount
from [dbo].[Exams] where ExamCategory in ('Major','Critical') and Month(EXOCCRDATE) = Month(getdate())
and Year(EXOCCRDATE) = Year(getdate()) group by Examid
The code works good when we have data for the current month. When we dont have any exams for the current month, the code outputs empty values. I want the code to be altered so that when there is no value returned in the output, i want a default value shown in the output.
I have attached the sample data that I am using. In the data we dont have dates for the month of October. So when I run the code it will display empty output. So what I need is I need a text like 'No Data' to be shown when no value is returned by the query.
I tried using case but it does not work.
write a query that lists item, mfg serial number, and inspection date. The caveat is that, where there is more than one record for an item, he only wants item listed once. For example:
PartNumberInspectionDateManufacturerSerialNumber
1A144878/4/2013.1101VAS073-7
3L115931/28/2014009VAS206-1
1/29/2014009VAS206-1
2/21/2014009VAS206-1
7/22/2014009VAS206-1
2A285631/1/2015010115ND9CV02
1A203921/3/2015010315ND9PV45
1/4/2015010315ND9PV45
3/1/2015010315ND9PV45
2A2794310/20/20120103700P003
I have a query below to show all the records with joining these two tables.
SELECT DISTINCT B.BF_ORGN_CD, B.LEV5, A.BF_ACTY_CD
FROM BF_ORGN A
INNER JOIN BF_ORGN_CNSL_TBL B
ON A.CD=B.BF_ORGN_CD
WHERE A.BF_ACTY_CD IS NOT NULL
ORDER BY B.BF_ORGN_CD,A.BF_ACTY_CD
My goal is only to show all the duplicate records.
Bf_ORGN_CD LEV5 BF_ACTY_CD
AC_21234_2 AC_21200_1 402
AC_21236_2 AC_21200_1 402
AC_21238_2 AC_21200_1 402
AC_29000_1 AC_29000_1 802 ---> NOT SHOW (ONLY 1 RECORD)
AC_29988_1 AC_29988_1 801 ---> NOT SHOW (ONLY 1 RECORD)
[code]...
How I want to see the below dates via 6 different Select Statements but I am unsure how to get this?
01-May-2015
01-Apr-2015
01-Mar-2015
01-Feb-2015
01-Jan-2015
01-Dec-2014
I have this Select statement
select replace(convert(char(11),getdate(),113),' ','-')
But it is returning the 15-May-2015 and it should be 01-May-2015 for this select statement
I have a query which wants to union join the data. no matter how many times I tried, I got an error. How to change my union query?
select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT
(Select distinct b.lev5
from bf_data a
inner join bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd
[Code] .....
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
[code]....
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).
ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11
I'd like to show the assets with a start date and end date for the placement of the asset.
The start date to be the placement date and the end date to be the next placement date of the asset.
Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.
ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()
I'm guessing some sort of recursion is required here to produce this.
Using below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
I have a table i need to show data according to id. distinct id and all name should be comma separated against each id.
DECLARE @TBL TABLE (ID NUMERIC(10), NAME VARCHAR(10))
INSERT INTO @TBL (ID,NAME)
VALUES(1, 'A'), (1,'B') ,(1,'C') ,(2,'E') ,(2,'B') , (3,'F') , (3,'G')
output :
id name
1 a,b,c
2 e,b
3 f,g
When I save my output (from a query I ran) to a text file, there seems to be rows of spaces. Is there a way i can just kill off any spaces at the end of my query? Like rtrim or something?
View 9 Replies View RelatedI have to set up archiving of some pretty large tables (125M+ rows) and I'm trying to figure out the best way to do it.
I've been using OUTPUT into, something like this:
delete top (100000) a
output deleted.RecordID
into dbo.RecordsArchive (RecordID)
from dbo.Records
It's difficult to tell if it sub-optimal code or just sheer volume, but any performance penalty using OUTPUT into in this way? or is it advantageous to insert the records to the archive first, and then delete them in two separate operations?
The below data come from table table1. Instead of below result Ex1: I need output similar to the ex2.
Ex1:
CaseNumberStart CaseNumberEndExported
15000013150000131
15000014150000141
15000504150005041
15000505150005051
Ex2:
CaseNumberStart CaseNumberEndExported
15000013150000142
15000504150005052
How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.
When I execute the following command, I get the output truncated to 79 characters, including three dots (as an ellipsis, I suppose).
EXEC master..xp_cmdshell 'powershell.exe "Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255"'When I execute the core command directly in Powershell, whether the text or ISE version, it works correctly, with or without the out-string -width command.
Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255What does it take to get SSMS to not truncate my output strings?
I have a table Test123 having three column EmpID,AttribName,AttribValue.
run the below query to generate table structure and data.
Create Table Test123(EmpID int,AttribName varchar(50),AttribValue varchar(50))
insert into Test123 values(1,'Name','X')
insert into Test123 values(1,'Age',50)
insert into Test123 values(1,'Salary',1000)
insert into Test123 values(2,'Name','Y')
insert into Test123 values(2,'Age',30)
insert into Test123 values(2,'Salary',2000)
insert into Test123 values(3,'Name','Z')
insert into Test123 values(3,'Age',35)
insert into Test123 values(3,'Salary','One Hundred')
And I want output in below format.
AttributeValueType
=================
AGE | NUMERIC
NAME | ALPHABET
SALARY | ALPHANUMERIC
==================
Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...
Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);
Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)
What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.
Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?
I am running sql script inside job.But sql is not saving the results in the file which i specified('Unable to open Step output file'). I tried same thing on different server, it works fine.Both are are 2008 R2.Is there any server level setting that i have to look at.On both servers i logged in as sal. Why is it working on server1 ,but not on server2?
View 6 Replies View RelatedOldest Day till I want to Purge[30 days of Old Data] : 03-17-2015
Running Cleanuphistory Procedure On Server:
NO of records are qualifying for deletion:0
Deleted Sysmail MAil Items, Old history: 1
Deleted Sysmail Log Old history: 1
0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??]
Deleted SQL JOB Old HISTORY: 1
*** END OF PROCEDURE. ***
[Code] .....
Hi. I'm trying to find out which "cases" have a new items added to our database. I have provided a sample layout.
ID ParentID Name CreateDate
358 2 SMITH, JOHN 3/3/2008 11:15:23 am
359 358 Invoice 3/5/2008 4:13:52 pm
360 358 Shipping 3/5/2008 5:11:09 pm
361 358 Receiving 3/6/2008 4:22:01 am
The main ID for this is 358. The invoice, shipping, and receiving items are child items. I would like to run a query that can report which cases have newly added items. This is hierarchical I guess and I'm quite lost. I hope this makes sense. Thanks for any help!
I have created a store procedure as below:
WITH TextType AS
(
SELECT AppTxtTypeId,AppTxtTypeCode, AppTxtTypeParentCode, Name,Description,Active,SortOrder ,0 as TypeLevel,AppTxtTypeId as parentId
FROM [ApplicationTextTypes]
WHERE AppTxtTypeParentCode IS NULL
[Code] ....
From this i am able to get data in the below format:
Parent
--Child1
--Child2
---Subchild1-Child1
---Subchild2-Child1
---Subchild1-Child2
---Subchild2-Child2
Actually my requirement is :
Parent
--Child1
---Subchild1-Child1
---Subchild2-Child1
--Child2
---Subchild1-Child2
---Subchild2-Child2
Hello,
I have the following query which grabs monthly usage data which is logged to a database table from a web page:
SELECT Button = CASE ButtonClicked
WHEN 1 THEN '1st Button'
WHEN 2 THEN '2nd Button'
WHEN 3 THEN '3rd Button'
WHEN 4 THEN '4th Button'
WHEN 5 THEN '5th Button'
WHEN 6 THEN '6th Button'
WHEN 7 THEN '7th Button'
WHEN 8 THEN '8th Button'
WHEN 9 THEN '9th Button'
ELSE 'TOTAL'
END,
COUNT(*) AS [Times Clicked]
FROM WebPageUsageLog (NOLOCK)
WHERE DateClicked BETWEEN @firstOfMonth AND @lastOfMonth
GROUP BY ButtonClicked WITH ROLLUP
ORDER BY ButtonClicked
The results look like this:
TOTAL 303
1st Button 53
2nd Button 177
3rd Button 10
4th Button 4
6th Button 18
7th Button 19
8th Button 21
9th Button 1
If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.
-Dave
I have few complex queries and I want to extract the output of results to all different dateformatted output files.
How to write the queries?
I know BCP is a solution but any other effective way to implement it?
I have a table which has few columns as Numeric value. I need to export the output into a csv file as a report with column headers. I have used bcp command. Here column name and Column Header name is the same. The BCP query which i have used is Below
bcp "SelectCompanyCode,MonthId,ActualityCode,CompanyCounterpartCode,LocalProductCode,LocalCustomerBillTo,
DestinationCountryCode,LocalCostCenterCode,LocalGLAccountCode,LocalProjectCode,TXCurrencyCode,
TXAmountYTD,LocalCurrencyCode,LocalAmountYTD,SourceSystemFromTable(nolock)" queryout D: est.csv -c -t ";" -r -S -T
Is there any way to export bcp with header or any other command to generate the csv file.
I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.
View 9 Replies View RelatedFollowing is my db table
student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d
How to generate following op?
student_id student_code student_parent_id student_name Hierarchy
1 11 0 a 11 - 111
2 111 1 b 11-111-1111
3 1111 2 c 11-111-1111-11111
4 11111 3 d 11111
I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
View 9 Replies View Related