I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish Even after this it is not working, So provided select on schema also. used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
Dear All I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
HelloI need this really faster in mS SQL 2000Usernumber (int)reportid (FK)reportreportid (PK)Category (int)SELECT A, B, C, D INTO UserCopy FROM UserWHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Categoryand the table user has a few millions rowsReport.ReportId is a Primary key for User.reportid (FK) for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximumthank you for helping
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type'). The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.
Let's say I have a table of users. Let's imagine there's two fields: username (PK), password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to (1) SELECT * FROM [User] WHERE username = 'whatever' AND password='whatever' or (2) SELECT * FROM [User] WHERE username = 'whatever' and then in my code check that the record returned matched the password?
I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
do I get any better speed if doing it on a SP instead of just SELECT without an SP ?
if I have many users on a web-site that will execute this SP - will they get any better speed because it is a SP ? - can SP cache itself - if so - for how long ?
(Why should I use SP if not passing any parameters ?)
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
I have two tables with the common column-name/ The first one include such columns as the name of football team, its country and budget. The second one include the team name, victories (quantity of it), lost games, and season. So I have the task to select such column as name, victories, lost games in 2nd table but only that ones that has budget over 1 mln? How to build select statement SELECT name, victories, lost g/ from TABLE2 WHERE budget >1000000 from TABLE1? Or should I equates the table1.name=table2.name??
I am trying to pull a min value of a date column from table 1, if table 1 does not have any record I need pull a date column from table 2. Table 2 will always have a unique record ID (No duplicate ID's).
Table 1
ID date 1 1/1/2014 1 1/5/2014
Table 2
ID date 1 1/5/2014 2 10/15/2014
Here is the desired result when running for ID = 1 (select Min(date) where ID = 1, I should be getting 1/1/2014 from Table 1.
If I am running the same query where ID=2 then I should be getting 10/15/2014 from Table 2. So basically I need to check if a value exists on Table 1 first, if there is no value on Table 1 and then to grab the value from Table 2.
What I am after is an SQL statement which tells me for a particular category, which suppliers ARE NOT related to it, so they can be assigned. E.g.
Category D has Suppliers 1-4 unassigned Category C has Supplier 4 unassigned Category B has 2, 3, 4 unassigned Category A has Supplier 2, 4 unassigned etc...
I've got the other side of the SQL statement which tells me which suppliers are assigned to a category (see below): SELECT Suppliers.* FROM CatToSupplier INNER JOIN Suppliers ON CatToSupplier.supplierId = Suppliers.supplierId WHERE (CatToSupplier.CatId = @CatId)
This is probably really easy, but I've become unstuck!
I have an automatic data collection system. The large majority of the time, all data is electronically gathered. On occasion, the need arises to substitute a manually entered value into the data when a valuable piece of information cannot be electronically secured. When a piece of data is open to substitution, it arrives with a value of -9999 (a value not naturally occuring) in Table A. I want to be able to use a trigger on table A to get its substitution value from a corresponding column (Same name) in Table B. Tables A & B will have an identical schema. A is raw data, B is the manually updated table. Any value in the raw table with the content of -9999 gets its replacement from Table B.
I have two tables with a 1-many relationship. I want to write aselect statement that looks in the table w/many records and comparesit to the records in the primary table to see if there are any recordsthat do not match based on a certain field.Here is how my tables are setup:Task Code TableTCode,TCodeFName,ActiveTracking Table(multiple records)ID,User,ProjectCode,TCode,Hours,DateI want to look at the tracking table and see if there are any TCodelisted here that are not listed in the Task Code table.How do I write a SQL statement to do this?
I can pull a last processed date for the entire model: URL...That works great for showing the last time anything in the entire model was processed. It is not table specific. Updating / processing table A also changes the timestamp on tables B and C.However, if I want to look at just a specific table in the model (build a column for each fact table and a measure to go with it) I find that doing any process operation updates all the =Now() columns in all the fact tables.If I have a model with 3 fact tables and I do a process table using ProcessFull on one of them, all three tables calculated columns "LastProcessed"  =NOW() are updated. URL...
Type>ProcessFull</Type> Â < Object> Â Â Â < DatabaseID>MyModel</DatabaseID> Â Â Â < DimensionID>TableA</DimensionID> Â </
[code]...
Is there a way to setup a measure in the model on a per table level to show last processed date for each individual table? LastDataRefresh:= MAX ('TableA'[LastProcessed])
For example: Our sales table may update three times a day, where as our warehouse inventory table is only updated nightly.I wanted to let end users see by adding a measure for each table when the last process event was for a given table in the model.
I'm looking at SSIS and SqlBulkCopy as a possible method to quickly insert and process large amounts of data, my current method uses the sp_xml_preparedocument and OPENXML to parse an xml document of the data I want to process and insert into the database, however I'm noticing the performance of SqlServer parsing the xml document isn't that good.
I found the C# SqlBulkCopy method (new in .NET 2.0) and I was thinking it would be faster to use that to load my data into a staging table and then use SSIS to extract the data from the staging table, process and transform it as necessary and finally load it into the final destination tables. I was able to create an Integration Services Project that selects the data from the staging table, does a bit of processing on one of the fields (by calling a stored procedure), and finally loads the processed data into the final table.
The problem with this is I need to clean out the rows that were extracted from the staging table and I'm not sure how I can accomplish this (and I can't just issue a "delete from staging_table" because there maybe new records in the staging table that were not processed), perhaps I can either delete each record as it is proccessed or somehow get the last proccessed identity id from the staging table and delete all records less than or equal to that id.
thanks in advance for any help you can provide, maybe there is an easy way to accomplish what I'm trying to do.
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
This is my Table : date(m/dd/yy) points Work 9/9/06 3 Design 9/10/06 3 Design 9/11/06 3 Programming9/12/06 3 Design 10/9/06 3 Design 10/10/06 3 Design 10/11/06 3 Programming10/12/06 2 Design HOw to select in the below format Month/Year Design Programming9/06 9 310/06 8 3
I want to select a value from 2 tables , can you please teach me how to do?forexampleTable_AcctNoList Column_AcctIndex , Column_CompanyCode, Column_BankCode, Column_AcctNoTable_DataBackup Column_CompanyCode, Column_KeepMonth how can I reference to Table_DataBackup when I am in Table_AccnNolist and get the value Column_KeepMonth from Table_DataBackup in fact ... I have to reference more than 2 tables in a query.. just hope you can give me a simple exmple how to join 2 table.. in this 2 tables ( Table_AcctNoList�Table_DataBackup)what I actually need is select * from Table_AccNoList where KeepMonth = 6 ( that's what I need but just don't know how to write a correct query ) thank you very much
I am trying to do a subselect using the same table. Trying below but does not like syntax and get the error message "Server: Msg 107, Level 16, State 2, Line 1 The column prefix 'lsp2' does not match with a table name or alias name used in the query"
select lsp.last_file,lsp.source_database from log_shipping_plan_history as lsp join (select max(lsp2.end_time)as ltime,lsp2.source_database from log_shipping_plan_history where lsp2.activity = 0 group by lsp2.source_database) as lsp2 on lsp.source_database = lsp2.source_database
I am trying to select records from a vendor table which is not in a vendor delete table.
I am not allowed to add columns to any of the tables and i am not allowed to delete any. so i decided to make a delete table and select everything that is not in is.
this is what i have and i have to base the select statment on 4 colums rather then one ID as usuall.
Can someone tell me what is wrong here and help me with this.
SELECT TOP 100 PERCENT CompanyID, DivisionID, DepartmentID, VendorID, VendorName, AccountNumber FROM dbo.VendorInformation WHERE (SELECT * FROM dbo.VendorsDeleted WHERE dbo.VendorInformation.VendorID <> dbo.VendorsDeleted.VendorID AND dbo.VendorInformation.DepartmentID <> dbo.VendorsDeleted.DepartmentID AND dbo.VendorInformation.DivisionID <> dbo.VendorsDeleted.DivisionID AND dbo.VendorInformation.CompanyID <> dbo.VendorsDeleted.CompanyID) ORDER BY VendorName
is there a clever way to have a table do a select upon itself? i've created a very simple table for tracking a threaded topic. the initial message has a Parent ID = 0 the response message will have a Parent ID = the original messge.
what i'd like to do is list all messages IN ORDER, with the responses directly underneath them.
in the example below, i'd like to have record 3 appear below record 1; then record 2 and 5, then record 4...
