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

avatar 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

avatar image

Blackhawk-17
12k 30 35 42

(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.

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:

x45

asked: Sep 25, 2012 at 11:08 AM

Seen: 671 times

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

Copyright 2016 Redgate Software. Privacy Policy