Transact SQL :: How To Frame A Range Based On Amount

Oct 24, 2015

I want to frame a range of data based on particular group of columns

If OBJECT_ID('tempdb..#ResellerRange') IS NOT NULL
    drop table #ResellerRange
create table #ResellerRange
(   ResID        varchar(10)
   , amt    decimal(18,2)
   , serialno    int)

   insert into #ResellerRange ( ResID,amt, serialno )
   values ('Raja',10,67),('raja',10,68),('raja',10,89),('Prabu',20,56)

I want below output
 
   resid    amt    min    max
    ----------------------------------   
   raja      10    67     68
    raja      10    89     89
    Prabu     20    56     56

View 5 Replies


ADVERTISEMENT

Transact SQL :: Query Distinct Results Based On Date Range

May 21, 2015

I have a data structure like this

UID , Name, amount, start date                               End Date
     1      A         10         2015-05-01 00:00:00             2015-05-01 23:59:59
     2      A         10         2015-05-02 00:00:00             2015-05-02 23:59:59
     3      A         10         2015-05-03 00:00:00             2015-05-03 23:59:59
     4      A         10         2015-05-04 00:00:00             2015-05-04 23:59:59
      5      B         10         2015-05-05 00:00:00             2015-05-05 23:59:59

[code]...

View 5 Replies View Related

Transact SQL :: Update Table Based On Available Date Range In Same Table

Dec 2, 2015

I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.

Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range

Note: ROW is Partition By ColumnA,ColumnB

TableName: PromotionList

ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL

Expected outcome after performing update on the table

ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL

View 4 Replies View Related

Analysis :: Annualized Amount Based On Completed Quarters

Aug 26, 2015

I would like to add a calculation to provide an annualized Gross Amount value.  Most solutions I have seen are for annualizing down to the day.  For instance, if today is the 123rd day of the year, the calculation would be Amount / 123 * 365.  What I want to do is annualize the amount based only on completed periods.  Today is 8/26.  

The last completed quarter ended on 6/30.  Therefore I want to basically take the sum of Q1 and Q2, divide by two, and multiply by four to get an annualized amount based on completed quarters.  Eventually I want to do something similar with months.

So here's what I have so far.  In my DSV my DimDate is actually based on a view.  The view has a Date column with the actual date, all of the normal fields you would expect in a DimDate table, and two columns that look like this:

CASE
WHEN [Date] < DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
THEN 'Y'
ELSE 'N'
END AS
CompletedQuarter,

[Code] ....

Note that for dates between 1/1 and 3/31 the CompletedQuarterCount will be 0.  I want any annualized amount to be 0 in that case because I only want to use completed quarters in my calculation.

I added both to my Pay Date dimension (which uses vwDimDate from the DSV) in the cube.  I have tried the calculation below and in my Excel pivot table I'm getting blanks for the calculated field.

CREATE MEMBER CURRENTCUBE.[Measures].[Gross Amount Annualized by Pay Date Quarter]
AS SUM(
IIF([Pay Date].[Pay Date Completed Quarter Count] > 0,
[Gross Amount] / [Pay Date].[Pay Date Completed Quarter Count] * 4,
0)
),
FORMAT_STRING = "Currency",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Claim' ;

So, is my calculation correct?

View 2 Replies View Related

Transact SQL :: Sum Amount By Grouping Date

Oct 3, 2015

I have a table that have customer name and their bill amount

a) tblBilling

which shows records like below (Billing Dates are shown in Year-Month-Day format)

Invoice   Customer             BillingDate       Amount            Tax
--------------------------------------------------------------------------
1             ABC                       20015-10-2           1000.00            500.00
2             DEF                        20015-10-2           2000.00        1000.00
3             GHI                        20015-10-2           1000.00            500.00
4             JKL                         20015-10-3           5000.00          2500.00
5             MNO                     20015-10-3           1500.00            750.00
6             PQR                       20015-10-4            500.00            250.00
7             STU                        20015-10-4           1000.00           500.00
8             VWX                      20015-10-4            2500.00         1250.00

I want to perform a query that should SUM Amount and Tax Colums by date basis, so we could get the following result

 BillingDate       Amount            Tax
--------------------------------------------------------------------------
20015-10-2           4000.00            2000.00
20015-10-3           6500.00            3250.00
20015-10-4           4000.00            2000.00

View 3 Replies View Related

Transact SQL :: How To Divide Amount By Cash And Credit Card

