Date Rounding For A View
Oct 1, 2007
I am trying to incorporate a few columns into a view that each shows a certain value based on a logged datetime specific to the value. Unfortunately, the logtimes are accurate down to milliseconds, and each value has it's own logtime. (They're suppsoed to log at midnight, but sometimes log a few seconds early or late).
I want to be able to round up to 00:00:00 if it's 23:59:59 and down to the same time if it's 00:00:01. I can't very well just drop the time component because if a device logged at 00:00:01 on Aug 4 for the Aug 3rd average, and 23:59:59 for the Aug. 4rd data, then I'd have two Aug 4th values and zero Aug. 3rd values.
Additionally, I need to keep this in a datetime format for reporting purposed in Crystal Reports.
Am I asking too much?
View 18 Replies
ADVERTISEMENT
Jul 20, 2005
SQL Server 7.0The following SQL:SELECT TOP 100 PERCENT fldTSRID, fldDateEnteredFROM tblTSRs WITH (NOLOCK)WHERE ((fldDateEntered >= CONVERT(DATETIME, '2003-11-21 00:00:00',102))AND(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:59', 102)))returns this record:fldTSRID: 4fldDateEntered: 24/11/2003Hello? How is 24/11/2003 <= '2003-11-23 23:59:59'?I tried decrementing the second predicate by seconds:(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:30', 102)))returns the record, but(fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:29', 102)))does NOT.What is happening here?Edward============================TABLE DEFINITION:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRNotes_tblTSRs]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRNotes] DROP CONSTRAINT FK_tblTSRNotes_tblTSRsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSRs]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblTSRs]GOCREATE TABLE [dbo].[tblTSRs] ([fldTSRID] [int] IDENTITY (1, 1) NOT NULL ,[fldDealerID] [int] NOT NULL ,[fldWorkshopGroupID] [int] NULL ,[fldSubjectID] [int] NULL ,[fldReasonID] [int] NULL ,[fldFaultID] [int] NULL ,[fldContactID] [int] NULL ,[fldMileage] [int] NULL ,[fldFirstFailure] [smalldatetime] NULL ,[fldNumberOfFailures] [int] NULL ,[fldTSRPriorityID] [int] NULL ,[fldTSRStatusID] [int] NULL ,[fldAttachedFilePath] [char] (255) NULL ,[fldFileAttached] [smallint] NOT NULL ,[fldFaultDescription] [ntext] NULL ,[fldFaultRectification] [ntext] NULL ,[fldEmergency] [int] NOT NULL ,[fldDateEntered] [smalldatetime] NOT NULL ,[fldEnteredBy] [int] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
View 8 Replies
View Related
Feb 25, 2006
This UDF will strip off the time portion of a DateTime. It can give you either midnight last night, or midnight tonight.
Lets say you have two datetime values @dateStart and @dateEnd, and you want to select records between these dates (excluding any time portion), then you would do:
SELECT *
FROM MyTable
WHERE MyDateTimeColumn >= dbo.kk_fn_UTIL_DateRound(@dateStart, 0)
AND MyDateTimeColumn < dbo.kk_fn_UTIL_DateRound(@dateEnd, 1)
If you want to display dates, without the time, then do:
SELECT dbo.kk_fn_UTIL_DateRound(MyDateColumn, 0) AS [My Date]
FROM MyTable
--
PRINT 'Create function kk_fn_UTIL_DateRound'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_DateRound]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.kk_fn_UTIL_DateRound
GO
CREATE FUNCTION dbo.kk_fn_UTIL_DateRound
(
@dtDatedatetime,-- Date Value to adjust
@intRoundint-- 0=Round down [Midnight last night], 1=Round up [Midnight tonight]
)
RETURNS datetime
/* WITH ENCRYPTION */
AS
/*
* kk_fn_UTIL_DateRoundConvert date to midnight tonight
*For a "limit" date of '01-Jan-2000' the test needs to be
*MyColumn < '02-Jan-2000'
*to catch any item with a time during 1st Jan
*
*SELECTdbo.kk_fn_UTIL_DateRound(GetDate(), 0)-- Midnight last night
*SELECTdbo.kk_fn_UTIL_DateRound(GetDate(), 1)-- Midnight tonight
*
* Returns:
*
* datetime
*
* HISTORY:
*
* 28-Jul-2005 KBM Started
*/
BEGIN
SELECT@dtDate = DATEADD(Day, DATEDIFF(Day, 0, @dtDate)+@intRound, 0)
RETURN @dtDate
/** TEST RIG
SELECT'01-Jan-2000', dbo.kk_fn_UTIL_DateRound('01-Jan-2000', 0)
SELECT'01-Jan-2000', dbo.kk_fn_UTIL_DateRound('01-Jan-2000', 1)
SELECT'01-Jan-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('01-Jan-2000 01:02:03', 0)
SELECT'01-Jan-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('01-Jan-2000 01:02:03', 1)
SELECT'28-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('28-Feb-2000 01:02:03', 0)
SELECT'28-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('28-Feb-2000 01:02:03', 1)
SELECT'29-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('29-Feb-2000 01:02:03', 0)
SELECT'29-Feb-2000 01:02:03', dbo.kk_fn_UTIL_DateRound('29-Feb-2000 01:02:03', 1)
**/
--==================== kk_fn_UTIL_DateRound ====================--
END
GO
PRINT 'Create function kk_fn_UTIL_DateRound DONE'
GO
--
Kristen
View 4 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Nov 23, 2006
hi am am very new to sql and im finding it really hard to do parts of my assignment.
some of the questions i am struggeling on are
Which employees were hired in March?
Which employees were hired on a Tuesday?
Show details of employee hiredates and the date of their first payday.
(Paydays occur on the last Friday of each month) (plus their names)
Refine your answer to 7 such that it works even if an employee is hired after the last Friday of the month (cf Martin)
can anyone help?
View 19 Replies
View Related
Aug 29, 2006
I want to add custom view so that it show the records that their dateis less than a specific field like "2007/12/25". Dates are saved in DB like "2006/08/29 12:00:00 A.M" nad smalldatetime format in MSSQL.what should I do?
View 4 Replies
View Related
Aug 10, 2006
plese help
I want to view date dimentions like DD/MM/YY, doen't want to include time.
Thank you
View 1 Replies
View Related
Jun 6, 2008
quote:Run the view for orders shipped from January 1, 2003 and June 30, 2003, formatting the shipped date as MON DD YYYY.
creating the view:
CREATE VIEW vw_orders
AS
SELECT
o.order_id,
c.customer_id,
c.name AS 'customer_name',
c.city,
c.country,
o.shipped_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
running the view:
SELECT *
FROM vw_orders
WHERE shipped_date between '01/01/2003' and '06/30/2003'
ORDER BY 'customer_name', 'country'
I tried:
in creating view,
shipped_date = CONVERT (char(12) , o.shipped_date, 107 )
then run a view as result:
quote:order_id customer_id customer_name city country shipped_date
---------- ---------------------------------------------------------- ---------------- -------------------- ------------
what should i do?
View 7 Replies
View Related
Jul 20, 2005
How to get the date a view was last modified? (As opposed to created)
View 2 Replies
View Related
May 2, 2008
Hi
i have created 1 view and in my view i have datetime column adn i have converted it with my custom format by mere convert(column_name,6) and i am done then after when i tried to compare it with my textbox it is taking as string and when i tried to convert my date format to original format it is not allowing me so i have to put 2 duplicate column in my view i am sure there muct be some thing whcih i am not aware
First i have donbe this--> select convert(varchar,columnname,6)-- > 02 May 08
then try to compare it as date with View --> select * from ?<some table> where column_name='TEXT_BOX not desired output
select * from ?<some table> where column_name which in base table='TEXT_BOX i am done
please give me some ideas
Thanks
Parth
View 1 Replies
View Related
May 24, 2007
I have a SQL Server View. The problem is that the DateTime field has many Null values which is causing a problem with my parsing of the data in MSAccess.
How would I use CAST (or CONVERT) to handle Null Date values in my SQL Server view?
I remember there was a way to use CAST or CONVERT similar to the nz type function in MSAccess to handle null date values but I can't remember the syntax. What is happening now is that I get a data mismatch in my MSAccess function when it hits a Null Date value. Can I somehow use the ISNULL or ISDate function? I believe I need to somehow return "" instead of Null.
View 6 Replies
View Related
Mar 19, 2007
I am using BETWEEN '02/01/2007' AND '2/01/2006' in the criteria of a VIEW and have tried <= '02/01/2007' AND >='2/01/2006' but both are not filtering the dates correctly. Is there another way? What am I missing?
View 4 Replies
View Related
Feb 28, 2008
I need to control DOF (date of order) which data type is datetime for today's date.
I use 1) or 2) but got null.
1) = getdate(),
2) = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
I use
between '2/28/2008' and '2/28/2008'
will get result.
How to get today's date?
View 7 Replies
View Related
Sep 20, 2006
i have created a view to a table (90,000+ records) that i want to use to filter the table by the current year and month. the code looks like this:
CREATE VIEW [billy.bhuj].[bo current month]
AS SELECT [dbo].[bo].[recnum], [dbo].[bo].[queue],
[dbo].[bo].[queue_name], [dbo].[bo].[node],
[dbo].[bo].[interval], [dbo].[bo].[tot_calls],
[dbo].[bo].[calls_less_20_sec], [dbo].[bo].[calls_more_20_sec],
[dbo].[bo].[calls_abandon], [dbo].[bo].[abandon_before_20_sec],
[dbo].[bo].[abandon_after_20_sec],
[dbo].[bo].[queue_date],
month (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate,
year (convert (datetime,[dbo].[bo].[queue_date], 103)) as QDate1,
convert (datetime,[dbo].[bo].[queue_date], 103) as QDate2,
year (convert (datetime,(getdate()), 104)) as year1,
[dbo].[bo].[region], [dbo].[bo].[queue_type],
[dbo].[bo].[month],
[dbo].[bo].[unit],
[dbo].[bo].[service], [dbo].[bo].[reportable], [dbo].[bo].[source_dest],
[dbo].[bo].[file_name]
FROM [dbo].[bo]
Where (year (convert (datetime,[dbo].[bo].[queue_date], 104)))
= (year (convert (datetime,(getdate()), 104)))
the syntax checks fine, and without the "where" clause , i get all the original data returned no problem, so the "month", "year", and "convert" functions work fine. however, when i try to filter the data with the "where" clause above, i get about 15-20 lines of data returned and an error message referring to "Arithmetic overflow...". on their own in the "select" area the statements do what they should, but in the "where" statement they don't. sorry, i'm a big time newbie in sql, so any help would be appreciated.
View 3 Replies
View Related
Mar 14, 2008
I'm trying to clean up our report server and prune reports that are no longer in use by our users. Is there a way to find out the last time a report was accessed?
Thanks.
View 1 Replies
View Related
Jul 31, 2015
I want to view all data that have a date of today, but my query is not returning them? Is it due to the actual data being a datetime and I am not accounting for time? How should I set this query up so that the data returns?
Create Table DateTest(ID int,Date1 datetime)
Insert Into DateTest Values(1, GetDate()), (2, GetDate()), (3, GetDate()), (4, GetDate())
Select * from DateTest
ORDER BY Date1 DESC
Select * from DateTest
where Date1 = convert(varchar(10), GetDate(), 126)
View 9 Replies
View Related
Jan 22, 2008
I created a data model for report builder. And since it wont let me use views, i tried to put all the tables im using in one of my already made views, to create a data source view like my sql view. But when i connect everything the same, my report model still doesnt narrow my search. I only want to see Breed information, but since i have a Breeder table,and Customer table, its showing me all customers, and not limiting it to the ones that are breeder customers.
heres the tables:
Code Snippet
FROM dbo.Tbl_Customer INNER JOIN
dbo.Tbl_Customer_Breeder ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Breeder.Customer_Code INNER JOIN
dbo.Tbl_Customer_Detail ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_Detail.Customer_Code INNER JOIN
dbo.Tbl_Customer_Category ON dbo.Tbl_Customer.Customer_Category_Id = dbo.Tbl_Customer_Category.Customer_Category_Id INNER JOIN
dbo.Tbl_Customer_Classification ON
dbo.Tbl_Customer.Customer_Classification_Id = dbo.Tbl_Customer_Classification.Customer_Classification_Id INNER JOIN
dbo.Tbl_Customer_In_Breed ON dbo.Tbl_Customer.Customer_Code = dbo.Tbl_Customer_In_Breed.Customer_Code INNER JOIN
dbo.Tbl_Breed ON dbo.Tbl_Customer_In_Breed.Breed_Id = dbo.Tbl_Breed.Breed_Id
What am i doing wrong, and are there any suggestions.
View 6 Replies
View Related
Nov 28, 2007
Hello,
i've written the following query:
SELECT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,
dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,
dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,
dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT
FROM dbo.KALENDER INNER JOIN
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE (SELECT MONTH(s.datum_zeit)) = ((SELECT MONTH(GETDATE()))-2)and
(SELECT year(s.datum_zeit)) = (SELECT year(GETDATE()))
order by s.stunde_id
when copying that query to the view editor and executing it, it trys to fix it somehow to:
SELECT TOP (100) PERCENT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,
dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,
dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,
dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT
FROM dbo.KALENDER INNER JOIN
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE ((SELECT MONTH(s.datum_zeit) AS Expr1
FROM ) =
(SELECT MONTH(GETDATE()) AS Expr1) - 2) AND
((SELECT YEAR(s.datum_zeit) AS Expr1
FROM ) =
(SELECT YEAR(GETDATE()) AS Expr1))
ORDER BY s.STUNDE_ID
... but this causes syntax-errors. I don't understand why this query works fine in the query editor but then gets automatically "destroyed" by the view editor. Do i have to use more statements to get the working query to run inside a view?
Thanks alot for reading.
View 1 Replies
View Related
Jul 9, 2005
I have the following code that retreives the current value of the item price. however it always rounds up. If I manually enter a return value like so:return (decimal)12.47 It returns the correct value, however if I set it with an expression like this:return (decimal)arParam[1].Value;It rounds the number up: How can I get it to not round up when insertign a value based ona expression?
public decimal GetCreditPrice(string CustomerSecurityKey)
{
try
{
System.Data.SqlClient.SqlParameter prmCrnt;
System.Data.SqlClient.SqlParameter[] arParam = new System.Data.SqlClient.SqlParameter[2];
prmCrnt = new System.Data.SqlClient.SqlParameter("@CustomerSecurityKey", SqlDbType.VarChar,25);
prmCrnt.Value = CustomerSecurityKey;
arParam[0] = prmCrnt;
prmCrnt = new System.Data.SqlClient.SqlParameter("@Price", SqlDbType.Decimal);
prmCrnt.Direction = ParameterDirection.Output;
arParam[1] = prmCrnt;
SqlHelper.ExecuteNonQuery(stConnection, CommandType.StoredProcedure, "GetCreditPrice", arParam);
return (decimal)arParam[1].Value;
}
catch(System.Exception ex)
{
throw ex;
}
}
View 1 Replies
View Related
Jun 2, 2008
I have the following statement and I want to round the final value(gbkmut.bdr_hfl)two decimal places.
begin
UPDATE gbkmut
SET gbkmut.bdr_hfl = gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate')
WHERE reknr = ' 1040'
end
View 5 Replies
View Related
Feb 4, 2008
all,
i want to round a number, for example 8.50 be 9
i use math.round
it works when it's like math.Round(8.50)
the result would be 9
but if i do this math.Round(max(Fields)) or max(math.Round(Fields))
> assume the Fields value's 8.50, the result is 8
anybody know how to solve this?
thanks!
Addin
View 7 Replies
View Related
Sep 22, 2004
I'm passing a value from an ASP.net app to an sql stored procedure which stores it in a table.
Problem is if, for example, the value is 2.81 the value is ending up as 2.08999... in my table, but if i do say 6.3 it's fine.
Ive tried having the variable in asp and the field where its stored in sql as a number of types but all with the same result.
Any ideas?
Geoff.
View 1 Replies
View Related
Jun 6, 2000
Looking for a way to round numbers to a specified number of significant digits. The ROUND function rounds to a specific decimal place but does not take into account the level of significance of the remaining numbers. (i.e.
ROUND(7.12435,2)=7.12000) The type of function I need would round the number in the following manner: SigFigRound(7.12435,3)=7.12 or
SigFigRound(7.12345,1)=7.
Any solutions?
View 1 Replies
View Related
Jul 23, 2004
I have rounding problems when editing or inserting a new record in float type fields.
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @FCost. Then I round @FCost=Round(@FCost,2).
When I try to inert this value to a new record again I'using Round(@FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
Manolis
View 2 Replies
View Related
Feb 20, 2004
I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding
SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up
Does this make sense?
View 11 Replies
View Related
Jun 11, 2008
I need to add 3% to my prices
UPDATE prices SET price = price * 1.03
But I also need to round up to the nearest 99p so for example 1.13 becomes 1.99
How can I do this, I presume I need to round up to the nearest whole number, i.e. Math.Ceiling and then -0.01 but I dont know the syntax in SQL.
Thank you
View 2 Replies
View Related
Sep 27, 2006
I need help on this query. I'm trying to have a number rounded, so I can truncate the decimal. The reason I want to do this is that it is for a planning function and I need it to round to a number that is divisible by an order minimum qty. Example: I show a need for 2611 items, but the item is only ordered in qtys of 100, so I'd need 2600 instead of 2611, because the vendor won't let me order out of qty. So, my query would take 2611 / minimum order qty (100) which would be 26.11 somehow take off the .11 then multiply back by 100, which would give me 2600.
use mas500test_app
-- UPDATE timItem
-- SET UserFld3 = 1
Select distinct I.ItemID, V.VendID, D.ShortDesc, B.Name as ItemBuyer, BV.Name as VendorBuyer,
IC.ItemClassID, PPL.PurchProdLineID, isNUll(BI.QtyOnHand,0) AS QtyOnHand,
IV.QtyOnPO, IV.QtyONSo, IV.QtyONBo, W.WhseID, ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) as Available,
IV.MaxStockQty, IV.MinStockQty, IV.MaxStockQty - IV.MinStockQty AS SafetyStock, I.UserFld6 as Rank, I.UserFld3,
-- Case
-- WHEN I.UserFld3 = 0
-- THEN '1'
-- ELSE I.UserFld3
-- END
-- as PackQty,
CASE
WHEN (IV.MaxStockQty - IV.MinStockQty) <> 0
THEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO) / (IV.MaxStockQty - IV.MinStockQty))
ELSE 0
END
AS MonthsOnHand,
CASE
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < IV.MinStockQty
THEN IV.MaxStockQty - ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) --* I.UserFld3
END
as QtyNeed, I.StdBinQty,
CASE
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < I.StdBinQty
THEN I.StdBinQty
WHEN ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) > I.StdBinQty
******This is the number I need rounded *****THEN ROUND(IV.MaxStockQty - (IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO) / I.userfld3), -2)))
END
AS OrdQty
from timwhsepurchprodln WPL
INNER JOIN TAPvENDOR v ON WPL.PrimaryVendKey = V.VendKey
INNER JOIN timPurchProdLine PPL ON WPL.PurchProdLineKey = PPL.PurchProdLinekey
inner join timInventory IV ON PPL.PurchProdLinekey = IV.PurchProdLinekey
INNER Join timBuyer B ON IV.BuyerKey = B.BuyerKey
INNER Join timBuyer BV ON V.BuyerKey = BV.BuyerKey
INNER JOIN timItem I ON I.ItemKey = IV.ItemKey
INNER JOIN timItemClass IC ON I.ItemClassKey = IC.ItemClassKey
INNER JOIN timWarehouse W ON W.WhseKey = IV.WhseKey
INNER JOIN timItemDescription D ON I.ItemKey = D.ItemKey
INNER JOIN timItemUnitOfMeas IUOM ON I.ItemKey = IUOM.ItemKey
INNER JOIN tciUnitMeasure UM ON IUOM.TargetUnitMeasKey = UM.UnitMeasKey
LEFT JOIN
(SELECT ItemKey, SUM(QtyOnHand) AS QtyOnHand
FROM timWhseBinInvt
GROUP BY ItemKey) BI ON BI.ItemKey = I.ItemKey
where IV.WhseKey = 22
and I.Status = 1
and ((IV.QtyOnPO + isNUll(BI.QtyOnHand,0) - IV.QtyOnSO - IV.QtyOnBO)) < IV.MinStockQty
order by VendID
View 18 Replies
View Related
Mar 13, 2007
I have the following sp which is appending records into my table. However the values appended are being round up eg
SC_PrimaryPupilPrice is 1.5
but when it is inserted into the sql table it is 2
The field in the sql table is numeric.
CREATE PROCEDURE spSM_AddWeeksandMealPrices
@dteWeekEnding datetime
as
INSERT INTO tblSM_Meals
(ML_Id,
ML_WeekEnding,
ML_SchoolNumber,
ML_PupilMealPrice,
ML_AdultMealPrice,
ML_SpillagesMealPrice,
ML_AdultRechargeMealPrice,
ML_ReservedMealPrice)
select convert(varchar,@dteWeekEnding ,103) + '*' + cast(SC_SchoolNumber as varchar(10)) , convert(datetime,@dteWeekEnding ,106),
SC_SchoolNumber,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryAdultPrice
from tblSM_Schools
GO
View 4 Replies
View Related
Feb 21, 2006
I have a problem...Data transformation rounds or truncate decimals!I have an ODBC source in witch is a table with float numbers (ODBC Driverpublish it as float).If I build a query form access or from excel with Query Analizer, I see alldecimal places, but when I try to insert data using DTS, float numbers willbe converted to its integer values.A "select * from table", with table ODBC table, gives integer value whenrunned from DTS to transform data from ODBC to MS-SQL Server table, andgives float values from Access or other tools.Where I can fix my problem?Thanks.Giorgio
View 1 Replies
View Related
Mar 25, 2008
When ISELECT CAST(96.58 AS DECIMAL(10 , 0)), it returns 97.When ISELECT CAST(575629 / 54 AS DECIMAL(10 , 0)), why it returns 10659? Itshould return 10660, right?What am I missing?Thanks,Faye Larson
View 1 Replies
View Related
Jul 20, 2005
Perhaps someone can settle an arguement for me ?I have a set of data that I need to group together. SQL Script below.CREATE TABLE [dbo].[CommTransactions] ([ID] [id_type] NOT NULL ,[TransactionID] [id_type] NULL ,[ClientID] [id_type] NULL ,[AccountCode] [varchar] (10) NULL ,[Amount] [float] NULL ,[CreateDateTime] [datetime] NULLFor the records I want to group the following applies.The ID is unique and distinct.The TransactionId is the same.The ClientId is the same.The AccountCode is different.The Amount will be the same.The CreateDateTime field is different by a few milliseconds.I want to create a single line showing two account codes in differentfields. i.e. Staff and Manager (where their ID is the account code).These can be entered in any order in the table mentioned.The problem I have is I need to link two records together (that's theproblem in it's most simplistic terms). However, there may beadditional records with the same TransactionId, ClientId, AccountCodeand Amount, but happened at a slightly different time. It could bedone on the same day.Now, the arguement is that we can group using the CreateDateTimefield. I argue that we can't as it will show down to the millisecondand any rounding will not always allow for a match. If we added thematching records once per day, then I can extract the date and groupon it, but if more than one group is added per day, then this wouldcause the logic to fail.So, are there any reliable methods for grouping date/time fieldsreliably if there is a small difference (I suspect not)?Is there anything I have missed ?Any help or suggestions would be appreciated.ThanksRyan
View 4 Replies
View Related
Feb 20, 2008
I have 2 record with same value A and B
A = 150.68273
B = 0.002000
1st Record A+B = 150.68473
2nd Record A+B = 150.68473
In the report column both of the result added up will show as 150.68, format code= N2
Now the diffrence is when i do the subtotal.
It gives me Diffrent value when i use
SUM(Round(Fields!ACCINT.Value + Fields!INTADV.Value,2)) = 301.36
and
SUM(Fields!ACCINT.Value + Fields!INTADV.Value) (format code= N2) = 301.37
why?
View 4 Replies
View Related
Dec 5, 2007
Hi All,
Is there a way in T-SQL using the round command to always round up regadless of the value.
For example:ROUND(normal_hours * pay_rate * 52, - 3) AS Expr3
normal_hours = 36
pay rate = 23.64
weeks in year = 52
(36 * 23.64) * 52 = 44,254.08
It rounds to 44,000. I want 45,000. Is this possible. Am I overlooking the obvious?
Thanks in Advance and Happy Holidays!!!!!!!!Adam
View 9 Replies
View Related
