I'm new to SQL and I could use the help. I have a data set which I need to filter down. The data looks like this:

code is nvarchar(85)
date is nvarchar(10)
quantity is numeric(38,15)

I'm tasked to get a list of data for the one code (code1). This data needs to show the quantity grouped by date. In months.

What's best way to do this with a bit of explanation behind it?

CodePudding user response：

we can use the function MONTH()to get the month of a date. SQL will implicitly convert a valid string to a date. We then GROUP BY month and code specifiying the code we want with WHERE.
NB the function MONTH()does not funciton in all dbms. Please specify which flavour of SQL you are using.

CREATE TABLE dataset (
  code varchar(10),
  date varchar(10),
  quantity decimal(10,8)
  );
INSERT INTO dataset values
('code1','20210613',1.0000000000),
('code1','20210719',4.0000000000),
('code1','20210726',3.0000000000);

SELECT
   d.code,
   MONTH(d.date) "month"
FROM 
   dataset d
WHERE
   d.code = 'code1'
GROUP BY
   d.code,
   MONTH(d.date)
ORDER BY 
   d.code,
   MONTH(d.date);
GO

code  | month
:---- | ----:
code1 |     6
code1 |     7

db<>fiddle here

If you want to only return a certain date range you can specify the year in the WHEREclause as follows:

WHERE
   d.code = 'code1'
   AND YEAR(d.date) = 2021

or you can specify first and last dates, which is longer to write, but more flexible.

WHERE
   d.code = 'code1'
   AND d.date > '20201231'
   AND d.date < '20230101'