Sql-server – a query that shows the load request received through GPRS by salesman

sql server

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.

Best Answer

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

Related Question