Nov 19, 2015

I have typed a query by combining multiple tables to show sum of TotalReceivedAmount by MRN. It also have payment mode which is paid by cash or credit card. Now the query shows the sum for MRN combining all the payment done by credit card and cash. How am I supposed to show the amount paid by credit card and cash separately?

Here is the query :

SELECT Distinct     dbo.CA_Payment.MRN, dbo.CA_Patient.FirstName, dbo.CA_PaymentModeMaster.Description AS PaymentMode, 
CASE (dbo.CA_Payment.PaymentModeID) WHEN 2 THEN dbo.CA_Payment.CardNumber END AS CreditCardNumber, CASE (CA_Registration.PatientTypeID) 
WHEN 1 THEN dbo.CA_PatientTypeMaster.Description WHEN 2 THEN dbo.CA_DebtorMaster.DebtorName END AS Debtor, 

[Code ....

View 6 Replies View Related

Transact SQL :: How To Deduct Retention Amount From Arrears Payment

Aug 27, 2015

We have retention policy , and pay at the time year completion , now policy change and it is converted from yearly to monthly and this with effect from April-15. 

if calculate the pay system will generate the Arrear payment of the employee from the month of April  onward but i already paid the retention amount for month for two month April and May which i need to deduct the same otherwise this will double amount .

View 2 Replies View Related

Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?

Nov 28, 2012

I have a sales tables which looks as below.

DEPARTMENT
Barnd_Name
Item_Group
     S_DATE
          S_AMOUNT
Administration
IBM

[code]....

Now i need Month Wise Running Totals.but i should check the following group as show below i that order

1) DEPARTMENT
1) Brand
3) Item Group
4) Month

View 12 Replies View Related

Transact SQL :: Use Print Function To Output Numeric Variable With Fixed Amount Of Leading Zeroes

Apr 23, 2015

I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string.  For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.

Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.

View 4 Replies View Related

Create A Record Based On Date Range

Mar 25, 2008

I need to create records based on date range on monthly basis, please help.

Here is an example:
ID Startdate EndDate
1 20070301 20070522

and I need to create the following data set based on start and end date range
ID Startdate EndDate
1 20070301 20070331
1 20070301 20070430

1 20070301 20070522

View 8 Replies View Related

Create (n) Number Of Records Based On Date Range

Mar 11, 2008

Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:

@StartDate = '01/01/2008'
@EndDate = '01/8/2008'

Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:

01/01/2008 <datacol1> <datacol2>
01/02/2008 <datacol1> <datacol2>
01/03/2008 <datacol1> <datacol2>
01/04/2008 <datacol1> <datacol2>
01/05/2008 <datacol1> <datacol2>
01/06/2008 <datacol1> <datacol2>
01/07/2008 <datacol1> <datacol2>
01/08/2008 <datacol1> <datacol2>

I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.

View 3 Replies View Related

Creating Rows Based On Date Range From Another Table

Aug 20, 2006

I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

View 7 Replies View Related

Loop Through Flat Files Based On A Date Range

Feb 9, 2007

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

View 25 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Transact SQL :: BCP - Numeric Value Out Of Range

Apr 18, 2015

I'm exporting out a number of tables via BCP for our Cognos group to report on.  Almost all tables can export and import successfully.  I'm using the following command to export out:

SET OUTPUT=K:BCP_FIN_Test
SET ERRORLOG=K:BCP_FIN_TestBCP_Error_Log
SET TIMINGS=K:BCP_FIN_TestBCP_Timings
bcp "SELECT TOP 100 * FROM FS84RPT.dbo.PS_VOUCHER Inner Join FS84RPT.[dbo].[PS_VCHR_ACCTG_LINE] on PS_VOUCHER.VOUCHER_ID= PS_VCHR_ACCTG_LINE.VOUCHER_ID and PS_VOUCHER.BUSINESS_UNIT = PS_VCHR_ACCTG_LINE.BUSINESS_UNIT WHERE PS_VCHR_ACCTG_LINE.FISCAL_YEAR
= '2014' and PS_VCHR_ACCTG_LINE.ACCOUNTING_PERIOD BETWEEN '9' AND '11' " queryout %OUTPUT%PS_VOUCHER.txt -e %ERRORLOG%PS_VOUCHER.err -o %TIMINGS%PS_VOUCHER.txt -T -N

The Query returns data fine in a SQL editor.  We have tried exporting/importing both with Native and Character settings.

