SELECT Question

Question

Post reply

SELECT Question

Benedito Reynaldo de Almeida Filho

SSC Rookie

Points: 35
More actions
January 3, 2007 at 11:20 am

#122212

hi,
i have to select the total products sold by day in a period of time, but i have to present all days in the period includind those where no products were sold.
thanks,
benedito.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 1

Sounds like a homework question. You'll need to look up the left join clause.

Tom

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 2

I think you would be better off looking at GROUP BY and ALL in BOL to find your answer.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

GROUP BY ALL is being depreciated. The left join from a calendar table is the safest solution.

Benedito Reynaldo de Almeida Filho SSC Rookie Points: 35 More actions · Answer 4

ok,

sounds like a good idea. but is there another choice if i don't really have a calender table to work with ?

benedito.

Benedito Reynaldo de Almeida Filho SSC Rookie Points: 35 More actions · Answer 5

hello again,

what i need is to list all days between two given dates in a single SQL command.

thanks,

benedito.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Answer for yourself:

Where those days are stored?

_____________
Code for TallyGenerator

Benedito Reynaldo de Almeida Filho SSC Rookie Points: 35 More actions · Answer 7

sergiy,

there's no table storing the dates. i want to pass 2 dates to a SQL command and calculate them in this command.

benedito.

TS Jones Valued Member Points: 53 More actions · Answer 8

There are a few ways of doing this. One would be to create a table with all the days in it and then link from there. Another is to do the same, but use a temp table. I have also seen this done in a single select statement using complex full outer joins, but that was not very readable.

Dave Klug SSCertifiable Points: 5811 More actions · Answer 9

If you don't have a clendar table to work with, create a temp table for dates and populate a row for every date in the range. You can then left join into this.

Sergiy SSC Guru Points: 110208 More actions · Answer 10

You did not answer the question.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 11

In order to display something you need to retrieve it from somewhere.

Where those not mentioned days suppose to be taken from?

_____________
Code for TallyGenerator

Michael Meierruth SSChampion Points: 10051 More actions · Answer 12

Be more specific, please! What do you mean by 'calculate them'?

On second thought, I haven't got a clue as to what your problem is.

Benedito Reynaldo de Almeida Filho SSC Rookie Points: 35 More actions · Answer 13

that's ok, guys,

i'm solving the problem with a SP.

thanks,

levogiro.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 14

Great! And when you have finished writing the SP, publish a copy of it on this thread. Maybe then we'll finally understand the problem you are trying to solve.

What is amazing is that your initial post presents a problem quite clearly. But there are so many uncertainties and 'if this' and 'if that' and 'what do you mean by this' and 'oh yes, I forgot about that...'.

It's all about specifications and presenting a problem clearly. When it's all clear, writing the SP is the easy part.