|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Scale not correct in queryreturns 575.112, the correct value, where the first returns 580.00. declare @BeginDate Datetime declare @EndDate Datetime set @BeginDate = '20050928' set @EndDate = '20050928' DECLARE @FinalDate Datetime SET @FinalDate = dateadd(day, 1, @EndDate) /* SET NOCOUNT ON */ SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) Descr, max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, b.cost FROM Mailings m INNER JOIN AddressValid v ON m.AddressID = v.AddressID INNER JOIN MailingBatch b ON m.BatchID = b.BatchID INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = d.MailCampaignDetailID INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode INNER JOIN PostageTypes t ON b.PostageType = t.PostageType WHERE (b.BatchType = 0 OR b.BatchType = 2) AND b.DateAdded BETWEEN @BeginDate AND @FinalDate GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost select cost from mailingbatch where batchid = 264 CREATE TABLE dbo.MailingBatch ( BatchID int IDENTITY (1, 1) NOT NULL, CompID int NOT NULL, MailCampaignDetailID int NOT NULL, BatchType int NOT NULL, PostageType int NULL, Cost decimal(18, 3) NOT NULL, DateMailed datetime NULL, parameters varchar (2000) NOT NULL, dateAdded datetime NOT NULL Perhaps the GROUP BY clause is internally causing some rounding to occur?
Show quote "larzeb" <larzeb@community.nospam> wrote in message news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... > The 2 queries below return different values for b.cost. I don't understand > why. The 2nd query > returns 575.112, the correct value, where the first returns 580.00. > > declare @BeginDate Datetime > declare @EndDate Datetime > set @BeginDate = '20050928' > set @EndDate = '20050928' > > DECLARE @FinalDate Datetime > SET @FinalDate = dateadd(day, 1, @EndDate) > /* SET NOCOUNT ON */ > SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) > Descr, > max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, > b.cost > FROM Mailings m > INNER JOIN AddressValid v ON m.AddressID = v.AddressID > INNER JOIN MailingBatch b ON m.BatchID = b.BatchID > INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = > d.MailCampaignDetailID > INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID > INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode > INNER JOIN PostageTypes t ON b.PostageType = t.PostageType > WHERE (b.BatchType = 0 OR b.BatchType = 2) AND > b.DateAdded BETWEEN @BeginDate AND @FinalDate > GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost > ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost > > select cost from mailingbatch where batchid = 264 > > CREATE TABLE dbo.MailingBatch ( > BatchID int IDENTITY (1, 1) NOT NULL, > CompID int NOT NULL, > MailCampaignDetailID int NOT NULL, > BatchType int NOT NULL, > PostageType int NULL, > Cost decimal(18, 3) NOT NULL, > DateMailed datetime NULL, > parameters varchar (2000) NOT NULL, > dateAdded datetime NOT NULL Jerry,
I don't think that's it. I ran an alternative select clause: SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) Descr, max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, (select cost from mailingbatch where batchid = b.batchid) as cost without b.cost in the group by clause with the same results. Show quote On Thu, 29 Sep 2005 10:18:14 -0700, "Jerry Spivey" <jspi***@vestas-awt.com> wrote: >Perhaps the GROUP BY clause is internally causing some rounding to occur? > >"larzeb" <larzeb@community.nospam> wrote in message >news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... >> The 2 queries below return different values for b.cost. I don't understand >> why. The 2nd query >> returns 575.112, the correct value, where the first returns 580.00. >> >> declare @BeginDate Datetime >> declare @EndDate Datetime >> set @BeginDate = '20050928' >> set @EndDate = '20050928' >> >> DECLARE @FinalDate Datetime >> SET @FinalDate = dateadd(day, 1, @EndDate) >> /* SET NOCOUNT ON */ >> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) >> Descr, >> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, >> b.cost >> FROM Mailings m >> INNER JOIN AddressValid v ON m.AddressID = v.AddressID >> INNER JOIN MailingBatch b ON m.BatchID = b.BatchID >> INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = >> d.MailCampaignDetailID >> INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID >> INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode >> INNER JOIN PostageTypes t ON b.PostageType = t.PostageType >> WHERE (b.BatchType = 0 OR b.BatchType = 2) AND >> b.DateAdded BETWEEN @BeginDate AND @FinalDate >> GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost >> ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost >> >> select cost from mailingbatch where batchid = 264 >> >> CREATE TABLE dbo.MailingBatch ( >> BatchID int IDENTITY (1, 1) NOT NULL, >> CompID int NOT NULL, >> MailCampaignDetailID int NOT NULL, >> BatchType int NOT NULL, >> PostageType int NULL, >> Cost decimal(18, 3) NOT NULL, >> DateMailed datetime NULL, >> parameters varchar (2000) NOT NULL, >> dateAdded datetime NOT NULL > Hmmm...don't know the cause. What if you did an explicit CAST to the same
data type that cost currently uses? Show quote "larzeb" <larzeb@community.nospam> wrote in message news:4bdoj15mbqivbq2ugr2k4jtaurskpscb27@4ax.com... > Jerry, > > I don't think that's it. I ran an alternative select clause: > > > SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) > Descr, > max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, > (select cost from mailingbatch where batchid = b.batchid) as cost > > without b.cost in the group by clause with the same results. > > > On Thu, 29 Sep 2005 10:18:14 -0700, "Jerry Spivey" > <jspi***@vestas-awt.com> wrote: > >>Perhaps the GROUP BY clause is internally causing some rounding to occur? >> >>"larzeb" <larzeb@community.nospam> wrote in message >>news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... >>> The 2 queries below return different values for b.cost. I don't >>> understand >>> why. The 2nd query >>> returns 575.112, the correct value, where the first returns 580.00. >>> >>> declare @BeginDate Datetime >>> declare @EndDate Datetime >>> set @BeginDate = '20050928' >>> set @EndDate = '20050928' >>> >>> DECLARE @FinalDate Datetime >>> SET @FinalDate = dateadd(day, 1, @EndDate) >>> /* SET NOCOUNT ON */ >>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>> MAX(z.Descr) >>> Descr, >>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>> Campaign, >>> b.cost >>> FROM Mailings m >>> INNER JOIN AddressValid v ON m.AddressID = v.AddressID >>> INNER JOIN MailingBatch b ON m.BatchID = b.BatchID >>> INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = >>> d.MailCampaignDetailID >>> INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID >>> INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode >>> INNER JOIN PostageTypes t ON b.PostageType = t.PostageType >>> WHERE (b.BatchType = 0 OR b.BatchType = 2) AND >>> b.DateAdded BETWEEN @BeginDate AND @FinalDate >>> GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>> ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>> >>> select cost from mailingbatch where batchid = 264 >>> >>> CREATE TABLE dbo.MailingBatch ( >>> BatchID int IDENTITY (1, 1) NOT NULL, >>> CompID int NOT NULL, >>> MailCampaignDetailID int NOT NULL, >>> BatchType int NOT NULL, >>> PostageType int NULL, >>> Cost decimal(18, 3) NOT NULL, >>> DateMailed datetime NULL, >>> parameters varchar (2000) NOT NULL, >>> dateAdded datetime NOT NULL >> Jerry,
I tried that also, and it returned the rounded result. I'm very confused. SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) Descr, max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, cast((select cost from mailingbatch where batchid = b.batchid) as decimal(18,3)) as cost Show quote On Thu, 29 Sep 2005 11:46:19 -0700, "Jerry Spivey" <jspi***@vestas-awt.com> wrote: >Hmmm...don't know the cause. What if you did an explicit CAST to the same >data type that cost currently uses? > >"larzeb" <larzeb@community.nospam> wrote in message >news:4bdoj15mbqivbq2ugr2k4jtaurskpscb27@4ax.com... >> Jerry, >> >> I don't think that's it. I ran an alternative select clause: >> >> >> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) >> Descr, >> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, >> (select cost from mailingbatch where batchid = b.batchid) as cost >> >> without b.cost in the group by clause with the same results. >> >> >> On Thu, 29 Sep 2005 10:18:14 -0700, "Jerry Spivey" >> <jspi***@vestas-awt.com> wrote: >> >>>Perhaps the GROUP BY clause is internally causing some rounding to occur? >>> >>>"larzeb" <larzeb@community.nospam> wrote in message >>>news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... >>>> The 2 queries below return different values for b.cost. I don't >>>> understand >>>> why. The 2nd query >>>> returns 575.112, the correct value, where the first returns 580.00. >>>> >>>> declare @BeginDate Datetime >>>> declare @EndDate Datetime >>>> set @BeginDate = '20050928' >>>> set @EndDate = '20050928' >>>> >>>> DECLARE @FinalDate Datetime >>>> SET @FinalDate = dateadd(day, 1, @EndDate) >>>> /* SET NOCOUNT ON */ >>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>> MAX(z.Descr) >>>> Descr, >>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>> Campaign, >>>> b.cost >>>> FROM Mailings m >>>> INNER JOIN AddressValid v ON m.AddressID = v.AddressID >>>> INNER JOIN MailingBatch b ON m.BatchID = b.BatchID >>>> INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = >>>> d.MailCampaignDetailID >>>> INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID >>>> INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode >>>> INNER JOIN PostageTypes t ON b.PostageType = t.PostageType >>>> WHERE (b.BatchType = 0 OR b.BatchType = 2) AND >>>> b.DateAdded BETWEEN @BeginDate AND @FinalDate >>>> GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>> ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>> >>>> select cost from mailingbatch where batchid = 264 >>>> >>>> CREATE TABLE dbo.MailingBatch ( >>>> BatchID int IDENTITY (1, 1) NOT NULL, >>>> CompID int NOT NULL, >>>> MailCampaignDetailID int NOT NULL, >>>> BatchType int NOT NULL, >>>> PostageType int NULL, >>>> Cost decimal(18, 3) NOT NULL, >>>> DateMailed datetime NULL, >>>> parameters varchar (2000) NOT NULL, >>>> dateAdded datetime NOT NULL >>> > Could cast and nvarchar if this is strictly for ad-hoc reporting.
Show quote "larzeb" <larzeb@community.nospam> wrote in message news:sseoj19ft9eko1j05lqfl3ttql13a9073v@4ax.com... > Jerry, > > I tried that also, and it returned the rounded result. I'm very confused. > > SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) > Descr, > max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, > cast((select cost from mailingbatch where batchid = b.batchid) as > decimal(18,3)) as cost > > > > On Thu, 29 Sep 2005 11:46:19 -0700, "Jerry Spivey" > <jspi***@vestas-awt.com> wrote: > >>Hmmm...don't know the cause. What if you did an explicit CAST to the same >>data type that cost currently uses? >> >>"larzeb" <larzeb@community.nospam> wrote in message >>news:4bdoj15mbqivbq2ugr2k4jtaurskpscb27@4ax.com... >>> Jerry, >>> >>> I don't think that's it. I ran an alternative select clause: >>> >>> >>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>> MAX(z.Descr) >>> Descr, >>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>> Campaign, >>> (select cost from mailingbatch where batchid = b.batchid) as cost >>> >>> without b.cost in the group by clause with the same results. >>> >>> >>> On Thu, 29 Sep 2005 10:18:14 -0700, "Jerry Spivey" >>> <jspi***@vestas-awt.com> wrote: >>> >>>>Perhaps the GROUP BY clause is internally causing some rounding to >>>>occur? >>>> >>>>"larzeb" <larzeb@community.nospam> wrote in message >>>>news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... >>>>> The 2 queries below return different values for b.cost. I don't >>>>> understand >>>>> why. The 2nd query >>>>> returns 575.112, the correct value, where the first returns 580.00. >>>>> >>>>> declare @BeginDate Datetime >>>>> declare @EndDate Datetime >>>>> set @BeginDate = '20050928' >>>>> set @EndDate = '20050928' >>>>> >>>>> DECLARE @FinalDate Datetime >>>>> SET @FinalDate = dateadd(day, 1, @EndDate) >>>>> /* SET NOCOUNT ON */ >>>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>>> MAX(z.Descr) >>>>> Descr, >>>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>>> Campaign, >>>>> b.cost >>>>> FROM Mailings m >>>>> INNER JOIN AddressValid v ON m.AddressID = v.AddressID >>>>> INNER JOIN MailingBatch b ON m.BatchID = b.BatchID >>>>> INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = >>>>> d.MailCampaignDetailID >>>>> INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID >>>>> INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode >>>>> INNER JOIN PostageTypes t ON b.PostageType = t.PostageType >>>>> WHERE (b.BatchType = 0 OR b.BatchType = 2) AND >>>>> b.DateAdded BETWEEN @BeginDate AND @FinalDate >>>>> GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>> ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>> >>>>> select cost from mailingbatch where batchid = 264 >>>>> >>>>> CREATE TABLE dbo.MailingBatch ( >>>>> BatchID int IDENTITY (1, 1) NOT NULL, >>>>> CompID int NOT NULL, >>>>> MailCampaignDetailID int NOT NULL, >>>>> BatchType int NOT NULL, >>>>> PostageType int NULL, >>>>> Cost decimal(18, 3) NOT NULL, >>>>> DateMailed datetime NULL, >>>>> parameters varchar (2000) NOT NULL, >>>>> dateAdded datetime NOT NULL >>>> >> Jerry,
It is for reporting only. I don't know what you mean by 'cast and nvarchar'. Can you help? Thanks Show quote On Thu, 29 Sep 2005 12:14:12 -0700, "Jerry Spivey" <jspi***@vestas-awt.com> wrote: >Could cast and nvarchar if this is strictly for ad-hoc reporting. >"larzeb" <larzeb@community.nospam> wrote in message >news:sseoj19ft9eko1j05lqfl3ttql13a9073v@4ax.com... >> Jerry, >> >> I tried that also, and it returned the rounded result. I'm very confused. >> >> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, MAX(z.Descr) >> Descr, >> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as Campaign, >> cast((select cost from mailingbatch where batchid = b.batchid) as >> decimal(18,3)) as cost >> >> >> >> On Thu, 29 Sep 2005 11:46:19 -0700, "Jerry Spivey" >> <jspi***@vestas-awt.com> wrote: >> >>>Hmmm...don't know the cause. What if you did an explicit CAST to the same >>>data type that cost currently uses? >>> >>>"larzeb" <larzeb@community.nospam> wrote in message >>>news:4bdoj15mbqivbq2ugr2k4jtaurskpscb27@4ax.com... >>>> Jerry, >>>> >>>> I don't think that's it. I ran an alternative select clause: >>>> >>>> >>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>> MAX(z.Descr) >>>> Descr, >>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>> Campaign, >>>> (select cost from mailingbatch where batchid = b.batchid) as cost >>>> >>>> without b.cost in the group by clause with the same results. >>>> >>>> >>>> On Thu, 29 Sep 2005 10:18:14 -0700, "Jerry Spivey" >>>> <jspi***@vestas-awt.com> wrote: >>>> >>>>>Perhaps the GROUP BY clause is internally causing some rounding to >>>>>occur? >>>>> >>>>>"larzeb" <larzeb@community.nospam> wrote in message >>>>>news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... >>>>>> The 2 queries below return different values for b.cost. I don't >>>>>> understand >>>>>> why. The 2nd query >>>>>> returns 575.112, the correct value, where the first returns 580.00. >>>>>> >>>>>> declare @BeginDate Datetime >>>>>> declare @EndDate Datetime >>>>>> set @BeginDate = '20050928' >>>>>> set @EndDate = '20050928' >>>>>> >>>>>> DECLARE @FinalDate Datetime >>>>>> SET @FinalDate = dateadd(day, 1, @EndDate) >>>>>> /* SET NOCOUNT ON */ >>>>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>>>> MAX(z.Descr) >>>>>> Descr, >>>>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>>>> Campaign, >>>>>> b.cost >>>>>> FROM Mailings m >>>>>> INNER JOIN AddressValid v ON m.AddressID = v.AddressID >>>>>> INNER JOIN MailingBatch b ON m.BatchID = b.BatchID >>>>>> INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = >>>>>> d.MailCampaignDetailID >>>>>> INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID >>>>>> INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode >>>>>> INNER JOIN PostageTypes t ON b.PostageType = t.PostageType >>>>>> WHERE (b.BatchType = 0 OR b.BatchType = 2) AND >>>>>> b.DateAdded BETWEEN @BeginDate AND @FinalDate >>>>>> GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>>> ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>>> >>>>>> select cost from mailingbatch where batchid = 264 >>>>>> >>>>>> CREATE TABLE dbo.MailingBatch ( >>>>>> BatchID int IDENTITY (1, 1) NOT NULL, >>>>>> CompID int NOT NULL, >>>>>> MailCampaignDetailID int NOT NULL, >>>>>> BatchType int NOT NULL, >>>>>> PostageType int NULL, >>>>>> Cost decimal(18, 3) NOT NULL, >>>>>> DateMailed datetime NULL, >>>>>> parameters varchar (2000) NOT NULL, >>>>>> dateAdded datetime NOT NULL >>>>> >>> > or varchar - to make it a string. CAST(cost AS varchar(25)). Not sure if
will work but worth a try. HTH Jerry Show quote "larzeb" <larzeb@community.nospam> wrote in message news:jrfoj11bgvoudm5vp3vo9oglodn4smtp1k@4ax.com... > Jerry, > > It is for reporting only. I don't know what you mean by 'cast and > nvarchar'. Can you help? > > Thanks > On Thu, 29 Sep 2005 12:14:12 -0700, "Jerry Spivey" > <jspi***@vestas-awt.com> wrote: > >>Could cast and nvarchar if this is strictly for ad-hoc reporting. >>"larzeb" <larzeb@community.nospam> wrote in message >>news:sseoj19ft9eko1j05lqfl3ttql13a9073v@4ax.com... >>> Jerry, >>> >>> I tried that also, and it returned the rounded result. I'm very >>> confused. >>> >>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>> MAX(z.Descr) >>> Descr, >>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>> Campaign, >>> cast((select cost from mailingbatch where batchid = b.batchid) as >>> decimal(18,3)) as cost >>> >>> >>> >>> On Thu, 29 Sep 2005 11:46:19 -0700, "Jerry Spivey" >>> <jspi***@vestas-awt.com> wrote: >>> >>>>Hmmm...don't know the cause. What if you did an explicit CAST to the >>>>same >>>>data type that cost currently uses? >>>> >>>>"larzeb" <larzeb@community.nospam> wrote in message >>>>news:4bdoj15mbqivbq2ugr2k4jtaurskpscb27@4ax.com... >>>>> Jerry, >>>>> >>>>> I don't think that's it. I ran an alternative select clause: >>>>> >>>>> >>>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>>> MAX(z.Descr) >>>>> Descr, >>>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>>> Campaign, >>>>> (select cost from mailingbatch where batchid = b.batchid) as cost >>>>> >>>>> without b.cost in the group by clause with the same results. >>>>> >>>>> >>>>> On Thu, 29 Sep 2005 10:18:14 -0700, "Jerry Spivey" >>>>> <jspi***@vestas-awt.com> wrote: >>>>> >>>>>>Perhaps the GROUP BY clause is internally causing some rounding to >>>>>>occur? >>>>>> >>>>>>"larzeb" <larzeb@community.nospam> wrote in message >>>>>>news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... >>>>>>> The 2 queries below return different values for b.cost. I don't >>>>>>> understand >>>>>>> why. The 2nd query >>>>>>> returns 575.112, the correct value, where the first returns 580.00. >>>>>>> >>>>>>> declare @BeginDate Datetime >>>>>>> declare @EndDate Datetime >>>>>>> set @BeginDate = '20050928' >>>>>>> set @EndDate = '20050928' >>>>>>> >>>>>>> DECLARE @FinalDate Datetime >>>>>>> SET @FinalDate = dateadd(day, 1, @EndDate) >>>>>>> /* SET NOCOUNT ON */ >>>>>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>>>>> MAX(z.Descr) >>>>>>> Descr, >>>>>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>>>>> Campaign, >>>>>>> b.cost >>>>>>> FROM Mailings m >>>>>>> INNER JOIN AddressValid v ON m.AddressID = v.AddressID >>>>>>> INNER JOIN MailingBatch b ON m.BatchID = b.BatchID >>>>>>> INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = >>>>>>> d.MailCampaignDetailID >>>>>>> INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID >>>>>>> INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode >>>>>>> INNER JOIN PostageTypes t ON b.PostageType = t.PostageType >>>>>>> WHERE (b.BatchType = 0 OR b.BatchType = 2) AND >>>>>>> b.DateAdded BETWEEN @BeginDate AND @FinalDate >>>>>>> GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>>>> ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>>>> >>>>>>> select cost from mailingbatch where batchid = 264 >>>>>>> >>>>>>> CREATE TABLE dbo.MailingBatch ( >>>>>>> BatchID int IDENTITY (1, 1) NOT NULL, >>>>>>> CompID int NOT NULL, >>>>>>> MailCampaignDetailID int NOT NULL, >>>>>>> BatchType int NOT NULL, >>>>>>> PostageType int NULL, >>>>>>> Cost decimal(18, 3) NOT NULL, >>>>>>> DateMailed datetime NULL, >>>>>>> parameters varchar (2000) NOT NULL, >>>>>>> dateAdded datetime NOT NULL >>>>>> >>>> >> When I cast it to varchar(18), the result was 580.00.
Show quote On Thu, 29 Sep 2005 12:35:01 -0700, "Jerry Spivey" <jspi***@vestas-awt.com> wrote: >or varchar - to make it a string. CAST(cost AS varchar(25)). Not sure if >will work but worth a try. > >HTH > >Jerry >"larzeb" <larzeb@community.nospam> wrote in message >news:jrfoj11bgvoudm5vp3vo9oglodn4smtp1k@4ax.com... >> Jerry, >> >> It is for reporting only. I don't know what you mean by 'cast and >> nvarchar'. Can you help? >> >> Thanks >> On Thu, 29 Sep 2005 12:14:12 -0700, "Jerry Spivey" >> <jspi***@vestas-awt.com> wrote: >> >>>Could cast and nvarchar if this is strictly for ad-hoc reporting. >>>"larzeb" <larzeb@community.nospam> wrote in message >>>news:sseoj19ft9eko1j05lqfl3ttql13a9073v@4ax.com... >>>> Jerry, >>>> >>>> I tried that also, and it returned the rounded result. I'm very >>>> confused. >>>> >>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>> MAX(z.Descr) >>>> Descr, >>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>> Campaign, >>>> cast((select cost from mailingbatch where batchid = b.batchid) as >>>> decimal(18,3)) as cost >>>> >>>> >>>> >>>> On Thu, 29 Sep 2005 11:46:19 -0700, "Jerry Spivey" >>>> <jspi***@vestas-awt.com> wrote: >>>> >>>>>Hmmm...don't know the cause. What if you did an explicit CAST to the >>>>>same >>>>>data type that cost currently uses? >>>>> >>>>>"larzeb" <larzeb@community.nospam> wrote in message >>>>>news:4bdoj15mbqivbq2ugr2k4jtaurskpscb27@4ax.com... >>>>>> Jerry, >>>>>> >>>>>> I don't think that's it. I ran an alternative select clause: >>>>>> >>>>>> >>>>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>>>> MAX(z.Descr) >>>>>> Descr, >>>>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>>>> Campaign, >>>>>> (select cost from mailingbatch where batchid = b.batchid) as cost >>>>>> >>>>>> without b.cost in the group by clause with the same results. >>>>>> >>>>>> >>>>>> On Thu, 29 Sep 2005 10:18:14 -0700, "Jerry Spivey" >>>>>> <jspi***@vestas-awt.com> wrote: >>>>>> >>>>>>>Perhaps the GROUP BY clause is internally causing some rounding to >>>>>>>occur? >>>>>>> >>>>>>>"larzeb" <larzeb@community.nospam> wrote in message >>>>>>>news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... >>>>>>>> The 2 queries below return different values for b.cost. I don't >>>>>>>> understand >>>>>>>> why. The 2nd query >>>>>>>> returns 575.112, the correct value, where the first returns 580.00. >>>>>>>> >>>>>>>> declare @BeginDate Datetime >>>>>>>> declare @EndDate Datetime >>>>>>>> set @BeginDate = '20050928' >>>>>>>> set @EndDate = '20050928' >>>>>>>> >>>>>>>> DECLARE @FinalDate Datetime >>>>>>>> SET @FinalDate = dateadd(day, 1, @EndDate) >>>>>>>> /* SET NOCOUNT ON */ >>>>>>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>>>>>> MAX(z.Descr) >>>>>>>> Descr, >>>>>>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>>>>>> Campaign, >>>>>>>> b.cost >>>>>>>> FROM Mailings m >>>>>>>> INNER JOIN AddressValid v ON m.AddressID = v.AddressID >>>>>>>> INNER JOIN MailingBatch b ON m.BatchID = b.BatchID >>>>>>>> INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = >>>>>>>> d.MailCampaignDetailID >>>>>>>> INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID >>>>>>>> INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode >>>>>>>> INNER JOIN PostageTypes t ON b.PostageType = t.PostageType >>>>>>>> WHERE (b.BatchType = 0 OR b.BatchType = 2) AND >>>>>>>> b.DateAdded BETWEEN @BeginDate AND @FinalDate >>>>>>>> GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>>>>> ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>>>>> >>>>>>>> select cost from mailingbatch where batchid = 264 >>>>>>>> >>>>>>>> CREATE TABLE dbo.MailingBatch ( >>>>>>>> BatchID int IDENTITY (1, 1) NOT NULL, >>>>>>>> CompID int NOT NULL, >>>>>>>> MailCampaignDetailID int NOT NULL, >>>>>>>> BatchType int NOT NULL, >>>>>>>> PostageType int NULL, >>>>>>>> Cost decimal(18, 3) NOT NULL, >>>>>>>> DateMailed datetime NULL, >>>>>>>> parameters varchar (2000) NOT NULL, >>>>>>>> dateAdded datetime NOT NULL >>>>>>> >>>>> >>> > Jerry,
Please forgive me. I'm afraid I had you and myself chasing the wrong thing. It is working properly. One of the INNER JOINs removed data which I assumed was there. The 580.00 and 575.112 came from two different results. I am sorry for your trouble but do appreciate your responses. It made me dig deeper. Show quote On Thu, 29 Sep 2005 12:35:01 -0700, "Jerry Spivey" <jspi***@vestas-awt.com> wrote: >or varchar - to make it a string. CAST(cost AS varchar(25)). Not sure if >will work but worth a try. > >HTH > >Jerry >"larzeb" <larzeb@community.nospam> wrote in message >news:jrfoj11bgvoudm5vp3vo9oglodn4smtp1k@4ax.com... >> Jerry, >> >> It is for reporting only. I don't know what you mean by 'cast and >> nvarchar'. Can you help? >> >> Thanks >> On Thu, 29 Sep 2005 12:14:12 -0700, "Jerry Spivey" >> <jspi***@vestas-awt.com> wrote: >> >>>Could cast and nvarchar if this is strictly for ad-hoc reporting. >>>"larzeb" <larzeb@community.nospam> wrote in message >>>news:sseoj19ft9eko1j05lqfl3ttql13a9073v@4ax.com... >>>> Jerry, >>>> >>>> I tried that also, and it returned the rounded result. I'm very >>>> confused. >>>> >>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>> MAX(z.Descr) >>>> Descr, >>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>> Campaign, >>>> cast((select cost from mailingbatch where batchid = b.batchid) as >>>> decimal(18,3)) as cost >>>> >>>> >>>> >>>> On Thu, 29 Sep 2005 11:46:19 -0700, "Jerry Spivey" >>>> <jspi***@vestas-awt.com> wrote: >>>> >>>>>Hmmm...don't know the cause. What if you did an explicit CAST to the >>>>>same >>>>>data type that cost currently uses? >>>>> >>>>>"larzeb" <larzeb@community.nospam> wrote in message >>>>>news:4bdoj15mbqivbq2ugr2k4jtaurskpscb27@4ax.com... >>>>>> Jerry, >>>>>> >>>>>> I don't think that's it. I ran an alternative select clause: >>>>>> >>>>>> >>>>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>>>> MAX(z.Descr) >>>>>> Descr, >>>>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>>>> Campaign, >>>>>> (select cost from mailingbatch where batchid = b.batchid) as cost >>>>>> >>>>>> without b.cost in the group by clause with the same results. >>>>>> >>>>>> >>>>>> On Thu, 29 Sep 2005 10:18:14 -0700, "Jerry Spivey" >>>>>> <jspi***@vestas-awt.com> wrote: >>>>>> >>>>>>>Perhaps the GROUP BY clause is internally causing some rounding to >>>>>>>occur? >>>>>>> >>>>>>>"larzeb" <larzeb@community.nospam> wrote in message >>>>>>>news:1c7oj1dt90m195i6e4rgis1ctgsnj5tl58@4ax.com... >>>>>>>> The 2 queries below return different values for b.cost. I don't >>>>>>>> understand >>>>>>>> why. The 2nd query >>>>>>>> returns 575.112, the correct value, where the first returns 580.00. >>>>>>>> >>>>>>>> declare @BeginDate Datetime >>>>>>>> declare @EndDate Datetime >>>>>>>> set @BeginDate = '20050928' >>>>>>>> set @EndDate = '20050928' >>>>>>>> >>>>>>>> DECLARE @FinalDate Datetime >>>>>>>> SET @FinalDate = dateadd(day, 1, @EndDate) >>>>>>>> /* SET NOCOUNT ON */ >>>>>>>> SELECT b.DateAdded, b.BatchID, v.zip5 Zip, count(zip5) Count, >>>>>>>> MAX(z.Descr) >>>>>>>> Descr, >>>>>>>> max(t.Descr) PostageType, max(c.Descr) + ' - ' + max(d.Descr) as >>>>>>>> Campaign, >>>>>>>> b.cost >>>>>>>> FROM Mailings m >>>>>>>> INNER JOIN AddressValid v ON m.AddressID = v.AddressID >>>>>>>> INNER JOIN MailingBatch b ON m.BatchID = b.BatchID >>>>>>>> INNER JOIN MailCampaignDetail d ON b.MailCampaignDetailID = >>>>>>>> d.MailCampaignDetailID >>>>>>>> INNER JOIN Mailcampaign c ON d.MailCampaignID = c.MailCampaignID >>>>>>>> INNER JOIN ZipCode z ON v.Zip5 = z.ZipCode >>>>>>>> INNER JOIN PostageTypes t ON b.PostageType = t.PostageType >>>>>>>> WHERE (b.BatchType = 0 OR b.BatchType = 2) AND >>>>>>>> b.DateAdded BETWEEN @BeginDate AND @FinalDate >>>>>>>> GROUP BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>>>>> ORDER BY b.DateAdded, b.BatchID, v.Zip5, b.cost >>>>>>>> >>>>>>>> select cost from mailingbatch where batchid = 264 >>>>>>>> >>>>>>>> CREATE TABLE dbo.MailingBatch ( >>>>>>>> BatchID int IDENTITY (1, 1) NOT NULL, >>>>>>>> CompID int NOT NULL, >>>>>>>> MailCampaignDetailID int NOT NULL, >>>>>>>> BatchType int NOT NULL, >>>>>>>> PostageType int NULL, >>>>>>>> Cost decimal(18, 3) NOT NULL, >>>>>>>> DateMailed datetime NULL, >>>>>>>> parameters varchar (2000) NOT NULL, >>>>>>>> dateAdded datetime NOT NULL >>>>>>> >>>>> >>> >  |
|||||||||||||||||||||||
Other interesting topics