Click here to Skip to main content
15,887,027 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
i am facing sql error in query ,that i want item qty wise sum but my query is generating error kindly guide

thanks

What I have tried:

SELECT DISTINCT EntryDate INTO #Date11
FROM Probale
--WHERE     (EntryDate BETWEEN @StartDate AND @Enddate)
ORDER BY EntryDate

DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, EntryDate), 112)
				+ ']','[' + CONVERT(varchar,DATEPART(DAY, EntryDate), 112) + ']')
FROM    #Date
ORDER BY EntryDate


-- Building the query with dynamic dates
--DECLARE @qry NVARCHAR(4000)
--SET @qry =
--'SELECT * FROM
--(SELECT EmpID, Status , ReportingDate
--FROM EmployeesAttendance)emp
--PIVOT (MAX(Status) FOR ReportingDate IN (' + @cols + ')) AS stat'

DECLARE  @qry NVARCHAR(4000) =
N'SELECT * 
FROM (SELECT ItemMasterFile.Codeitem,ItemMasterFile.Descriptionitem,Probale.prdqty, 
DATEPART(DAY, Probale.EntryDate)as DDate
FROM Probale  Inner Join ItemMasterFile on Probale.Codeitem=ItemMasterFile.Codeitem )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat 
'
-- Executing the query

EXEC(@qry)
Posted
Updated 16-May-19 19:20pm
Comments
Bryian Tan 16-May-19 22:22pm    
Have you try to print out @cols and see what in it?
akhter86 16-May-19 23:15pm    
how to print out
Bryian Tan 16-May-19 23:57pm    
PRINT @cols
akhter86 17-May-19 0:54am    
i just run this query on sql not print col

1 solution

You need to start by looking at what you have in #Date - your code fills #Date11:
SQL
SELECT DISTINCT EntryDate INTO #Date11
FROM Probale
--WHERE     (EntryDate BETWEEN @StartDate AND @Enddate)
ORDER BY EntryDate
But your COALESCE command uses #Date which you don't show:
SQL
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, EntryDate), 112)
				+ ']','[' + CONVERT(varchar,DATEPART(DAY, EntryDate), 112) + ']')
FROM    #Date
ORDER BY EntryDate
If you meant #Date11 in that, that would explain why Cols is empty.
 
Share this answer
 
Comments
akhter86 17-May-19 4:04am    
OriginalGriff i did #Date but result is not coming
OriginalGriff 17-May-19 4:10am    
And did you try #Date11?
Quote: "If you meant #Date11 in that, that would explain why Cols is empty."
akhter86 17-May-19 5:08am    
i didn't get what you saying
OriginalGriff 17-May-19 5:47am    
What data did you expect to use in your COALESCE command? What did you expect it to generate?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900