A.Goutam
Reputation: 3494
How to get total count of given weeks
I am trying to create a query that the weekofyear returned with a value if there are 4 or more DISTINCT(DELIVERYDATE) in that week. Therefore, if there only 3 days, it would return a null value. If 4 or more DISTINCT(DELIVERYDATE) then return 'weekofyear' value.
SELECT READYDATE,TOTALAMOUNT,ORDER_TRACK_NUMBER,
(CONVERT(VARCHAR(4),LEFT(DELIVERYDATE,4)) + '' + CONVERT(VARCHAR(4),DATEPART( wk, DELIVERYDATE))) AS WEEKOfYear
FROM CurrentOrder COrder
WHERE
COrder.CANCELLED = 0
AND COrder.DELIVERYDATE>='20171116'
AND COrder.DELIVERYDATE<='20171116'
ORDER BY DISPID
Expected Output that I want as a result of the query
READYDATE TOTALAMOUNT ORDERNUM DELIVERYDATE WEEKOfYear
2017-11-16 11.24 6784230 2017-11-16 201746
2017-11-16 0.00 6788903 2017-11-16 201746
2017-11-16 91.85 6789075S 2017-11-16 201746
2017-11-16 640.00 6789080S 2017-11-16 201746
2017-11-16 0.00 6789213S 2017-11-16 201746
2017-11-15 0.00 6789098S 2017-11-15 NULL
2017-11-15 0.00 6789103S 2017-11-15 NULL
2017-11-15 0.00 6789110S 2017-11-15 NULL
2017-11-13 250.00 6789751S 2017-11-13 NULL
2017-11-13 250.00 6789756S 2017-11-13 NULL
2017-11-13 25.00 6789773S 2017-11-13 NULL
2017-11-14 242.00 6789779S 2017-11-14 201746
2017-11-14 23.88 6789816S 2017-11-14 201746
2017-11-14 190.00 6789833S 2017-11-14 201746
2017-11-14 215.00 6789845S 2017-11-14 201746
2017-11-14 108.00 6789873S 2017-11-14 201746
CREATE TABLE [dbo].[CurrentOrder](
[OrderId] [int] NOT NULL,
[ORDER_TRACK_NUMBER] [varchar](50) NULL,
[TOTALAMOUNT] [decimal](18, 10) NULL,
[READYDATE] [date] NULL,
[DELIVERYDATE] [date] NULL,
[CANCELLED] [tinyint] NULL,
[DispID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[OrderId] 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
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (1, N'6784230', CAST(11.0000000000 AS Decimal(18, 10)), CAST(0x883D0B00 AS Date), CAST(0x883D0B00 AS Date), 0, 56)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (2, N'6788903', CAST(0.0000000000 AS Decimal(18, 10)), CAST(0x883D0B00 AS Date), CAST(0x883D0B00 AS Date), 0, 56)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (3, N'6789075S', CAST(92.0000000000 AS Decimal(18, 10)), CAST(0x883D0B00 AS Date), CAST(0x883D0B00 AS Date), 0, 56)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (4, N'6789080S', CAST(640.0000000000 AS Decimal(18, 10)), CAST(0x883D0B00 AS Date), CAST(0x883D0B00 AS Date), 0, 56)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (5, N'6789213S', CAST(0.0000000000 AS Decimal(18, 10)), CAST(0x883D0B00 AS Date), CAST(0x883D0B00 AS Date), 0, 56)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (6, N'6789098S', CAST(0.0000000000 AS Decimal(18, 10)), CAST(0x873D0B00 AS Date), CAST(0x873D0B00 AS Date), 0, 66)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (7, N'6789103S', CAST(0.0000000000 AS Decimal(18, 10)), CAST(0x873D0B00 AS Date), CAST(0x873D0B00 AS Date), 0, 66)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (8, N'6789110S', CAST(0.0000000000 AS Decimal(18, 10)), CAST(0x873D0B00 AS Date), CAST(0x873D0B00 AS Date), 0, 66)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (9, N'6789751S', CAST(250.0000000000 AS Decimal(18, 10)), CAST(0x853D0B00 AS Date), CAST(0x853D0B00 AS Date), 0, 65)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (10, N'6789756S', CAST(250.0000000000 AS Decimal(18, 10)), CAST(0x853D0B00 AS Date), CAST(0x853D0B00 AS Date), 0, 65)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (11, N'6789773S', CAST(25.0000000000 AS Decimal(18, 10)), CAST(0x853D0B00 AS Date), CAST(0x853D0B00 AS Date), 0, 65)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (12, N'6789779S', CAST(242.0000000000 AS Decimal(18, 10)), CAST(0x863D0B00 AS Date), CAST(0x863D0B00 AS Date), 0, 39)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (13, N'6789816S', CAST(24.0000000000 AS Decimal(18, 10)), CAST(0x863D0B00 AS Date), CAST(0x863D0B00 AS Date), 0, 39)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (14, N'6789833S', CAST(190.0000000000 AS Decimal(18, 10)), CAST(0x863D0B00 AS Date), CAST(0x863D0B00 AS Date), 0, 39)
INSERT [dbo].[CurrentOrder] ([OrderId], [ORDER_TRACK_NUMBER], [TOTALAMOUNT], [READYDATE], [DELIVERYDATE], [CANCELLED], [DispID]) VALUES (15, N'6789845S', CAST(215.0000000000 AS Decimal(18, 10)), CAST(0x863D0B00 AS Date), CAST(0x863D0B00 AS Date), 0, 39)
Description :- DeliveryDate - 2017-11-16 is more than 4 so that is displaying the value in WEEKOfYear but for DeliveryDate '2017-11-15has only three orders so this is displaying Null same as for the date2017-11-13. But again DELIVERDATE2017-11-14` have more than 4 order for that date so this is displaying the WEEKOfYear.
Thanks for your help in advance.
Upvotes: 1
Views: 105
Answers (3)
Zohar Peled
Reputation: 82524
You can use count() with over() clause to get the number of rows per delivery date, and a case to determine whether to show the weekOfYear or null, like this:
SELECT READYDATE,
TOTALAMOUNT,
ORDER_TRACK_NUMBER,
CASE WHEN COUNT(*) OVER(PARTITION BY DELIVERYDATE) > 3 THEN
CAST(DATEPART(YEAR, DELIVERYDATE) as char(4)) +
CAST(DATEPART(WEEK, DELIVERYDATE) as char(2))
ELSE
NULL
END As WeekOfYear
FROM CurrentOrder
WHERE CANCELLED = 0
ORDER BY DISPID
Results:
READYDATE TOTALAMOUNT ORDER_TRACK_NUMBER WeekOfYear
2017-11-14 242,0000000000 6789779S 201746
2017-11-14 24,0000000000 6789816S 201746
2017-11-14 190,0000000000 6789833S 201746
2017-11-14 215,0000000000 6789845S 201746
2017-11-16 11,0000000000 6784230 201746
2017-11-16 0,0000000000 6788903 201746
2017-11-16 92,0000000000 6789075S 201746
2017-11-16 640,0000000000 6789080S 201746
2017-11-16 0,0000000000 6789213S 201746
2017-11-13 250,0000000000 6789751S NULL
2017-11-13 250,0000000000 6789756S NULL
2017-11-13 25,0000000000 6789773S NULL
2017-11-15 0,0000000000 6789098S NULL
2017-11-15 0,0000000000 6789103S NULL
2017-11-15 0,0000000000 6789110S NULL
Upvotes: 2
Raska
Reputation: 209
This will give outpt mentioned in your desired result :-
select mastr.* , case when mastr.deliverydate = del.deliverydate then (CONVERT(VARCHAR(4),LEFT(mastr.DELIVERYDATE,4)) + '' + CONVERT(VARCHAR(4),DATEPART( wk, mastr.DELIVERYDATE))) else null end as 'WeekOfYear' from
[dbo].[CurrentOrder] mastr
left join
(Select distinct deliverydate from
(select deliverydate, rank() over(partition by deliverydate order by orderid) as cnt
from [dbo].[CurrentOrder] where [CANCELLED] = 0) ranked
Where cnt > 3) del
on mastr.deliverydate = del.deliverydate
Upvotes: 0
David Rushton
Reputation: 5040
This approach uses a common table expression (CTE) to find each delivery date with 4 or more records. Using the delivery date it calculates the week of year. We can then use an outer join to append the new field to every matching record in the original table.
WITH DWeek AS
(
-- Calculate week of year for every delivery date with 4 or more records.
SELECT
DELIVERYDATE,
(CONVERT(VARCHAR(4),LEFT(DELIVERYDATE,4)) + '' + CONVERT(VARCHAR(4),DATEPART( wk, DELIVERYDATE))) AS WEEKOfYear
FROM
CurrentOrder
WHERE
DELIVERYDATE IS NOT NULL
AND CANCELLED = 0
AND DELIVERYDATE >= '20171114'
AND DELIVERYDATE <= '20171116'
GROUP BY
DELIVERYDATE
HAVING
COUNT(1) >= 4
)
SELECT
co.*,
s.WEEKOfYear
FROM
CurrentOrder AS co
LEFT OUTER JOIN DWeek AS s ON s.DELIVERYDATE = co.DELIVERYDATE
;
Returns
OrderId ORDER_TRACK_NUMBER TOTALAMOUNT READYDATE CANCELLED DELIVERYDATE WEEKOfYear
1 6784230 11.2400000000 2017-11-16 0 2017-11-16 201746
2 6788903 0.0000000000 2017-11-16 0 2017-11-16 201746
3 6789075S 91.8500000000 2017-11-16 0 2017-11-16 201746
4 6789080S 640.0000000000 2017-11-16 0 2017-11-16 201746
5 6789213S 0.0000000000 2017-11-16 0 2017-11-16 201746
6 6789098S 0.0000000000 2017-11-15 0 2017-11-15 NULL
7 6789103S 0.0000000000 2017-11-15 0 2017-11-15 NULL
8 6789110S 0.0000000000 2017-11-15 0 2017-11-15 NULL
9 6789751S 250.0000000000 2017-11-13 0 2017-11-13 NULL
10 6789756S 250.0000000000 2017-11-13 0 2017-11-13 NULL
11 6789773S 25.0000000000 2017-11-13 0 2017-11-13 NULL
12 6789779S 242.0000000000 2017-11-14 0 2017-11-14 201746
13 6789816S 23.8800000000 2017-11-14 0 2017-11-14 201746
14 6789833S 190.0000000000 2017-11-14 0 2017-11-14 201746
15 6789845S 215.0000000000 2017-11-14 0 2017-11-14 201746
EDIT
My original answer missed a number of important details from the OP. This is a totally reworked answer.
Upvotes: 1
Related Questions
- TSQL Calculate week number of the month
- Cumulative count over weeks in SQL
- T-sql count number of times a week on rows with date interval
- Find Sequential week counts
- How to sum up values in a column for each week number?
- How can I get count of rows for every week?
- SQL Query to get Totals By Week
- Calculating a running count of Weeks
- getting the current cumulative COUNT() for each week
- How to show total weekly count in one table