SQL help please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL help please

Author

Topic

Reggiee
Starting Member

6 Posts

Posted - 2009-07-22 : 05:46:43

Hi All

Im running some SQL (and let me say here im by no means an expert - and thats being polite !!!!)

Now in theory I thought the SQL worked but it only works when there is actually a number in the field im running the query against

Now usually this field would be populated with numbers but occasionally it can have an empty entry ie nothing there (note it is nothing and not a 0 for example)

My problem is that when i run the query i dont know how to get it to return say 0 when the field is actually empty - I guess this is the age old problem of how do i return some data when theres nothing there

The query is below which i hope makes sense

SELECT tf.Site, s.StoreName, tf.Date, tf.ECR, SUM(tsf.Value) AS [Sales Total], tf.GrandTotal AS [Gross Total], tf.[Discount%] AS [Staff Discount], SUM(tsf.Value)
- tf.GrandTotal + tf.[Discount%] AS [Sales-GT+Disc]
FROM Stage.TransFact AS tf INNER JOIN
Stage.TempSalesFact AS tsf ON tf.Date = tsf.Dated AND tf.Site = tsf.Site AND tf.ECR = tsf.ECR INNER JOIN
DimStores AS s ON tsf.Site = s.StoreNumber
WHERE (tf.Date = CONVERT(datetime, CONVERT(varchar(10), DATEADD(dd, - 1, GETDATE()), 103), 103))
GROUP BY tf.Site, s.StoreName, tf.Date, tf.ECR, tf.GrandTotal, tf.[Discount%]
HAVING (SUM(tsf.Value) - tf.GrandTotal + tf.[Discount%] <> 0)
ORDER BY tf.Site, tf.ECR

the where clause is to only return yesterdays data but its the having bit where im struggling where im trying to say only return data if the formula does not give 0 as the answer ie to us thats an error so only show the errors

i think that wherever theres a number in both the SUM(tsf.Value) AS [Sales Total] and tf.GrandTotal AS [Gross Total] then it works but it doesnt seem to return anything at all if either field is empty in the DB so maybe the SQL (and i dont know how) needs to return a 0 wherever the field is empty ???

All help appreciated and having only just joined this forum i hope this is in the right forum

Thanks All

Reggiee

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-22 : 05:53:43

table structure
sample data
expected output in relation to sample data
would be great!

If you say empty - do you mean NULL-value?

No, you're never too old to Yak'n'Roll if you're too young to die.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-22 : 07:38:15

If you want to check if the Discount% is empty and not return any rows if it is, but return the rest of the rows, then just add:

AND [Discount%] is not null

or you can use:

AND isnull([Discount%],0) <> 0

Reggiee
Starting Member

6 Posts

Posted - 2009-07-22 : 09:58:05

Thanks so far guys and sorry if not enough info

OK table structure is as follows

Stage.TempSalesFact columns are dated, site, ECR, product, value, quantity,

stage.TransFact columns are date, site, ECR (key fields) , discount%, grandtotal,

dimstores columns storekey (key field), storenumber, storename

i think the problem is as follows:

im trying to create a formula that compares grandtotal from stage.TransFact with the sum of the value column from Stage.TempSalesFact ie sum(value)

so in simple terms my formula is sum(value) - grandtotal ........... for the time being we can ignore discounts etc etc if that helps

however i cant be sure that there will always be a value in either the grandtotal field or the sum(value) field for the chosen site ie site column from Stage.TransFact

so if there are values the formula works ie SUM(tsf.Value) - tf.GrandTotal and my having clause of SUM(tsf.Value) - tf.GrandTotal <> 0 ie one field is bigger than the other works too

however if either the grandtotal or sum(value) values are empty (sorry not sure difference between empty and null etc but to me a value might be 123.43 or empty ie no value in that field) then my sql doesnt work at all in that the sql doesnt seem to return anything at all

eg a successful line might read

Site Store Name Date Till Sales Total Gross Total Staff Discount Sales - GT - Disc
566 Stranraer 21-Jul-09 103 £285.36 £2115.99 £0.00 (£1830.63)

sorry about the formatting so in this instance it works as both figures im after (again ignore discounts for now) have a value

but if for example for the same site etc the sales total was empty then it just returns nothing so i guess what i need is that whenever the sql runs and it finds an empty cell ??? then it returns a 0 so that the formula will work

crikes its hard explaining this when not face to face lol

hope this is helping and you can help some more

Cheers Guys

Thanks All

Reggiee

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-22 : 10:04:54

