x

Summary of Transaction

Table Master
-------------
BillNo   nvarchar no 100
BillDate  datetime no 8


 Data in table Master
 -----------------------
 A0300000160 2010-10-06 19:06:16.000 
 A0300000161 2010-10-06 19:06:29.000 
 A0300000162 2010-10-06 19:07:03.000 
 A0300000163 2010-10-06 19:07:34.000 
 A0300000164 2010-10-06 19:09:24.000 


Table Child
------------------

BillNo nvarchar no 40      
ProductID nvarchar no 40      
ProductRate real no 4 24   
Qty real no 4 24   
ccode nvarchar no 8      

Data in table Child
--------------------
 A0300000161 TK009 22.0 2.0 TK
 A0300000162 CC001 44.09 1.0 CC
 A0300000162 TK009 22.0 1.0 TK
 A0300000163 CP008 342.60  0.25 CP
 A0300000164 TK009 22.0 2.0 TK
 A0300000165 TK009 22.0 1.0 TK




Table Cateogry_Master
----------------------

CCode nvarchar no 40
CName nvarchar no 200
Date in Category_Master
----------------------
FC Coffee
CP Coffee Powder
SN Snacks
ES Hot Coffee
CC Cold Coffee
MR Merchandise
TK Takeaway

Expected Result

CName    Total_Bills
----------------------
Takeaway     3
Coffe Powder 1
Mixed        1     

Expected Result : Summary of transaction, if each bill has mixed cateogy of products then it will be counted as Mixed otherwise can calculate based on category code

This is my code but am not getting expecting result.

select 
  count(*) as count, 
  c.ccode 
from 
  tblbilldetails as c,
  tblbill b 
where 
  b.billno=c.billno 
  and b.billdate>='2010-10-11 11:43:48.000' 
  and b.billdate<='2010-10-11 12:43:48.000' 
group by 
  c.ccode
more ▼

asked Sep 25, 2012 at 11:08 AM in Default

abdol gravatar image

abdol
0 2 2 4

What have you tried so far? Any query, no matter how simple, will show us how far you have got. Otherwise it just feels like we are doing all the work for you.

This feels like a homework question, and you'll learn more if you try....
Sep 25, 2012 at 12:29 PM Kev Riley ♦♦

I have pasted the text from this answer into the original question, and then converted it to a comment to the question.

//Magnus Ahlkvist
Sep 28, 2012 at 11:19 AM abdol
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

In your code you are asking for times that are both greater and less than a range... a time can only be one or the other:

where 
b.billno=c.billno 
and b.billdate>='2010-10-11 11:43:48.000' 
and b.billdate<='2010-10-11 12:43:48.000' 
Try bundling and using an OR for your date range.
more ▼

answered Sep 28, 2012 at 12:14 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x30

asked: Sep 25, 2012 at 11:08 AM

Seen: 552 times

Last Updated: Sep 28, 2012 at 12:14 PM