how to get current month and current day of data using WITH clause in sqlserver

See more:

SQL-Server

Hi,

i have a query that returns last 7 days of data from the current date but i want to get current month data and current day of data when user enter date from front end application. here i am giving query that returns last 7 days of data .

ALTER PROCEDURE [dbo].[New_QueryTotalShipOrders_WEEKITEM]  
@DateFrom  NVARCHAR(50)
AS
DECLARE @CurrentDate NVARCHAR(10)

BEGIN
SET @CurrentDate= @DateFrom--'2012-07-27'
SET @CurrentDate=(SELECT REPLACE(@CurrentDate,'-','/'));

WITH DateList AS
(
SELECT  DATEADD(dd, 0, CONVERT(DATETIME, @CurrentDate)) AS CreateDateTime, 1 AS Cnter
UNION ALL
SELECT  DATEADD(dd, -1, CreateDateTime), DateList.Cnter + 1
FROM DateList
WHERE DateList.Cnter  < 7
)
SELECT  DateList.CreateDateTime AS ShipDay, COALESCE(Temp.TotalCount, 0) AS TotalCount, 'Consolidate' as ConsolidateCount
FROM DateList
LEFT JOIN  (
SELECT  COUNT(Id) TotalCount
,CONVERT(VARCHAR(10), CreatedDateTime, 112) AS CreateDateTime
FROM    ShipmentDetail sd
Inner JOin(select Distinct shipmentId,Sku From items) as i on i.ShipmentID=sd.Id
WHERE   CreatedDateTime >= DATEADD(dd, -6, @CurrentDate)
AND CreatedDateTime < DATEADD(dd, 1, @CurrentDate) 
and sd.Id IN (SELECT i1.ShipmentID

 FROM Items i1 (NOLOCK)
 WHERE i1.SubOrderId = sd.SubOrderId  and i.Sku in('2x3SGC','3X5SNC','4x5SGC','ADDRLBL','STICKER01'))

GROUP BY CONVERT(VARCHAR(10), CreatedDateTime, 112)
) Temp
ON CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.CreateDateTime

union all

SELECT  DateList.CreateDateTime AS ShipDay, COALESCE(Temp.TotalCount, 0) AS TotalCount,'NonConsolidate' as NonConsolidateCount
FROM DateList
LEFT JOIN  (
SELECT  COUNT(Id) TotalCount
,CONVERT(VARCHAR(10), CreatedDateTime, 112) AS CreateDateTime
FROM    ShipmentDetail sd
Inner JOin(select Distinct shipmentId,Sku From items) as i on i.ShipmentID=sd.Id
WHERE   CreatedDateTime >= DATEADD(dd, -6, @CurrentDate)
AND CreatedDateTime < DATEADD(dd, 1, @CurrentDate)
and sd.Id IN (SELECT i1.ShipmentID

 FROM Items i1 (NOLOCK)
 WHERE i1.SubOrderId = sd.SubOrderId  and i.Sku in('NOTEBOOK01','NOTEBOOK02','NOTEPAD01','5X7SNP','CALENDAR01','CALENDAR02'))

GROUP BY CONVERT(VARCHAR(10), CreatedDateTime, 112)
) Temp
ON CONVERT(VARCHAR(10), DateList.CreateDateTime, 112) = Temp.CreateDateTime

END