Always put isnull() around parts of your formular.
If one part returns NULL then the complete result is always NULL.
Instead of: sum(bla) + (sum(blub) do: isnull(sum(bla),0.0) + isnull(sum(blub),0.0) for example

No, you're never too old to Yak'n'Roll if you're too young to die.

Reggiee
Starting Member

6 Posts

Posted - 2009-07-23 : 06:15:38

Sorry guys its more complicated than this - whats actually happening is that there is no data at all returned rather than bits of the data - for example see below for a much more simple query

SELECT [Date]
,[Site]
,[ECR]
,[GrandTotal]
FROM [CFVoyagerOLAP].[Stage].[TransFact]
where Date='2009-06-28'

returns

Date Site ECR GrandTotal
2009-06-28 3 102 53.78
2009-06-28 3 103 235.73

however the issue is this there is an ECR 101 but for example that ECR wasnt used that day so no data is returned at all rather than some data but with just say the GrandTotal bit missing

so what i need to do is insert all the relevant data as if it had been used but the GrandTotal was 0.00 - see below

Date Site ECR GrandTotal
2009-06-28 3 101 0.00
2009-06-28 3 102 53.78
2009-06-28 3 103 235.73

now there is another table which can be linked to this table which tells me how many ECR's there are for that store so somehow i need to run the original query where we HAVE data and then do something like insert the missing data where we have nothing for say ECR 101 in this example

the table that shows the list of ECR's is below

SELECT [Storenumber]
,[ECR]
,[LastReportTaken]
,[Inactive]
FROM [CFVoyagerOLAP].[dbo].[ActiveSiteEcr]

Storenumber ECR LastReportTaken Inactive
003 101 2009-07-22 0
003 102 2009-07-22 0
003 103 2009-07-22 0
003 104 2009-07-22 0
003 105 2009-07-22 0

the site 3 and the storenumber will be the links we need and only return the 0.00 where the inactive data is 0

Hope this is clear (as mud) and you can help here

Thanks All

Reggiee

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-23 : 06:24:55

Use ActiveSiteEcr and a left join. so:


select 
 TF.[Date]
,TF.Site
,TF.ECR
,isnull(TF.GrandTotal,0) as GrandTotal
FROM CFVoyagerOLAP.dbo.ActiveSiteEcr A
LEFT JOIN CFVoyagerOLAP.Stage.TransFact TF
ON TF.Site = A.Storenumber
AND TF.ECR = A.ECR
where TF.Date='2009-06-28'
and A.Inactive = 0

Reggiee
Starting Member

6 Posts

Posted - 2009-07-23 : 09:55:30

Thanks RickD but its still only returning ECR's with data - see below

Date Site ECR GrandTotal
2009-06-28 3 102 53.78
2009-06-28 3 103 235.73
2009-06-28 4 102 156.29
2009-06-28 4 103 95.96
2009-06-28 4 104 95.68

Site 3 for example has ECR's 101, 104 and 105 in addition to the above (all active) where Id have hoped to see 0.00 values too

Any clues ?

Thanks All

Reggiee

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-23 : 15:13:59

Change the order of joins..try this..

SELECT TF.[Date], 
       TF.Site, 
       TF.ECR, 
       isnull(TF.GrandTotal,0) AS GrandTotal 
FROM   CFVoyagerOLAP.Stage.TransFact TF 
       LEFT JOIN CFVoyagerOLAP.dbo.ActiveSiteEcr A 
         ON TF.Site = A.Storenumber 
            AND TF.ECR = A.ECR 
WHERE  TF.DATE = '2009-06-28' 
       AND A.Inactive = 0

Reggiee
Starting Member

6 Posts

Posted - 2009-07-24 : 04:05:40

Thanks vijayisonly

Still no luck

I think I see where the problem may be in that there is NO data at all for say ECR 101 on that day in the CFVoyagerOLAP.Stage.TransFact table rather than just missing data

So i guess what needs to happen is to (somehow ???) insert the missing data where there is none

ie the current query says give me a 0.00 read for the GrandTotal where its null - however there is no data there at all on that day for that ECR so is it possible for the query to do something like if there is no data there then insert all the fields ie date, site, ECR etc and a GrandTotal of 0.00 ???

Sorry if this is impossible but the one constant factor we do have is that the CFVoyagerOLAP.dbo.ActiveSiteEcr table always tells us how many ECR's there are at any one site so for example we know that site 3 may have 5 tills but we only have data for 2 till so we need to force in 3 entries for the missing ECR's - if this is possible ?

Thanks as always

Thanks All

Reggiee

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-24 : 05:41:21

But if you have data in ActiveSiteEcr, a left join (My original query) should return the rows, even if they don't exist in TransFact?!?

Just seen the problem.. doh..

Try this:


select 
 isnull(TF.[Date],getdate()) as [Date]
,A.StoreNumber as Site
,A.ECR
,isnull(TF.GrandTotal,0) as GrandTotal
FROM CFVoyagerOLAP.dbo.ActiveSiteEcr A
LEFT JOIN CFVoyagerOLAP.Stage.TransFact TF
ON TF.Site = A.Storenumber
AND TF.ECR = A.ECR
where TF.Date='2009-06-28'
and A.Inactive = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources