Drew is right! you can follow Jeff Moden[/url]'s article[/url] about the tally table. It is a very good article and helps me a lot. You can check that out if...

try this one..

declare @startDate datetime

declare @endDate datetime

set @startDate = '2011-12-22 15:20:00.000'

set @endDate = '2011-12-22 16:03:09.000'

select convert(varchar(30), (datediff(mi, @startDate, @endDate) / 60))

+ ':' +

convert(varchar(30), (datediff(mi, @startDate, @endDate) % 60))

select ID,

(case when [Large] > 0 then 'Yes' else 'No' end)[Large],

(case when [Small] > 0 then 'Yes' else 'No' end)[Small]

from ##TableName a

pivot

(

count(Size)

for Size in ([Large], [Small])

) b

Hi Cadavre!

Thank you very much for your response I just read it by now and tried to run all the possible scenarios specially where I was stuck, like if the...

ok my apology, here is what i've made so far

declare @startTime datetime = '2011-12-09 07:31 AM'

declare @endTime datetime = '2011-12-09 4:31 PM'--540 mins

select datediff(mi, @startTime, @endTime) -

sum

(

case when...

I don't have code yet, that is what I am thinking of, so I it is open to all approaches just to arrive with the solution.

Thanks for your cooperation! 🙂

Im stuck with the condition if the @startTime or @endTime is in between the BreakTimeFrom and BreakTimeTo.

can you try this?

declare @a varchar(255) = ''

select @a = @a + Col1 + Col2 + Col3 + ';'

from

(

select 'A'[Col1], 'B'[Col2], 'ABC'[Col3]

union all

select...

try to look for Change Data Capture(cdc) feature of Sql Server. This would be efficient than creating triggers for each table especially when you have lots of table to be...

Walang Anuman Sir! ^_^,

(case when) statement is not supported in access.

can u try this?

select Category,

sum(iif(left(Barcode, 1) in ('M', 'D'), Qty, 0)[Regular],

sum(iif(left(Barcode, 1) in ('A', 'B'), Qty, 0)[Sales],

from SalesDetail

group by Category

another solution...

select Category,

sum(case when left(Barcode, 1) in ('M', 'D') then Qty else 0 end)[Regular],

sum(case when left(Barcode, 1) in ('A', 'B') then Qty else 0 end)[Sales]

from SalesDetail

group by Category

Is there trigger associated with this table?