Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have mobile sales system, in which the salesman sends their stock request in handheld device and through GPRS the data comes to MS SQL database. I need o show a report that shows the which request is received at what time to warehouse administrator and by clicking on request number they can see the detail of that request (each item how much quantity) on daily basis.

WarehouseTransaction

WarehouseID  TransactionID      TransactionDate             RequestedBy      TransactionTypeID
27             36101398         2012-04-18 14:49:34.000     3                 1

Warehousedetail

 WarehouseID PackID  Quantity
    27       4          25.000000000
    27       26         175.000000000
    27       30         25.000000000

Whenever I run this query the data should be specific to today date only.

share|improve this question
    
What is packId? –  Rohan Dec 24 '12 at 11:54
add comment

1 Answer 1

Unless I am missing something in your explanation, it sounds like you just want to do something like the following.

If your sample data is:

CREATE TABLE WarehouseTransaction([WarehouseID] int, [TransactionID] int, [TransactionDate] datetime, [RequestedBy] int, [TransactionTypeID] int);

INSERT INTO WarehouseTransaction ([WarehouseID], [TransactionID], [TransactionDate], [RequestedBy], [TransactionTypeID])
VALUES (27, 36101398, '2012-04-18 14:49:34', 3, 1),
    (29, 1234578, getdate(), 3, 1);

CREATE TABLE Warehousedetail ([WarehouseID] int, [PackID] int, [Quantity] numeric(20, 5)) ;

INSERT INTO Warehousedetail ([WarehouseID], [PackID], [Quantity])
VALUES
    (27, 4, 25.000000000),
    (27, 26, 175.000000000),
    (27, 30, 25.000000000),
    (29, 4, 25.000000000),
    (29, 26, 175.000000000),
    (29, 30, 25.000000000) ;

Then the query will be similar to this:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= Cast(getdate() as date)
  and t.TransactionDate <= DateAdd(d, 1, Cast(getdate() as date))

See SQL Fiddle with Demo

Which generates the result:

| WAREHOUSEID | TRANSACTIONID |                 TRANSACTIONDATE | REQUESTEDBY | TRANSACTIONTYPEID | PACKID | QUANTITY |
-----------------------------------------------------------------------------------------------------------------------
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |      4 |       25 |
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |     26 |      175 |
|          29 |       1234578 | December, 24 2012 13:29:00+0000 |           3 |                 1 |     30 |       25 |

You did not specify what version of SQL Server but the Cast(getdate() as date) will work in SQL Server 2008+, if you are not in SQL Server 2008, then you can use the following:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= DateAdd(Day, Datediff(Day,0, GetDate()), 0)
  and t.TransactionDate <= DateAdd(d, 1, Datediff(Day,0, GetDate()))

Edit, if you want your transactions to contain yesterday and today in SQL Server 2005 you can use:

select t.WarehouseID,
  t.TransactionID,
  t.TransactionDate,
  t.RequestedBy,
  t.TransactionTypeID,
  d.PackId,
  d.Quantity
from WarehouseTransaction t
inner join Warehousedetail d
  on t.WarehouseID = d.WarehouseID
where t.TransactionDate >= DateAdd(Day, Datediff(Day,0, GetDate()), -1)
  and t.TransactionDate <= DateAdd(d, 1, Datediff(Day,0, GetDate()))

See SQL Fiddle with Demo

share|improve this answer
    
dear, when I'm running the query in my server for a transactionID which has 5 item, the query shows 4647 records!! –  user1820065 Dec 25 '12 at 4:44
    
ok solved I just changed the condition from warehouseid to transactionid :) –  user1820065 Dec 25 '12 at 4:47
    
I'm using the SQL 2005, now if I want to have transaction for today and yesterday how the condition should be ? –  user1820065 Dec 25 '12 at 7:08
    
@user1820065 see my edit with a query for both yesterday and today transactions –  bluefeet Dec 25 '12 at 21:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.