March 20, 2018 at 8:25 am
Guys, is there a way to add a week number column to the following, so essentially i have a new column with the week number in in based on the 'created' column.
DECLARE @sd DATETIME;
DECLARE @ed DATETIME;
-- set the start date to the first day of this month
SET @sd = DATEADD(WEEK, -8, GETDATE())
--code for inbound by dealers
SELECT logfile.Dealer,Dealers.Name,Agents.Name,logfile.created, LOGFILE.Tran1
,COUNT(*) AS Booked
, SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END) AS SER
,COUNT(DISTINCT RegNo) AS Vehs
,COUNT(*)
-SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS Other
, SUM(TotalValue) AS [Value]
FROM LogFile
LEFT OUTER JOIN RepairCodes
ON RepairCode = ServCode
AND LogFile.Dealer = RepairCodes.Dealer
JOIN Dealers ON Dealers.Dealer=LogFile.Dealer
JOIN Agents ON Agents.OpNum=LogFile.OpNum
AND dbo.LogFile.Created >= @sd
--AND dbo.LogFile.Created < @ed
AND DBO.LogFile.Tran1 in ( 'oBB', 'IBB', 'W3B')
AND logfile.dealer in ('BA','BAA', 'NSK', 'CA', 'BVW', 'MVW', 'MCH', 'BF', 'CF', 'NF', 'CHF', 'CVW')
GROUP BY LogFile.Dealer, Dealers.Name, AGENTS.nAME,logfile.created, LOGFILE.Tran1
order by Dealers.Name
March 20, 2018 at 10:22 am
You'll want to use the DATEPART function which is hugely valuable in sql. Syntax would be "select datepart(wk,ColumnName)...". Below is a link to the Microsoft reference for that function which allows you to break out various elements of datetime fields ( e.g. months, day names etc.)
https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql
March 20, 2018 at 11:18 am
Getting the week number from a date is trivial. Just plopping it on the Select statement when there is a GROUP clause, not so much.
You need to look at your groupings and determine if there is a common date that the week can be derived from. If so, then you can simply add the DatePart to the GROUP clause and to the Select statement. If you have something more involved, DDL, input data and expected results would be very helpful.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply