December 4, 2017 at 12:38 am
I have a table where we store price information for each item and prices can be changed anytime during the month. How can I store and retrieve correct information based on price updates. Please check the below table structure and my query.
Item Price
| Code | Price | Description | DateFrom | DateTo |
| 1 | 10 | item 1 | 10/10/2015 | 10/5/2016 |
| 2 | 5 | item 2 | 10/6/2016 | 10/7/2016 |
| 3 | 7 | item 3 | 3/7/2017 | 5/7/2017 |
| 1 | 13 | item 1 | 10/6/2016 | 10/7/2017 |
| 5 | 3 | item 5 | 10/7/2016 | 10/10/2017 |
| 1 | 15 | item 1 | 1/1/2016 | 12/31/2016 |
Sales
| Icode | Ccode | Qty | Price | Date |
| 1 | 111 | 10 | 10 | 10/10/2015 |
| 1 | 111 | 5 | 13 | 10/7/2016 |
| 1 | 111 | 3 | 15 | 12/31/2016 |
I require the following result based on price updates.
| description | qty | total | date |
| Item1 | 10 | 100 | 13/10/2015 |
| Item1 | 5 | 65 | 10/7/2016 |
| Item 1 | 3 | 45 | 12/31/2016 |
What would be the query to get this result.
December 4, 2017 at 1:18 am
Could you please post DDL and include your sample data.
...
December 4, 2017 at 2:35 am
Welcome to SSC Waqar
You've already got what you need in the Sales table, multiple Qty and Price, and join back to ItemPrice table to get the item description.
If you want better help, please post DDL, sample data in readily consumable format, along with your query which you have tried and people will be more willing to help, please see the first link in my signature on posting readily consumable scripts for people.
December 4, 2017 at 4:12 am
CREATE TABLE [dbo].[Sales](
[Icode] [nvarchar](255) ,
[Ccode] [nvarchar](255) ,
[Qty] [int] ,
[Price] [float],
[Date] [datetime]
CREATE TABLE [dbo].[Item](
[Code] [varchar],
[Price] [float],
[Description] [varchar],
[DateFrom] [datetime],
[DateTo] [datetime]
What I have in my sales table is not the solution, it is the table that has information for all the clients who purchased items.However, following is my query and it's result.
select a.description,sum(b.qty),sum(b.qty)*a.price as total,date
from item a
inner join sales b on a.code=b.icode
where date between '2015-03-03' and '2017-08-10'
group by description,a.price,date
| description | qty | total | date |
| Item1 | 10 | 100 | 10/10/2015 |
| Item1 | 5 | 50 | 7/10/2016 |
| Item 1 | 10 | 130 | 10/10/2015 |
| Item 1 | 5 | 65 | 7/10/2016 |
| Item 1 | 10 | 150 | 10/10/2015 |
| Item 1 | 5 | 75 | 7/10/2016 |
which is repetitive and wrong data.
December 4, 2017 at 6:32 am
How can I do that as I tried it.
December 4, 2017 at 9:30 pm
CREATE TABLE [dbo].[Sales](
[Icode] [varchar](255) ,
[Ccode] [varchar](255) ,
[Qty] [int] ,
[Price] [float],
[Date] [datetime]
Insert into [dbo].[sales] (Icode,ccode,qty,price,date)
values
('1','111',10,10,'10/10/2015')
('1','111',5,13,'10/07/2016')
('1','111',3,15,'12/31/2016')
CREATE TABLE [dbo].[Item](
[Code] [varchar](10),
[Price] [float],
[Description] [varchar](50),
[DateFrom] [datetime],
[DateTo] [datetime]
Insert into [dbo].[sales] (code,price,description,datefrom,dateto)
values
('1','10','item1','10/10/2015','10/5/2016')
('2','5','item2','10/6/2016','10/7/2016')
('3','7','item3','3/7/2017','5/7/2017')
('1','13','item1','10/6/2016','10/7/2017')
('5','3','item5','10/7/2016','10/10/2017')
('1','15','item1','1/1/2016','12/31/2016')
select a.description,sum(b.qty),sum(b.qty)*a.price as total,date
from item a
inner join sales b on a.code=b.icode
where date between '2015-03-03' and '2017-08-10'
group by description,a.price,date
December 5, 2017 at 1:20 am
You need to move the date up into the join clause
select a.description,sum(b.qty),sum(b.qty)*a.price as total,date
from item a
inner join sales b on a.code=b.icode and b.date between a.datefrom and a.dateto
group by description,a.price,date
The problem you have is that you have prices for Item 1 that overlap. Is that true? Should an item have multiple prices that overlap?
Also please test your code, it doesn't compile, your missing ending brackets on your create table statements and comma's at the end of your insert value lines, also if using MDY date formatting specify that SET DATEFORMAT MDY, otherwise use ISO date formatting to ensure you don't get any conversion issues.
SELECT i.description, s.qty, s.qty*s.price as total, s.date
FROM sales s
INNER JOIN (SELECT DISTINCT i.code, i.description FROM item i ) i
ON s.icode = i.code
December 5, 2017 at 2:18 am
anthony.green - Tuesday, December 5, 2017 1:20 AMYou need to move the date up into the join clause
select a.description,sum(b.qty),sum(b.qty)*a.price as total,date
from item a
inner join sales b on a.code=b.icode and b.date between a.datefrom and a.dateto
group by description,a.price,dateThe problem you have is that you have prices for Item 1 that overlap. Is that true? Should an item have multiple prices that overlap?
Also please test your code, it doesn't compile, your missing ending brackets on your create table statements and comma's at the end of your insert value lines, also if using MDY date formatting specify that SET DATEFORMAT MDY, otherwise use ISO date formatting to ensure you don't get any conversion issues.
SELECT i.description, s.qty, s.qty*s.price as total, s.date
FROM sales s
INNER JOIN (SELECT DISTINCT i.code, i.description FROM item i ) i
ON s.icode = i.code
Agreed on all of the above points regarding testing etc. and the overlap of dates, having said that I have been in Supermarkets where an item has two prices!! Great when this happens to be beer!
An alternative to Anthony's could be to use the WHERE clause as in:
select I.description,sum(S.qty),sum(S.qty)*I.price as total,S.date
from item I
inner join sales S on I.code=S.icode
where S.[date] >= I.DateFrom
AND S.[Date] <= I.DateTo
group by description,I.price,S.date
...
December 17, 2017 at 11:43 pm
Thanks, I will check and reply you.
December 18, 2017 at 1:13 am
HappyGeek - Tuesday, December 5, 2017 2:18 AMAn alternative to Anthony's could be to use the WHERE clause as in:
select I.description,sum(S.qty),sum(S.qty)*I.price as total,S.date
from item I
inner join sales S on I.code=S.icode
where S.[date] >= I.DateFrom
AND S.[Date] <= I.DateTo
group by description,I.price,S.date
Since it's an Inner Join, there's no difference in performance of functionality between predicates in the JOIN or in the WHERE. I'd personally put it in the join, as it's part of the condition for matching the tables, but WHERE is perfectly fine too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply