Combining PIVOT And INSERT Queries
Nov 21, 2007
Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...
SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantID, QuestionID, Answer FROM [3_Temp]
WHERE SurveyID=1) AS preData PIVOT
( MAX(Answer) FOR QuestionID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )
AS data
ORDER BY RespondantID
Thanks,
Martin
View 1 Replies
ADVERTISEMENT
Nov 22, 2003
hello everyone
there is a smalllll problem facing mee...well i want to combine the result of 2 queries together
, the queries are :
select x1,x2,x3 from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z where table1.anything = 5
and the other query
select x1, x2 from Table1 where table1.anything = 5
is there anyway????
Thank you
View 2 Replies
View Related
Jan 23, 2006
I have a transactions table that stores prices for products bought and sold.
If I want average buying prices I use:
SELECT AVG(price), product FROM transactions WHERE transactiontype=1 GROUP BY product
and for selling prices:
SELECT AVG(price), product FROM transactions WHERE transactiontype=2 GROUP BY product
Is there a way to combine this into one SQL query, to create one bindable dataset ?
View 2 Replies
View Related
Apr 12, 2006
These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.
I hope that's clear; if not, maybe the SQL will make it clearer:
SELECT
[tblTickets].[id] AS TicketId,
SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblTickets].[id]
SELECT
[tblCombinations_Tickets].[ticket_id] AS cTicketId,
SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]
WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @userID AND ([tblOrders].[order_status]=@purchasedOrder OR [tblOrders].[id]=@currentSessionOrder))
GROUP BY [tblCombinations_Tickets].[ticket_id]
I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.
I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.
This has got to be laughably simple. But I'm stuck :( Can anyone help?
View 3 Replies
View Related
Jun 19, 2008
I need to combine two queries into one.
Query 1 (main query)
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
Result
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26 (material qty)
1569.26 (production qty)
(where 1314.26 is sum material and 1569.26 is production)
Query 2
selectsum(EmployeeLaborEvent.Hours) as hours
fromdbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = Event.JobGuid
Left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
Result:
1647.50 (which are sum of hours, this figure is correct)
Now I try to merge query 2 into Query 1 like this:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production,
sum(EmployeeLaborEvent.Hours) as hours
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
When I run the query the result is:
3505048
SR 434 T-5201SR 434 T-5201
2007-10-11 16:36:45.647
Y
1314.26(material)
1569.26 (production)
NULL (hours)
The material and production stay the same (and is correct). Hours are wrong.
Any clues? Thank you.
View 4 Replies
View Related
Dec 18, 2006
Hi All,I have a problem with a table that I want to get nice data out of in asingle query. The guys here reckon it can't be done in a single querybut I wanted to prove them wrong !! Essentially, I want to get the samecolumn out of the single table, but in one case the column must have awhere clause associated with it, and the other case it does not have awhere clause...Lets say have a table like this :-date || user || transaction type || Amountso, each row contains a transaction type, and a corresponding amountfor this transaction.There can be any number of transactions (and transaction types) peruser per day.Here is what I want :I want to get one particular transaction type as a percentage of thetotal transactions : for example, a list of the % amount that Debittransactions have occurred for a user for a day, with respect to alltransactions that the user has done that day, so :Debits Jim performed on day 1 are 50% of all transactions he performedDebits Jim performed on day 2 are 55% of all transactions he performed... and so on.At the moment, I do this :select date, user, sum(amount) as debit_Amount where transaction_type='debit'group by date, userand dump that into tmp table Debitsthen I doselect date, user, sum(amount) as total_Amountgroup by date, userand dump this into tmp table Totalsand then I have to do a :SELECT Debits.User, (Debits.debit_Amount / Totals.total_Amount) asperc_Debit , Debits.dateFROM Debits, TotalsWHERE Debits.date = Totals.date AND Debits.User = Totals.UserCan anyone suggest a way of doing this without the need for thesetemporary tables???Thanks!
View 7 Replies
View Related
May 13, 2008
can someone assist me in joining the two queries together into 1
so that I can get one result set
---------------------------------------------------------
SELECT count(ee_occup_levels) AS OcpLvl, ee_occup_levels AS [Occupation Levels], headcount
FROM headcountdec
WHERE period = 'March 2008' AND headcount ='NewHires' AND staffno IS NOT NULL AND ee_occup_levels is not null
GROUP BY ee_occup_levels, race, gender, headcount
ORDER BY gender, Race, ee_occup_levels, headcount
select count(headcount + headcount) AS Transfares
from headcountdec
where Period = 'March 2008' AND headcount IN ('TransferOut', 'TransferIn')
View 1 Replies
View Related
May 4, 2005
I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))
When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?
View 1 Replies
View Related
Jun 3, 2015
I have two tables in my data model,
1. Fact table with sales by customer, Territory ID etc
2. Table with targets per Territory ID
I want two combine them in one pivot table.
Pivot: Territory;Sales;Target
What I'm getting is the correct column with sales, but the overall sum of all territories in column for targets.Relationship is established between two tables via Territory ID
View 13 Replies
View Related
Sep 11, 2007
Hi all,
I have the following tables
Tbl_Request
------------------------------------------------------------------------------------
RequestType NoOfPositionsRequired SkillCategory
Req1 10 .Net
Req2 3 Java
Req1 2 SQL
Req3 5 Java
----------------------------------------------------------------------------------
Tbl_User
------------------------------------------------------
ID SkillCategory Experienced
-----------------------------------------------------
101 Java 0
102 .Net 1
103 Java 1
104 SQL 1
105 .Net 0
106 J2EE 0
---------------------------------------------------
Experience is a bool column.
Required Output:
---------------------------------------------------------------------------------------------------------------------------------
SkillCategory Req1 Req2 Req3 TotalDemand Exp NonExp Total Supply
---------------------------------------------------------------------------------------------------------------------------------
.Net 12 0 0 12 1 1 2
Java 0 3 5 8 1 2 2
SQL 1 0 0 1 1 0 1
----------------------------------------------------------------------------------------------------------------------------------
Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.
Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.
I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.
View 8 Replies
View Related
Apr 3, 2008
I have two queries that I'm trying to combine, but can't figure out how to combine them ... successfully!?! The first query is pretty simple in that I'm looking at several fields from two different tables, no big deal.
The second query calculates the years, months, days between two dates that are used in the first query. I'm stumped on how to combine the queries so that they place nice with each other and return results.
I will post my feable attempt at merging them. No matter how I order the code, I continue to get the same error, pertaining to the last line of code ... Line 73: Incorrect syntax near 'vpi'.
Any help would be greatly appreciated! Thank you! Jena
select
RTRIM(RTRIM(vpi.LastName) + ', ' + RTRIM(ISNULL vpi.FirstName,''))) Employee,
convert(varchar,vpi.FromEffectiveDate,101) PositionStart,
convert(varchar,vpi.ToEffectiveDate,101) PositionChange,
convert(varchar,vpi.PositionStartDate,101) PositionStartDate,
datediff(hour,vpi.FromEffectiveDate,vpi.ToEffectiveDate) as time_diff,
vpi.PositionReason, vpi.PositionCode, vpc.PositionCodeDescription
from
(
select [Age] = convert(varchar, [Years]) + ' Years ' +
convert(varchar, [Months]) + ' Months ' +
convert(varchar, [Days]) + ' Days',
*
from
(
select
[Years] = casewhen BirthDayThisYear <= Today
then datediff(year, BirthYearStart, CurrYearStart)
else datediff(year, BirthYearStart, CurrYearStart) - 1
end,
[Months]= casewhen BirthDayThisYear <= Today
then datediff(month, BirthDayThisYear, Today)
else datediff(month, BirthDayThisYear, Today) + 12
end,
[Days]= casewhen BirthDayThisMonth <= Today
then datediff(day, BirthDayThisMonth, Today)
else datediff(day, dateadd(month, -1, BirthDayThisMonth), Today)
end,
Birth = convert(varchar(10) ,Birth, 121),
Today = convert(varchar(10), Today, 121)
from
(
selectBirthDayThisYear =
casewhenday(dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)) <> day(Birth)
thendateadd(day, 1, dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth))
elsedateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)
end,
BirthDayThisMonth =
case when day(dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) <> day(Birth)
thendateadd(day, 1, dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth))
elsedateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)
end,
*
from
(
selectBirthYearStart = dateadd(year, datediff(year, 0, Birth), 0),
CurrYearStart = dateadd(year, datediff(year, 0, Today), 0),
BirthMonthStart = dateadd(month, datediff(month, 0, Birth), 0),
CurrMonthStart = dateadd(month, datediff(month, 0, Today), 0),
*
from
(
select
birth = convert(datetime, fromeffectivedate) ,
Today = case when convert(datetime, toeffectivedate) = '3000-01-01'
THEN convert(datetime, convert(int,getdate()))
else toeffectivedate
end, *
from vHRL_PositionInfo
) aaaa
) aaa
) aa
)a
) vHRL_PositionInfo vpi inner join position_codes vpc on vpi.PositionCode = vpc.PositionCode
View 2 Replies
View Related
Oct 17, 2013
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;
OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y
View 3 Replies
View Related
Apr 29, 2015
I have these two CTE queries, the first one is the existing one which runs fine and returns what we need, the second is a new CTE query which result I need to join in to the existing CTE, now that would be two CTE's can that be done at all?The second query is working on the same table as the first one so there are overlaps in names, and they are the same, only columns I need to "join" is the "seconds" and "AlarmSessions".
;with AlarmTree as (
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
[code]....
View 4 Replies
View Related
Mar 5, 2012
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
View 6 Replies
View Related
May 20, 2015
I have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.
(SELECT DISTINCT
idname, name, address, address2, awardedtoname, suppno
FROM contract INNER JOIN
house ON contract.idname = house.idname)
JOIN
(SELECT DISTINCT
tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,
[Code] ....
How I could combine the results?
View 9 Replies
View Related
Nov 20, 2015
I have to run this 3 times for similar but slightly different parameters. I only change the very top peice of code slightly each time:
1. Partition by Forename, Surname, DOB, Postcode
2. Partition by Forename, DOB, Postcode
3. Forename, Surname, DOB.
As you can see very subtle change, ideally I'd like to find a way to run just one report where all of the above occur, the issue I face running separately is one person may appear on 1 or more giving duplicates.
USE HealthBI_Views;this bit below is basically grouping my output on Forename, Surname, DOB & Postcode. What the whole process is trying to achieve is to show where a patient which has all of the above fields identical but has a different patient identifier suggesting that the service has allocated to unique identifiers to the same person which will result in both records needing to be merged.
WITH cte
AS (SELECT *,
COUNT(HEYNo) OVER (
PARTITION BY Forename, Surname,
DOB
[code]...
--- this bit below is simply showing all instances where the above criteria is met and there are more then one instances.WHERE countOfHeyNo > 1
--- The final output display all patient identifiable information from the MF_PATIENT table so that the report can be created in SSRS and run routinely by the Data Quality Team who can then investigate each occurance.
View 5 Replies
View Related
Mar 5, 2008
I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.
The following query returns, for each store, the total number of records in the ProductInventory table:
SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
GROUP BY Stores.Name
The following query returns, for each store, the total number of records in the Customers table:
SELECT Stores.Name,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name
I combined the two queries:
SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
LEFT JOIN Customers ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name
When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected
What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
View 7 Replies
View Related
Jun 16, 2015
how to combine measures and fields coming from an analytical model (tabular) along with some Excel calculations. Basically I want to provide users with a simple report (to be displayed in SharePoint Excel services) containing charts and slicers. The data comes from a tabular model, and most of the calculations are in the model as well.However there is some little tweaking that must be done. For example I might need additional calculated columns, but I don't feel the need to modify the tabular model for that. I was wondering if I could do this within Excel as well -- but without having to bring all the data through a pivot table, then manipulate it and then show it on the report. So to be clear I do not want any pivot tables lying around, even if on a hidden sheet.
I noticed that when selecting a pivot chart in Excel, at the ribbon menu under "PIVOTCHART TOOLS"/"ANALYSE" there is a group of buttons named "Calculations". One of them is named OLAP Tools.Is it fair to assume that these options will allow me to create new measures at the Excel side, without affecting my tabular model?
View 2 Replies
View Related
Jun 2, 2008
Hi ALL,
I am not able to understand the concept of "PIVOT" queries.I am not able get when and how to use this types of query.Help Me.
Cheers,
Mathi
India.
View 3 Replies
View Related
Aug 18, 2015
I pull data from Sql Server through the query, I want to pass the region parameter to the power pivot connection query. So that I can automatically pull the required region data. The parameter should pick the value from the excel range. And also how to control this through VBA
View 4 Replies
View Related
Feb 11, 2004
Is there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?
I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.
View 3 Replies
View Related
Oct 8, 2014
I have 4 tables which I am extracting 2 distinct values;
Select Distinct UniId, PID from dbo.Event1
Select Distinct UniId, PID from dbo.Event2
Select Distinct UniId, PID from dbo.Event3
Select Distinct UniId, PID from dbo.Event4
Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)
Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .
View 2 Replies
View Related
May 27, 2008
In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.
INSERT INTO STU_SUMMARY
(SUMMARY_PERIOD,
CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU)
VALUES (GETDATE(),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
(select count(*) counter from DATA_ERROR_DETAIL),
)
View 1 Replies
View Related
Mar 10, 2008
hi need help
how to "select from pivot stored procedure and insert into table"
this line
INSERT INTO [nili].[dbo].[tb_pivot_edit]
tnx
Code Snippet
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080401', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @NumDays = 28
BEGIN
SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28])
) AS p
END
ELSE IF @Numdays = 30
BEGIN
SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM (
SELECT ID,new_unit,mhlka_id,mhlka,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
INSERT INTO [nili].[dbo].[tb_pivot_edit]
([Fname]
,[new_unit]
,[mhlka_id]
,[mhlka]
,[fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5]
,[fld6]
,[fld7]
,[fld8]
,[fld9]
,[fld10]
,[fld11]
,[fld12]
,[fld13]
,[fld14]
,[fld15]
,[fld16]
,[fld17]
,[fld18]
,[fld19]
,[fld20]
,[fld21]
,[fld22]
,[fld23]
,[fld24]
,[fld25]
,[fld26]
,[fld27]
,[fld28]
,[fld29]
,[fld30]
,[fld31])
END
View 1 Replies
View Related
Jun 1, 2007
Hi,Ive got a class in my code which contains data from several tables. A user profile has a single record in a user_profile tables, for each of these records there is several records in a another table, which itself has several records in a tags table.So far the save method on the class saves the record to the user profile table, then loops through the next two tables info to insert each record. I want to decrease the number of trips to the database and am wondering if anyone can advise on the best way to do this.I am considering using a dataset to retrieve and store the records, instead of a datareader Im using currently. Then when Im done altering the data in the dataset (all three tables) I can commit all the changes in one database transaction?? I have been avoiding datasets due to the fact datareaders are apparently faster.My other option is to try make three calls to the database, one per table, updating my stored procedures to accept arrays of data using XML?? Can anyone tell me what is the best option for me?Thanks,C
View 5 Replies
View Related
Feb 22, 2005
Hello
I have a question about something odd happening with an insert query.
I am working on a password protected site and I want to moniter general student use. I have an on page load even which picks up their user id and the url and sends it to the database
Because my site is a beta site, I also want the users to be able to add their comments as and when they want, so I built a small feedback form on each page.
The quick feedback form has a panel to hide it until a user clicks on a radio button and this reveals a text box and a submit button.
However, since I have added this second sql connection with an insert, I now get an error message I was getting a connection string error when I tried to point at the connection string in the web.config file, so I put the full path in to the database and now I get an error message that says the conTrackUser has not been declared, but as you can see from the code it certainly has. So I'm beginning to thing that the error is something entierly different, but that's probably me being thick.
I've pasted the two insert blocks below, I can't see anything wrong with them, but then I'm mostly groping about in the dark with asp.net.
Is it not possible to have two insert commands on a page? That would be daft.
Here is the code.
Track and initilize the radio button and panel for the feedback:
Sub Page_Load(Sender As Object, E As EventArgs)
Dim UserID as String = session("UserID")
pnlQuickFeedback.Visible = False
If Not Page.IsPostback Then
Dim conTrackUser As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conTrackUser = New SqlConnection("server='server'; user id='me'; password='nuts'; database='database'")
cmdInsert = New SqlCommand("TrackUser5530", conTrackUser)
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Parameters.Add("@UserIdentity", session("UserID") )
cmdInsert.Parameters.Add("@URL", Request.Url.AbsoluteUri )
conTrackUser.Open()
cmdInsert.ExecuteNonQuery()
conTrackUser.Close()
End If
End Sub
The sub for the feedback:
Sub SubmitFbbtn_Click(Sender As Object, E As EventArgs)
Dim UserID as String = session("UserID")
'If Not Page.IsPostback Then
Dim conQuickFeedback As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand
conQuickFeedback = New SqlConnection("server='server'; user id='me'; password='nuts'; database='database'")
cmdInsert = New SqlCommand("QuickComments5530", conQuickFeedback)
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Parameters.Add("@UserIdentity", session("UserID") )
cmdInsert.Parameters.Add("@Comments", txtComments.text)
cmdInsert.Parameters.Add("@URL", Request.Url.AbsoluteUri )
conQuickFeedback.Open()
cmdInsert.ExecuteNonQuery()
conQuickFeedback.Close()
pnlQuickFeedback.Visible = False
feedbackFormbtn.Visible = True
feedbackFormbtn.Checked = False
End Sub
thanks
View 1 Replies
View Related
Mar 5, 2001
Does anyone know how to improve performance on insert statements. I have to run a query of several thousand insert statements, but it just takes too long. Does anyone know of any good tips to improve performance?
joe
View 3 Replies
View Related
Feb 9, 2015
I am trying to insert the queries in a table such as the following, not able to insert it.
create table test (txt Varchar(200))
insert into test values('select *from master.dbo.sysprocesses WHERE status NOT IN('sleeping','background')')
View 4 Replies
View Related
Sep 23, 2015
We want to implement cascading slicers in PowerPivot 2010. Does Excel 2010 have 'Insert Slicer Hierarchy' feature in PowerPivot?
View 5 Replies
View Related
May 16, 2004
I have a SQL stored procedure like so:
CREATE PROCEDURE sp_PRO
@cat_num nvarchar (10) ,
@descr nvarchar (200) ,
@price DECIMAL(12,2) ,
@products_ID bigint
AS
insert into product_items (cat_num, descr, price, products_ID) values (@cat_num, @descr, @price, @products_ID)
when I try and insert something like sp_PRO '123154', 'it's good', '23.23', 1
I can't insert "'" and "," because that is specific to how each item is delimited inorder to insert into the stored procedure. But if I hard code this into a aspx page and don't create a stored procedure I can insert "'" and ",". I have a scenario where I have to use a stored procedure...confused.
View 3 Replies
View Related
Jul 20, 2005
I am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Sep 8, 2007
I'm writing an application for Windows Mobile 5 / Pocket PC using VB.NET 2005. The database is connected using an instance of SqlCeConnection and updated by an SqlCeCommand.
The application can perform select queries on data originally entered into the database through Visual Studio, or perform update / insert queries at run time. Anything inserted or updated can be returned by a select query whilst the application is running, however, anything I have inserted or updated doesn't appear to be written to the SDF file and hence is not in the database after restarting the application.
Am I missing something that's different between performing queries on an SQL CE database on Pocket PC and an ODBC source in a normal Windows application?
View 13 Replies
View Related
Feb 9, 2007
Hi,
My scenario:
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Thanks in advance for your help.
Regards,
$wapnil
View 4 Replies
View Related
