Grouping by Dates

[RITE1]

I have a table with many transactions in it and I want to group the data by different sets of dates - the table looks like:

itemlookup code quanitity datetransfered
xxxxxxxxx xx x/xx/xxxx xx:xx:xx PM/AM
yyyyyyyyy xx x/xx/xxxx xx:xx:xx PM/AM

note: quanitity is on a per transaction basis, could have multiple transaction for the same item in the same day.

I want


itemlookup code quanitity datetransfered
xxxxxxxxx (total q for that day) (that day)
yyyyyyyyy (total q for that day) (that day)
xxxxxxxxx (total q for the next day) (next day)
yyyyyyyyy (total q for the next day) (next day)
so on and so forth

Thanks for any help!

 

[Extentec]

If I understand what you are trying to do try something like:

SELECT itemlookup_code, quanitity, datetransfered FROM table ORDER BY datetransfered DESC, GROUP BY datetransfered

http://www.onti.net

 

[vidru]

If datetransferred has a time stamp, try this:

SELECT itemlookup_code, Quanitity = SUM(Quantity), 
       DateTransferred = CONVERT(SMALLDATETIME, CONVERT(CHAR(10), datetransferred, 101))
FROM TableName 
GROUP BY itemlookup_code, CONVERT(SMALLDATETIME, CONVERT(CHAR(10), datetransferred, 101))
ORDER BY datetransferred

-dave

 

[RITE1]

vidru - that was exactly what I was looking for - awesome.

Can you explain the code so that I can manipulate it to do the same for months or certain times of the day, etc. Thanks!!

 

[vidru]

Look up the CAST and CONVERT functions in Books Online. That'll show you many different ways/formats for converting date-time and fields.

In this particular case, there are two datatype conversions.
First, it converts the datetime to a 10 character string in the format 'mm/dd/yy'. Then, it converts the resulting string to a SMALLDATETIME. When the original date was converted to a CHAR(10), whatever time stamp it had was essentially truncated, so when it's converted back to a datetime, the time portion will be '00:00' (treated as 12:00 am).

Hope that clears it up for you somewhat. Like I said, check out Books Online for more info.

-dave

 

[RITE1]

I have been messing around with the db and in my effort to learn what vidru did I came up with this script:

SELECT itemlookupcode, COUNT(*) AS number_of_products, datetransferred
FROM viewitemmovementhistory
GROUP BY itemlookupcode, DATEPART(dd, datetransferred), datetransferred

it seems to give me the same results. I am very new to this, can someone critique my code?