Below is the errors on import:

Here is the table structure:

Column/Type/Computer/Length
BUSINESS_UNIT char no 5
VOUCHER_ID char no 8
VOUCHER_LINE_NUM int no 4
TOTAL_DISTRIBS int no 4

[Code] ....

View 8 Replies View Related

Performing Date Range Queries Based On A Non-Calendar Fiscal Year

Sep 20, 2006

Hi,

Using SQL Server 2000, I need to perform date range type queries that involve my company's Fiscal Year, which is not the same as the calendar year. My company's Fiscal Year if from Sept 1 to Aug 31, where Aug 31st year determines the Fiscal Year. For example, since today's date is 09/20/2006, the current Fiscal Year is 2007.

An example of a typical query requirement:

Find all the sales figures to-date for the current Fiscal Year. So, a WHERE clause will consist of a date range query from 09/01/2006 to 8/31/2007.

Initially, I created a Function to find the current Fiscal Year based on the current date, by calling the GETDATE() function and passing the results to the following function:

CREATE FUNCTION dbo.fnGetFY (@CurrentDatetime datetime)
RETURNS int
AS
BEGIN
DECLARE @FY int
IF (SELECT MONTH(@CurrentDatetime)) > 8
SET @FY = YEAR(@CurrentDatetime) + 1
ELSE
SET @FY = YEAR(@CurrentDatetime)
RETURN(@FY)
END

So, the view queries that involve the Fiscal Year call the above function.

However, these function calls drag down the VIEW query response time to the point where the time is either unacceptable or an ODBC Timeout occurs, even with Query Analyzer.

Is there a way to create a Global server parameter to hold the current Fiscal Year value, so function calls are not necessary? Or set Fiscal Year date ranges for a database or server system parameter?

Does anyone know of a efficent, response timewise, way to handle Fiscal Year date range queries?

Will appreciate the help!!!

View 5 Replies View Related

Transact SQL :: How To Select First And Last Date Within A Range

Oct 12, 2015

I have a table with the following data:

Type Date Subtype
1 1-1-2015 10
1 5-1-2015 10
1 6-1-2015 10
1 15-1-2015 11
1 20-1-2015 10

[Code] ....

I want to group by the data to the following while saving the first and last date within each type and subtype: 

type startdate enddate subtype
1 1-1-2015 6-1-2015 10
1 15-1-2015 15-1-2015 11
1 20-1-2015 22-1-2015 10
1 25-1-2015 28-1-2015 12
2 2-1-2015 5-1-2015 11
2 6-1-2015 7-1-2015 12
2 14-1-2015 20-1-2015 11
2 23-1-2015 30-1-2015 13

I tried several approaches, but I can't fnd a solution to do this with T-SQL.

View 8 Replies View Related

Transact SQL :: Date Within Range Calculations

Jul 31, 2015

I'm trying to move some logic that I have currently within a program and putting it into SQL instead.

My table has the following 3 fields that are of interest to me: StartDate (DateTime), StopDate(DateTime), Length (int)

Length is calculated based on StopDate - StartDate and is expressed to the nearest minute.

What I want to do is query the Db giving a start date and end date and return all records that fall within that date range. I then want to present that data such that the earliest date is set to the start date criteria, the last to the end date criteria and the length recalculated.

Say for example I query for results between 01/02/2015 07:00:00 and 01/02/2015 19:00:00 and I get the following:

Start Stop Length
01/02/2015 06:30:00 01/02/2015 07:05:00 35
01/02/2015 07:05:00 01/02/2015 10:00:00 175
01/02/2015 10:00:00 01/02/2015 19:15:00 555

I would like the output to show as

Start Stop Length
01/02/2015 07:00:00 01/02/2015 07:05:00 5
01/02/2015 07:05:00 01/02/2015 10:00:00 175
01/02/2015 10:00:00 01/02/2015 19:00:00 540

Is there a way to do this?

View 5 Replies View Related

Transact SQL :: Check If A Date Is Within A Range Of Dates?

Aug 20, 2015

Basically, I have a membership table that lists each member with an effective period, Eff_Period, that indicates a month when a member was active. So, if a member is active from Jan to Mar, there will be three rows with Eff_Periods of 201501, 201502 and 201503.

