June 6, 2013 at 5:08 am
I have the following query:
SELECT Sales_Intrastat.SalesID, Sales_Intrastat.Year, Sales_Intrastat.Period, Sales_Intrastat.Sales, Sales_Intrastat.CostOfSales, InstrastatCustomers.Depot,
InstrastatCustomers.AccountName, InstrastatCustomers.CountryCode, InstrastatCustomers.AccountNumber, CountryCode.Description,
InstrastatCustomers.ContractCode
FROM Sales_Intrastat INNER JOIN
InstrastatCustomers ON InstrastatCustomers.AccountNumber = Sales_Intrastat.AccountNumber INNER JOIN
CountryCode ON CountryCode.CountryCode = InstrastatCustomers.CountryCode
WHERE (Sales_Intrastat.Period IN (@Period)) AND (InstrastatCustomers.Depot IN (@Depot)) AND (Sales_Intrastat.Year IN (@Year)) AND
(InstrastatCustomers.CountryCode IN (@CountryCode))
I want to create a new query to retrieve the results in this manner:
Between Year and Period TO Year and Period
My current report has dropdowns where the user can:
Select a Year and then Select a Period.
So my new Report will have the Parameters
Select a Year Select a Perioid Select a Year Select a Peroid
Can anyone give me an idea of how to write the new query. I tried using a BETWEEN in two sets of Year and Period and this would work.
June 6, 2013 at 8:56 am
This is not the optimal solution, you would be better using dates. For a better performing query, please post DDL and sample data as indicated on the article linked in my signature.
I'm assuming your Year and Period columns are int, but you should correct me if they're not.
SELECT Sales_Intrastat.SalesID
,Sales_Intrastat.Year
,Sales_Intrastat.Period
,Sales_Intrastat.Sales
,Sales_Intrastat.CostOfSales
,InstrastatCustomers.Depot
,InstrastatCustomers.AccountName
,InstrastatCustomers.CountryCode
,InstrastatCustomers.AccountNumber
,CountryCode.Description
,InstrastatCustomers.ContractCode
FROM Sales_Intrastat
INNER JOIN InstrastatCustomers ON InstrastatCustomers.AccountNumber = Sales_Intrastat.AccountNumber
INNER JOIN CountryCode ON CountryCode.CountryCode = InstrastatCustomers.CountryCode
WHERE CAST( Sales_Intrastat.Year AS CHAR(4)) + CAST(Sales_Intrastat.Period AS CHAR(2)) >= CAST(@YearFrom AS CHAR(4)) + CAST(@PeriodFrom AS CHAR(2))
AND CAST( Sales_Intrastat.Year AS CHAR(4)) + CAST(Sales_Intrastat.Period AS CHAR(2)) <= CAST(@YearTo AS CHAR(4)) + CAST(@PeriodTo AS CHAR(2))
AND (InstrastatCustomers.Depot IN (@Depot))
AND (InstrastatCustomers.CountryCode IN (@CountryCode))
June 6, 2013 at 9:50 am
Thanks for reply.
My table structure is:
SalesID int Primary Key.
Company int
AccountNumber varchar(10)
Year varchar(10)
Period int
Sales money
CostOfSales money
Sample data:
01|C3000|2013|1| 9191.16| 7335.65
01|C3001|2013|1| 0.00| 0.00
01|C3002|2013|1| 0.00| 0.00
01|C3003|2013|1| 281.06| 268.13
01|C3004|2013|1| 0.00| 0.00
01|C3005|2013|1| 0.00| 0.00
01|C3006|2013|1| 0.00| 0.00
June 6, 2013 at 10:10 am
dbman (6/6/2013)
Thanks for reply.My table structure is:
SalesID int Primary Key.
Company int
AccountNumber varchar(10)
Year varchar(10)
Period int
Sales money
CostOfSales money
Sample data:
01|C3000|2013|1| 9191.16| 7335.65
01|C3001|2013|1| 0.00| 0.00
01|C3002|2013|1| 0.00| 0.00
01|C3003|2013|1| 281.06| 268.13
01|C3004|2013|1| 0.00| 0.00
01|C3005|2013|1| 0.00| 0.00
01|C3006|2013|1| 0.00| 0.00
This falls woefully short of posting ddl and sample data. Even worse is that you only posted 1 table and your query has several. We don't even know which table this is.
To show you what would be the best approach I turned your post into an example of what is considered readily consumable ddl and sample data. You will notice that all you have to do is select the code and run it on your system. No additional formatting, creating tables etc. This way the volunteers around here can work on your solution instead of setting up the problem.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
SalesID int identity Primary Key,
Company int,
AccountNumber varchar(10),
Year varchar(10),
Period int,
Sales money,
CostOfSales money
)
insert #Something
select 01, 'C3000', 2013, 1, 9191.16, 7335.65 union all
select 01, 'C3001', 2013,1, 0.00, 0.00 union all
select 01, 'C3002', 2013,1, 0.00, 0.00 union all
select 01, 'C3003', 2013,1, 281.06, 268.13 union all
select 01, 'C3004', 2013,1, 0.00, 0.00 union all
select 01, 'C3005', 2013,1, 0.00, 0.00 union all
select 01, 'C3006', 2013,1, 0.00, 0.00
select * from #Something
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2013 at 10:48 am
Apologies: Below are the full table structures, sql, datasets i am using
Customer Table:
AccountID int Primary Key
Company int
Depot int
AccountNumber varchar(30)
AccountName varchar(50)
CountryCode int
ContractCode int
Sales_Intrastat:
SalesID int Primary Key
Company int
AccountNumber varchar(10)
Year varchar(10)
Period int
Sales money
CostOfSales money
CountyCode:
UID int Primary Key
CountryCode int
Description varchar(50)
EU varchar(50)
In my report i have a dataset:
DistintPeriod:
SELECT DISTINCT Period
FROM Sales_Intrastat
ORDER BY Period
DisinctYear:
SELECT DISTINCT Year
FROM Sales_Intrastat
DistinctCountryCode:
SELECT DISTINCT CountryCode
FROM CountryCode
ORDER BY CountryCode
Sales:
SELECT Sales_Intrastat.SalesID, Sales_Intrastat.Year, Sales_Intrastat.Period, Sales_Intrastat.Sales, Sales_Intrastat.CostOfSales, InstrastatCustomers.Depot,
InstrastatCustomers.AccountName, InstrastatCustomers.CountryCode, InstrastatCustomers.AccountNumber, CountryCode.Description,
InstrastatCustomers.ContractCode
FROM Sales_Intrastat INNER JOIN
InstrastatCustomers ON InstrastatCustomers.AccountNumber = Sales_Intrastat.AccountNumber INNER JOIN
CountryCode ON CountryCode.CountryCode = InstrastatCustomers.CountryCode
WHERE (Sales_Intrastat.Period IN (@Period)) AND (InstrastatCustomers.Depot IN (@Depot)) AND (Sales_Intrastat.Year IN (@Year)) AND
(InstrastatCustomers.CountryCode IN (@CountryCode))
June 6, 2013 at 10:54 am
dbman (6/6/2013)
Apologies: Below are the full table structures, sql, datasets i am using
This is still not consumable ddl. I should able to copy and paste your post into SSMS and hit F5. Look at the example I posted. Yours looks nothing like that.
You also need to include some sample data. We can't help you with a query when we have no data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2013 at 3:14 am
Customer Table Sample Data:
001|08|C3000|Company1|014|6020
001|08|C3001|Company2|014|6020
001|08|C3002|Company3|014|6030
001|08|C3003|Company|014|6030
001|08|C3004|Company5|014|6030
001|08|C3005|Company6|014|6030
001|08|C3006|Company7|014|6030
Sales Table Sample Data:
01|C3000|2013|1| 9191.16| 7335.65
01|C3001|2013|1| 0.00| 0.00
01|C3002|2013|1| 0.00| 0.00
01|C3003|2013|1| 281.06| 268.13
01|C3004|2013|1| 0.00| 0.00
01|C3005|2013|1| 0.00| 0.00
01|C3006|2013|1| 0.00| 0.00
01|C3007|2013|1| 0.00| 0.00
01|C3008|2013|1| 0.00| 0.00
June 7, 2013 at 7:42 am
How about create table statements and insert statements? If you really need some help you have to put in some of the effort. After all you are getting 100% of the compensation for completing the work. 😉
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2013 at 9:29 am
dbman (6/7/2013)
Customer Table Sample Data:001|08|C3000|Company1|014|6020
001|08|C3001|Company2|014|6020
001|08|C3002|Company3|014|6030
001|08|C3003|Company|014|6030
001|08|C3004|Company5|014|6030
001|08|C3005|Company6|014|6030
001|08|C3006|Company7|014|6030
Sales Table Sample Data:
01|C3000|2013|1| 9191.16| 7335.65
01|C3001|2013|1| 0.00| 0.00
01|C3002|2013|1| 0.00| 0.00
01|C3003|2013|1| 281.06| 268.13
01|C3004|2013|1| 0.00| 0.00
01|C3005|2013|1| 0.00| 0.00
01|C3006|2013|1| 0.00| 0.00
01|C3007|2013|1| 0.00| 0.00
01|C3008|2013|1| 0.00| 0.00
This is what we are looking for when posting sample data:
insert into dbo.MyCustomerTable( -- Replace dbo.MyCustomerTable with your table name
AccountID,
Company,
Depot,
AccountNumber,
AccountName,
CountryCode,
ContractCode
)
values
(001,08,'C3000','Company1',014,6020),
(001,08,'C3001','Company2',014,6020),
(001,08,'C3002','Company3',014,6030),
(001,08,'C3003','Company',014,6030),
(001,08,'C3004','Company5',014,6030),
(001,08,'C3005','Company6',014,6030),
(001,08,'C3006','Company7',014,6030);
The above code can be copied, pasted into a query window in SSMS and executed if the table exists.
You also need to provide the sql code (DDL) that will create your table(s) for us.
June 10, 2013 at 3:20 am
Customer Table
CREATE TABLE [dbo].[InstrastatCustomers](
[AccountID] [int] IDENTITY(1,1) NOT NULL,
[Company] [int] NULL,
[Depot] [int] NULL,
[AccountNumber] [varchar](30) NULL,
[AccountName] [varchar](50) NULL,
[CountryCode] [int] NULL,
[ContractCode] [int] NULL,
)
INSERT INTO [dbo].[InstrastatCustomers]
([Company]
,[Depot]
,[AccountNumber]
,[AccountName]
,[CountryCode]
,[ContractCode])
VALUES
(001,08,'C3000','Company1',014,6020),
(001,08,'C3001','Company2',014,6020),
(001,08,'C3002','Company3',014,6030),
(001,08,'C3003','Company',014,6030),
(001,08,'C3004','Company5',014,6030),
(001,08,'C3005','Company6',014,6030),
(001,08,'C3006','Company7',014,6030);
GO
CREATE TABLE [dbo].[Sales_Intrastat](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[Company] [int] NULL,
[AccountNumber] [varchar](10) NULL,
[Year] [varchar](10) NULL,
[Period] [int] NULL,
[Sales] [money] NULL,
[CostOfSales] [money] NULL,
)
INSERT INTO [dbo].[Sales_Intrastat]
([Company]
,[AccountNumber]
,[Year]
,[Period]
,[Sales]
,[CostOfSales])
VALUES
(1,C3012,2013,5,513.52,389.41),
(1,C3013,2013,5,0.00,0.00),
(1,C3014,2013,5,0.00,0.00),
(1,C3015,2013,5,889.75,674.20),
(1,C3016,2013,5,268.03,226.32),
(1,C3017,2013,5,73.77,51.59);
GO
June 10, 2013 at 7:20 am
You really need to test the code you post, it won't work as written because your strings are not correctly wrapped with ''. We are now 4 days and 10 posts into this thread and you have only posted 2 of the 3 tables and we still have no idea what you want for output.
The script below will fix this issues for your insert. I also tossed together a script for CountryCode. Totally off topic but I would recommend not using the same name for your tables and column names. It can get horribly confusing.
INSERT INTO [dbo].[Sales_Intrastat]
([Company]
,[AccountNumber]
,[Year]
,[Period]
,[Sales]
,[CostOfSales])
VALUES
(1,'C3012',2013,5,513.52,389.41),
(1,'C3013',2013,5,0.00,0.00),
(1,'C3014',2013,5,0.00,0.00),
(1,'C3015',2013,5,889.75,674.20),
(1,'C3016',2013,5,268.03,226.32),
(1,'C3017',2013,5,73.77,51.59);
GO
create table CountryCode
(
CountryCode int
)
insert CountryCode
select 14
Ok so now we finally have all of the ddl needed for this. In your first post you mentioned that you pass in some parameters and expect some sort of result. Can you tell us what those parameter values would be and what would be the expected result from the sample data you provided?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 8:57 am
Hi Sean
Thanks for your help so far.
The Parameters I am tryin to pass are:
@Year @Period , @Depot @CountryCode
The actual report is a matrix report. The fields are:
[Year] [Period]
Country, Account No, AccountName, ContractCode, Sales
June 10, 2013 at 9:11 am
dbman (6/10/2013)
Hi SeanThanks for your help so far.
The Parameters I am tryin to pass are:
@Year @Period , @Depot @CountryCode
The actual report is a matrix report. The fields are:
[Year] [Period]
Country, Account No, AccountName, ContractCode, Sales
WOW this is like pulling teeth. I can read the names of the variables. Can you tell me what a sample run would look like? What VALUES would be there and what do you expect for output? Not the columns, the ACTUAL output. Remember we can't see your screen, we don't your project and we have no idea what you are trying to do. From your original post it seems like there is some sort of logic that you can't figure out.
I want to create a new query to retrieve the results in this manner:
Between Year and Period TO Year and Period
My current report has dropdowns where the user can:
Select a Year and then Select a Period.
So my new Report will have the Parameters
Select a Year Select a Perioid Select a Year Select a Peroid
Can anyone give me an idea of how to write the new query. I tried using a BETWEEN in two sets of Year and Period and this would work.
We have no idea what that means. I guess you have two sets of parameters and you want data between them?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 9:25 am
I have attached a screenshot of my report. I have cleaned out the AccountNames though!
Now currently the user can select a Year and Period and a depot and a CountyCode.
Rather than the user being able to select a Year and a Period, I would like the user to be able to select ( FROM YEAR FROM PERIOD ) TO (FROM YEAR FROM PERIOD)
So for example
2012 Perioid 4 TO 2013 Period 1.
June 10, 2013 at 9:45 am
dbman (6/10/2013)
I have attached a screenshot of my report. I have cleaned out the AccountNames though!Now currently the user can select a Year and Period and a depot and a CountyCode.
Rather than the user being able to select a Year and a Period, I would like the user to be able to select ( FROM YEAR FROM PERIOD ) TO (FROM YEAR FROM PERIOD)
So for example
2012 Perioid 4 TO 2013 Period 1.
You said in your original post that you want to pass 2 values for Year and Period so that you end up with a range. Sending a screen shot of a functioning report does NOT help anybody understand the logic to help you with your query. I give up. I have tried at least 5 times to have you post the details of your question. Maybe somebody else will come along and help. I truly hope you can figure out your issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply