[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 149

# Query dates but as colums

Good afternoon,
Ive been asked to generate a report for a month which is easy and works:-
``````SELECT [Fault Logs: Notes].[Written By], [Fault Logs].Branch, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time]
FROM [Fault Logs] INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].[FaultLogNo]
WHERE ((([Fault Logs: Notes].[Date and time])>=#1/1/2011# And ([Fault Logs: Notes].[Date and time])<=#1/31/2011#))
GROUP BY [Fault Logs: Notes].[Written By], [Fault Logs].Branch;
``````

However here comes the hard bit, what they want is instead of the SumOfFix Time being the entire month they want 2 colums, 1 being 1-19th of the month and the other being 20-end of month.

The issue I have is I have no idea how to get a summed data split between two ranges. Any ideas?

0
tonelm54
1 Solution

Commented:
Select X.[Written By], X.Branch, X.SumOfFix Time_1 ,Y.SumOfFix Time_2 From
(
SELECT [Fault Logs: Notes].[Written By], [Fault Logs].Branch, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time_1]
FROM [Fault Logs] INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].[FaultLogNo]
WHERE ((([Fault Logs: Notes].[Date and time])>=#1/1/2011# And ([Fault Logs: Notes].[Date and time])<=#1/19/2011#))
GROUP BY [Fault Logs: Notes].[Written By], [Fault Logs].Branch ) X
Inner Join
(
SELECT [Fault Logs: Notes].[Written By], [Fault Logs].Branch, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time_2]
FROM [Fault Logs] INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].[FaultLogNo]
WHERE ((([Fault Logs: Notes].[Date and time])>=#1/20/2011# And ([Fault Logs: Notes].[Date and time])<=#1/31/2011#))
GROUP BY [Fault Logs: Notes].[Written By], [Fault Logs].Branch) Y
on X.[Fault Logs: Notes].[Written By] = Y.[Fault Logs: Notes].[Written By] and
X.[Fault Logs].Branch = Y.[Fault Logs].Branch
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.