All well and good.But, a member may not necessarily have continuous months for active membership. They might have only been active for Jan, Feb and Jun. That would still give them three rows, but with noncontinuous Eff_Periods; they'd be 201501, 201502 and 201506.There is also a table that logs member activity. It has an Activity_Date that holds the date of the activity - betcha didn't see that comin'. What I'm trying to do is determine if an activity took place during a period when the member was active.

My original thought was to count how many rows a member has in the Membership table and compare that number to the number of months between the MIN(Eff_Period) and the MAX(Eff_Period). If the numbers didn't matchup, then I knew that the member had a disconnect somewhere; he became inactive, then active again. But, then I thought of the scenario I detailed above and realized that the counts could match, but still have a discontinuity.So, is there a nifty little SQL shortcut that could determine if a target month is contained within a continuous or discontinuous list of months?

View 14 Replies View Related

Transact SQL :: Select First And Last Record For Certain Date Range

Jun 16, 2015

I have a situation where an agent has number of activities for a certain date range. If an agent has multiple activities within certain date range, I would like BALANCE BEFORE from the first activity and BALANCE AFTER from the last activity. Here is my current SQL query that returns the following data:

DECLARE @BeginDate Datetime
DECLARE @EndDate Datetime
Set @BeginDate = '05-1-2015'
Set @EndDate = '05-31-2015'
SELECT
a.AgentName,
R.BALANCEBEFORE,

[Code] ....

AGENTNAME          BALANCE BEFORE  BALANCE AFTER          DATE
DOUGLAS              9738.75                9782.75                     2015-05-11
DOUGLAS              9782.75                9804.75                     2015-05-12
DOUGLAS              9804.75                9837.75                     2015-05-13

In the sample data above, ideally I would like my query to return data as follow:

AGENTNAME          BALANCE BEFORE                  BALANCE AFTER
DOUGLAS              9738.75 (from first activity)    9837.75 (from last activity)

Not sure how I can write sql query to accomplish this.

View 7 Replies View Related

Transact SQL :: How To Query Records For A Date Range

Oct 14, 2015

This one is making my head hurt!  Trying to figure out how to query for records between date range.  The records have a start_date and an end_date field.  The end_date field maybe null.

For example, say you wanted to see the records of everyone checked into a hotel during a given date range.  You need to account for the people that checked in before you @start_date parameter and may check out after your @end_date parameter.  

fyi- As for the null end_date field, think of this as they have checked in and not sure when they will checkout yet.

View 7 Replies View Related

Transact SQL :: Loop A Month In A Specified Date Range?

Jul 23, 2015

I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:

declare @table1 table (
YEAR_EFF int,
MONTH_EFF int,
IDNumber (8),
SUBS_CNT smallint,
MEM_CNT smallint)
declare @StartDate datetime,

[code]....

Others says I need to use exec sp_executesql N'' but how do I use it using my code above?

View 7 Replies View Related

Transact SQL :: Get Activated Data For A Date Range?

Jun 29, 2015

I've data like below:

Now, I've to get active data for a particular date range. Let me explain the active data definition as below:

StartDate : 01-Jul-2015
EndDate : 31-Dec-2015

It should return all the data which was active for that date range even if it was only for one day.If no data found for that date range, check the last record before start date and and if its active then it should be returned else not.

I though of creating a function and pass primary key with date range and return the final status but that doesn't seems like an optimized query.

View 4 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

Transact SQL :: Query DateTime Field By Date Range

Dec 2, 2015

I have a table of errors with a DateTime field for when the error occurred.  I want to query the table for a given date range omitting the time portion.  What is the most efficient way to perform this query?

View 5 Replies View Related

Transact SQL :: Grouping Records With A Date Range Into Islands

Nov 18, 2015

I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way.  I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.

