Day Query for Charts

[stlrain95]

I am trying to create a chart based on the 2 pieces of data.

I have a standard capacity of 15000 and fluxuating counts. I want this sorted by day. Here is my query...but doesn't work right??

SELECT Sum((Day([IMP]))) AS [Impression Count], 15000 AS Capcity, MASTER.Cust_Date
FROM MASTER
GROUP BY 15000, MASTER.Cust_Date;


What am I missing with the IMP side?

 

[Blorf]

IMP is a date field yes? And the day function returns the day of the month of that date, so summing that confuses me.

Can you give some more info on what you are doing?

Thanks,
ChaZ

 

[stlrain95]

Okay....looking at a pie chart or similar.

IMP = Impression Count
Capacity = 15000 is total capacity each day

If I have 3 orders each with their own impression count for 1 day...need them grouped and totaled so that I can compare to Capacity.

does that make more sense?

 

[GingerR]

so would your pie look like this:

a big fat circle
One slice = i.e. 899 (total of the IMPs for that day)
One slice = 601 (1500-899)

? is that it?

if so, try this:

SELECT Sum((Day([IMP]))) AS [Impression Count], 15000-Sum((Day([IMP]))) AS [Remaining Count], MASTER.Cust_Date
FROM MASTER
GROUP BY MASTER.Cust_Date;

hope that helps--g

 

[stlrain95]

It is really weird....
I have 6500 as a sum for the impression count...but when I run the query...it comes up with 48??

What could be causing this issue??

 

[GingerR]

what's your sql now? please post it.
please give sample data that's causing your problem.
does it work with some data but not others, or not working in the same bad way for all data?

thanks.

g

 

[stlrain95]

SQL is the same as example above.
My data is normal.

I am consistent with 15000
And the IMP is across the board.

IMP = 2000 & 4500 (same date)
So the sum for 1 day should be 6500....but it is coming out 48??

Remaining count is fine....but based on the 48

 

[GingerR]

get rid of that whole day() thing. Not sure why you had that in there anyhow?

SELECT MASTER.Cust_Date, Sum(MASTER.[Imp]) AS [Impression Count], 15000-Sum([IMP]) AS [Remaining Count]
FROM MASTER
GROUP BY MASTER.Cust_Date;

 

[stlrain95]

Sorry, but I need the day in there because I am summing IMP by Day....I have dates in there.

So what I need is this.

11/11/04 2000
11/11/04 4500

Total Available 15000 11/11/04

Sum:
6500 11/11/04
8500 11/11/04 Available.

Hope that helps more.

 

[GingerR]

just try it.
The way you had the Day() function is there was wrong. You're converting a number (IMP) into a day. You saw how it was wrong--it gave you 48. See Blorf's first post way up top...s/he says the same thing.

Have you tried what I wrote? GROUP BY Cust_Date is what groups by the date.

Using my code, I get:

INPUT:
1/1/04 4500
1/1/04 2000

OUTPUT:
Cust_Date Impression Count Remaining Count
1/1/2004 6500 8500

which is what you asked for.

 

[stlrain95]

Greatness!! Works.
thank you,