I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500) INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200) INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125) INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300) INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year], T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run) , (SELECT SUM(Stores) FROM #ABC WHERE [Year] = T.[Year] AND [Month] = T.[Month]) AS [Sum_Stores], (SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.
The data looks like: ABCD-001869202 ABCD-001869203 ABCD-001869204 ABCD-001869205 ABCD-001869209 ABCD-0018692010 ABCD-0018692011 ABCD-001869309 ABCD-001869310 ABCD-001869311 ABCD-001869312 ABCD-001869313 ABCD-001869314
Desired out put:
From To ABCD-001869202 ABCD-001869205 ABCD-001869209 ABCD-0018692011 ABCD-001869309 ABCD-001869314
I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:
SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher INTO #tempVoucher FROM LEDGERJOURNALTRANS where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday') --- DECLARE @ITEM_ID uniqueidentifier DECLARE ITEM_CURSOR CURSOR
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing( DepDate datetime, val int) insert into testing values ('2014-01-10 00:00:00.000', 1) insert into testing values ('2014-05-19 00:00:00.000', 10) insert into testing values ('2014-08-15 00:00:00.000', 20) insert into testing values ('2014-11-20 00:00:00.000', 30)
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.
So, the result would be 0, 2, 2.5, 1
I must return the value in a table. Thank you all for your help!
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
I am firing a query like:SELECT TaskName, StartDate FROMTasksWHERE StartDate >= '01-Aug-2003'Now the result comes as:TaskName StartDate--------------------------Task1 01-Aug-2003Task5 10-Oct-2003etc.Now what I want is this:Sl. No. TaskName StartDate----------------------------------1 Task1 01-Aug-20032 Task5 10-Oct-2003How do I get the Sl. No. field (it does not exist in the table).Can I do it?-surajit
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and what groups they are related to:
I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100 SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security] FROM HS140_Rpt_Tmp_ForSummary s WHERE s.[Security] = 'Yes' GROUP BY Market
Hello Friends My problem is Suppose Query is - Select * from tbl_Employee TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this. Serial Name Post 1 XYZ QER 2 SDF OPO 3 WER IPO If any body knows please post the solution its urgent.
I have a query that returns a list of results from the database and insert the results into a datatable. Is there any way to add a column to the query result that has autonumbers in it. for example the first record would have 1 the next 2, the next 3, and so on?
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....
I need to create a derived column for each row in a SQL dataset.
This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).
One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?
I have a query that uses rollup to get totals and top 101 to restrict the rows to top 100 rows plus the total column. The total column shows total for all rows not just the top 100 which is exactly what i need. The only thing I need now is a row that shows total for all rows that have been excluded, or the total row - total of the top 100.
Here's the query: (Biggest factor i have to keep in mind is query speed.)
Code Block
WITH CTE_name AS ( SELECT TOP 101 b.name, SUM(ISNULL(CAST(b.launched AS BIGINT),0)) AS quantityPrepared, SUM(ISNULL(CAST(b.bounced AS BIGINT),0)) AS bounced, SUM(ISNULL(CAST(b.delivered AS BIGINT),0)) AS delivered FROM dimension tt INNER HASH JOIN batch b ON (b.batch_id = tt.batch_id) WHERE tt.datelaunched >= '11/19/2007' AND tt.datelaunched < '11/20/2007' AND tt.id = 1 GROUP BY b.name WITH ROLLUP ORDER BY SUM(ISNULL(CAST(b.launched AS BIGINT),0)) DESC )
SELECT name, quantityPrepared bounced, delivered FROM CTE_name ORDER BY 2
Hi, I've searched quite a bit for help with this syntax but have given up. I need help with the where clause of a query using SQL SERVER that selects records a certain number of days before the current date. I have tried this and it's incorrect syntax: WHERE (fldDate < ({ fn NOW() - 500 }) Can someone please help me out with correct syntax for this? thanks much.
I have an issue with Delete statement.In the code given below (its a part of actual proc),if we use TRUNCATE to clean the temp tables, everything goes fine.But if I use DELETE in place of truncate, system skips the IF loop 'if (@script_type = 1 OR @script_type = 2)'I am not able to understand this behavioral difference between DELETE and TRUNCATE.Recently the database is being used for replication, but that should not be a reason.
SELECT @max_rows = COUNT('X') FROM #temp_table1 SET@row_cnt = 1 WHILE @row_cnt <= @max_rows BEGIN
I have a table with a list of jobs along with their start and end datetime values.
I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.
Example
Start Date=2014-05-15 12:00:00.000 End Date=2014-05-19 13:00:00.000
Total Time should be: 2 Days, 1 Hour and 0 Minutes
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
I have a request where i would like to get the start date/time and end date/time and flag (with an int) which hours (24 hour clock) have values between the two dates. Example car comes into service on 2013-12-25 at 0800 and leaves 2013-12-25 at 1400 the difference is 6 hours and i need my table to show
As i'm working away at it i'm trying to figure out how i could use a Time Dimension table for this but dont really see much. So far i have the difference between the two times in hours (hour_diff) and the start hour (min_hour) so i would like to do something where i update the first hour (min_hour) and update columns based on the numbers of hours (hour_diff)