Data example:
DECLARE @LengthOfStay TABLE
(
PersonVARCHAR(8) NOT NULL,
CheckInDATE NOT NULL,
CheckOutDATE NULL

[code]...

View 7 Replies View Related

Transact SQL :: Combining Dates To Group Into A Date Range?

Nov 17, 2015

My scenario is: a person has many events, all based on a date.  I need to aggregate the person to show min and max dates for a period, the period being defined as ending when there is not an event following the next date.

DECLARE @Events TABLE
(
PK_EventINTIDENTITY(1,1) PRIMARY KEY
,FK_UserINTNOT NULL
,EventDateDATENOT NULL
)
DECLARE @User TABLE

[Code] ....

I would expect the groups to look something like below:

Is this where a recursive CTE may be used?

View 6 Replies View Related

Transact SQL :: Recognizing Month And Proportion In A Date Range

Oct 8, 2015

I need to recognize only one month in a date range e to make a proportion of the quantity. Practically, period 31-08-2015 - 30-09-2015 is 31 days, 1 belonging August and 30 belonging September so 3.2258% of the quantity must belong August and 96.7742% September. The quantity 200, so 193.54 belong September (That's what I need to achieve). Range 01-09-2015 / 30-09-2015 Qty 500, all 500 belong September.

Range 01-07-2015 / 20-08-2015 Qty 2500 0 belong September. A little bit more complicated if I got 25-06-2015 / 16-12-2015. it 30 day for September and with a datediff I can count the days and make a proportion. I can write piece by piece the code but I'd prefer of course to have only one query for this.

The DDL:
create table forum (idd int, byfrom date, byto date, qty int)
insert into forum values
(1,'2015-06-15','2015-08-18',300),(2,'2015-09-16','2015-10-04',400),(3,'2015-07-28','2015-09-27',1000),
(4,'2015-09-01','2015-09-30',500),(5,'2015-09-03','2015-09-03',300),(6,'2015-08-02','2015-09-02',100),
(7,'2015-07-01','2015-07-30',500),(8,'2015-06-03','2015-12-08',500),(9,'2015-09-01','2015-09-30',500),
(10,'2015-08-04','2015-09-04',300)

View 14 Replies View Related

Transact SQL :: Add Random Dates To Existing Table From Defined Range

Jun 15, 2015

I'm trying to add random dates to date column in existing table, but these need to be week days (Mon-Fri).I'm a beginner in TSQL, worked with MS Access many years - in Access I used to do something a bit different:

DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
Table had ID, I gave a date it would start from (31/08/2015) and then range of ID to apply new date:
UPDATE table1 SET table1 .date = DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#)
WHERE (((table1 .ID) Between 1 And 5456));

This was applying random dates in range of 31/08/2015 + 5 days, so I could give a starting date of Sunday to get random dates populated over given IDs from Monday to Friday.Now, how can I do it in TSQL?I have a table with ID and dates column. I would like to apply new random dates from some range, but making sure they will be week days.

View 3 Replies View Related

Transact SQL :: How To Find Whether A Column Lies In Range Of Smallint / Int / Bigint

May 12, 2015

UPDATE P   
SET  
P.IsError=1
,P.IsDrawingRevNo=1 
,ErrorMessage=ISNULL(ErrorMessage,'')+'| DrawingRevisionNumber DataType Is Not Valid, smallint expected(-32768 AND 32767)'
FROM ZPTSMGR.ProjectDrawingRaw P
WHERE  P.LogId=@LogId AND   P.ProjectId=@ProjectId AND  P.Revision > 32767   (P.Revision  NOT BETWEEN  -32768 AND 32767)  --SMALLINT RANGE  -32768 to 32767.

--DataType Range
--tinyint DataType  (MinVal: 0, MaxVal: 255). Its storage size is 1 byte.
--smallint DataType from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes.
--int DataType   -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes.
--Bigint DataType -- from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.

The SQl statement fails, and not able to update it. The IsError flag need to set since the value does not lies in given range of smallint.--------say 457896523 which is not a small int value

View 7 Replies View Related

Transact SQL :: How To Hard Code Mention Date Range In SP To Get Expected Results

Oct 7, 2015

how to hard code mention date range in my SP to get expected results in my query 01/01/2012 to 12/31/2012

DECLARE @ACCOUNT AS INT
DECLARE @POSTING_DATE AS DATETIME
DECLARE @FIRST_POSTING_DATE AS DATETIME
SET @POSTING_DATE = {?POSTING_DATE}
SET @ACCOUNT = {?ACCOUNT}

[code]...

View 3 Replies View Related

How To Separate Period Amount From YTD Amount

Mar 18, 2008

I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:

SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid

FROM
UrsinusCollege.dbo.gl7accounts a

join
ursinuscollege.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid

where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')

order by a.accountnumber

Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.

Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.

Thanks very much.

Sue

View 6 Replies View Related

SQL Server To Main Frame

Jun 23, 2004

I'm trying to send data from a table on SQL server to a DB2 table on the Main Frame and I get this error. Any ideas?

[IBM][CLI Driver][DB2]SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: "UPDATABLE WORKFILE", type of resource: "100", and resource name: "TEMP DATABASE". SQLSTATE=57011

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved