October 19, 2018 at 1:48 am
I have a below tables and want to get the result as per updated price of items in a query.
CREATE TABLE [dbo].[Sales](
[Icode] [varchar](10) ,
[Ccode] [varchar](13),
[qty] [numeric](18, 0) ,
[price] [float] ,
[date] [datetime]
Insert into [dbo].[sales] (Icode,ccode,qty,price,date)
values
('1','111','10','10','2015-10-10')
('1','111','5','13','2018-10-10')
('1',' '111', ' 5 ', '13','2017-07-10')
CREATE TABLE [dbo].[items](
[categorycode] [varchar](7),
[Icode] [varchar](10),
[price] [float],
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]
insert into items (categorycode,icode,price,description,datefrom,dateto)
values
('5','5','10','item1','2018-02-01','2018-10-30')
('1','5','17','item1','2018-10-31','2018-12-30')
('3','7','13','item1','2017-10-08','2018-10-07')
CREATE TABLE [dbo].[categories](
[varchar](10) ,
[cateogry] [varchar](15) ,
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]
insert into categories (code,category,description,datefrom,dateto)
values
('1','data 1','','2017-10-08','2018-10-07')
('3','data 2','','2015-03-07','2018-05-10')
('1','edata 1','','2015-10-08','2018-10-30')
('5','edata 2','','2015-03-07','2017-12-31')
CREATE TABLE [dbo].[categoriesitems](
[itemcode] [varchar](10),
[cateogrycode] [varchar](15),
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]
insert into categoriesitems (itemcode,categorycode,description,datefrom,dateto)
values
('1','1','','2015-03-07','2018-05-10')
('1','1','2017-12-31','2018-12-30')
('1','5','','2015-12-31','2017-12-30')
Data
| itemcode | categorycode | item | qty | value |
| 1 | 1 | item 1 | 5 | 85 |
October 19, 2018 at 11:48 am
Thanks for the sample data. The only thing missing now is an explanation of what you really want - like a sample result that you're trying to return..??
October 22, 2018 at 11:48 pm
If a user execute the data between '10-07-2018' and '10-10-2018', the below result is to be appeared.
| itemcode | categorycode | item | qty | value |
| 1 | 1 | item 1 | 5 | 85 |
This is the result I am trying to get, as you can see the price is 17 and category is 1 from above table structure. on large data, it should be duplicate and each table would be included in our query.
Thanks.
October 23, 2018 at 2:35 pm
Waqar571 - Friday, October 19, 2018 1:48 AMI have a below tables and want to get the result as per updated price of items in a query.CREATE TABLE [dbo].[Sales](
[Icode] [varchar](10) ,
[Ccode] [varchar](13),
[qty] [numeric](18, 0) ,
[price] [float] ,
[date] [datetime]Insert into [dbo].[sales] (Icode,ccode,qty,price,date)
values
('1','111','10','10','2015-10-10')
('1','111','5','13','2018-10-10')
('1',' '111', ' 5 ', '13','2017-07-10')CREATE TABLE [dbo].[items](
[categorycode] [varchar](7),
[Icode] [varchar](10),
[price] [float],
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]insert into items (categorycode,icode,price,description,datefrom,dateto)
values
('5','5','10','item1','2018-02-01','2018-10-30')
('1','5','17','item1','2018-10-31','2018-12-30')
('3','7','13','item1','2017-10-08','2018-10-07')CREATE TABLE [dbo].[categories](
[varchar](10) ,
[cateogry] [varchar](15) ,
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]insert into categories (code,category,description,datefrom,dateto)
values
('1','data 1','','2017-10-08','2018-10-07')
('3','data 2','','2015-03-07','2018-05-10')
('1','edata 1','','2015-10-08','2018-10-30')
('5','edata 2','','2015-03-07','2017-12-31')CREATE TABLE [dbo].[categoriesitems](
[itemcode] [varchar](10),
[cateogrycode] [varchar](15),
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]insert into categoriesitems (itemcode,categorycode,description,datefrom,dateto)
values
('1','1','','2015-03-07','2018-05-10')
('1','1','2017-12-31','2018-12-30')
('1','5','','2015-12-31','2017-12-30')Data
itemcode categorycode item qty value 1 1 item 1 5 85
Did you try running any of this? Half of it doesn't compile.USE TEMPDB;
GO
DROP TABLE Sales;
DROP TABLE categories;
DROP TABLE items;
DROP TABLE [categoriesitems];
GO
CREATE TABLE [dbo].[Sales](
[Icode] [varchar](10) ,
[Ccode] [varchar](13),
[qty] [numeric](18, 0) ,
[price] [float] ,
[date] [datetime]
);
CREATE TABLE [dbo].[categories]( [varchar](10) ,
[cateogry] [varchar](15) ,
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]
);
CREATE TABLE [dbo].[items](
[categorycode] [varchar](7),
[Icode] [varchar](10),
[price] [float],
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]);
CREATE TABLE [dbo].[categoriesitems](
[itemcode] [varchar](10),
[cateogrycode] [varchar](15),
[description] [varchar](15),
[datefrom] [datetime],
[dateto] [datetime]);
go
insert into items (categorycode,icode,price,description,datefrom,dateto)
values
('5','5','10','item1','2018-02-01','2018-10-30')
,('1','5','17','item1','2018-10-31','2018-12-30')
,('3','7','13','item1','2017-10-08','2018-10-07');
insert into categories (code,[cateogry],description,datefrom,dateto)
values
('1','data 1','','2017-10-08','2018-10-07')
,('3','data 2','','2015-03-07','2018-05-10')
,('1','edata 1','','2015-10-08','2018-10-30')
,('5','edata 2','','2015-03-07','2017-12-31');
Insert into [dbo].[sales] (Icode,ccode,qty,price,date)
values
('1','111','10','10','2015-10-10')
,('1','111','5','13','2018-10-10')
,('1', '111', '5', '13','2017-07-10');
insert into categoriesitems (itemcode,[cateogrycode],description,datefrom,dateto)
values
('1','1','','2015-03-07','2018-05-10')
,('1','1','','2017-12-31','2018-12-30')
,('1','5','','2015-12-31','2017-12-30');
There, fixed it for you. Please do everybody here that might want to help a favor and post code that at least compiles and runs when you're posting CREATE TABLE and INSERT scripts.. =)
October 23, 2018 at 10:48 pm
Thanks.
What about the query to get the required result.
October 23, 2018 at 11:24 pm
ROFL. SEND MONEY.
Seriously though - that's not how this place works. It's not a "please do my work for me" site. If you're looking for that, then you're going to be disappointed. It's YOUR work, so YOU should do it. If you need a consultant to do it, then pay one.
Read the Best Practices article... You can't even post sample data that compiles. Give me a break.
October 24, 2018 at 10:25 pm
I have already tried some queries but there is something to add as I can not get the result, I could send the query when required. The table structure is also created.
October 25, 2018 at 7:49 am
Waqar571 - Wednesday, October 24, 2018 10:25 PMI have already tried some queries but there is something to add as I can not get the result, I could send the query when required. The table structure is also created.
And what were the queries, and what data were they running against? We can't just magically invent answers for you. We need sample data to work with, or we're just guessing instead of using logic and reason...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 25, 2018 at 9:21 am
sgmunson - Thursday, October 25, 2018 7:49 AMWaqar571 - Wednesday, October 24, 2018 10:25 PMI have already tried some queries but there is something to add as I can not get the result, I could send the query when required. The table structure is also created.And what were the queries, and what data were they running against? We can't just magically invent answers for you. We need sample data to work with, or we're just guessing instead of using logic and reason...
I have to agree. Please post what you have tried to meet your requirements. I would also say that you need to be more specific in stating what you are trying to accomplish.
This:
If a user execute the data between '10-07-2018' and '10-10-2018', the below result is to be appeared.
really doesn't mean much to me as written. Are you saying when you query the data for the date range of '10-07-2018' and '10-10-2018'? Is this closed interval, open interval, semi-closed interval? Does your actual data have a time component that you didn't mention to us?
October 25, 2018 at 11:32 pm
Below is my query for the above tables.
SELECT a.icode,a.categorycode,a.description,sum(qty),date from items a
inner join sales b on a.icode=b.icode
inner join categories c on a.categorycode=c.code
inner join categoriesitems d on d.itemcode=a.icode and d.cateogrycode=a.categorycode
where date between '2018-10-7' and '2018-10-10'
group by a.icode,a.categorycode,a.description,date
the result is as below.
icode categorycode description qty date
5 1 item1 20 2018-10-10
5 5 item1 5 2018-10-10
Hope this would help you.
October 26, 2018 at 8:20 am
Waqar571 - Thursday, October 25, 2018 11:32 PMBelow is my query for the above tables.
SELECT a.icode,a.categorycode,a.description,sum(qty),date from items a
inner join sales b on a.icode=b.icode
inner join categories c on a.categorycode=c.code
inner join categoriesitems d on d.itemcode=a.icode and d.cateogrycode=a.categorycode
where date between '2018-10-7' and '2018-10-10'
group by a.icode,a.categorycode,a.description,datethe result is as below.
icode categorycode description qty date
5 1 item1 20 2018-10-10
5 5 item1 5 2018-10-10Hope this would help you.
Are you going to answer the questions I asked?
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply