Here is my Query, I don't know whether I'm getting it right?
--Quarter 1
SELECTD.MerchantName, A.MID, A.TID, ISNULL(SUM(A.SumTrxnMon), 0) AS SumTrxnMon, E.FullName, E.DxBEmail
INTO#Quarter1
FROMdbo.tblRPT_Spend AS A INNER JOIN
dbo.tblMer_DeployORetrieveTerm AS B ON A.MID = B.MID AND A.TID = B.TID INNER JOIN
now I want to call all the sp on the basis of input like If filename is Fdoor then it shold fire the SP_Archive_using_merge_Fdoor , if file name is Fdoop then it shoilud fire the SP_Archive_using_merge_Fdoop like that .
below is the 2 sp .
--First SP ALTER PROCEDURE [dbo].[SP_Archive_using_merge_Fdoor] AS BEGIN SET NOCOUNT ON DECLARE @Source_RowCount int DECLARE @New_RowCount int
I can fetch the counts for total present and absent
Query i have tried is
Declare @StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458' Begin select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
I want to write sql query which runs in a background on cyclic basis. Basically i want to count the row entries of 1 table and store the data and the count in two distinct columns.
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
Anyone have any experience in connecting to a Basis database? I want to build an SSIS package to ETL data out of a Basis database and into a sql server 2005 db. I have downloaded the ODBC driver, but am not usre how to plug it in to SSIS to do teh extraction.
Also I am totally new to Basis...it is not an RDBMS at all but the literature says it supports SQL queries, so I am not sure about where to start.
Hello All,i have 2 excel files which i want to upload to sql database. i know how to upload to the folder. my question how to i use these excel files and convert them into tables in sql database.i have to do this on a regular basis. is there anyway i can do this. the 2 files when converted to tables, i need to create relationships etc. any suggestions or references please. i appreciate it. Thanks
I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to run this script on a daily basis and save the results as a .csv file.
I've tried creating a job in SQL Manager - that didn't work. Then I found a tutorial that had me use MS Visual Studio... couldn't get that to function either.
what program I should be using, and how to go about creating this as a task / job that can run on a scheduled basis?
Requirements: We have Our Local DataBase(SQL Server) Guess eg. DBLocal We have a requirements to update this DBLocal Table Data with Other Database DBRemote(SQL Server) table data. This Task is Schedule in a Week. Means We needs to update this DBLocal Table Every Week from DBRemote Data.
Both Side we have only one table. Means Source is One Table and Destination is Table One.
Right Now I have a connection string for DBRemote(SQL Server) and SQL Statement for getting Data from DBRemote(SQL Server).
can any one tell me what I need to do for achive this requirement?
please provide me link also; from there i can get enough information for my requirements.
On what basis, SQL server decides the sequence of these rows?
I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.
I'm trying to build a where clause that looks at two columns to determine if QUARTERLY and ANNUAL payments are due. rec_day could be any day. I got the monthly one pretty easily: WHERE rec_start <= GETDATE() AND rec_frequency = 'Monthly' AND rec_day = DAY(GETDATE()) So if the rec_day was 9 and the rec_frequency was 'Quarterly' then I would be looking to see if todays date is 1/9 or 4/9 or 7/9 or 10/9 (not sure how I do this part) I'm pretty much stuck on where to go with the quarterly where clause: WHERE rec_start <= GETDATE() AND rec_frequency = 'Quarterly' AND rec_day = ????
I tried the following, but it did not work: (CAST(DATEPART(Q, GETDATE()) * 3 - 2 AS VARCHAR(2)) + '/' + CAST(rec_day AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) = GETDATE())
I have transaction table I am pulling transactions from and can't quite figure out how to get a sum based on a few items.
I am trying to figure out how to show the sum of the InvestorAssumption and group it based on the DealId, then DealTransType and show the sum of the InvestorAssumption.
So I want to see a total of the InvestorAssumption by quarter split up by DealTransType, then DealId.
Not sure how to do this and any help would be greatly appreciated.
Here is my current view.
Code:
SELECT TOP 100 PERCENT dbo.DealTransactions.DealTransId, dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, DATEADD(quarter, DATEDIFF(quarter, 0, dbo.DealTransactions.DealTransDate), 0) AS TransDateQuart, dbo.DealTransactions.DealTransType, dbo.DealTransTypes.DealTransTypeName AS TransactionType, dbo.DealTransactions.DealTransAmount, dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100 AS InvestorAssumption, dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransDate AS TransDateActual FROM dbo.DealTransactions INNER JOIN dbo.DSGvwInvestorPercentage ON dbo.DealTransactions.DealId = dbo.DSGvwInvestorPercentage.DealID INNER JOIN dbo.DealTransTypes ON dbo.DealTransactions.DealTransType = dbo.DealTransTypes.DealTransTypeId GROUP BY dbo.DealTransTypes.DealTransTypeName, dbo.DealTransactions.DealTransAmount, dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, dbo.DSGvwInvestorPercentage.InvestorPercentage * dbo.DealTransactions.DealTransAmount / 100, dbo.DealTransactions.DealTransType, dbo.DSGvwInvestorPercentage.InvestorPercentage, dbo.DealTransactions.DealTransId, dbo.DealTransactions.DealTransDate ORDER BY dbo.DealTransactions.DealId, dbo.DSGvwInvestorPercentage.InvestorID, dbo.DealTransTypes.DealTransTypeName, dbo.DealTransactions.DealTransAmount
RE: Quarterly report - calculating for each of the next four months?
As I play with joins and functions to help mold my data.. I'm thinking about options.
I have a status table with termination status entries that will dictate participation spans for members.. a member can have multiple stop and start status entries in that status table. I've built an sql function that given a member's ID and a month/year will return the number of days the member particpated in the month.
I need to produce an SSRS report that given a starting month/year will report days of activity for every member for the next four months.
So my report will look like this: memberid 2007/06 2007/07 2007/08 2007/09 001 10 0 20 5 002 0 2 55 1
Should I build a view that given the starting yearmonth returns month1 - 4's data in a row?
or is there a smarter design? Possibly a row for each month? or SSRS formulas? Maybe the formula is gonna be a performance killer.
I want the count of orders of a particular table on weekly basis i.e if date given to me is 10/3/2014 then my output should be count of orders from date 10/3/2014 to 09/3/2014(one week) then count of orders from 2/3/2014 to 08/3/2014(another week) and then from 24/2/2014 to 01/3/2014(another week).....
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
The SSIS package will need to accomplish the following:
1. The package will somehow need to know what the file name should be based on the last file processed. So for example if we just loaded the Jan 2015 file, the next monthly file to drop should be the Feb 2015 file. For the quarterly files, it should be Q1 2015, then Q2 2015, etc...
2. Based on the file (Monthly or Quarterly), the package needs to somehow split and process one way for Monthly and another way for Quarterly.
I want to select data from a cube using an MDX statement and show the data on a graph report.
I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.
Then set the 'Date' Column to the x-axis and the Value column to the y-axis.
The user also must have the option to not show certain periods (Switch of daily and weekly)
My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.
---- Code
SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,
NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,
[Measures].[ValueAfterLogic],DESC)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS
FROM [Workplace])
WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData ( Id BIGINT, --is actually an identity column Manifest_Id BIGINT, Doc_Num BIGINT, Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X) FROM #InvLogData GROUP BY X HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
I am not sure how to tackle the following. Environment has SQL Server 2005 and above which hosts one database (table1 & table2) per server. I have created a new SQL 2012 server (database called collection (table1 & table2) which will be the central point. I need to do the following:
1. Connect to each server get the data from specific table 2. Add a additional column called "datasourceserver" and add the server name where data came from 3. Able to schedule this task so that new data are sync to SQL 2012 server
How do I format a date value in SQL 2000 sp? Tired the following none are returning the quivilent rates. What is the syntax please? SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03/21/2008') SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '2008/03/2008') SELECT *FROM tblCompanyWHERE ( C_LastBuyDate = '03-21-2008')
Trying to get the PSI Outcome, Expected, and PSIIndex every month whether it has data or not. Created a CTE and left outer joined with PSI table, but it's still not pulling every month for every PSIKey.
