June 22, 2012 at 9:18 am
Hi all I am trying to pass some parameters to an SQL query between 2 months, this is my attempt below but it incorrect, can anybody point out my error please
SELECT DISTINCT TOP (10) Part_Number, SUM(Qty) AS Qty, SUM(Cost) AS Cost, Part_Description
FROM SLADB.dbo.PartsUsedTB
WHERE (DATEPART(yyyy, @date1) = DATEPART(yyyy, Date_Used)) AND (DATEPART(mm, @date1) = DATEPART(mm,Date_Used) BETWEEN DATEPART(yyyy, @date2) = DATEPART(yyyy, Date_Used)) AND (DATEPART(mm, @date2) = DATEPART(mm,Date_Used))
GROUP BY Part_Number, Part_Description
ORDER BY Qty DESC, Cost DESC
Jay
June 22, 2012 at 9:28 am
Sorry, but your code is confusing. What are you trying to accomplish and what are the parameters being passed?
June 22, 2012 at 9:31 am
this.PartsUsedTBTableAdapter.FillBy10(this.SLADBDataSet.PartsUsedTB, dateTimePicker1.Value.Date,
dateTimePicker2.Value.Date);
var p1 = new ReportParameter("from", dateTimePicker1.Value.ToString("MM-yyyy"));
var p2 = new ReportParameter("too", dateTimePicker2.Value.ToString("MM-yyyy"));
//var p3 = new ReportParameter("Percent", percent);
reportViewer1.LocalReport.SetParameters(new[] { p1, p2});
reportViewer1.LocalReport.EnableHyperlinks = true;
reportViewer1.RefreshReport();
June 22, 2012 at 9:31 am
As I look at the code more, it would help if you posted the DDL (CREATE TABLE statement) for the table, some sample data for the table, and the expected results based on the sample data and provided parameters.
June 22, 2012 at 9:32 am
jerome.morris (6/22/2012)
this.PartsUsedTBTableAdapter.FillBy10(this.SLADBDataSet.PartsUsedTB, dateTimePicker1.Value.Date,dateTimePicker2.Value.Date);
var p1 = new ReportParameter("from", dateTimePicker1.Value.ToString("MM-yyyy"));
var p2 = new ReportParameter("too", dateTimePicker2.Value.ToString("MM-yyyy"));
//var p3 = new ReportParameter("Percent", percent);
reportViewer1.LocalReport.SetParameters(new[] { p1, p2});
reportViewer1.LocalReport.EnableHyperlinks = true;
reportViewer1.RefreshReport();
This, I'm sorry, doesn't really help me.
June 22, 2012 at 9:38 am
Sorry,
USE [SLADB]
GO
/****** Object: Table [dbo].[PartsUsedTB] Script Date: 06/22/2012 16:36:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PartsUsedTB](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Date_Used] [datetime] NULL,
[Engineer] [nchar](50) NULL,
[Machine] [nchar](30) NULL,
[Module] [nchar](100) NULL,
[Qty] [int] NULL,
[Part_Number] [nchar](50) NULL,
[Part_Description] [nchar](100) NULL,
[Cost] [decimal](18, 2) NULL,
[Status] [bit] NULL,
[Count] [int] NULL,
[SerialNumber] [nchar](100) NULL,
[Con2] [nchar](100) NULL,
[Con3] [nchar](100) NULL,
[Con4] [nchar](100) NULL,
[Con5] [int] NULL,
[Con6] [int] NULL,
[Con7] [int] NULL,
[Con8] [bit] NULL,
[Con9] [bit] NULL,
[Con10] [bit] NULL,
CONSTRAINT [PK_PartsUsedTB] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I am trying to get all Part_Number listed between 2 months inclusive. So Jan to Feb
Does this help
thanks
June 22, 2012 at 9:38 am
Shot in the dark...
WHERE Date_Used between @date1 and @date2
???
_______________________________________________________________
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 22, 2012 at 9:45 am
I have to go with Sean's shot in the dark since you haven't provided sample data or expected results with a given example of the parameters that may be passed to the query.
June 22, 2012 at 9:46 am
This is how my query used to be but was getting duplictes, is it possible to add a DISTINCT clause to this query ?
SELECT DISTINCT TOP (10) Part_Number, SUM(Qty) AS Qty, SUM(Cost) AS Cost, Part_Description
FROM PartsUsedTB
WHERE (Date_Used BETWEEN @date1 AND @date2)
GROUP BY Part_Number, Part_Description
ORDER BY Qty DESC, Cost DESC
I still get duplicate Part_Numbers using above ?
Thanks
June 22, 2012 at 9:50 am
That is because your data is not normalized. You have multiple rows with the same part number but the description is different. If you had a parts table and this parts used table referenced it you would not have this issue. Your table in general looks like it needs some serious normalization.
_______________________________________________________________
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 22, 2012 at 9:51 am
Sample data and expected results. I have nothing to run your code against to test or to determine what your problem(s) may be with regard to actual results.
What you call a duplicate may not be a duplicate based on the query itself.
June 22, 2012 at 9:56 am
Hi Sean, you might be right but this is very new to me and I have only just started to use SQL. What would you advice. I am not fully sure I understand 🙁
Thanks
June 22, 2012 at 10:03 am
To be more descriptive in the normalization issues I figured I should explain what I mean.
You have a column Engineer. I assume this is the column that holds the name of the engineer that used this part. This should be a foreign key reference to the Engineer table instead of holding the engineers name. How do you handle name changes etc?
Machine - see engineer.
Module - see engineer.
Parts should be in its own table. Part Description belongs to the part not the row where it was used. The description of a part does not change when the part is used.
Serial Number does not work here. A Serial Number belongs to an individual item and this table has a count. That indicates you can use 3 parts. That of course makes sense but then how do you store the serial number?
Con2, con3, con4...no clue what these are but you should have a table for those. How are you going to handle needing an 11th? You have to change the table and every line of code that interacts with it.
IMHO you should NEVER use ID as the primary key. It will end up being a foreign key somewhere else and then you have no idea what it is. Maybe a name like PartsUsedTB_ID would be better. Also things like Status are just too vague. What does that mean? Status is Yes or No? Of course bit fields can also be NULL but that is another topic. Maybe this column is indicating if the job is complete? Then a name like IsComplete is far more descriptive.
It seems that the whole approach is slightly off here.
Please don't take this as negative, it is intended to be constructive criticism.
_______________________________________________________________
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 22, 2012 at 10:10 am
Wow that's brilliant advice, I will have a read once I am at home and perhaps if I come back with some more information regarding what you have asked and what I intend to do you can help me.
I have a table called engineers that holds names so if they have a id I can use this instead of the whole name ? but I don't know how to handle relationships yet
Thank you
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply